All Apps and Add-ons

SCCM queries for integration with Splunk

I have MS-SCCM in my Customer Location and integrated with Splunk with the help of DB-Connect. Can anyone help with the queries for Client Status Messages, Client Software Inventory, Client Health and Client Endpoint Protection?

0 Karma
1 Solution

SplunkTrust
SplunkTrust

princemanto2580,

One problem I had with SCCM's DB inputs were that they were built for an old version of DB Connect, and using a style that's not even supported any more. So they break and won't pull in data.

If you are reasonably comfortable with SQL, you can open the existing queries from disk (I think in that version they're in the SCCM app's input.conf file?) and use those as a sort of pattern to rebuild them from scratch. Most of the query can be just copied and pasted into a new Database Input, but be careful - IIRC they use a wrapper select that you don't need, line endings may need fixing up, and other things.

Or if you have someone who is a bit more of a DBA, ask them for help - if they are even moderately familiar with SQL they should be able to make this happen pretty quickly - again, they weren't huge structural changes, just a handful of little detail changes to the queries.

To be honest - and maybe we did this at my old $job, I can't remember - it seems like this is a perfect place to build a MS-SQL View for each of those queries, get that working right inside SSMS (SQL Server Management Studio), then just select <field1>, <field2>, ... <fieldn> from <whatever_you_named_the_view> for the DB Connect side of things. That would move all the logic into the view so SQL Server can optimize it, and let Splunk just do a simple query instead of that beast that is there already. BUT if you don't have a DBA team, maybe this isn't the best way because it also moves the logic Splunk needs out of Splunk. So. Maybe not. Of course, this app is very unlikely to be updated.

If you are not so good with SQL and don't have access to someone who is, tonight or tomorrow morning I can likely grab the copies of at least several of those queries from my old job and paste them in here.

Anyway, I hope this helps! If you need those updated queries, just let me know as a comment to this answer and I will grab them when I can. Or someone else will paste them in as a new answer.

Happy Splunking,
-Rich

View solution in original post

Explorer

You'll have better results if you create new views in SQL with Ricapar's search's and then querying those new views with DBConnect. From that you can pull all the fields and the dashboards should work with little modification. Each dbconnect search I copied the details from the SCCM app inputs.conf data so that the sourcetype, source, and index so the transition is relatively seamless.

0 Karma

Explorer

these queries do not. you can add them by editing the 'conf' file but they cannot be validated through the interface so they do not work.

0 Karma

SplunkTrust
SplunkTrust

princemanto2580,

One problem I had with SCCM's DB inputs were that they were built for an old version of DB Connect, and using a style that's not even supported any more. So they break and won't pull in data.

If you are reasonably comfortable with SQL, you can open the existing queries from disk (I think in that version they're in the SCCM app's input.conf file?) and use those as a sort of pattern to rebuild them from scratch. Most of the query can be just copied and pasted into a new Database Input, but be careful - IIRC they use a wrapper select that you don't need, line endings may need fixing up, and other things.

Or if you have someone who is a bit more of a DBA, ask them for help - if they are even moderately familiar with SQL they should be able to make this happen pretty quickly - again, they weren't huge structural changes, just a handful of little detail changes to the queries.

To be honest - and maybe we did this at my old $job, I can't remember - it seems like this is a perfect place to build a MS-SQL View for each of those queries, get that working right inside SSMS (SQL Server Management Studio), then just select <field1>, <field2>, ... <fieldn> from <whatever_you_named_the_view> for the DB Connect side of things. That would move all the logic into the view so SQL Server can optimize it, and let Splunk just do a simple query instead of that beast that is there already. BUT if you don't have a DBA team, maybe this isn't the best way because it also moves the logic Splunk needs out of Splunk. So. Maybe not. Of course, this app is very unlikely to be updated.

If you are not so good with SQL and don't have access to someone who is, tonight or tomorrow morning I can likely grab the copies of at least several of those queries from my old job and paste them in here.

Anyway, I hope this helps! If you need those updated queries, just let me know as a comment to this answer and I will grab them when I can. Or someone else will paste them in as a new answer.

Happy Splunking,
-Rich

View solution in original post

I have seen the link you are referring, but I really can't figure out the queries as not much experience on SQL.

https://github.com/Ricapar/splunk-sccm/blob/master/default/inputs.conf

It will be a great help if you can share the working queries over here.

0 Karma

SplunkTrust
SplunkTrust

Test them, and obviously double-check the index, etc... Or - I'd really just recommend hacking out the SQL bits and using those as your query, but then build everything else using your own DB Connect. That way you know all the other bits will be set up right for you.

I hope these help. If they don't, reply back that ... I have failed. 😞

(LOL, it'll be OK even if they're broken! We can figure them out.)

0 Karma

One thing is sure, without having SQL skill never try to rectify the error of these queries.

3 out of 5 is done but it was kind of brainstorming to make the correct query. Only sccm:malware and sccm:status_message are in progress.

But really appreciate and thanks for sharing the queries, it helps us to choose the right table_name.

0 Karma

Motivator

Hi Princemanto2580, 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/ 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.

It will be really helpful if you can share the steps which you have done to configure the app to pull the SCCM information.

0 Karma

Did you complete DB-Connect configuration?

This is the first change need to complete.

0 Karma

Motivator

How do you configure it correctly?

0 Karma

SplunkTrust
SplunkTrust

And ...

[sccm_resource_dbinput]
connection = <PUT YOUR SERVER HERE>
host = sccm
index = sccm
input_timestamp_column_fullname = (001) .timestamp.datetime
input_timestamp_column_name = timestamp
interval = 600
max_rows = 10000
mode = advanced
query = 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 v_combineddeviceresources\
INNER JOIN (\
  select \
  ResourceId, \
  max(agenttime) as lastdiscoverytime\
from v_agentdiscoveries group by ResourceId) as disc\
  on disc.ResourceId = v_combineddeviceresources.machineid \
\
where convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), COALESCE(lastactivetime, disc.lastdiscoverytime)), DATENAME(TzOffset, SYSDATETIMEOFFSET())))  > ?\
ORDER BY convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), COALESCE(lastactivetime, disc.lastdiscoverytime)), DATENAME(TzOffset, SYSDATETIMEOFFSET())))
sourcetype = sccm:resource
tail_rising_column_name = timestamp
ui_query_mode = advanced
0 Karma

SplunkTrust
SplunkTrust

More...

[sccm_agent_discoveries_dbinput]
connection = <PUT YOUR SERVER HERE>
host = sccm
index = sccm
input_timestamp_column_fullname = (001) v_agentdiscoveries.agenttime.datetime
input_timestamp_column_name = agenttime
interval = 300
max_rows = 10000
mode = advanced
query = select \
  da.agenttime,\
  disc.resourceid, \
  disc.netbios_name0 as name, \
  da.agentname\
from v_r_system disc\
left join v_agentdiscoveries da\
  on da.resourceid = disc.resourceid\
where AgentTime > ?\
ORDER BY AgentTime
sourcetype = sccm:discovery
tail_rising_column_name = agenttime
ui_query_mode = advanced
0 Karma

SplunkTrust
SplunkTrust

And ...

[sccm_installed_software_dbinput]
connection = <PUT YOUR SERVER HERE>
host = sccm
index = sccm
input_timestamp_column_fullname = (001) .timestamp.datetime
input_timestamp_column_name = timestamp
interval = 900
max_rows = 10000
mode = advanced
query = ;WITH MyQuery 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 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 v_hs_installed_software s\
left outer join  v_lu_softwarehash sh\
   on sh.softwarepropertieshash = s.softwarepropertieshash0\
left outer join v_lu_softwarelist sl\
  on sl.softwareid = sh.softwareid\
inner join v_lu_category as cat\
  on cat.categoryid = coalesce(sl.categoryid, 4892)\
inner join v_lu_family as fam\
  on fam.familyid = coalesce(sl.familyid, 4891)\
where softwarepropertieshash0 is not null\
) \
\
SELECT * from MyQuery WHERE [timestamp] > ?\
ORDER BY [timestamp]\
\

sourcetype = sccm:installed_software
tail_rising_column_name = timestamp
ui_query_mode = advanced
0 Karma

SplunkTrust
SplunkTrust

And another

[sccm_status_message_dbinput]
connection = <PUT YOUR SERVER HERE>
host = sccm
index = sccm_status
input_timestamp_column_fullname = (002) .timestamp.datetime
input_timestamp_column_name = timestamp
interval = 300
max_rows = 10000
mode = advanced
query = ;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 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 v_StatusMessage  msg\
left join v_StatMsgWithInsStrings  strings\
  on msg.recordid = strings.recordid\
left join attr\
  on msg.recordid = attr.recordid\
  WHERE msg.recordid > ?\
  ORDER BY msg.recordid
sourcetype = sccm:status_message
tail_rising_column_name = recordid
ui_query_mode = advanced
0 Karma

SplunkTrust
SplunkTrust

Here's one

[ta_sccm_malware_dbinput]
connection = <PUT YOUR SERVER HERE>
host = sccm
index = sccm
input_timestamp_column_fullname = (001) v_GS_Threats.timestamp.datetime
input_timestamp_column_name = timestamp
interval = 300
max_rows = 10000
mode = advanced
query = 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     \
                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 N'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 N'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 \
WHERE m.detectiontime > ?\
ORDER BY m.detectiontime desc
sourcetype = sccm:malware
tail_rising_column_name = timestamp
ui_query_mode = advanced
0 Karma

Forgot to add these details:

Work was done so far: Successfully configure the DB-Connect on Splunk to read the entire SCCM-DB.
SCCM Version: 2012 R2
Splunk Version: 6.5.0 (on premise)

0 Karma