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