How to configure the SCCM app to pull the sccm logs in splunk?

[Hemnaath]

Hi All, We have a customer requirement to get the SCCM information in splunk, so to do this, we have downloaded the SCCM app from splunk base https://splunkbase.splunk.com/app/2750/ link text and we are using splunk 6.6.1 enterprise splunk instances. The app was successfully installed into the splunk environment, but we are not sure how to configure this app to get the required data from the SCCM. Kindly guide me on this.

SCCM version 1.0
Splunk enterprise version 6.6.1

[nick405060]

I am pulling SCCM logs like this:

[default]
host = ABCSCCM01

[monitor://E:\Microsoft Configuration Manager\Logs\*.log]
sourcetype = sccm_log_raw
index = sccm

And am also querying v_FullCollectionMembership and v_AssignmentState_Combined via DBConnect (for us it's just MS-SQL Server Using jTDS Driver) but there are obviously way more tables you can pull from.

[ahiguero]

no updates we were never able to make it work.

[jlucius]

Hello,

Any Update on this? I also want to integrate the SCCM with Splunk and I am searching for a starting point. It would be perfect if anybody could share their work, so more people can build upon it and improve it. Would be nice to have it e.g. on Github so people can fork / modify / improve it.

[nick405060]

I'm about to post an answer here that may give you some updates (I'm pulling logs + doing sql queries)

[ahiguero]

Hello Nick

we are trying to modify the existing app to work with the current version of splunk, but it is a pain to be honest

[nick405060]

Ugh. If you want, you're welcome to post what you're trying to do so far, and I can also try some experimenting with it on my end.

[leinadf]

It's kind of tricky the configuration, of the app because no one has given any maintenance, but you need the DB connect app (if is a distributed environment install it in the Indexer), after that you need to create a connection to the SCCM database, until this part it's piece of cake, now the tricky part.

1. In the DB connect you have to recreate the queries and give the same treatment like the rising columns, intervals, sourcetypes, indexes and so on.
2. Once you've created the queries in the db connect app, you'll be able to store the information in the indexer. Look carefully to the
3. The lookups the app uses are queries to create the collections, I executed the queries to create the CSV output and moved it to the right PATH of the app.

After this point the app started to show right information, except the Endpoint protection part, I'm still troubleshooting that.

These are the queries I used to get information from SCCM, I know there is an easier way just haven't found it yet.

[sccm_agent_discoveries_dbinput]
select
"da"."agenttime",
"disc"."resourceid",
"disc"."netbios_name0" as "name",
"da"."agentname"
from "CM_INF"."dbo"."v_r_system" "disc"
left join "CM_INF"."dbo"."v_agentdiscoveries" "da"
on "da"."resourceid" = "disc"."resourceid"
Where agenttime >?
order by agenttime asc

[sccm_installed_software_dbinput]
with "software" as (
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_INF"."dbo"."v_gs_installed_software_categorized"
UNION ALL
select
"s".[timestamp],
"s"."resourceid",
active=0,
"s"."softwarepropertieshash0",
"s"."softwarepropertieshashex0",
coalesce ("sl"."commonname", "s"."productname0") as "product_name",
coalesce ("sl"."commonpublisher", "s"."publisher0") as "product_publisher",
coalesce ("sl"."commonversion", "s"."productversion0") as "product_version",
"installdate0" as "installation_date",
"fam"."familyname",
"cat"."categoryname"
from "CM_INF"."dbo"."v_hs_installed_software" "s"
left outer join  "CM_INF"."dbo"."v_lu_softwarehash" "sh"
on "sh"."softwarepropertieshash" = "s"."softwarepropertieshash0"
left outer join "CM_INF"."dbo"."v_lu_softwarelist" "sl"
on "sl"."softwareid" = "sh"."softwareid"
inner join "CM_INF"."dbo"."v_lu_category" as "cat"
on "cat"."categoryid" = coalesce("sl"."categoryid", 4892)
inner join "CM_INF"."dbo"."v_lu_family" as "fam"
on "fam"."familyid" = coalesce("sl"."familyid", 4891)
where "softwarepropertieshash0" is not null)
select *
from "software"
WHERE [timestamp] > ?
ORDER BY [timestamp] ASC

[sccm_resource_dbinput]
WITH "s" as (
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_INF"."dbo"."v_combineddeviceresources"
INNER JOIN (
select
"ResourceId",
max("agenttime") as "lastdiscoverytime"
from "CM_INF"."dbo"."v_agentdiscoveries" group by "ResourceId"
) as "disc"
on "disc"."ResourceId" = "CM_INF"."dbo"."v_combineddeviceresources"."machineid")
select * from 
"s"
where [timestamp] >?
order by [timestamp]

[sccm_status_message_dbinput]
WITH "attr" AS
(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_INF"."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"
)
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_INF"."dbo"."v_StatusMessage"  "msg"
left join "CM_INF"."dbo"."v_StatMsgWithInsStrings"  "strings"
on "msg"."recordid" = "strings"."recordid"
left join "attr"
on "msg"."recordid" = "attr"."recordid"
WHERE "msg"."recordid" >?
ORDER BY "msg"."recordid" asc

[ta_sccm_malware_dbinput]
with "malware" AS 
    (SELECT "m"."detectiontime" AS [timestamp],
         'SystemCenterEndpointProtection' AS "vendor_product",
         'SecurityIncident' AS [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 "m"."DetectionSource"
        WHEN 0 THEN 'unknown'
        WHEN 1 THEN 'user'
        WHEN 2 THEN 'system'
        WHEN 3 THEN 'realtime'
        WHEN 4 THEN 'ioav'
        WHEN 5 THEN 'nis'
        WHEN 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
        "CleaningAction"
        WHEN 0 THEN
        'unknown'
        WHEN 1 THEN
        'clean'
        WHEN 2 THEN
        'quarantine'
        WHEN 3 THEN
        'remove'
        WHEN 6 THEN
        'allow'
        WHEN 8 THEN
        'userdefined'
        WHEN 9 THEN
        'noaction'
        WHEN 10 THEN
        N'block'
        END AS "action_type",
        CASE 
        "CleaningAction"
        WHEN  0 THEN
        'unknown'
        WHEN  1 THEN
        'blocked'
        WHEN 2 THEN
        'deferred'
        WHEN 3 THEN
        'locked'
        WHEN 6 THEN
        'allowed'
        WHEN 8 THEN
        'unknown'
        WHEN 9 THEN
        'allowed'
        WHEN 10 THEN
        N'blocked'
        END AS [action],
        CASE 
        "m"."ActionSuccess"
        WHEN  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 "CM_INF"."dbo"."v_GS_Threats" "m"
    LEFT JOIN "CM_INF"."dbo"."v_R_System" "sys"
        ON "m"."ResourceID" = "sys"."ResourceID"
    LEFT JOIN "CM_INF"."dbo"."v_ThreatCatalog" "metadata"
        ON "m"."ThreatID" = "metadata"."ThreatID"
    LEFT JOIN "CM_INF"."dbo"."v_ThreatSeverities" "sev"
        ON "metaData"."SeverityID"="sev"."SeverityID"
    LEFT JOIN "CM_INF"."dbo"."v_ThreatCategories" "cat"
        ON "metaData"."CategoryID"="cat"."CategoryID")
SELECT *
FROM "malware"
where timestamp >?
order by timestamp

[nick405060]

Thanks! From doing a bit of research, it seems Splunk paid the creator of the app 30k in 2015 to come up with it, and then immediately deprecated the DB Connect version that worked with it rendering it useless. With all the effort you had to put into making it work in 2018... I feel like it's not fair whatsoever for Splunk to claim that they "offer a SCCM app for Splunk"

I might try what you did one day, but at this point just pumping in the raw SCCM logs with a UF is going to have to do the trick for me. Thanks for the solution once again though!

[ahiguero]

hello Nick

can you share your inputs.conf file to see what inputs you are tacking form SCCM? are you taking all the logs?

[nick405060]

File is just

[default]
host = ABCSCCM01

[monitor://E:\Microsoft Configuration Manager\Logs\*.log]
sourcetype = sccm_log_raw
index = sccm

Definitely let me know if you have a better approach!

[ahiguero]

got it so you are basically dumping every single log on that directory. another question. any special rules on that sourcetype?

We are currently analyzing the boarding but we are hitting the same issues that you have. the connector to sccm does not work so the next step is working with the logs themselves

thanks in advance nick

Alvaro

[nick405060]

Yep, 79 logs, mostly policypv, statmgr, SMSProv, ddm, PkgXferMgr, and sinvproc.

No special rules yet, I haven't done much with the input besides grabbing the logs. Mostly due to my lack of familiar with SCCM logs (it would be much nicer if the app worked and the data was easier to dive into) but it's still on my todo list and on my firm's (we need mostly patching data from SCCM to get into Splunk, and eventually other SCCM data), so if you don't mind please let me know if you end up doing anything cool with your SCCM log ingestion

[ahiguero]

you got it.. any plans on pulling sccm logs from the clients themselves?

[nick405060]

nah, not as of right now

[nick405060]

@ahiguero any updates on how you ended up configuring SCCM? I'm still struggling because I need patching information and other information that is not in the logs I'm pulling

[adonio]

hello there,

kindly read the README file for this app
looks like its originally designed to work with DB Connect 1.1.6 - currently depreciated.
here are the installation notes form the apps README file:

### INSTALLATION
> Note:
> Depending on the size and age of your SCCM environment you may choose to change the frequency of default intervals, enable tail.follow.only in the db-tail input, disable unwanted event collections, or modify the sccm_status retention. Know your SCCM table sizes and Splunk® Enterprise licensing limits before proceeding.

1. Install the App for SCCM app and restart Splunk®
2. Assign the sccm_user role to the appropriate users
3. Add the database connection to the app
 1. Open the app and navigate to Settings | External Databases
 2. Click New
 3. On the Add New screen, set the name to sccm, fill in the sccm database connection information, and click Save. User a service account with read only permissions on the SCCM database.
4. Append the contents of inputs.conf into dbx\local\inputs.conf file
5. Modify the db-tail database input intervals if needed.
6. Restart Splunk® to start collecting data
7. To immediately start using all features of the app, manually run the included collection saved reports

you will also find the developer contact there.
my assumption is, after reading a little on the app, i s that you can leverage DB Connect and read data form SCCM DB and index into splunk.
write your dashboards and reports as you see fit or leverage prebuilt ideas within that app

hope it helps

[nick405060]

I have no settings option within the app, even after adding the sccm_user role and restarting

Probably because I'm using 6.3 Let me know if you have any luck getting it to work with a non-6.2 version of Splunk (in your case 6.6)

[Hemnaath]

Hi Adonio, thanks for sharing the location to find the document and contact information. I have gone through the above read me document, but its not that clear, so is there any other document where it has been explained in details.

[adonio]

@Hemnaath
nothing that i am aware of.
but yet, i just looked at the app and read the README file.
by looking at the app, it seems like it connects to the SCCM database leveraging DB Connect input.
i am positive that with some exploration you can create your own DB input and enjoy the SCCM data.

[nick405060]

But the real question is HOW do you create the correct DB input