4

I'm trying my hand at building a custom SCCM report and could use some help from those who have more experience with the database structure than I do.

What I would like is to list:

  • Device Collection
    • Device
      • Software Update Group

The idea is to be able to run a report based on a Device Collection then have that report show, for each device in that collection, the compliance status of each Software Update Group deployed to that device.

If there is already a report that I have not found let me know but otherwise if you could help me out with which tables/views I would have to join to get this information it would be much appreciated.

Right now I am going in the console to Monitoring, Deployments, then for each Deployment and checking to see which machines are Compliant etc. This is kind of the reverse of what I am looking for and is more time consuming especially when I am looking for if a specific machine is fully updated or not.

Chuck Herrington
  • 517
  • 2
  • 7
  • 17

1 Answers1

0

I managed to figure out the query. It's a little rough but here it is for anyone else trying to do the same thing:

SELECT d.CollectionID, d.CollectionName, m.Name, m.ResourceID, a.AssignmentName, a.AssignmentID, s.TopicType, s.StateID, n.StateDescription, s.StateTime
FROM v_DeploymentSummary d
inner join vCI_CIAssignments a on d.AssignmentID = a.AssignmentID
inner join v_ClientCollectionMembers m on m.CollectionID = d.CollectionID
left join (select * from v_AssignmentStatePerTopic where StateID = 1) s on a.AssignmentID = s.AssignmentID and m.ResourceID = s.ResourceID 
left join v_StateNames n on s.TopicType = n.TopicType and s.StateID = n.StateID
where d.CollectionName like '%windows server 2008 r2 - ga'
order by m.name, a.AssignmentName, d.CollectionName,s.AssignmentID

I found the article SQL Server Views in System Center 2012 Configuration Manager which helped but still had to do a bunch of digging and trial and error.

Chuck Herrington
  • 517
  • 2
  • 7
  • 17