Splunk community NEEDS an answer for getting SCCM data into Splunk

[nick405060]

Alright, so I've been trying for a long time to get SCCM data into Splunk in a non-6.2 Splunk environment. Considering this is a pervasive problem among the Splunk community, and there are dozens of users who have posted for years and not gotten any answers back, this needs to be addressed by Splunk.

We need a comprehensive answer on how to do this.

[pl2345]

I got the SCCM app working today with a lot of frustration and time rewriting the SQL queries for DB Connect.

We're using Splunk Enterprise 8.2.0 and DB Connect 3.5.1

Once I got these searches working, I had to go through the macros and lookups and update them for the current DB Connect term, ie. dbquery became |dbxquery.

I wouldnt have been able to solve most of the complex queries without this article, Troubleshooting Tips for Splunk DB Connect 3 | Function1 

Hope that helps.

*********************************************************************
Status:
*********************************************************************

[sccm_agent_discoveries_dbinput]
	Works as intended

[dbmon-tail://sccm/sccm_status_message_dbinput]
	Works, but not as intended

[dbmon-tail://sccm/sccm_installed_software_dbinput]
	Works as intended

[dbmon-tail://sccm/ta_sccm_malware_dbinput]
	Will not execute - Empty table/views, this is more an issue with SCCM 
        not having the appropriate data to populate the view.

[dbmon-tail://sccm/sccm_resource_dbinput]
	Works as intended

*********************************************************************
[sccm_agent_discoveries_dbinput]
*********************************************************************

SELECT AgentTime,
         [v_R_System].ResourceID,
         [v_R_System].Netbios_Name0 AS "name",
         AgentName
FROM [CM_360].[dbo].[v_AgentDiscoveries]
LEFT JOIN [v_R_System]
    ON [v_AgentDiscoveries].ResourceId = [v_R_System].ResourceID
WHERE AgentTime > ?
ORDER BY  AgentTime ASC

*********************************************************************
[dbmon-tail://sccm/sccm_status_message_dbinput]

This works, but not as intended. The search is an upgraded version of the original. RecordID fails as a rising column. Timestamp is not an ideal rising column because if there are multiple entries for the same time, all but one of the rows will be skipped. The workaround is to set the rising column to timestamp with a >= clause to catch everything.
*************************************************************

SELECT RecordID,	timestamp,	timestamputc,	MessageID,	Severity,	severityid,	MachineName,	SiteCode,	ModuleName,	Component,	string1,	string2,	string3,	string4,	string5,	string6,	string7,	string8,	string9,	string10,	packageid,	advertisementid,	collectionid,	username,	dp,	policyid,	policyassignmentid,	meter_ruleid,	client_sms_uniqueid	site_code,	package_version	time_key,	unique_updateid	productid,	ci_assignmentid,	objectid,	object_type,	sdm_typeid,	sdm_type_version,	update_source_uniqueid,	collection_extended_propsid,	wol_object_type	wol_batchid,	machine_extended_propsid,	wol_num_requests,	unknown_machine,	mac_addresses,	smbiosid
FROM 
    (SELECT msg.RecordID,
         convert(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), msg.[Time] ),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS [timestamp],
         msg.[Time] AS [timestamputc],
         msg.MessageID,
         Severity =
        CASE msg.Severity
        WHEN 1073741824 THEN
        'informational'
        WHEN -1073741824 THEN
        'error'
        WHEN -2147483648 THEN
        'warning' END, 
        msg.Severity AS severityid, msg.MachineName, msg.SiteCode, msg.ModuleName, msg.Component, replace(strings.InsString1,'"','') AS string1, replace(strings.InsString2,'"','') AS string2, replace(strings.InsString3,'"','') AS string3, replace(strings.InsString4,'"','') AS string4, replace(strings.InsString5,'"','') AS string5, replace(strings.InsString6,'"','') AS string6, replace(strings.InsString7,'"','') AS string7, replace(strings.InsString8,'"','') AS string8, replace(strings.InsString9,'"','') AS string9, replace(strings.InsString10,'"','') AS string10, packageid, advertisementid, collectionid, username, replace(dp,'"','') AS dp, policyid, policyassignmentid, meter_ruleid, client_sms_uniqueid, site_code, package_version, time_key, unique_updateid, productid, ci_assignmentid, objectid, object_type, sdm_typeid, sdm_type_version, update_source_uniqueid, collection_extended_propsid, wol_object_type, wol_batchid, machine_extended_propsid, wol_num_requests, unknown_machine, mac_addresses, smbiosid
    FROM "CM_360"."dbo"."v_StatusMessage" msg
    LEFT JOIN "CM_360"."dbo"."v_StatMsgWithInsStrings" strings
        ON msg.RecordID = strings.RecordID 
    LEFT JOIN 
        (SELECT RecordID,
         [400] AS [packageid],
         [401] AS [advertisementid],
         [402] AS [collectionid],
         [403] AS [username],
         [404] AS [dp],
         [405] AS [policyid],
         [406] AS [policyassignmentid],
         [407] AS [meter_ruleid],
         [408] AS [client_sms_uniqueid],
         [409] AS [site_code],
         [410] AS [package_version],
         [411] AS [time_key],
         [412] AS [unique_updateid],
         [413] AS [productid],
         [414] AS [ci_assignmentid],
         [415] AS [objectid],
         [416] AS [object_type],
         [417] AS [sdm_typeid],
         [418] AS [sdm_type_version],
         [419] AS [update_source_uniqueid],
         [420] AS [collection_extended_propsid],
         [421] AS [wol_object_type],
         [422] AS [wol_batchid],
         [423] AS [machine_extended_propsid],
         [424] AS [wol_num_requests],
         [425] AS [unknown_machine],
         [426] AS [mac_addresses],
         [427] AS [smbiosid]
        FROM 
            (SELECT RecordID,
         AttributeID,
         AttributeValue
            FROM "CM_360"."dbo"."v_StatMsgAttributes" ) AS s PIVOT ( MAX(AttributeValue) FOR AttributeID IN ([400], [401], [402], [403], [404], [405], [406], [407], [408], [409], [410], [411], [412], [413], [414], [415], [416], [417], [418], [419], [420], [421], [422], [423], [424], [425], [426], [427]) ) AS piv) AS test
                ON msg.RecordID = test.RecordID) AS querytest WHERE timestamp >= ?
ORDER BY timestamp ASC

*********************************************************************
[dbmon-tail://sccm/sccm_installed_software_dbinput]
*********************************************************************

SELECT timestamp,	ResourceID,	active,	SoftwarePropertiesHash0,	SoftwarePropertiesHashEx0,	product_name,	product_version,	product_publisher,	installation_date,	category,	family FROM (
SELECT timestamp,
         ResourceID,
         active=1,
         SoftwarePropertiesHash0,
         SoftwarePropertiesHashEx0,
         NormalizedName AS product_name,
         NormalizedVersion AS product_version,
         NormalizedPublisher AS product_publisher,
         InstallDate0 AS installation_date,
         CategoryName AS category,
         FamilyName AS family
    FROM "CM_360"."dbo"."v_GS_INSTALLED_SOFTWARE_CATEGORIZED"
    UNION ALL SELECT 
         "v_HS_INSTALLED_SOFTWARE".timestamp,
         "v_HS_INSTALLED_SOFTWARE".ResourceID,
         active=0,
         "v_HS_INSTALLED_SOFTWARE".SoftwareproPertiesHash0,
         "v_HS_INSTALLED_SOFTWARE".SoftwarePropertiesHashEx0,
         coalesce  ("v_LU_SoftwareList".CommonName, "v_HS_INSTALLED_SOFTWARE".ProductName0) AS product_name,
         coalesce ("v_LU_SoftwareList".CommonPublisher, "v_HS_INSTALLED_SOFTWARE".Publisher0) AS product_publisher,
         coalesce ("v_LU_SoftwareList".CommonVersion,
         "v_HS_INSTALLED_SOFTWARE".ProductVersion0) AS product_version,
         InstallDate0 AS installation_date,
         "v_LU_Family".FamilyName,
         "v_LU_Category".CategoryName
    FROM "v_HS_INSTALLED_SOFTWARE" 
    LEFT OUTER JOIN "v_LU_SoftwareHash"
        ON "v_LU_SoftwareHash".SoftwarePropertiesHash = "v_HS_INSTALLED_SOFTWARE".SoftwarePropertiesHash0 
    LEFT OUTER JOIN "v_LU_SoftwareList"
        ON "v_LU_SoftwareList".SoftwareID = "v_LU_SoftwareHash".SoftwareID
    INNER JOIN "v_LU_Category"
        ON "v_LU_Category".CategoryID = coalesce("v_LU_SoftwareList".CategoryID, 4892)
    INNER JOIN "v_LU_Family"
        ON "v_LU_Family".FamilyID = coalesce("v_LU_SoftwareList".FamilyID, 4891)
WHERE SoftwarePropertiesHash0 is NOT NULL
 ) AS software WHERE timestamp >= ?
ORDER BY timestamp ASC

*********************************************************************
[dbmon-tail://sccm/ta_sccm_malware_dbinput]

Will not execute - Empty table/views
*********************************************************************

with malware AS 
    (SELECT m.detectiontime AS [timestamp],
         'SystemCenterEndpointProtection' AS vendor_product, 'SecurityIncident' AS [type], 'MalwareInfection' AS action_type, m.resourceid, sys.Netbios_Name0 AS dest_name, sys.Resource_Domain_OR_Workgr0 AS dest_nt_domain, m.detectiontime, m.actiontime, m.ProductVersion AS product_version, m.detectionid,
        CASE
        WHEN m.DetectionSource = 0 THEN
        'unknown'
        WHEN m.DetectionSource = 1 THEN
        'user'
        WHEN m.DetectionSource = 2 THEN
        'system'
        WHEN m.DetectionSource = 3 THEN
        'realtime'
        WHEN m.DetectionSource = 4 THEN
        'ioav'
        WHEN m.DetectionSource = 5 THEN
        'nis'
        WHEN m.DetectionSource = 6 THEN
        'bho'
        END AS detection_source, m.UserName AS [user], m.Process AS target_process, m.Path AS file_path, ISNULL(metaData.Name,'unknown') AS [signature], IsNULL(sev.Severity,'unknown') AS severity, IsNULL(cat.Category,'invalid') AS category,
        CASE
        WHEN CleaningAction = 0 THEN
        'unknown'
        WHEN CleaningAction = 1 THEN
        'clean'
        WHEN CleaningAction = 2 THEN
        'quarantine'
        WHEN CleaningAction = 3 THEN
        'remove'
        WHEN CleaningAction = 6 THEN
        'allow'
        WHEN CleaningAction = 8 THEN
        'userdefined'
        WHEN CleaningAction = 9 THEN
        'noaction'
        WHEN m.CleaningAction = 10 THEN
        'block'
        END AS action_type,
        CASE
        WHEN CleaningAction = 0 THEN
        'unknown'
        WHEN CleaningAction = 1 THEN
        'blocked'
        WHEN CleaningAction = 2 THEN
        'deferred'
        WHEN CleaningAction = 3 THEN
        'blocked'
        WHEN CleaningAction = 6 THEN
        'allowed'
        WHEN CleaningAction = 8 THEN
        'unknown'
        WHEN CleaningAction = 9 THEN
        'allowed'
        WHEN m.CleaningAction = 10 THEN
        'blocked'
        END AS [action],
        CASE
        WHEN m.ActionSuccess =1 THEN
        'true'
        ELSE 'false'
        END AS action_result, m.ErrorCode AS action_error_code,
        CASE
        WHEN m.PendingActions & 4 <> 0 THEN
        'fullscan'
        WHEN m.PendingActions & 8 <> 0 THEN
        'reboot'
        WHEN m.PendingActions & 16 <> 0 THEN
        'settingsmodified'
        WHEN m.PendingActions & 32768 <> 0 THEN
        'systemsweeper'
        ELSE 'noaction'
        END AS pending_action
    FROM v_GS_Threats m
    LEFT JOIN v_R_System sys
        ON m.ResourceID = sys.ResourceID
    LEFT JOIN v_ThreatCatalog metadata
        ON m.ThreatID = metadata.ThreatID
    LEFT JOIN v_ThreatSeverities sev
        ON metaData.SeverityID=sev.SeverityID
    LEFT JOIN v_ThreatCategories cat
        ON metaData.CategoryID=cat.CategoryID )
SELECT *
FROM malware

*********************************************************************
[dbmon-tail://sccm/sccm_resource_dbinput]
*********************************************************************

SELECT timestamp,
         resourceid,
         resource_type,
         name,
         smsid,
         sitecode,
         domain,
         clientedition,
         clienttype,
         clientversion,
         isclient,
         isobsolete,
         isactive,
         isvirtualmachine,
         isaoaccapable,
         deviceowner,
         suppressautoprovision,
         isapproved,
         isblocked,
         isalwaysinternet,
         isinternetenabled,
         clientcerttype,
         username,
         lastclientchecktime,
         clientcheckpass,
         adsitename,
         userdomainname,
         adlastlogontime_epoch,
         clientremediationsuccess,
         clientactivestatus,
         laststatusmessage_epoch,
         lastpolicyrequest_epoch,
         lastddr_epoch,
         lasthardwarescan_epoch,
         lastsoftwarescan_epoch,
         lastmpservername,
         lastactivetime_epoch,
         cp_status,
         cp_latestprocessingattempt,
         cp_lastinstallationerror,
         deviceos,
         ep_deploymentstate,
         ep_deploymenterrorcode,
         ep_deploymentdescription,
         ep_policyapplicationstate,
         ep_policyapplicationerrorcode,
         ep_policyapplicationdescription,
         ep_enabled,
         ep_clientversion,
         ep_productstatus,
         ep_engineversion,
         ep_antivirusenabled,
         ep_antivirussignatureversion,
         ep_antivirussignatureupdatedatetime_epoch,
         ep_antispywareenabled,
         ep_antispywaresignatureversion,
         ep_antispywaresignatureupdatedatetime_epoch,
         ep_lastfullscandatetimestart_epoch,
         ep_lastfullscandatetimeend_epoch,
         ep_lastquickscandatetimestart_epoch,
         ep_lastquickscandatetimeend_epoch,
         ep_infectionstatus,
         ep_pendingfullscan,
         ep_pendingreboot,
         ep_pendingmanualsteps,
         ep_pendingofflinescan,
         ep_lastinfectiontime_epoch,
         ep_lastthreatname,
         unknown
FROM 
    (SELECT CONVERT(datetime,
        
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
        
         COALESCE("lastactivetime",
        
         "disc"."lastdiscoverytime")),
        
         DATENAME(TzOffset,
        
         SYSDATETIMEOFFSET()))) AS [timestamp],
        
         "machineid" AS "resourceid",
        
        
        CASE "architecturekey"
        WHEN 5 THEN
        'system'
        WHEN 2 THEN
        'unknown system'
        END AS "resource_type",
         "name",
         "smsid",
         "sitecode",
         "domain",
         "clientedition",
         "clienttype",
         "clientversion",
         "isclient",
         "isobsolete",
         "isactive",
         "isvirtualmachine",
         "isaoaccapable",
         "deviceowner",
         "suppressautoprovision",
         "isapproved",
         "isblocked",
         "isalwaysinternet",
         "isinternetenabled",
         "clientcerttype",
         "username",
         "lastclientchecktime",
         "clientcheckpass",
         "adsitename",
         "userdomainname",
         "adlastlogontime" AS "adlastlogontime_epoch",
         "clientremediationsuccess",
         "clientactivestatus",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "laststatusmessage"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "laststatusmessage_epoch",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "lastpolicyrequest"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "lastpolicyrequest_epoch",
         "lastddr" AS "lastddr_epoch",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "lasthardwarescan"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "lasthardwarescan_epoch",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "lastsoftwarescan"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "lastsoftwarescan_epoch",
         "lastmpservername",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         COALESCE("lastactivetime",
         "disc"."lastdiscoverytime")),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS lastactivetime_epoch,
         "cp_status",
         "cp_latestprocessingattempt",
         "cp_lastinstallationerror",
         "deviceos",
         "ep_deploymentstate",
         "ep_deploymenterrorcode",
         "ep_deploymentdescription",
         "ep_policyapplicationstate",
         "ep_policyapplicationerrorcode",
         "ep_policyapplicationdescription",
         "ep_enabled",
         "ep_clientversion",
         "ep_productstatus",
         "ep_engineversion",
         "ep_antivirusenabled",
         "ep_antivirussignatureversion",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "ep_antivirussignatureupdatedatetime"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "ep_antivirussignatureupdatedatetime_epoch",
         "ep_antispywareenabled",
         "ep_antispywaresignatureversion",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "ep_antispywaresignatureupdatedatetime"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "ep_antispywaresignatureupdatedatetime_epoch",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "ep_lastfullscandatetimestart"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "ep_lastfullscandatetimestart_epoch",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "ep_lastfullscandatetimeend"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "ep_lastfullscandatetimeend_epoch",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "ep_lastquickscandatetimestart"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "ep_lastquickscandatetimestart_epoch",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "ep_lastquickscandatetimeend"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "ep_lastquickscandatetimeend_epoch",
         "ep_infectionstatus",
         "ep_pendingfullscan",
         "ep_pendingreboot",
         "ep_pendingmanualsteps",
         "ep_pendingofflinescan",
         CONVERT(datetime,
         SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3),
         "ep_lastinfectiontime"),
         DATENAME(TzOffset,
         SYSDATETIMEOFFSET()))) AS "ep_lastinfectiontime_epoch",
         "ep_lastthreatname",
         "unknown"
    FROM "CM_360"."dbo"."v_CombinedDeviceResources"
    INNER JOIN 
        (SELECT "ResourceId",
         MAX("agenttime") AS "lastdiscoverytime"
        FROM "CM_360"."dbo"."v_agentdiscoveries"
        GROUP BY  "ResourceId") AS "disc"
            ON "disc"."ResourceId" = "CM_360"."dbo"."v_combineddeviceresources"."machineid") as query WHERE timestamp > ?
ORDER BY timestamp ASC



	

[richgalloway]

@nick405060 Is your problem resolved? If so, please accept an answer to help future readers.

[MuS]

Hi nick405060,

I don't want to claim any knowledge here, but this awesome answer https://answers.splunk.com/answers/657104/sccm-queries-for-integration-with-splunk.html of @rich7177 helped me a lot lately to get all the things from SCCM into ES.

Hope this helps ...

cheers, MuS

[iamarkaprabha]

I had onboarded SCCM DB into Splunk. Can you put some additional information for me to figure out the entire scene.

[nick405060]

Which driver did you use to connect to the DB?

[ahiguero]

not only what driver . there are db inputs that need to be updated and it would be nice to know how the queries have been update to make it work.

[inventsekar]

Not an answer to this question, but, yes,.. i was into the similar situation with SSL configuration, the documentation was worst, asked multiple questions here, no luck.

I am sure even if we follow up with Splunk support, it would take around 3 months and 2 hundred emails to and fro.

[gjanders]

Perhaps some more info or a very brief summary of what you have tried? I found https://splunkbase.splunk.com/app/2750/ which is really old, that app was using an older version of DB Connect.

IF the SQL queries are still valid you could just use a newer DB connect version, although I suspect SCCM could have changed as well since 2015!

[nick405060]

2750 is only compatible with 6.2. It will take some time, but I can go through and aggregate all the data on the problems me/the community has had into this one question. It's probably better to have it all in one place instead of 20+ unanswered questions all over SA.