Let’s see the easiest method to create SQL queries for SCCM without opening the SQL management studio. I think this would be the easiest way to get a good number of complex SQL queries to create SCCM custom reports.
You can create SCCM custom reports using the report builder. You can also use PowerBI to build useful reports using SCCM data. But finding out the correct SQL query for each custom report is not a very easy process in my experience.
When you get a sample SQL query, it’s easy to build custom reports using those queries. There are many tips and tricks to build custom reports for SCCM. One of them is using the default SCCM reports. You have many default reports available in Configuration Manager, and it comes as ready to use.
- Convert WQL Queries To SQL Queries Using SCCM Trick
- SCCM Report for DotNet Framework Versions
- Create SCCM Report for Visual Studio
- SCCM Dynamic Report Link to Get Specific Application Details
- ConfigMgr Reports Application Package Distribution
NOTE! – This post is only for educational purposes. Make sure you are using the supported method to build SQL queries as mentioned by Garth in the comments 🙂
Easiest Method to Create SQL Query for SCCM
As I mentioned above, this is the easiest method to create a very complex SCCM SQL query. You don’t have to spend hours and hours in SQL management studio to build these types of complex queries. You will have to get these queries smartly. This is what we are going to see today from this post.
You can check the following sections to get more details about this method of finding out SQL queries for SCCM without opening SQL management studio. But, before getting to the process of getting queries, let me share some of the complex queries I found using this method.
I don’t think I will ever make an SCCM custom report or SQL query with my SQL skills. You don’t need any SQL skills to get these complex SQL queries related to SCCM.
SQL Query to Find Out List of Features with SCCM Version
The following SQL query helps find out the list of features available in the following location in the SCCM console \Administration\Overview\Updates and Servicing\Features. You can get the following query without searching for all the SQL query syntax etc.
These types of SQL queries for SCCM are already available in SCCM, and it’s ready to use. Also, these queries can be customized using report builder or SQL management studio to create custom reports in SCCM.
You can use the following SQL query to find out the list of SCCM update features available with each CB version release. You can get these details without using any constant value in the SQL query. So, this query can be used in all environments.
- The Feature Type = 0 means this feature is in prerelease.
- The Feature Type = 1 means that this feature is in production.
The feature called fn_LatestInstalledPackageFeatures(1033) is used in SQL query to retrieve these details from the SQL database. I have seen similar functions are being used in many default SCCM reports. I’m very interested in seeing some SCCM CB hidden features by using the following value called SMS_CM_UpdateFeatures Exposed.
select all SMS_CM_UpdateFeatures.DateReleased,SMS_CM_UpdateFeatures.Description,SMS_CM_UpdateFeatures.Exposed,
SMS_CM_UpdateFeatures.FeatureGuid,SMS_CM_UpdateFeatures.FeatureType,SMS_CM_UpdateFeatures.LocaleID,
SMS_CM_UpdateFeatures.MoreInfoLink,SMS_CM_UpdateFeatures.Name,SMS_CM_UpdateFeatures.Status
from fn_LatestInstalledPackageFeatures(1033) AS SMS_CM_UpdateFeatures
SQL Query to Find Out Deployment Status of an SCCM App
The following SQL query gives you the application deployment status data. You can find out the details of an application installation using this query. This query is already available in SCCM, and you need to check and find out the details.
I have not even opened SQL management studio to get the query. The following query uses some constant values to get the data from SCCM. You can easily change the query statement to get deployment status for all the applications in SCCM. However, I don’t think you should try this production environment because that could impact the SQL DB and SCCM performance.
The following SQL query to find out the deployment status of an application is not using SQL tables or views directly; instead, it uses a SQL function called fn_AppDeploymentAssetDetails(1033). I have seen these types of functions are in use for SCCM default reports.
select all SMS_AppDeploymentAssetDetails.AppCI,SMS_AppDeploymentAssetDetails.AppName,SMS_AppDeploymentAssetDetails.AppStatusType,
SMS_AppDeploymentAssetDetails.AssignmentID,SMS_AppDeploymentAssetDetails.AssignmentUniqueID,SMS_AppDeploymentAssetDetails.CollectionID,
SMS_AppDeploymentAssetDetails.CollectionName,SMS_AppDeploymentAssetDetails.ComplianceState,SMS_AppDeploymentAssetDetails.DeploymentIntent,
SMS_AppDeploymentAssetDetails.DTCI,SMS_AppDeploymentAssetDetails.DTModelID,SMS_AppDeploymentAssetDetails.DTName,SMS_AppDeploymentAssetDetails.
DTResultID,SMS_AppDeploymentAssetDetails.EnforcementState,SMS_AppDeploymentAssetDetails.ExtendedInfoDescriptionID,SMS_AppDeploymentAssetDetails.
ExtendedInfoID,SMS_AppDeploymentAssetDetails.InstalledState,SMS_AppDeploymentAssetDetails.IsMachineAssignedToUser,SMS_AppDeploymentAssetDetails.
IsMachineChangesPersisted,SMS_AppDeploymentAssetDetails.IsVM,SMS_AppDeploymentAssetDetails.MachineID,SMS_AppDeploymentAssetDetails.MachineName,
SMS_AppDeploymentAssetDetails.PolicyModelID,SMS_AppDeploymentAssetDetails.Revision,SMS_AppDeploymentAssetDetails.StartTime,
SMS_AppDeploymentAssetDetails.StatusType,SMS_AppDeploymentAssetDetails.Technology,SMS_AppDeploymentAssetDetails.UpdateState,SMS_AppDeploymentAssetDetails.
UserName,SMS_AppDeploymentAssetDetails.VMHostName from fn_AppDeploymentAssetDetails(1033) AS SMS_AppDeploymentAssetDetails
where (((((SMS_AppDeploymentAssetDetails.AssignmentID = N'16777242' AND SMS_AppDeploymentAssetDetails.PolicyModelID = N'16777992')
AND SMS_AppDeploymentAssetDetails.StatusType = N'1') AND SMS_AppDeploymentAssetDetails.AppStatusType = SMS_AppDeploymentAssetDetails.StatusType)
AND SMS_AppDeploymentAssetDetails.DTCI = N'16777991') AND SMS_AppDeploymentAssetDetails.EnforcementState = N'1001')
SQL Query to get Content Distribution Status of an App
You can get the content distribution status of an app from the following SQL query for SCCM. Similar to the previous query, I have not done any research to create this query to find out the content distribution status. It was very easy to find out the query using the following trick.
I have not even opened SQL management studio to get the below query to find the content distribution status. The following query uses some constant values like Package ID. You can easily change the query statement to get the content distribution status for all the applications in SCCM.
You can find the Package IDs for SCCM Applications using the method explained in the article. The package ID can be used in the following SQL query.
The following SQL query to find out the content distribution status of an application is not using SQL tables or views directly; instead, it uses a SQL function called fn_ListObjectContentExtraInfo(1033). I have seen these types of functions are in use for SCCM default reports.
You can use the same query for any of the applications from your SCCM environment. This SQL query for SCCM content distribution status provides Source Size, Source Version, Status of the content distribution to DPs, etc.
select all SMS_ObjectContentExtraInfo.DateCreated,SMS_ObjectContentExtraInfo.Description,SMS_ObjectContentExtraInfo.FeatureType,
SMS_ObjectContentExtraInfo.LastUpdateDate,SMS_ObjectContentExtraInfo.NumberErrors,SMS_ObjectContentExtraInfo.NumberInProgress,
SMS_ObjectContentExtraInfo.NumberInstalled,SMS_ObjectContentExtraInfo.NumberUnknown,SMS_ObjectContentExtraInfo.ObjectID,
SMS_ObjectContentExtraInfo.ObjectType,SMS_ObjectContentExtraInfo.ObjectTypeID,SMS_ObjectContentExtraInfo.PackageID,SMS_ObjectContentExtraInfo.SoftwareName,
SMS_ObjectContentExtraInfo.SourceSite,SMS_ObjectContentExtraInfo.SourceSize,SMS_ObjectContentExtraInfo.SourceVersion,SMS_ObjectContentExtraInfo.Targeted
from fn_ListObjectContentExtraInfo(1033) AS SMS_ObjectContentExtraInfo where SMS_ObjectContentExtraInfo.PackageID = N'MEM00014'
Copy SQL Query from SCCM Log File – SMSProv.log
As I mentioned above, these SQL queries for SCCM are already available in ConfigMgr itself. You must know the correct place to look. In this scenario, you will need to open SMSProv.log from the site server or SMS provider server to find the relevant SQL queries for SCCM.
You can use SMSProv.log to find the WQL queries as well. The WQL queries help to create dynamic collections. You can refer to the post easiest Method To Create SCCM WQL Queries For Collection to get more details about WQL queries.
You will need to follow the steps mentioned below to find appropriate SQL queries to create SCCM custom reports. For example – If you want to find out the SQL query related to Content Distribution, then you can navigate to:
- \Monitoring\Overview\Distribution Status\Content Status
- Select any of the application that you want to know the staus.
- Note down the time when you clicked on that application from console.
- Open SMSProv.log and search with the keyword “SQL” as shown in the below screenshot.
- Copy the SQL query and try the query in SQL managment studio to check whether this is useful or not.
Using the provider “trick” will produce unsupported queries and should not be used.
Thank you for the clarification:) But this post can be used for educational purposes!
Hello Anoop,
Can you please help me with the SCCM query to pull a report of device with build installed date on specific collection.
Regards,
Pavan