topic Re: Best approach for using a sub-search to compare time frames in Splunk Search

Best approach for using a sub-search to compare time frames

trevorQmulos — Sat, 27 Aug 2016 02:39:53 GMT

I am looking for the most efficient way to do a sub search to see if vulnerabilities still exist now vs 90 days.

Currently I do a search from 90 days back and spit that file to a csv and then do a lookup and pass those to the search but want to automate this process instead of constantly updating a lookup file. I have listed the base search below.

index=network sourcetype=nessus severity!=informational 
|  dedup signature_id, dest_dns  ]
|  dedup signature_id, dest_dns 
| eval hostnamesplit=split(dest_dns,".") 
| eval hostnamesplit=mvindex(hostnamesplit,0)
| lookup AssetTag.csv Asset as hostnamesplit OUTPUT BusinessUnit1 System1
| eval Combo=mvzip(BusinessUnit1, System1) 
| fields - System1, BusinessUnit1  
| mvexpand Combo 
| makemv Combo delim="," 
| eval BU1=mvindex(Combo,0) 
| eval Sys1=mvindex(Combo,1) 
| fields - Combo
| dedup hostnamesplit signature_id BU1 Sys1
| table BU1 signature Sys1 dest_dns severity 
|  rename  Sys1 AS "System", signature AS "Signature", BU1 AS "Business Unit",dest_dns as "Host" severity as "Severity"

Re: Best approach for using a sub-search to compare time frames

gfuente — Mon, 29 Aug 2016 12:20:57 GMT

Hello

You could use the outputlookup command to automate the refresh of the lookup. Schedule this query at midnight everyday, for the past 90 days:

"your base query that list all the vulnerabilities" | outputlookup AssetTag.csv

Then, the rest of the logic of your query will continue beign valid

Regards

Re: Best approach for using a sub-search to compare time frames

trevorr2004 — Mon, 29 Aug 2016 13:01:58 GMT

The AssetTag.csv is how I map systems to Business Units. Nothing is kept track inside that csv about scanned vulnerabilities. Basically I just want to take a search from today and check to see if they still existed 90 days ago to track how effective our patching currently is.

Re: Best approach for using a sub-search to compare time frames

gfuente — Mon, 29 Aug 2016 13:10:32 GMT

Ok, my mistake. But the solution is the same. Schedule the 90 days vulnerabilities query and dump the results to a lookup, and then in your actual search use the lookup to perform the comparison. It will be much better than running a 90 days subsearch everytime you need to run the main search

Re: Best approach for using a sub-search to compare time frames

sundareshr — Mon, 29 Aug 2016 13:27:48 GMT

Try this

index=network sourcetype=nessus severity!=informational (earliest=-90d@d latest=-90d@d+1d) OR (earliest=@d) 
 |  dedup signature_id, dest_dns  ]
 |  dedup signature_id, dest_dns 
 | eval hostnamesplit=split(dest_dns,".") 
 | eval hostnamesplit=mvindex(hostnamesplit,0)
 | lookup AssetTag.csv Asset as hostnamesplit OUTPUT BusinessUnit1 System1
 | eval Combo=mvzip(BusinessUnit1, System1) 
 | fields - System1, BusinessUnit1  
 | mvexpand Combo 
 | makemv Combo delim="," 
 | eval BU1=mvindex(Combo,0) 
 | eval Sys1=mvindex(Combo,1) 
 | fields - Combo
 | dedup hostnamesplit signature_id BU1 Sys1
 | eval month=strftime(_time, "%b")
 | eval x=BU1."^".signature."^".Sys1."^".dest_dns."^".severity
 | chart count over x by month
 | fields - count
 | rex field=x (?<BU1>[^\^]+)\^(?<signature>[^\^]+)\^(?<Sys1>[^\^]+)\^(?<dest_dns>[^\^]+)\^(?<severity>[^\^]+)
 |  rename  Sys1 AS "System", signature AS "Signature", BU1 AS "Business Unit",dest_dns as "Host" severity as "Severity"

Re: Best approach for using a sub-search to compare time frames

trevorQmulos — Mon, 29 Aug 2016 13:36:57 GMT

Error in 'SearchParser': Missing a search command before '^'. Error at position '791'

I also had a rogue ] in the search

Re: Best approach for using a sub-search to compare time frames

sundareshr — Mon, 29 Aug 2016 13:45:00 GMT

Missing quotes in the rex command. Make this change

... | rex field=x "(?<BU1>[^\^]+)\^(?<signature>[^\^]+)\^(?<Sys1>[^\^]+)\^(?<dest_dns>[^\^]+)\^(?<severity>[^\^]+)" | ...

Re: Best approach for using a sub-search to compare time frames

trevorQmulos — Mon, 29 Aug 2016 14:38:14 GMT

Does this compare the two time frames in the initial search? So basically this should return anything that was found today that also was found 90 days ago.

Re: Best approach for using a sub-search to compare time frames

sundareshr — Mon, 29 Aug 2016 14:40:35 GMT

That's correct.

Re: Best approach for using a sub-search to compare time frames

trevorQmulos — Thu, 22 Sep 2016 15:10:34 GMT

Say that our scan results are dumped in over periods of time so not just one month. Am I able to do something like this

 index=network sourcetype=nessus severity!=informational dest_dns=hqw0prd1rebs61.ent.pbgc.gov (earliest=-120d@d latest=-90d@d+1d) OR (earliest=-30d@d latest=@d)

Sorry for the late response, revisiting this subject and running into a few issues.

Re: Best approach for using a sub-search to compare time frames

trevorQmulos — Thu, 22 Sep 2016 15:51:25 GMT

I have also noticed that its not making sure that events were found in both time ranges. For instance, It's displaying results that were found in June but if I do a separate search, that signature doesn't show up in the past 30 days. Any other suggestions on how to resolve the issue?

Re: Best approach for using a sub-search to compare time frames

sundareshr — Thu, 22 Sep 2016 16:21:00 GMT

Yes, you can use that timeframe.

Re: Best approach for using a sub-search to compare time frames

sundareshr — Thu, 22 Sep 2016 16:26:30 GMT

Try this change to your query

You now have

| chart count over x by month
| fields - count

Change this to

| stats values(month) as months by x
| where mvcount(months)=2
| fields - months

Re: Best approach for using a sub-search to compare time frames

trevorQmulos — Thu, 22 Sep 2016 16:37:01 GMT

I get under the statistics, no results found when I made the selected changes.

 index=network sourcetype=nessus severity!=informational (earliest=-120d@d latest=-90d@d+1d) OR (latest=-30d@d earliest=@d) dest_dns=hqw0prd1rebs61.ent.pbgc.gov
  | dedup signature_id, dest_dns  
  | eval hostnamesplit=split(dest_dns,".") 
  | eval hostnamesplit=mvindex(hostnamesplit,0)
  | lookup AssetTag.csv Asset as hostnamesplit OUTPUT BusinessUnit1 System1
  | eval Combo=mvzip(BusinessUnit1, System1) 
  | fields - System1, BusinessUnit1  
  | mvexpand Combo 
  | makemv Combo delim="," 
  | eval BU1=mvindex(Combo,0) 
  | eval Sys1=mvindex(Combo,1) 
  | fields - Combo  
  | dedup hostnamesplit signature_id BU1 Sys1 | eval x=BU1."^".signature."^".Sys1."^".dest_dns."^".severity   | rex field=x "(?<BU1>[^\^]+)\^(?<signature>[^\^]+)\^(?<Sys1>[^\^]+)\^(?<dest_dns>[^\^]+)\^(?<severity>[^\^]+)"   
 | stats values(month) as months by x
 | where mvcount(months)=2
 | fields - months
 |  rename  Sys1 AS "System", signature AS "Signature", BU1 AS "Business Unit",dest_dns as "Host" severity as "Severity"

Is what my query is as of now.

Re: Best approach for using a sub-search to compare time frames

trevorQmulos — Fri, 23 Sep 2016 18:04:18 GMT

Was able to get it working perfectly. Thanks again for all of your help sundareshr.