SCCM check software from SQL query

Question

Dear experts,

Can we run query in SCCM SQL server to check if a certain collection of devices has a specific software or not? For example, we need to check if the devices have been installed with Python, no matter if the devices are on or offline.

I knew that we can start CMPivot to check the information in realtime but most of the devices don't really stay online all the time.

Is it possible?

Answer 1

Hi, @Eaven HUANG

Thank you for posting in Microsoft Q&A forum.

Yes, it's easy to filter the device that has Python installed from a certain collection, please try the SQL query, remember to replace the collection ID with your own collection ID:

select distinct Name0,DisplayName0 from v_Add_Remove_Programs 
inner join v_r_system
on v_r_system.ResourceID=v_Add_Remove_Programs.ResourceID
inner join v_ClientCollectionMembers
on v_ClientCollectionMembers.ResourceID=v_r_system.ResourceID
where v_Add_Remove_Programs.DisplayName0 like '%Python%'
and v_ClientCollectionMembers.CollectionID='PRI00014'

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Yes there is already built in reports for this, take a look at them.