I don't think it liked the copy/paste format. Try this below. I updated the
blog as well.
DECLARE @NullVal datetime
SELECT sites.SMS_Assigned_Sites0 AS AssignedSite
,TotalSys.Total AS TotalActiveClients
,SuccSys.Succ AS HWSuccess
,SuccSW.Succ AS SWSuccess
,CONVERT(decimal(5,2),(SuccSys.Succ*100.00/TotalSys.Total)) AS 'HW
Percentage'
,CONVERT(decimal(5,2),(SuccSW.Succ*100.00/TotalSys.Total)) AS 'SW
Percentage'
FROM v_RA_system_smsassignedsites sites
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Succ
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_System sis
ON sit.ResourceID = sis.ResourceID
INNER JOIN v_gs_workstation_status sts
ON sis.ResourceID = sts.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
AND sts.LastHWScan > 7
GROUP BY sit.sms_assigned_sites0
) SuccSys
ON sites.SMS_Assigned_Sites0 = SuccSys.AssSite
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Succ
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_System sis
ON sit.ResourceID = sis.ResourceID
INNER JOIN v_GS_LastSoftwareScan sts
ON sis.ResourceID = sts.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
AND sts.LastScanDate > 10
GROUP BY sit.SMS_Assigned_Sites0
) SuccSW
ON SuccSW.AssSite = sites.SMS_Assigned_Sites0
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Total
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_system sis
ON sit.ResourceID = sis.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
GROUP BY sit.SMS_Assigned_Sites0
) TotalSys
ON sites.SMS_Assigned_Sites0 = TotalSys.Asssite
GROUP BY sites.SMS_Assigned_Sites0
,TotalSys.Total
,SuccSys.Succ
,SuccSW.Succ
ORDER BY 4 DESC
From: [email protected] [mailto:[email protected]] On
Behalf Of Linkey, Mike
Sent: Thursday, October 30, 2014 11:38 AM
To: [email protected]
Subject: [mssms] RE: ConfigMgr 2012 Dashboards in SCOM 2012
Trying to set this up and having issues with the HW SW scans query. Did it
past correctly to the blog? I have not got it working yet.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of John Aubrey
Sent: Thursday, October 30, 2014 6:33 AM
To: '[email protected]'
Subject: [mssms] RE: ConfigMgr 2012 Dashboards in SCOM 2012
I thought this would be the best time to start a blog since the idea of a
knowledgebase keeps getting shot down at work. Below is a link to the queries
I used in the report. You will need some collection ID's and the site code in
a couple places.
http://thatsystemcenterguy.wordpress.com/
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Boseman, Marcia H -
Raleigh, NC
Sent: Tuesday, October 28, 2014 1:59 PM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: ConfigMgr 2012 Dashboards in SCOM 2012
Thank you very much
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of John Aubrey
Sent: Tuesday, October 28, 2014 1:56 PM
To: '[email protected]'
Subject: [mssms] RE: ConfigMgr 2012 Dashboards in SCOM 2012
I'll put something together tonight. I have a few other reports that are
pretty cool dashboard type I'll add too.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of LEROY Mathieu
Sent: Tuesday, October 28, 2014 1:40 PM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: ConfigMgr 2012 Dashboards in SCOM 2012
Me too :)
Cordialement,
Mathieu Leroy
De : [email protected]<mailto:[email protected]>
[mailto:[email protected]] De la part de Jason Lang
Envoyé : mardi 28 octobre 2014 18:24
À : [email protected]<mailto:[email protected]>
Objet : [mssms] RE: ConfigMgr 2012 Dashboards in SCOM 2012
I like this as well. Looking it over, I (probably) could whip it up myself when
I found time, but generally I despise reinventing the wheel. If you're able and
willing to share, I'd like to mooch :)
Jason Lang
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Boseman, Marcia H -
Raleigh, NC
Sent: Tuesday, October 28, 2014 10:12 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: ConfigMgr 2012 Dashboards in SCOM 2012
Yes please send the queries to me.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of John Aubrey
Sent: Tuesday, October 28, 2014 9:31 AM
To: '[email protected]'
Subject: [mssms] RE: ConfigMgr 2012 Dashboards in SCOM 2012
I do have this. This is actually pulled from SCCM server DB. I have it on our
dashboard home page of the IT intranet. I can share the queries if wanted. I
ripped most from the IT @ Microsoft blog a while ago. Please don't judge the
Update Compliance, it's broken. I think this would be more of a client health
dashboard then a SCCM health dashboard.
[cid:[email protected]]
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of John Aubrey
Sent: Tuesday, October 28, 2014 9:13 AM
To: '[email protected]'
Subject: [mssms] RE: ConfigMgr 2012 Dashboards in SCOM 2012
I would be interested in this as well. I had one before, I'll see if I can dig
it up. I wasn't anything super fancy.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Boseman, Marcia H -
Raleigh, NC
Sent: Tuesday, October 28, 2014 9:04 AM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] ConfigMgr 2012 Dashboards in SCOM 2012
Has anyone created any Custom dashboards for Configmgr 2012 MP Dashboards in
SCOM 2012 they would be willing to share for monitoring ConfigMgr 2012?
