This is part two in a three part post regarding SCCM Computer Startup Scripts and SCCM Health.
Part 2 – The .MOF file additions
Part 3 – The SQL query for reporting
SQL Query for reporting
Once the MOF files are edited to include the attributes from the registry that the SCCM startup script wrote, it is now time to create a SQL view that you can use for reporting.
This post is going to be pretty straightforward and will not include information on creating custom SCCM reports.
The attached SQL script should be run on your SCCM Database. If you want to create a SCCM report based on the new SQL view, you will need to modify the view’s security.
SQL Script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[v_SCCM_Health_Check] as
select distinct a.name0 as ‘MachineName’
, a.itemkey
, a.operating_system_name_and0 as ‘OperatingSystem’
, os.CSDVersion0 as ‘OS_ServicePack’
, a.client0 as ‘SCCM_Client’
, a.Client_Version0 as ‘SCCM_ClientVersion’
, case when a.User_Name0 like ‘%\%’ then substring(a.User_Name0,charindex(‘\’,a.User_Name0)+ 1,charindex(‘\’,a.User_Name0))
else a.User_Name0 end AS ‘LastLoggedOnUser’
, case when scud.TopConsoleUser0 like ‘%\%’ then substring(scud.TopConsoleUser0,charindex(‘\’,scud.TopConsoleUser0)+ 1,charindex(‘\’,scud.TopConsoleUser0))
else scud.TopConsoleUser0 end as ‘MainUser’
, nlp.FullName0 as ‘MainUserFullName’
, a.AD_Site_Name0 as ‘AD_Site’
, ou.OU
, a.Resource_Domain_OR_Workgr0 as ‘Domain’
, f.SCCM_AssignedSite00 as ‘SCCM_Health_Check_Assigned_Site’
, f.HS_SCCM_AssignedSite00 as ‘SCCM_Health_Check_Assigned_Site_History’
, e.SiteCode as ‘SCCM_Site_Code’
, f.SCCM_Install00 as ‘SCCM_install_count’
, f.CCM_Client_Check00 as ‘SCCM Client Check’
, b.LastHWScan as ‘SCCM_LastHWscan’
, c.LastUpdateDate as ‘SCCM_LastSWscan’
, Dateadd(hour,(datediff(hour,getutcdate(),getdate())),d.lastscantime) as ‘Last_WSUSscan’
, scan.StateName ‘WSUSscan_State’
, scan.ErrorStatusID
, scan.ErrorCode
, scan.HexErrorCode
, uagent.version00 ‘WSUS_AgentVersion’
, d.lastlocation as ‘WSUSserver’
, f.last_run00 as ‘SCCM_Health_Check_Last_Run’
, f.Scan_Run00 as ‘SCCM_Health_Check_run_count’
, f.Account_Check00 as ‘SCCM_in_Admin_group’
, f.HS_Account_Check00 as ‘SCCM_in_Admin_group_History’
, f.AdminShare_Check00 as ‘Admin_share_present’
, f.HS_AdminShare_Check00 as ‘Admin_share_present_History’
, f.SCCM_AutoAssignment00 as ‘SCCM_Client_assignment’
, f.HS_SCCM_AutoAssignment00 as ‘SCCM_Client_assignment_History’
, f.CCM_Service_Check00 as ‘SCCM_Service_Check’
, f.HS_CCM_Service_Check00 as ‘SCCM_Service_Check_History’
, CCMService.State0 as ‘SMS_Agent_Host_Service’
, f.WindowsUpdate_Service_Che0 as ‘SCCM_Windows_Update_Service_Check’
, f.HS_WindowsUpdate_Service_0 as ‘SCCM_Windows_Update_Service_Check_History’
, AutoUpdates.State0 as ‘Auto_Updates_Service’
from SMS_CEN.dbo.System_DISC a
left join SMS_CEN.dbo.WorkstationStatus_DATA b
on a.itemkey = b.machineid
left join SMS_CEN.dbo.SoftwareInventoryStatus c
on a.itemkey = c.clientid
left join SMS_CEN.dbo.Update_ScanStatus d
on a.itemkey = d.machineid
left join SMS_CEN.dbo.Windows_Update_Agent__DATA uagent
on a.itemkey = uagent.machineid
left join SMS_CEN.dbo.Sites_DATA e
on a.itemkey = e.machineid
left join dbo.SCCM_Health_Check_DATA f
on a.itemkey = f.machineid
left join dbo.Operating_System_DATA os
on a.itemkey = os.machineid
left join
(
select uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
isnull(uss.LastErrorCode,0) as ErrorCode,
dbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
from v_UpdateScanStatus uss
join v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
join v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID
join v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
join v_StateNames sn on sn.TopicType = 501 and
sn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, ‘4.0’), 1)<‘4’) then 7 else isnull(uss.LastScanState, 0) end)
where 1= 1
–and (sass.SMS_Assigned_Sites0= ‘P01’)
–and (sn.StateName=’Scan failed’)
and (sus.UpdateSource_UniqueID = ‘{BE893E98-BD57-4E80-93F6-5D38B61C6069}’)
) scan
on a.itemkey = scan.ResourceID
left JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUD
on a.itemkey = scud.resourceID
left join dbo.v_GS_NETWORK_LOGIN_PROFILE nlp
on nlp.name0 = scud.TopConsoleUser0 and nlp.resourceID = scud.resourceid
left join
(
select machineid, State0
from dbo.Services_DATA
where name0 = ‘ccmexec’
) CCMService
on a.itemkey = CCMService.machineid
left join
(
select machineid, State0
from dbo.Services_DATA
where name0 = ‘wuauserv’
) AutoUpdates
on a.itemkey = AutoUpdates.machineid
left join
(
select itemKey, max(system_ou_name0) as ‘OU’
from dbo.System_System_OU_Name_ARR
group by itemkey
) OU
on a.itemkey = ou.itemkey
where 1 = 1