Querying Software Update Data in Configuration Manager Using SQL

Swahela Mulla 95 Reputation points
2023-10-30T14:34:57.2133333+00:00

Hello Everyone,

I am working with Microsoft Configuration Manager (SCCM), and I'm interested in extracting detailed data about patch deployment. Specifically, I want to collect data related to all five categories of Software Updates:

  1. Software Updates – A Compliance
  2. Software Updates – B Deployment Management
  3. Software Updates – C Deployment States
  4. Software Updates – D Scan
  5. Software Updates – E Troubleshooting

My goal is to use SQL queries to retrieve this information from SCCM's database.

Have any of you created SQL queries to retrieve data from these categories? If so, I would greatly appreciate it if you could share your SQL queries or point me in the direction of any Microsoft documentation or resources related to this task.

I am particularly interested in understanding patch compliance and deployment status for all of these categories.
I know about Software updates views in Configuration Manager.

Thank you in advance for your assistance and guidance.

Best regards,

Swahela Mulla

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,998 questions
Microsoft Configuration Manager Updates
Microsoft Configuration Manager Updates
Microsoft Configuration Manager: An integrated solution for for managing large groups of personal computers and servers.Updates: Broadly released fixes addressing specific issue(s) or related bug(s). Updates may also include new or modified features (i.e. changing default behavior).
1,058 questions
Microsoft Configuration Manager
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,596 questions
{count} votes

2 answers

Sort by: Most helpful
  1. LiHongMSFT-4306 28,041 Reputation points
    2023-10-31T08:22:38.4766667+00:00

    Hi @Swahela Mulla

    Try this:

    SELECT v_UpdateInfo.ArticleID, v_UpdateInfo.BulletinID, v_UpdateInfo.Title, 
        v_StateNames.StateName, v_UpdateComplianceStatus.LastStatusCheckTime, 
        v_UpdateComplianceStatus.LastEnforcementMessageTime 
    FROM v_R_System INNER JOIN v_UpdateComplianceStatus ON 
        v_R_System.ResourceID = v_UpdateComplianceStatus.ResourceID INNER JOIN v_UpdateInfo ON 
        v_UpdateComplianceStatus.CI_ID = v_UpdateInfo.CI_ID INNER JOIN v_StateNames ON 
        v_UpdateComplianceStatus.LastEnforcementMessageID = v_StateNames.StateID 
    WHERE (v_StateNames.TopicType = 402) AND (v_R_System.Netbios_Name0 LIKE 'Computer1') 
    ORDER BY v_StateNames.StateName, v_UpdateInfo.DateLastModified
    

    Referring from this doc: Sample queries for software updates in Configuration Manager

    Best regards,

    Cosmog Hong

    0 comments No comments

  2. Garth Jones 1,356 Reputation points
    2023-10-31T14:06:57.5566667+00:00

    You can open the reports to see the SQL query within each report. You can also get a list of all supported Views from the Docs site. https://learn.microsoft.com/en-us/mem/configmgr/develop/core/understand/sqlviews/sql-server-views-configuration-manager

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.