NEW How to show software name and version

[Gustavo]

Hi there,

I am using Garth's query to query for a specific software installed for both x86 and x64. I would like to show as new columns software name and version. I have added these 2 fields in the general tab but I don't get the results that I am looking for. How could I show these fields in every register returned?

This is the query:
select SMS_R_System.ResourceId, SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.IPAddresses, SMS_R_System.LastLogonUserName, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_R_System.Client 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_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ResourceId in (select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID

from SMS_G_System_ADD_REMOVE_PROGRAMS where

SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%vlc%") or SMS_R_System.ResourceId in (select SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID from

SMS_G_System_ADD_REMOVE_PROGRAMS_64 where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%vlc%")

Thanks in advance
Gustavo

 

[Garth]

That query is for a collection, so you should add more too it. Why do you want to do that?

 

[Gustavo]

That query is for a collection, so you should add more too it. Why do you want to do that?

Hi Garth,

First of all i have to say that we have recently migrated to SCCM. So why? Because I have an excel historical file for Microsoft products licenses (Office,Visio,Access...) and we have quite different versions installed. Every month I got a report for each product and I used pivot tablet to know how many computers have some MS product installed, so all this info is stored in the historical file every month .
I have been taking a look at reports, but I cannot customize them, I mean, I have a report to get a specific software installed on computers, but I have to select the software from a list. So I would have to make a report for every different version. ( I guess that it is possible to make it with Report Builder but It is a bit difficult for me at this moment)

I saw your query in the forums and I thought that I could accomplish it making some small changes, but I have not been able to find the way to include the software name and version, I am not sure how I have to join the tables to get these data.

Would it be better to use SQL to obtain what I need?

Thanks
BR

 

[Garth]

Yes you should use SQL for this. WQL is not meant for reporting. Heck you can create report with pivot already done if you like. It will just take a bit of work.

This book can help you with writing the report. https://www.informit.com/store/system-center-configuration-manager-reporting-unleashed-9780134466811

 

[Gustavo]

Yes you should use SQL for this. WQL is not meant for reporting. Heck you can create report with pivot already done if you like. It will just take a bit of work.

This book can help you with writing the report. https://www.informit.com/store/system-center-configuration-manager-reporting-unleashed-9780134466811

Ok Garth, understood! I will try to use SQL, that book looks good, it could be a good startint point. I'm not sure what you mean with create report with pivot, I have made some test from excel connecting to the SCCM database and querying with SQL and it might be helpful I guess, but I will have to study a bit more SQL

Many thanks for your help and advice.
BR