SOLVED SQL Report Help office versions

[vin418]

Hi Team,

Need your support in how i can fetch SQL report with below columns Using SQL in SCCM from particular OU in AD.

1. System Name
2. Serial Number
3. Model
4. OS Name
5. MS Office Version

 

[Garth]

What do you have for a query now? what and what isn't working with it?

 

[vin418]

Hi Garth,

I tried to fetch the report using WMI Query, but the report was in-complete and giving only partial information. So i need help to get a correct SQL Query which will give me all Output without any miss. When i run the Query i get Output of 1000 Machines only from 2000 machines, when i add office version it gives me only less number of Machine name ater removing duplicate. There are some machines which does not have HW Inventory information but those machine are not showing in the list as blank also.
I will upload my WMI query which i used:

select SMS_R_System.Name, SMS_G_System_SYSTEM_ENCLOSURE.SerialNumber, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_G_System_COMPUTER_SYSTEM.UserName, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_G_System_OPERATING_SYSTEM.Version, SMS_G_System_OFFICE_PRODUCTINFO.ProductVersion from SMS_R_System 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 inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OFFICE_PRODUCTINFO on SMS_G_System_OFFICE_PRODUCTINFO.ResourceID = SMS_R_System.ResourceId where SMS_R_System.SystemOUName = "Specific OU" and SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "%Microsoft Office 2013%" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "%Microsoft Office 2013%" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office 365 ProPlus%" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Enterprise 2007" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office O MUI (English) 2013" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office O MUI (English) 2016" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office OSM UX MUI (English) 2016" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Professional 2007" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Professional Hybrid 200" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Professional Plus 2007" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Professional Plus 2010" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Professional Plus 2013" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Professional Plus 2016" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Professional Plus Subscription 2010" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Single Image 2010" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Standard 2007" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Standard 2010" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Standard 2013" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Standard 2016" or SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office X MUI (English) 2010"

 

[Garth]

First off You can't write a report using WQL.
Secondly, What exactly do you mean by it is in-complete?
Next you need to use brackets within your query as the OU part will only apply to the one version of office.
Next you will never see NULL details when you are filter out computers by particular versions

 

[vin418]

Hi Garth,

thanks for your assistance.
Is there any SQL query which i can use to fetch the null values also.

 

[Garth]

Change your ARP join to a Left outer join.

 

[vin418]

Thanks Grath. It worked.