You could use field extraction or the rex command. | rex field="Activity Result" "Activity Result: {"IsProductValidated":"false","ErrorCodes":\[{"errorCode":"(?P<errorCode>\S+)","errorMessage":"(?P<errorMessage>.+)."}]}"  ... View more

I brought up the certs to our ACAS admin, and they confirmed they did update the tenable certs.  They gave me the certs, but I'm not sure where to put them to get it to work. ... View more

I posted an answer in another page that may help. I rewrote all the DB queries so it's all native to the SCCM app. Re: Splunk community NEEDS an answer for getting S... - Splunk Community ... View more

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      ... View more

We were never able to get SSO back up with Splunk, but its currently on our to do list. If I figure it out I'll let you know. ... View more

No problem, let us know if you get it working or not. ... View more

Hi Will, When I inherited our Splunk instance, the previous admin had said that he copied the windows data to Linux, and was having no success, and we've been either logging in with a username and password and trying to configure it and doing it via the CLI. ... View more

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. ... View more

I recently got Splunk and SCCM to play nice together. What I ended up doing was taking Ricapars searches, and creating new views in my SQL server using them, because putting the searches into DBConnect wouldn't allow me to save them. From there I created new dbconnect searches using just those queries and rebuilt the dashboards. I'm working on getting the permissions from the company to publish the app, so hopefully that will help. ... View more

You shouldn't need an SA account. I modified it because I was getting erros because of the lase eval statement to: index=_internal source=*license_usage.log type="RolloverSummary" earliest=@d | stats sum(b) AS used max(stacksz) AS quota by _time | eval usedGB=round(used/1024/1024/1024,3) | eval quotaGB=round(quota/1024/1024/1024,3) | table _time, usedGB, quotaGB | eval percentage=round(usedGB / totalGB, 1)*100 | eval usage = usedGB . " ("%)" | fields _time, usedGB, quotaGB, usage | where 'usedGB' > 17 From Splunk, enter the above modified search from tiagofbmm's, and adjust the 17 at the end to whatever usage you want it to alert on, and run it. Then click on Save As in the right hand corner of the New Search window and save it as an alert. Set up your alert preferences and save, and it should trigger whenever you set. ... View more

I recently got the SCCM app working with Splunk 7.1.3 and DBConnect 3.2.0. I used the SCCM app as a template and modified as necessary, granted it took me a few months to get everything working right: I used SQL Server manager to create new views based on the dbconnect queries in the package. This was mainly because I could input them into dbconnect and run them, but when I went to save them I'd receive an error. I rewrote most of the dashboards and pages to work with the new queries and had success with them, The only issue so far is that because the rising column doesn't work with the queries, some of the time pickers don't work, or I had to get creative with time sorting. After that it was just setting up alerts for the SCCM daily/weekly/monthly recommended monitoring. Let me know if you need more specific pointers on getting it working. ... View more