From Sccm WQL Query to SQL

Hi Folks, Today we will check how to run sccm query on sql. First of all we need a create a query on sccm console, ıf you have already created, you can also use same wql query to get its sql query on logs. Let's start with query about Windows 10 machines;

Select distinct SMS_R_System.NetbiosName, SMS_R_System.LastLogonUserName, SMS_R_System.IPAddresses, SMS_R_System.ADSiteName, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, SMS_G_System_COMPUTER_SYSTEM.Model from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where DATEPART(DY, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime) != DATEPART(DY, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan)

Now you must make an SQL query from this one to create a report with the list of computers and users. It is quite complicated to convert this query into SQL.The fact is that to execute queries the ConfigMgr engine translates WQL queries into t-SQL syntax using WMI provider and executes an SQL query against SCCM database. All these operations are recorded to the \ConfigMgr\Logs\smsprov.log file.

And Then we all need is done. We get the Sql query.