SCCM (Configuration Manager 2007) T-SQL - PLEASE HELP!!!

• almar.fivaz

  SSC Journeyman

  Points: 83

  More actions

  August 4, 2009 at 5:43 am

  #133692

  Hi,

  Any help regarding below mentioned WILL BE GREATLY APRECIATED!!!

  I have a T-SQL query which shows patch status for the Enterprise. The way that the query works out the percentages is what I need to change but have NO IDEA on how to do it.

  If you look at the last SELECT statement it works out the percentage on the @CollCount variable which reflects to All Systems which I do not want. I want to work out the percentage per subCollectionID but do not know how.

  PLEASE HELP!!! 🙂

  Here is the query

  --SCCM Patch Management Enterprise Compliancy Report

  --As most of you know if you have read any of the reports that I have posted in the past, here at the Commonwealth we use collections to track agencies. This report will show you an overall status and then a breakdown of each agency. This is done by grabbing the parent collection and listing each agency.

  --**********************************************************

  --Note: You will need to change your ScopeID to match your location and the Collection ID in the last Select statement to your parent Collection.

  --**********************************************************

  --AuthListID=ScopeId_8BF42CAA-F2A7-4063-A86D-C427EAB89450/AuthList_DC329234-6F0F-4256-879B-FBA1E43A2F0B

  --CollID=SMS00001

  declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID='ScopeId_52580E37-86DE-4A05-9B8F-CDC1BDB5C812/AuthList_EF924F28-2CF2-478B-88FD-7EF7ABECC41B'

  declare @CollCount int, @NumClients int; select @CollCount = count(*), @NumClients=isnull(sum(cast(IsClient as int)), 0) from v_ClientCollectionMembers ccm where ccm.CollectionID='SMS00001'

  Select

  CollectionName=vc.Name,

  NumberInCollection=@CollCount,

  NonClients=@CollCount-@NumClients,

  PComputers=convert(numeric(5,2), (@CollCount-@NumClients)*100.00 / isnull(nullif(@CollCount, 0), 1))

  from v_Collection vc

  where vc.CollectionID='SMS00001'

  SELECT v_Collection.Name

  , sn.StateName AS Status, COUNT(*) AS NumberOfComputers

  , CONVERT(numeric(5, 2)

  , ISNULL(COUNT(*), 0)* 100.00 / ISNULL(NULLIF (@CollCount, 0), 1)) AS PComputers

  , 'ScopeId_52580E37-86DE-4A05-9B8F-CDC1BDB5C812/AuthList_EF924F28-2CF2-478B-88FD-7EF7ABECC41B' AS AuthListID

  FROM v_ClientCollectionMembers AS cm INNER JOIN

  v_UpdateListStatus_Live AS cs ON cs.CI_ID = @CI_ID AND cs.ResourceID = cm.ResourceID INNER JOIN

  v_Collection ON cm.CollectionID = v_Collection.CollectionID LEFT OUTER JOIN

  v_StateNames AS sn ON sn.TopicType = 300 AND sn.StateID = ISNULL(cs.Status, 0)

  WHERE (cm.CollectionID = 'SMS00001')

  GROUP BY sn.StateName, v_Collection.Name

  ORDER BY NumberOfComputers DESC

  SELECT v_Collection.Name, sn.StateName AS Status, COUNT(*) AS NumberOfComputers, CONVERT(numeric(5, 2), ISNULL(COUNT(*), 0)

  * 100.00 / ISNULL(NULLIF (@CollCount, 0), 1)) AS PComputers

  FROM v_ClientCollectionMembers AS cm INNER JOIN

  v_UpdateListStatus_Live AS cs ON cs.CI_ID = @CI_ID AND cs.ResourceID = cm.ResourceID INNER JOIN

  v_Collection ON cm.CollectionID = v_Collection.CollectionID INNER JOIN

  v_StateNames AS sn ON sn.TopicType = 300 AND sn.StateID = ISNULL(cs.Status, 0) AND cm.CollectionID IN

  (SELECT subCollectionID

  FROM v_CollectToSubCollect

  WHERE (parentCollectionID = 'CEN00061'))

  GROUP BY sn.StateName, v_Collection.Name

  ORDER BY v_Collection.Name DESC