SCCM check software from SQL query

Question

SCCM check software from SQL query

Eaven HUANG 2,191

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?

Accepted answer

1 additional answer

Your answer

Answer 1

AllenLiu-MSFT 49,316 Microsoft External Staff

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.

AllenLiu-MSFT 49,316 Reputation points Microsoft External Staff

2023-01-18T05:52:42.6666667+00:00

Just checking the current status of the problem? Is there any other assistance we can provide?
Eaven HUANG 2,191 Reputation points

2023-01-18T06:04:51.96+00:00

Thanks Allen. Is there a reference table or link where I can check what fields are available for querying? To be sure, this will extract the information no matter if the machine is on or offline , correct?
AllenLiu-MSFT 49,316 Reputation points Microsoft External Staff

2023-01-18T07:06:55.62+00:00

Hi, @Eaven HUANG

To be sure, this will extract the information no matter if the machine is on or offline, correct?

Correct. It's not the real-time query, the data is collected by hardware inventory.

A simple query, you will get all the fields are available for querying.
AllenLiu-MSFT 49,316 Reputation points Microsoft External Staff

2023-01-19T08:47:53.07+00:00

Is there any other assistance we can provide?
Eaven HUANG 2,191 Reputation points

2023-01-21T16:09:06.73+00:00

Dear @AllenLiu-MSFT

It failed with

Msg 208, Level 16, State 1, Line 1

Invalid object name 'v_Add_Remove_Programs'.
Eaven HUANG 2,191 Reputation points

2023-01-21T16:14:04.46+00:00

It also failed with

Msg 208, Level 16, State 1, Line 1

Invalid object name 'v_r_system'.

I'm very very new to SQL server, do I need to specify which database or table that I'm querying on?
Eaven HUANG 2,191 Reputation points

2023-01-21T16:17:12.64+00:00

I checked online that I need proper permissions to read the databases, what permissions do I need and how to grant the access?

Thanks a lot for your help.
Eaven HUANG 2,191 Reputation points

2023-01-21T16:29:06.8266667+00:00

All good Allen, it was the default database being used, I changed it from master to the CM database and it works through.

Answer 2

Garth Jones 1,366

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

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Share via

SCCM check software from SQL query

1 additional answer

Your answer