Hello, I am working on creating a patching dashboard and I'm looking for guidance on retrieving SSCM (System Center Configuration Manager) patch update details/reports using SQL queries in PowerShell. I would appreciate it if someone could provide me with a step-by-step procedure or any relevant documentation to help me achieve this. Specifically, I would like to know: How can I connect to the SSCM database using PowerShell? What SQL queries can I use to retrieve patch update details, such as the list of installed patches, their status, and any relevant metadata? Are there any specific tables or views in the SSCM database that store this information? Are there any considerations or permissions required to access the SSCM database using PowerShell? Any assistance or guidance would be highly appreciated. Thank you in advance!

This blog will help you get started. https://askgarth.com/blog/how-to-use-powershell-to-query-sql-database/ Here is a list of supported Sql views. https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/sql-server-views-configuration-manager

Retrieving SSCM Patch Update Details/Report using SQL Queries in PowerShell

Swahela Mulla 95

Hello,

I am working on creating a patching dashboard and I'm looking for guidance on retrieving SSCM (System Center Configuration Manager) patch update details/reports using SQL queries in PowerShell. I would appreciate it if someone could provide me with a step-by-step procedure or any relevant documentation to help me achieve this.

Specifically, I would like to know:

How can I connect to the SSCM database using PowerShell?
What SQL queries can I use to retrieve patch update details, such as the list of installed patches, their status, and any relevant metadata?
Are there any specific tables or views in the SSCM database that store this information?
Are there any considerations or permissions required to access the SSCM database using PowerShell?

Any assistance or guidance would be highly appreciated. Thank you in advance!

1 answer

Garth 5,801 Reputation points

2023-07-11T10:30:09.33+00:00

This blog will help you get started. https://askgarth.com/blog/how-to-use-powershell-to-query-sql-database/

Here is a list of supported Sql views. https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/sql-server-views-configuration-manager
Please sign in to rate this answer.
AllenLiu-MSFT 47,241 Reputation points Microsoft Vendor

2023-07-12T06:23:36.53+00:00

Hi, @Swahela Mulla

Thank you for posting in Microsoft Q&A forum.

Here are the views in the SSCM database that store this information of updates:

https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/software-updates-views-configuration-manager

And here is the sample queries for software updates:

https://github.com/MicrosoftDocs/memdocs/blob/main/memdocs/configmgr/develop/core/understand/sqlviews/sample-queries-software-updates-configuration-manager.md

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".

Swahela Mulla 95 Reputation points

2023-07-14T10:07:25.4+00:00

Hello @AllenLiu-MSFT and Garth

Are there any specific tables available in the SSCM database that store software update information?

Swahela Mulla 95 Reputation points

2023-07-14T10:12:40.93+00:00

Hello @AllenLiu-MSFT & Garth

Are there any specific tables available in the SSCM database that store software update information?

Garth 5,801 Reputation points

2023-07-14T11:03:04.1933333+00:00

Hey @swadeshatlg swadeshatlg It is not supported to query the tables. All the support views are listed with the docs site I have already linked to. Have you review them?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Retrieving SSCM Patch Update Details/Report using SQL Queries in PowerShell

1 answer

Your answer