Microsoft Endpoint Manager: Configuration Manager

Overview

Microsoft Endpoint Manager: Configuration Manager, previously System Center Configuration Manager and still universally called SCCM or ConfigMgr, is Microsoft’s on-premises management platform for Windows estates. A client agent on each device reports hardware and software inventory, software update compliance, Endpoint Protection and Defender health and deployment results back to a site server. Devices and users are grouped into collections, and packages, applications and task sequences drive software distribution and operating system builds. Everything the site collects is stored in the site database, a Microsoft SQL Server database usually named CM_<sitecode>, and exposed through a large set of reporting views whose names begin with v_.

The ReadyWorks connector reads that site database directly. It offers a single authentication method driven by the mssql.php driver: you supply the SQL Server host name, the port (1433 by default), the site database name, and a database account with read access to the reporting views. There is no WMI call, no SMS Provider and no ConfigMgr console involvement. Each job carries its own SQL statement in its Data Selection field, ReadyWorks executes that statement against the site database, and the result set lands in the staging table named on the job.

SCCM is normally the most complete record an organisation has of its Windows estate, which is why the shipped job set is the largest of any database-backed connector here. Twenty-three templates cover the device master, installed software, users and the three different ways SCCM associates a user with a device, collections and their membership, software update compliance and missing update counts, malware detections and the threat catalogue, antimalware health, client health, packages, and task sequence execution status. In the ReadyWorks estate fabric that supports device refresh and Windows readiness assessment (TPM specification version, memory, disk size, chassis type and CPU all come back on the device master job), application rationalisation, migration wave planning, and finding the devices SCCM has stopped hearing from.

NOTE: Installed software arrives in two parts. SCCM Applications returns one row per device per installed program, identified only by device resource ID and an MD5 match code built from display name, publisher and version. SCCM Programs DISTINCT returns the readable program name, publisher and version against the same match code. Enable both jobs, or the application data is unreadable.

Most templates filter on Obsolete0 = 0 and Decommissioned0 = 0, and several also exclude device names containing a pipe character. That is the standard ConfigMgr pattern for skipping superseded duplicate records and orphaned discovery rows, so counts from these jobs will be lower than a raw count of the discovery table.

The connector is inbound only. It defines no outbound job fields and ships no outbound templates, so ReadyWorks never writes to the site database or triggers anything in ConfigMgr.

Connector Properties

Authentication Methods

SCCM offers one authentication method. It is a direct Microsoft SQL Server connection to the Configuration Manager site database using an account supplied on the connection.

Method 1: Username / Password (SCCM_user)

Connect to a MS SQL Server

A standard SQL Server login against the ConfigMgr site database. You give the source server, the port (pre-filled with 1433 and validated as a number below 65536), the site database name (typically CM_<sitecode>), and the username and password of the database account. The mssql.php driver authenticates natively, so there is no stored authentication recipe and no headers or tokens to configure. All five fields are required. Read access to the v_ reporting views is sufficient; every shipped job is a SELECT and nothing in this connector writes to ConfigMgr.

Connection Configuration Fields (5)

No authentication recipe is stored. The driver script authenticates natively using the fields above.

NOTE: Point the connection at the primary site database, or at the central administration site database if you want a hierarchy-wide view. A secondary site does not hold the full inventory.

NOTE: Several templates read base tables rather than reporting views, including CollectionMembers, System_DISC, User_DISC, SMSPackageTypes and customseverityreference. An account granted access only to the v_ views will fail on those jobs.

Inbound Job Fields Enabled (8)

SCCM inbound jobs expose a deliberately small field set: description, schedule, enabled flag, destination table, data identity, the Data Selection box holding the SQL, the fields to index, and the run order.

NOTE: Data Selection is marked optional on the form, but this is a SQL connector and a job with an empty Data Selection has nothing to execute. Every shipped template supplies one; any job you add yourself must too.

NOTE: Fields to Index matters more here than on most connectors. The SCCM tables are large and the shipped templates set index fields on the columns you will actually join on, such as ItemKey, resourceid, machineid and match_code_xf. Keep those settings when you edit a job.

Inbound Job Templates (23)

Twenty-three inbound jobs ship with the connector, each one a SQL statement against the ConfigMgr site database. Together they reproduce most of what the built-in ConfigMgr reports draw on: device hardware, installed software, user association, collections, update compliance, malware and antimalware health, client health, packages and task sequence status. They are designed to be run as a set, since several of them are only useful joined to another.

Job Template Configuration

Settings Common to All Twenty-Three Templates

Settings That Vary by Template

Job Parameters and Enumeration

SCCM Computer

Data Selection:

SELECT DISTINCT c.ItemKey,
		  client.SiteCode,
		  c.Name0,
		  c.Operating_System_Name_and0,
		  c.Client0,
		  c.Active0,
		  c.Client_Type0,
		  c.Build01,
		  os.Caption0,
		  cs.Manufacturer0,
		  CASE WHEN (cs.Manufacturer0 = 'LENOVO' AND csp.Version0 IS NOT NULL) THEN csp.Version0 ELSE cs.Model0 END AS [model_xf],
		  cs.Model0,
		  se.SerialNumber0,
		  c.Virtual_Machine_Host_Name0,
		  c.Resource_Domain_OR_Workgr0,
		  cs.SystemType0,
		  csum.ClientActiveStatus,
		  mem.TotalPhysicalMemory0,
		  (SELECT TOP 1 ChassisTypes0
		  FROM dbo.v_GS_SYSTEM_ENCLOSURE AS se
		  WHERE se.ResourceID = c.ItemKey) AS [ChassisTypes0],
		  c.AD_Site_Name0,
		  cpu.[Name0] AS [cpu_Name0],
		  BIOS.SerialNumber0 as BIOSSerialNumber0,
		  BIOS.ReleaseDate0 as BIOSReleaseDate0,
		  BIOS.SMBIOSBIOSVersion0 as BIOSVersion0,
		  (SELECT TOP 1 ROUND(CAST(hd.Size0 AS float) / 1024, 0)
		  FROM dbo.v_GS_DISK hd
		  WHERE hd.ResourceID = c.ItemKey AND hd.DeviceID0 LIKE '%DRIVE0%' AND cs.Model0 NOT LIKE '%Virtual%') AS [disk_size_gb_xf],
		  (SELECT TOP 1 Model0
		  FROM dbo.v_GS_DISK hd
		  WHERE hd.ResourceID = c.ItemKey AND hd.DeviceID0 LIKE '%DRIVE0%' AND cs.Model0 NOT LIKE '%Virtual%') AS [hard_drive_model_xf],
		  (SELECT TOP 1 ROUND(CAST(ldisk.FreeSpace0 AS float) / 1024, 0)
		  FROM dbo.v_GS_LOGICAL_DISK ldisk
		  WHERE ldisk.ResourceID = c.ItemKey AND ldisk.DeviceID0 LIKE 'C:') AS [disk_size_free_gb_xf],
		  tpm.[SpecVersion0],

		  (SELECT TOP 1 cmagent.[AgentTime] FROM dbo.[v_AgentDiscoveries] AS cmagent
		  WHERE cmagent.[ResourceId] = c.ItemKey
		   AND cmagent.[AgentName] LIKE 'Heartbeat Discovery'
		   ORDER BY AgentTime DESC) AS [sccm_agent_time_xf]

   FROM   dbo.vSMS_R_System c
		  LEFT JOIN dbo.v_ClientMachines AS client ON c.ItemKey = client.resourceid
		  LEFT JOIN dbo.v_GS_COMPUTER_SYSTEM cs ON c.ItemKey = cs.ResourceID
		  LEFT JOIN dbo.v_GS_COMPUTER_SYSTEM_PRODUCT csp ON c.ItemKey = csp.ResourceID
		  LEFT JOIN dbo.v_GS_X86_PC_MEMORY mem ON cs.ResourceID = mem.ResourceID
		  LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE AS se ON c.ItemKey = se.ResourceID
		  LEFT JOIN dbo.v_GS_PROCESSOR AS cpu ON c.ItemKey = cpu.ResourceID
		  LEFT JOIN dbo.v_GS_TPM AS tpm ON c.ItemKey = tpm.ResourceID
		  LEFT JOIN dbo.v_GS_PC_BIOS BIOS ON c.ItemKey=bios.ResourceID
		  LEFT JOIN dbo.v_GS_OPERATING_SYSTEM AS os ON c.ItemKey=os.ResourceID
		 LEFT JOIN dbo.v_CH_ClientSummary AS csum ON csum.ResourceID = c.ItemKey


   WHERE
		  c.Decommissioned0=0 AND c.Obsolete0=0 AND c.Name0 NOT LIKE N'%|%';

SCCM Computer Malware

Data Selection:

SELECT t.threatid,
	   c.itemkey,
	   Dateadd(day, Datediff(day, 0, t.detectiontime), 0) AS [detection_date_xf],
	   N'SystemCenter Endpoint Protection'                [observer_product_name_xf],
	   t.productversion,
	   CASE
		 WHEN t.detectionsource = 0 THEN N'Unknown'
		 WHEN t.detectionsource = 1 THEN N'User'
		 WHEN t.detectionsource = 2 THEN N'System'
		 WHEN t.detectionsource = 3 THEN N'Realtime'
		 WHEN t.detectionsource = 4 THEN N'IOAV'
		 WHEN t.detectionsource = 5 THEN N'NIS'
		 WHEN t.detectionsource = 6 THEN N'BHO'
	   END                                                [observer_detection_xf],
	   CASE
		 WHEN t.cleaningaction = 0 THEN N'Unknown'
		 WHEN t.cleaningaction = 1 THEN N'Clean'
		 WHEN t.cleaningaction = 2 THEN N'Quarantine'
		 WHEN t.cleaningaction = 3 THEN N'Remove'
		 WHEN t.cleaningaction = 6 THEN N'Allow'
		 WHEN t.cleaningaction = 8 THEN N'UserDefined'
		 WHEN t.cleaningaction = 9 THEN N'NoAction'
		 WHEN t.cleaningaction = 10 THEN N'Block'
	   END                                                [remediation_type_xf],
	   CASE
		 WHEN t.actionsuccess = 1 THEN N'True'
		 ELSE N'False'
	   END                                                [remediation_result_xf],
	   t.errorcode,
	   CASE
		 WHEN t.pendingactions & 4 <> 0 THEN N'FullScan'
		 WHEN t.pendingactions & 8 <> 0 THEN N'Reboot'
		 WHEN t.pendingactions & 16 <> 0 THEN N'SettingsModified'
		 WHEN t.pendingactions & 32768 <> 0 THEN N'SystemSweeper'
		 ELSE N'NoActionRequired'
	   END                                                [remediation_pending_action_xf],
	   CASE
		 WHEN t.actionsuccess = 0 THEN N'True'
		 ELSE N'False'
	   END                                                [is_active_malware_xf]
FROM   dbo.v_gs_threats t
	   INNER JOIN dbo.vsms_r_system c
			   ON t.resourceid = c.itemkey
	   INNER JOIN dbo.v_threatcatalog tc
			   ON t.threatid = tc.threatid
WHERE  c.obsolete0 = 0
	   AND c.decommissioned0 = 0;

SCCM Applications

Data Selection:

SELECT resourceid,
		 CONVERT(VARCHAR(32), HASHBYTES('MD5', CONCAT(Replace(p.displayname0, CHAR(9), ' '), Replace(p.publisher0, CHAR(9), ' '), version0)), 2) [match_code_xf],
		 CASE WHEN ISDATE(InstallDate0) = 1 THEN InstallDate0
  	   ELSE NULL END AS [timestamp_xf]
  FROM   dbo.v_gs_add_remove_programs p
		 INNER JOIN dbo.vsms_r_system s ON p.resourceid = s.itemkey
  WHERE  s.obsolete0 = 0 AND
		 s.decommissioned0 = 0 AND
		 p.displayname0 IS NOT NULL AND
		 p.displayname0<>'' AND
		 p.Publisher0 IS NOT NULL AND
		 p.Version0 IS NOT NULL AND
		 UNICODE(p.displayname0) <> 127 AND
		 UNICODE(RIGHT(p.version0, 1)) > 31
  UNION
  SELECT resourceid,
		 CONVERT(VARCHAR(32), HASHBYTES('MD5', CONCAT(Replace(p.displayname0, CHAR(9), ' '), Replace(p.publisher0, CHAR(9), ' '), version0)), 2) [match_code_xf],
		 CASE WHEN ISDATE(InstallDate0) = 1 THEN InstallDate0
  	   ELSE NULL END AS [timestamp_xf]
  FROM   dbo.v_gs_add_remove_programs_64 p INNER JOIN dbo.vsms_r_system s ON p.resourceid = s.itemkey
  WHERE  s.obsolete0 = 0 AND
		 s.decommissioned0 = 0 AND
		 p.displayname0 IS NOT NULL AND
		 p.displayname0<>'' AND
		 p.Publisher0 IS NOT NULL AND
		 p.Version0 IS NOT NULL AND
		 UNICODE(p.displayname0) <> 127 AND
		 UNICODE(RIGHT(p.version0, 1)) > 31;

SCCM Computer Update

Data Selection:

SELECT machineid,
	   ucs.ci_id,
	   laststatuschangetime,
	   laststatuschecktime,
	   ucs.status

	FROM   dbo.vsms_update_compliancestatus ucs
	INNER JOIN dbo.vsms_softwareupdate u ON ucs.ci_id = u.ci_id
	INNER JOIN dbo.vsms_r_system c ON ucs.machineid = c.itemkey AND u.isenabled = 1 AND u.issuperseded = 0
	WHERE  c.obsolete0 = 0 AND c.decommissioned0 = 0;

SCCM Malware

Data Selection:

SELECT tc.threatid,
	   Coalesce(tc.NAME, N'UnknownThreat') [malware_name_xf],
	   Coalesce(sev.severity, N'Unknown')  [malware_severity_xf],
	   Coalesce(cat.category, N'Invalid')  [malware_category_xf]
	FROM   dbo.v_threatcatalog tc
		   LEFT JOIN dbo.v_threatseverities sev ON tc.severityid = sev.severityid
		   LEFT JOIN dbo.v_threatcategories cat ON tc.categoryid = cat.categoryid;

SCCM Update

Data Selection:

SELECT su.ci_id,
	   su.articleid,
	   su.bulletinid,
	   title,
	   severity,
	   customname,
	   infourl
	FROM dbo.v_updateinfo su
	LEFT OUTER JOIN dbo.customseverityreference s ON su.severity=s.severityid;

SCCM User

Data Selection:

SELECT *
FROM dbo.v_r_user
	WHERE  user_name0 IS NOT NULL AND
	   full_user_name0 NOT LIKE '%$%';

SCCM User Computer Primary

Data Selection:

SELECT
		c.ResourceID AS [ResourceID]
		 -- ,c.Name0 AS [Computername]
		 ,vru.Windows_NT_Domain0 AS [User_Domain0]
		 ,vru.User_Name0 AS [User_Name0]
		 -- ,CONCAT(vru.Windows_NT_Domain0, '\', vru.User_Name0) AS [User_FullName]
  FROM dbo.V_R_System c
  INNER JOIN dbo.v_UsersPrimaryMachines upm on upm.MachineID = c.ResourceID
  INNER JOIN dbo.v_R_User vru on upm.UserResourceID = vru.ResourceID
  WHERE
		 (
				(c.user_domain0 IS NOT NULL AND c.user_name0 IS NOT NULL) OR (vru.Windows_NT_Domain0 IS NOT NULL AND vru.User_Name0 IS NOT NULL)
		 ) AND
		c.Decommissioned0=0 AND
		c.Obsolete0=0 AND
		c.Name0 NOT LIKE N'%|%';

SCCM User Computer Top Console

Data Selection:

SELECT DISTINCT
	  ResourceID, TopConsoleUser0,
	  substring(TopConsoleUser0, 1, CHARINDEX('\',TopConsoleUser0)-1) AS User_Domain0,
	  substring(TopConsoleUser0, CHARINDEX('\',TopConsoleUser0)+1, LEN(TopConsoleUser0)) AS User_Name0
  FROM dbo.v_GS_SYSTEM_CONSOLE_USAGE
  WHERE
	  TopConsoleUser0 IS NOT NULL
	  order by ResourceID;

SCCM User Computer Last Logon

Data Selection:

SELECT
		c.[ResourceID] AS [ResourceID]
		 -- ,c.Name0 AS [Computername]
		 ,c.[User_Domain0] AS [User_Domain0]
		 ,c.[User_Name0] AS [User_Name0]
		 -- ,CONCAT(vru.Windows_NT_Domain0, '\', vru.User_Name0) AS [User_FullName]
  FROM dbo.V_R_System c
  INNER JOIN dbo.v_R_User vru on c.[User_Domain0] = vru.[Windows_NT_Domain0] AND c.[User_Name0] = vru.[User_Name0]
  WHERE
		 c.[User_Domain0] IS NOT NULL AND
		   c.[User_Name0] IS NOT NULL AND
		c.[Decommissioned0] = 0 AND
		c.[Obsolete0] = 0 AND
		c.Name0 NOT LIKE N'%|%';

SCCM Collection

Data Selection:

SELECT SiteID,
	CollectionName,
	CollectionType
	FROM dbo.v_Collections;

SCCM Computer Collection

Data Selection:

SELECT cm.SiteID,
	cm.MachineID
	FROM dbo.CollectionMembers cm
	LEFT OUTER JOIN dbo.System_DISC sd ON cm.MachineID=sd.ItemKey AND cm.ArchitectureKey=5
	WHERE
	sd.Obsolete0 IS NULL OR sd.Obsolete0=0;

SCCM User Collection

Data Selection:

SELECT cm.SiteID,
	cm.MachineID
	FROM dbo.CollectionMembers cm
	LEFT OUTER JOIN dbo.User_DISC ud ON cm.MachineID=ud.ItemKey WHERE cm.ArchitectureKey=4;

SCCM Deployment Summary

Data Selection: SELECT * FROM dbo.v_DeploymentSummary;

SCCM Package

Data Selection:

SELECT DISTINCT p.*, 'source_size_mb_xf' = n.SourceSize/1024, t.Name AS package_type_name_xf
	FROM dbo.v_Package p
	LEFT JOIN dbo.v_PackageStatusRootSummarizer n
	ON
	p.PackageID = n.PackageID
	LEFT JOIN dbo.SMSPackageTypes t ON p.PackageType = t.PackageTypeID;

SCCM Scan History

Data Selection:

SELECT DISTINCT p.*, t.Name AS t_Name
	FROM dbo.v_Package p
	LEFT JOIN dbo.v_PackageStatusRootSummarizer n ON p.PackageID = n.PackageID
	LEFT JOIN dbo.SMSPackageTypes t ON p.PackageType = t.PackageTypeID;

SCCM Scan History Machines

Data Selection:

SELECT computer.ItemKey,
		   client.SiteCode,
		   computer.Name0,
		   computer.Operating_System_Name_and0,
		   computer.Client_Type0
	FROM   dbo.vsms_r_system computer
	LEFT OUTER JOIN dbo.v_clientmachines AS client ON computer.ItemKey=client.ResourceID
	WHERE  computer.Decommissioned0=0 AND computer.Obsolete0=0;

SCCM Scan History Missing Update Count

Data Selection:

SELECT  [status].machineid,
	SUM(CASE u.severity WHEN 10 THEN 1 ELSE 0 END) AS [missing_critical_update_count_xf],
	SUM(CASE u.severity WHEN 8 THEN 1 ELSE 0 END)  AS [missing_important_update_count_xf]
	FROM dbo.vsms_update_compliancestatus [status]
	INNER JOIN dbo.vsms_softwareupdate u ON [status].ci_id=u.ci_id
	WHERE [status].[status]<>3 AND u.isenabled=1 AND u.issuperseded=0 AND u.severity>=8
	GROUP BY [status].machineid;

SCCM Scan History End Point Compliance

Data Selection:

SELECT  resourceid,
			CASE
				WHEN enabled = 1 THEN 1
				ELSE 0
			END                       AS [enabled_xf],
			[version]                 AS [client_version_xf],
			CASE
				WHEN rtpenabled = 1 THEN 1
				ELSE 0
			END                       AS [real_time_protection_enabled_xf],
			CASE
				WHEN onaccessprotectionenabled = 1 THEN 1
				ELSE 0
			END                       AS [on_access_protection_enabled_xf],
			CASE
				WHEN ioavprotectionenabled = 1 THEN 1
				ELSE 0
			END                       AS [io_protection_enabled_xf],
			CASE
				WHEN behaviormonitorenabled = 1 THEN 1
				ELSE 0
			END                       AS [behavior_monitor_enabled_xf],
			CASE
				WHEN antivirusenabled = 1 THEN 1
				ELSE 0
			END                       AS [antivirus_enabled_xf],
			CASE
				WHEN antispywareenabled = 1 THEN 1
				ELSE 0
			END                       AS [antispyware_enabled_xf],
			CASE
				WHEN nisenabled = 1 THEN 1
				ELSE 0
			END                       AS [nis_enabled_xf],
			CASE
				WHEN lastquickscandatetimestart > Getdate() THEN 0
				ELSE Datediff(day, lastquickscandatetimestart, Getdate())
			END                       AS [quick_scan_age_days_xf],
			CASE
				WHEN lastfullscandatetimestart > Getdate() THEN 0
				ELSE Datediff(day, lastfullscandatetimestart, Getdate())
			END                       AS [full_scan_age_days_xf],
			CASE
				WHEN antivirussignatureupdatedatetime > Getdate() THEN 0
				ELSE Datediff(day, antivirussignatureupdatedatetime, Getdate())
			END                       AS [signature_age_days_xf],
			engineversion,
			antivirussignatureversion
	FROM   dbo.vsms_g_system_antimalwarehealthstatus;

SCCM Scan History Health Summary

Data Selection:

SELECT cs.resourceid,
		   cs.lastonline,
		   cs.laststatusmessage,
		   cs.lasthealthevaluation,
		   cs.lasthealthevaluationresult,
		   cs.clientactivestatus,
		   cs.clientstate,
		   cs.lastddr,
		   cs.lasthw,
		   cs.lastsw,
		   cs.laststatusmessage,
		   cs.lastpolicyrequest
	FROM  dbo.[v_ch_clientsummary] cs;

SCCM Scan History Scan Summary

Data Selection:

SELECT resourceid,
	lastscantime
	FROM dbo.v_updatescanstatus;

SCCM Programs DISTINCT

Data Selection:

SELECT DISTINCT
		 Replace(p.displayname0, CHAR(9), ' ')  [program_name_xf],
		 Replace(p.publisher0, CHAR(9), ' ')    [publisher_xf],
		 version0,
		 CONVERT(VARCHAR(32), HASHBYTES('MD5', CONCAT(Replace(p.displayname0, CHAR(9), ' '), Replace(p.publisher0, CHAR(9), ' '), version0)), 2) [match_code_xf]
  FROM   dbo.v_gs_add_remove_programs p
		 INNER JOIN dbo.vsms_r_system s ON p.resourceid = s.itemkey
  WHERE  s.obsolete0 = 0 AND
		 s.decommissioned0 = 0 AND
		 p.displayname0 IS NOT NULL AND
		 p.displayname0<>'' AND
		 p.Publisher0 IS NOT NULL AND
		 p.Version0 IS NOT NULL AND
		 UNICODE(p.displayname0) <> 127 AND
		 UNICODE(RIGHT(p.version0, 1)) > 31
  UNION
  SELECT DISTINCT
		 Replace(p.displayname0, CHAR(9), ' ')  [program_name_xf],
		 Replace(p.publisher0, CHAR(9), ' ')    [publisher_xf],
		 version0,
		 CONVERT(VARCHAR(32), HASHBYTES('MD5', CONCAT(Replace(p.displayname0, CHAR(9), ' '), Replace(p.publisher0, CHAR(9), ' '), version0)), 2) [match_code_xf]
  FROM   dbo.v_gs_add_remove_programs_64 p INNER JOIN dbo.vsms_r_system s ON p.resourceid = s.itemkey
  WHERE  s.obsolete0 = 0 AND
		 s.decommissioned0 = 0 AND
		 p.displayname0 IS NOT NULL AND
		 p.displayname0<>'' AND
		 p.Publisher0 IS NOT NULL AND
		 p.Version0 IS NOT NULL AND
		 UNICODE(p.displayname0) <> 127 AND
		 UNICODE(RIGHT(p.version0, 1)) > 31;

SCCM Task Sequence Status Data

Data Selection:

select PackageName,
ai.AdvertisementID,
ai.CollectionName,
Name0,
User_Name0,
LastAcceptanceMessageIDName,
LastAcceptanceStateName,
LastAcceptanceStatusTime,
LastStatusMessageIDName,
LastStateName,
convert(varchar, dateadd(hour, -4, (
 select top 1 ExecutionTime
 from dbo.v_TaskExecutionStatus tes
 where tes.ResourceID = sys.ResourceID AND tes.step <> '' AND tes.AdvertisementID = cas.AdvertisementID ORDER BY ExecutionTime DESC
)), 100) as LastStatusTime,
LastExecutionResult,
(
 select top 1 step
 from dbo.v_TaskExecutionStatus tes
 where tes.ResourceID = sys.ResourceID AND tes.step <> '' AND tes.AdvertisementID = cas.AdvertisementID ORDER BY ExecutionTime DESC
) as lastTsStep,
(
 select top 1 ActionName
 from dbo.v_TaskExecutionStatus tes
 where tes.ResourceID = sys.ResourceID AND tes.actionName <> ''  AND tes.AdvertisementID = cas.AdvertisementID ORDER BY ExecutionTime DESC
) as lastActionName,
(
 select top 1 GroupName
 from dbo.v_TaskExecutionStatus tes
 where tes.ResourceID = sys.ResourceID AND tes.GroupName <> ''  AND tes.AdvertisementID = cas.AdvertisementID ORDER BY ExecutionTime DESC
) as lastGroupName,
(
 select top 1 ActionOutput
 from dbo.v_TaskExecutionStatus tes
 where tes.ResourceID = sys.ResourceID AND tes.ActionOutput <> ''  AND tes.AdvertisementID = cas.AdvertisementID ORDER BY ExecutionTime DESC
) as lastActionOutput
from dbo.v_ClientAdvertisementStatus cas
inner join dbo.v_R_System sys on sys.ResourceID=cas.ResourceID
inner join dbo.v_AdvertisementInfo ai on ai.AdvertisementID=cas.AdvertisementID;

NOTE: All twenty-three templates ship with Enabled set to No and a Daily schedule. Enable only the jobs you need and stagger the schedules: several of these queries scan the largest tables in the site database and running them together at 01:15 will be noticeable on a busy site server.

NOTE: SCCM Applications and SCCM Programs DISTINCT are a pair joined on match_code_xf. The same is true of the scan history jobs, which all key on resource ID and are meant to be joined to SCCM Scan History Machines.

Outbound Job Templates (0)

The SCCM connector ships no outbound templates and exposes no outbound job fields. It reads from the ConfigMgr site database and never writes to it.