Thanks Stephen, I'm actually trying to get a graphical representation/dashboard of compliance for my updates deployment (SSRS/SQL n00b). I was trying to use this as a guide to get me started:
http://blogs.msdn.com/b/shitanshu/archive/2010/05/02/part-3-sql-queries-used-for-creating-custom-configuration-manager-dashboard-in-microsoft-it.aspx SELECT vc.Name,at.title, CollectionID='SMS00001', Status=sn.StateName, NumberOfComputers=count(*), PComputers=convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / isnull(nullif((SELECT count(*) FROM v_ClientCollectionMembers WHERE CollectionID='SMS00001') , 0), 1))) FROM v_ClientCollectionMembers cm JOIN v_Collection vc on vc.collectionID = cm.collectionid JOIN v_UpdateListStatus_Live cs on cs.CI_ID in (SELECT CI_ID FROM v_authlistinfo WHERE Title =@title) AND cs.ResourceID=cm.ResourceID LEFT JOIN v_StateNames sn on sn.TopicType=300 AND sn.StateID=isnull(cs.Status, 0) JOIN v_authlistinfo at on cs.CI_ID=at.CI_ID WHERE cm.CollectionID='SMS00001' GROUP BY sn.StateName,vc.Name, at.title ORDER BY count(*) desc Note: The query us I ended up with this (editing it a tiny bit): SELECT vc.Name,at.title, CollectionID='CAS000E9', Status=sn.StateName, NumberOfComputers=count(*), PComputers=convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / isnull(nullif((SELECT count(*) FROM v_ClientCollectionMembers WHERE CollectionID='CAS000E9') , 0), 1))) FROM v_ClientCollectionMembers cm JOIN v_Collection vc on vc.collectionID = cm.collectionid JOIN v_Update_DeploymentSummary_Live cs on cs.CI_ID in (SELECT CI_ID FROM v_authlistinfo WHERE Title = 'All Required Security Updates - September 2013') AND cm.ResourceID=cm.ResourceID LEFT JOIN v_StateNames sn on sn.TopicType=300 AND sn.StateID=isnull(cs.NumTotal, 0) JOIN v_authlistinfo at on cs.CI_ID=at.CI_ID WHERE cm.CollectionID='CAS000E9' GROUP BY sn.StateName,vc.Name, at.title ORDER BY count(*) desc But I am not getting any data returned when the query is ran. Any thoughts on that ? Anyone have any SSRS out there for reports / dashboard for CM12 ? From: [email protected] [mailto:[email protected]] On Behalf Of Stephen Leuthold Sent: Monday, September 23, 2013 10:54 AM To: [email protected] Subject: RE: [mssms] v_UpdateListStatus_Live I wrote one that you can filter on deployment name. You don't need to declare @UserSIDs in an SRS report, this is for SSMS. You should be able to tweak it to get the expected result. DECLARE @UserSIDs VARCHAR(100) = 'YOURSIDHERE' SELECT a.AssignmentName AS DeploymentName, m.Name0 AS ComputerName0, m.User_Domain0+'\'+m.User_Name0 AS LastLoggedOnUser, asite.SMS_Assigned_Sites0 AS AssignedSite, m.Client_Version0 AS ClientVersion, s.StateTime AS [Last State Received], s.StateType, sn.StateName AS Status, statusinfo.MessageName AS ErrorStatusName FROM fn_rbac_CIAssignment(@UserSIDs) a JOIN ( SELECT AssignmentID, ResourceID, StateType, StateID, StateTime, LastStatusMessageID FROM fn_rbac_AssignmentState_Combined(@UserSIDs) WHERE StateType in (300,301) ) s ON s.AssignmentID=a.AssignmentID --and s.StateType=a.TopicType LEFT JOIN fn_rbac_StateNames(@UserSIDs) sn on sn.TopicType=s.StateType and sn.StateID=isnull(s.StateID, 0) JOIN fn_rbac_R_System(@UserSIDs) m on m.ResourceType=5 and m.ResourceID=s.ResourceID and isnull(m.Obsolete0,0)=0 LEFT JOIN fn_rbac_RA_System_SMSAssignedSites(@UserSIDs) asite on m.ResourceID = asite.ResourceID LEFT JOIN fn_rbac_AdvertisementStatusInformation(@UserSIDs) statusinfo on statusinfo.MessageID=nullif(s.LastStatusMessageID&0x0000FFFF, 0) WHERE a.AssignmentName LIKE '%TEST%' -- Deployment Name ORDER BY a.AssignmentName, sn.StateName ________________________________ From: [email protected]<mailto:[email protected]> To: [email protected]<mailto:[email protected]> Subject: [mssms] v_UpdateListStatus_Live Date: Mon, 23 Sep 2013 14:30:58 +0000 Is there an equivalent to v_UpdateListStatus_Live in CM12?
