topic Re: Combining Searches from Multiple Sourcetypes in Splunk Search

Combining Searches from Multiple Sourcetypes

m314219 — Mon, 05 Oct 2020 19:56:14 GMT

Looking for some advice on combining searches from multiple sourcetypes into a single report for my auditing team. They have requested a report showing hostnames and for each host the current AV definitions, the last time the sec log was cleared or archived, and then content from a couple of text files that is produced by some scheduled tasks on the systems. I currently have all of this info in Splunk, but will need to create a single report to show it all. As an example, how could I take the three queries I've put together so far into the same report?

index=windows source="WinEventLog:Security" EventCode="1105" | rename Date as LastSecLogArchive | stats latest(LastSecLogArchive) by host

index=windows sourcetype="Symantec:VirusDefs" | stats latest(CurrDefs) by host

index=windows source="WinEventLog:Security" EventCode="1102" | rename Date as LastSecLogClear | stats latest(LastSecLogClear) by host

Re: Combining Searches from Multiple Sourcetypes

to4kawa — Mon, 05 Oct 2020 21:39:50 GMT

Re: Combining Searches from Multiple Sourcetypes

m314219 — Tue, 06 Oct 2020 03:59:02 GMT

Thanks! How would you add in another search like this?

index=_internal source=*metrics.log group=tcpin_connections earliest=-2d@d arch=x64 | eval age = (now() - _time ) | stats min(age) as age, max(_time) as LastTime by hostname | convert ctime(LastTime) as "UF Last Active On" | eval Status=case(age < 1800,"Running",age > 1800,"DOWN") | rename Status as "UF Status" | table hostname,"UF Status","UF Last Active On"

Re: Combining Searches from Multiple Sourcetypes

to4kawa — Wed, 07 Oct 2020 10:23:44 GMT

I'm not sure what you want to do.

index=windows (source="WinEventLog:Security" (EventCode="1105" OR EventCode="1102" ) ) OR sourcetype="Symantec:VirusDefs" | stats latest(eval(if(EventCode="1105", Date,NULL))) as LastSecLogArchive ,latest(eval(if(EventCode="1102", Date,NULL))) as LastSecLogClear ,latest(CurrDefs) as CurrDefs by host | join host [search index=_internal source=*metrics.log group=tcpin_connections earliest=-2d@d arch=x64 | eval age = (now() - _time ) | stats min(age) as age, max(_time) as LastTime by hostname | convert ctime(LastTime) as "UF Last Active On" | eval Status=case(age < 1800,"Running",age > 1800,"DOWN") | rename Status as "UF Status" | table hostname,"UF Status","UF Last Active On" |rename hostname as host]

like this?

Re: Combining Searches from Multiple Sourcetypes

m314219 — Fri, 09 Oct 2020 11:11:45 GMT

Thanks! Was able to finish the final search:

(index=windows OR index=_internal) (source="WinEventLog:Security" (EventCode="1105" OR EventCode="1102") OR (source=*metrics.log group=tcpin_connections earliest=-2d@d arch=x64)) OR sourcetype="Symantec:VirusDefs" OR sourcetype="PowerShell:WinSystemInfo" NOT PS_SN="VMware-*" | eval age = (now() - _time ) | stats latest(PS_OS_Version) as OSVersion latest(PS_W10_Build) as W10Build latest(PS_Manufac) as Manufac latest(PS_Model) as Model latest(PS_SN) as SN latest(PS_AV_Date) as AVDate latest(PS_BitLocker) as BitLocker latest(eval(if(EventCode="1105",Date,NULL))) as LastSecLogArchive latest(eval(if(EventCode="1102",Date,NULL))) as LastSecLogClear max(_time) as LastActive min(age) as age by host | convert ctime(LastActive) as "UF Last Active On" | eval Status=case(age < 1800,"Running",age > 1800,"DOWN") | rename Status as "UF Status" | table host,OSVersion,W10Build,AVDate,BitLocker,LastSecLogArchive,LastSecLogClear,"UF Status","UF Last Active On",Manufac,Model,SN