inputs.conf

# INSTRUCTIONS
# 1. Modify index locations if needed
# 2. Paste into dbx inputs.conf

#############
# WARNINGS
# * Do not replace apps\dbx\local\inputs.conf FILE or delete the existing batches input. Doing so will cause you to have a bad time.
# * If you have a very large ConfigMgr environment the initial imports can be very large.
# * If you have a very large ConfigMgr environment you may need to create indexes on the HIST tables to improve performance
#############

[dbmon-tail://sccm/ta_sccm_malware_dbinput]
host = sccm
index = sccm
interval = 5m
output.format = kv
output.timestamp = 1
output.timestamp.column = timestamp
output.timestamp.format = yyyy-MM-dd HH:mm:ss.SSS
query = with malware as (\r\nSELECT \r\n  m.detectiontime as [timestamp],\r\n  'SystemCenterEndpointProtection' AS vendor_product,\r\n  'SecurityIncident' AS [type],\r\n  --'MalwareInfection' AS action_type,\r\n  m.resourceid, \r\n  sys.Netbios_Name0 as dest_name,\r\n  sys.Resource_Domain_OR_Workgr0 as dest_nt_domain,\r\n  m.detectiontime, \r\n  m.actiontime, \r\n  m.ProductVersion as product_version, \r\n  m.detectionid, \r\n  CASE  \r\n    WHEN m.DetectionSource = 0 THEN 'unknown' \r\n    WHEN m.DetectionSource = 1 THEN 'user' \r\n    WHEN m.DetectionSource = 2 THEN 'system' \r\n    WHEN m.DetectionSource = 3 THEN 'realtime' \r\n    WHEN m.DetectionSource = 4 THEN 'ioav' \r\n    WHEN m.DetectionSource = 5 THEN 'nis' \r\n    WHEN m.DetectionSource = 6 THEN 'bho'  \r\n  END AS detection_source,\r\n  m.UserName as [user],\r\n  m.Process AS target_process, \r\n  m.Path AS file_path, \r\n  ISNULL(metaData.Name,'unknown') AS [signature], \r\n  IsNULL(sev.Severity,'unknown') AS severity, \r\n  IsNULL(cat.Category,'invalid') AS category,\r\n  CASE  \r\n    WHEN CleaningAction = 0 THEN 'unknown' \r\n    WHEN CleaningAction = 1 THEN 'clean' \r\n    WHEN CleaningAction = 2 THEN 'quarantine' \r\n    WHEN CleaningAction = 3 THEN 'remove' \r\n    WHEN CleaningAction = 6 THEN 'allow' \r\n    WHEN CleaningAction = 8 THEN 'userdefined' \r\n    WHEN CleaningAction = 9 THEN 'noaction' \r\n    WHEN m.CleaningAction = 10 THEN N'block' \r\n  END AS action_type, \r\n  CASE  \r\n    WHEN CleaningAction = 0 THEN 'unknown' \r\n    WHEN CleaningAction = 1 THEN 'blocked' \r\n    WHEN CleaningAction = 2 THEN 'deferred' \r\n    WHEN CleaningAction = 3 THEN 'blocked' \r\n    WHEN CleaningAction = 6 THEN 'allowed' \r\n    WHEN CleaningAction = 8 THEN 'unknown' \r\n    WHEN CleaningAction = 9 THEN 'allowed' \r\n    WHEN m.CleaningAction = 10 THEN N'blocked' \r\n  END AS [action],\r\n  CASE       \r\n    WHEN m.ActionSuccess =1 THEN 'true' \r\n    ELSE 'false' \r\n  END AS action_result, \r\n  m.ErrorCode AS action_error_code, \r\n  CASE \r\n    WHEN m.PendingActions & 4 <> 0 THEN 'fullscan' \r\n    WHEN m.PendingActions & 8 <> 0 THEN 'reboot' \r\n    WHEN m.PendingActions & 16 <> 0 THEN 'settingsmodified' \r\n    WHEN m.PendingActions & 32768 <> 0 THEN 'systemsweeper' \r\n    ELSE 'noaction' \r\n  END AS pending_action\r\nFROM v_GS_Threats m\r\nLEFT JOIN v_R_System sys on m.ResourceID = sys.ResourceID\r\nLEFT JOIN v_ThreatCatalog metadata on m.ThreatID = metadata.ThreatID\r\nLEFT JOIN v_ThreatSeverities sev on metaData.SeverityID=sev.SeverityID \r\nLEFT JOIN v_ThreatCategories cat on metaData.CategoryID=cat.CategoryID \r\n)\r\nselect * from malware\r\n{{WHERE m.$rising_column$ > ?}}
sourcetype = sccm:malware
tail.rising.column = timestamp

[dbmon-tail://sccm/sccm_status_message_dbinput]
host = sccm
index = sccm_status
interval = 5m
output.format = kv
output.timestamp = 1
output.timestamp.column = timestamp
output.timestamp.format = yyyy-MM-dd HH:mm:ss.SSS
query = WITH attr AS\r\n(SELECT recordid,\r\n	[400] AS [packageid],\r\n	[401] AS [advertisementid],\r\n	[402] AS [collectionid],\r\n	[403] AS [username],\r\n	[404] AS [dp],\r\n	[405] AS [policyid],\r\n	[406] AS [policyassignmentid],\r\n	[407] AS [meter_ruleid],\r\n	[408] AS [client_sms_uniqueid],\r\n	[409] AS [site_code],\r\n	[410] AS [package_version],\r\n	[411] AS [time_key],\r\n	[412] AS [unique_updateid],\r\n	[413] AS [productid],\r\n	[414] AS [ci_assignmentid],\r\n	[415] AS [objectid],\r\n	[416] AS [object_type],\r\n	[417] AS [sdm_typeid],\r\n	[418] AS [sdm_type_version],\r\n	[419] AS [update_source_uniqueid],\r\n	[420] AS [collection_extended_propsid],\r\n	[421] AS [wol_object_type],\r\n	[422] AS [wol_batchid],\r\n	[423] AS [machine_extended_propsid],\r\n	[424] AS [wol_num_requests],\r\n	[425] AS [unknown_machine],\r\n	[426] AS [mac_addresses],\r\n	[427] AS [smbiosid]\r\nFROM \r\n	(Select RecordId, AttributeId, AttributeValue from v_StatMsgAttributes) as s\r\n	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\r\n)\r\nSELECT msg.recordid,\r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), msg.[time] ), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) As [timestamp],\r\n  msg.[time] as [timestamputc],\r\n  msg.messageid,\r\n  severity = \r\n    CASE msg.severity\r\n      WHEN 1073741824 THEN 'informational'\r\n	  WHEN -1073741824 THEN 'error'\r\n	  WHEN -2147483648 THEN 'warning'\r\n    END,\r\n  msg.severity AS severityid,\r\n  msg.machinename,\r\n  msg.sitecode,\r\n  msg.modulename,\r\n  msg.component,\r\n  replace(strings.insstring1,'"','') as string1,\r\n  replace(strings.insstring2,'"','') as string2,\r\n  replace(strings.insstring3,'"','') as string3,\r\n  replace(strings.insstring4,'"','') as string4,\r\n  replace(strings.insstring5,'"','') as string5,\r\n  replace(strings.insstring6,'"','') as string6,\r\n  replace(strings.insstring7,'"','') as string7,\r\n  replace(strings.insstring8,'"','') as string8,\r\n  replace(strings.insstring9,'"','') as string9,\r\n  replace(strings.insstring10,'"','') as string10,\r\n  packageid, \r\n  advertisementid, \r\n  collectionid, \r\n  username, \r\n  replace(dp,'"','') as dp, \r\n  policyid, \r\n  policyassignmentid, \r\n  meter_ruleid, \r\n  client_sms_uniqueid, \r\n  site_code, \r\n  package_version, \r\n  time_key, \r\n  unique_updateid, \r\n  productid, \r\n  ci_assignmentid, \r\n  objectid, \r\n  object_type, \r\n  sdm_typeid, \r\n  sdm_type_version, \r\n  update_source_uniqueid, \r\n  collection_extended_propsid, \r\n  wol_object_type, \r\n  wol_batchid, \r\n  machine_extended_propsid, \r\n  wol_num_requests, \r\n  unknown_machine, \r\n  mac_addresses, \r\n  smbiosid \r\nfrom v_StatusMessage  msg\r\nleft join v_StatMsgWithInsStrings  strings\r\n  on msg.recordid = strings.recordid\r\nleft join attr\r\n  on msg.recordid = attr.recordid\r\n{{WHERE msg.$rising_column$ > ?}}
sourcetype = sccm:status_message
table = ta_sccm_statusmsg_dbinput
tail.rising.column = recordid

[dbmon-tail://sccm/sccm_installed_software_dbinput]
host = sccm
index = sccm
interval = 15m
output.format = kv
output.timestamp = 1
output.timestamp.column = timestamp
output.timestamp.format = yyyy-MM-dd HH:mm:ss.SSS
query = with software as (\r\nselect\r\n  [timestamp],\r\n  resourceid,\r\n  active=1,\r\n  softwarepropertieshash0,\r\n  softwarepropertieshashex0,\r\n  normalizedname as product_name,\r\n  normalizedversion as product_version,\r\n  normalizedpublisher as product_publisher,\r\n  InstallDate0 as installation_date,\r\n  categoryname as category,\r\n  familyname as family\r\nfrom v_gs_installed_software_categorized\r\nUNION ALL\r\nselect\r\n  s.[timestamp],\r\n  s.resourceid,\r\n  active=0,\r\n  s.softwarepropertieshash0,\r\n  s.softwarepropertieshashex0,\r\n  coalesce (sl.commonname, s.productname0) as product_name,\r\n  coalesce (sl.commonpublisher, s.publisher0) as product_publisher,\r\n  coalesce (sl.commonversion, s.productversion0) as product_version,\r\n  installdate0 as installation_date,\r\n  fam.familyname,\r\n  cat.categoryname\r\nfrom v_hs_installed_software s\r\nleft outer join  v_lu_softwarehash sh\r\n   on sh.softwarepropertieshash = s.softwarepropertieshash0\r\nleft outer join v_lu_softwarelist sl\r\n  on sl.softwareid = sh.softwareid\r\ninner join v_lu_category as cat\r\n  on cat.categoryid = coalesce(sl.categoryid, 4892)\r\ninner join v_lu_family as fam\r\n  on fam.familyid = coalesce(sl.familyid, 4891)\r\nwhere softwarepropertieshash0 is not null)\r\nselect *\r\nfrom software\r\n{{WHERE $rising_column$ > ?}}\r\n
sourcetype = sccm:installed_software
tail.rising.column = timestamp

[dbmon-tail://sccm/sccm_agent_discoveries_dbinput]
host = sccm
index = sccm
interval = 15m
output.format = kv
output.timestamp = 1
output.timestamp.column = agenttime
output.timestamp.format = yyyy-MM-dd HH:mm:ss.SSS
query = select \r\n  da.agenttime,\r\n  disc.resourceid, \r\n  disc.netbios_name0 as name, \r\n  da.agentname\r\nfrom v_r_system disc\r\nleft join v_agentdiscoveries da\r\n  on da.resourceid = disc.resourceid\r\n{{WHERE $rising_column$ > ?}}
sourcetype = sccm:discovery
tail.rising.column = agenttime
table = sccm_agent_discoveries_dbinput

[dbmon-tail://sccm/sccm_resource_dbinput]
host = sccm
index = sccm
interval = 10
output.format = kv
output.timestamp = 1
output.timestamp.column = timestamp
output.timestamp.format = yyyy-MM-dd HH:mm:ss.SSS
query = WITH s as (\r\nSELECT \r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), COALESCE(lastactivetime, disc.lastdiscoverytime)), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as [timestamp],\r\n  machineid as resourceid, \r\n  CASE architecturekey\r\n    WHEN 5 THEN 'system'\r\n	WHEN 2 THEN 'unknown system'\r\n  END as resource_type, \r\n  name, \r\n  smsid, \r\n  sitecode, \r\n  domain, \r\n  clientedition, \r\n  clienttype, \r\n  clientversion, \r\n  isclient, \r\n  isobsolete, \r\n  isactive, \r\n  isvirtualmachine, \r\n  isaoaccapable, \r\n  deviceowner, \r\n  suppressautoprovision, \r\n  isapproved, \r\n  isblocked, \r\n  isalwaysinternet, \r\n  isinternetenabled, \r\n  clientcerttype, \r\n  username, \r\n  lastclientchecktime, \r\n  clientcheckpass, \r\n  adsitename, \r\n  userdomainname, \r\n  adlastlogontime as adlastlogontime_epoch, \r\n  clientremediationsuccess, \r\n  clientactivestatus, \r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), laststatusmessage), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as laststatusmessage_epoch,\r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), lastpolicyrequest), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as lastpolicyrequest_epoch,\r\n  lastddr as lastddr_epoch, \r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), lasthardwarescan), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as lasthardwarescan_epoch,\r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), lastsoftwarescan), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as lastsoftwarescan_epoch,\r\n  lastmpservername, \r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), COALESCE(lastactivetime, disc.lastdiscoverytime)), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as lastactivetime_epoch,\r\n  cp_status, \r\n  cp_latestprocessingattempt, \r\n  cp_lastinstallationerror, \r\n  deviceos, \r\n  ep_deploymentstate, \r\n  ep_deploymenterrorcode, \r\n  ep_deploymentdescription, \r\n  ep_policyapplicationstate, \r\n  ep_policyapplicationerrorcode, \r\n  ep_policyapplicationdescription, \r\n  ep_enabled, \r\n  ep_clientversion, \r\n  ep_productstatus, \r\n  ep_engineversion, \r\n  ep_antivirusenabled, \r\n  ep_antivirussignatureversion, \r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), ep_antivirussignatureupdatedatetime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as ep_antivirussignatureupdatedatetime_epoch,\r\n  ep_antispywareenabled, \r\n  ep_antispywaresignatureversion, \r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), ep_antispywaresignatureupdatedatetime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as ep_antispywaresignatureupdatedatetime_epoch,\r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), ep_lastfullscandatetimestart), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as ep_lastfullscandatetimestart_epoch,\r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), ep_lastfullscandatetimeend), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as ep_lastfullscandatetimeend_epoch,\r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), ep_lastquickscandatetimestart), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as ep_lastquickscandatetimestart_epoch,\r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), ep_lastquickscandatetimeend), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as ep_lastquickscandatetimeend_epoch,\r\n  ep_infectionstatus, \r\n  ep_pendingfullscan, \r\n  ep_pendingreboot, \r\n  ep_pendingmanualsteps, \r\n  ep_pendingofflinescan, \r\n  convert(datetime,SWITCHOFFSET(CONVERT(DATETIMEOFFSET(3), ep_lastinfectiontime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as ep_lastinfectiontime_epoch,\r\n  ep_lastthreatname, \r\n  unknown \r\nFROM v_combineddeviceresources\r\nINNER JOIN (\r\n  select \r\n  ResourceId, \r\n  max(agenttime) as lastdiscoverytime\r\nfrom v_agentdiscoveries group by ResourceId\r\n) as disc\r\n  on disc.ResourceId = v_combineddeviceresources.machineid)\r\nselect * from s\r\n{{WHERE $rising_column$ > ?}}
sourcetype = sccm:resource
tail.rising.column = timestamp
table = sccm_resource_dbinput