SOLVED SQL Query for Installed Windows update compliance

[vin418]

HI All,

is there any SQL query for fetching the Windows update compliance report for all systems.

 

[Gokul]

Are you looking for deployment compliance ?

 

[vin418]

Hi Gokul,

I need query for Deployment Compliance and also Im looking for a seperate query through which i can pull up for installed updates also.

 

[Gokul]

Hi ,
Attaching query and i have commented some of the line . You can modify according to your convince. You have to mention Deployment ID. If more deployment are present, you can replace the below last line with

 where a.AssignmentID in('DeplomentID1','DeplomentID1')

select
vrs.name0 as machineName,
--vrs.AD_site_Name0 as ADSiteName,
--vrs.User_Name0 as UserName,
--a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
sn.StateName as LastEnforcementState
--,
--assc.StateTime asStatusTime,
--assc.LastErrorCode as LastErrorCode
from v_CIAssignment a
join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
join v_StateNames sn on assc.Statetype = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_R_System vrs on vrs.ResourceID=assc.ResourceID
where a.AssignmentID = 'Mention Your DeploymentID'

 

[Garth]

HI All,

is there any SQL query for fetching the Windows update compliance report for all systems.

What is wrong with the built-in reports for this?

 

[Prajwal Desai]

Exactly.. There are built-in reports for this. Why not try those reports first..

 

[vin418]

What is wrong with the built-in reports for this?

Hi Grath,

I'm confused with which one is the correct report for the update compliance that i need to run to get it.

 

[vin418]

Exactly.. There are built-in reports for this. Why not try those reports first..

Hi Prajwal,

I'm aware about the default reports on this, but a little bit confused regarding the name.

 

[Garth]

Hi Grath,

I'm confused with which one is the correct report for the update compliance that i need to run to get it.

Here is a list of all of the built-in report and a short description of what each of them returns.

List of reports - Configuration Manager

Review a list of reports that are supplied with Configuration Manager. The reports appear in various categories.

docs.microsoft.com

After reviewing the list just run them to see if they give you what you want.

 

[vin418]

Hi ,
Attaching query and i have commented some of the line . You can modify according to your convince. You have to mention Deployment ID. If more deployment are present, you can replace the below last line with

 where a.AssignmentID in('DeplomentID1','DeplomentID1')

select
vrs.name0 as machineName,
--vrs.AD_site_Name0 as ADSiteName,
--vrs.User_Name0 as UserName,
--a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
sn.StateName as LastEnforcementState
--,
--assc.StateTime asStatusTime,
--assc.LastErrorCode as LastErrorCode
from v_CIAssignment a
join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
join v_StateNames sn on assc.Statetype = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_R_System vrs on vrs.ResourceID=assc.ResourceID
where a.AssignmentID = 'Mention Your DeploymentID'

Hi Gokul,

Thanks for the query and guidance.
It worked correctly.

Regards

Vineeth