topic Re: How to compare the same search from the previous day to current day using set diff? in Splunk Search

How to compare the same search from the previous day to current day using set diff?

darrenaefc — Mon, 18 Mar 2019 15:11:45 GMT

Hi guys,

I am very new to Splunk (about 1 month or so) and I am having some trouble incorporating "set diff" into my search to compare the same search from previous day to now. Can anyone help??
Below is my search used to retrieve the data i am looking for i am unsure where and how "set diff" should be used.

index=security sourcetype= (blank) source="myfiles.csv"
| dedup displayName actions | table displayName actions
| rex field=actions "hosts\W+(?P.*?)]" max_match=0
| table displayName Hosts | mvexpand Hosts | makemv delim="," Hosts | rex mode=sed field=Hosts "s/'/ /g"
| mvexpand Hosts

Re: How to compare the same search from the previous day to current day using set diff?

somesoni2 — Mon, 18 Mar 2019 15:27:07 GMT

Using set diff, it has to be done like this (two subsearches, one selecting data for yesterday and other selecting data for today)

| set diff [search index=security sourcetype= (blank) source="myfiles.csv" earliest=-1d@d latest=@d
| dedup displayName actions | table displayName actions
| rex field=actions "hosts\W+(?P<Hosts>.*?)]" max_match=0
| table displayName Hosts | mvexpand Hosts | makemv delim="," Hosts | rex mode=sed field=Hosts "s/'/ /g"
| mvexpand Hosts]  [search index=security sourcetype= (blank) source="myfiles.csv" earliest=@d latest=now
| dedup displayName actions | table displayName actions
| rex field=actions "hosts\W+(?P<Hosts>.*?)]" max_match=0
| table displayName Hosts | mvexpand Hosts | makemv delim="," Hosts | rex mode=sed field=Hosts "s/'/ /g"
| mvexpand Hosts]

But I would rather do it using below, better search (no subsearch, selecting data for yesterday and today)

index=security sourcetype= (blank) source="myfiles.csv" earliest=-1d@d  latest=now
| bucket span=1d _time
| dedup _time displayName actions | table _time displayName actions
| rex field=actions "hosts\W+(?P<Hosts>.*?)]" max_match=0
| table _time displayName Hosts | mvexpand Hosts | makemv delim="," Hosts | rex mode=sed field=Hosts "s/'/ /g"
| mvexpand Hosts
| stats dc(_time) as daysReported by displayName Hosts | where daysReported=1 | table displayName Hosts

Re: How to compare the same search from the previous day to current day using set diff?

woodcock — Mon, 18 Mar 2019 15:45:21 GMT

I would not use set diff for many reasons; try this:

index=security sourcetype= (blank) source="myfiles.csv" earliest=-2d@d latest=@d
| dedup displayName actions
| fields displayName actions
| rex field=actions max_match=0 "hosts\W+(?P.*?)]"
| fields -actions
| mvexpand Hosts
| makemv delim="," Hosts | rex mode=sed field=Hosts "s/'/ /g"
| mvexpand Hosts
| bin _time span=1d
| eval _time = if(_time < relative_time(now(), "-1d@d"), "YesterYesterDay", "YesterDay")
| chart values(Hosts) OVER displayName BY _time
| nomv YesterYesterDay 
| nomv YesterDay 
| rex field=YesterYesterDay mode=sed "s/[\r\n\s]+/;/g" 
| rex field=YesterDay mode=sed "s/[\r\n\s]+/;/g" 
| eval setdiff = split(replace(replace(replace(replace(mvjoin(mvsort(mvappend(split(replace(YesterYesterDay, "(;|$)", "#1;"), ";"), split(replace(YesterDay, "(;|$)", "#0;"), ";"))), ";"), ";(\w+)#0\;\1#1", ""), ";\w+#1", ""), "#0", ""), ";(?!\w)|^;", ""), ";")
| makemv delim=";" YesterYesterDay
| makemv delim=";" YesterDay

See this run-anywhere example:

| tstats values(sourcetype) AS sourcetype WHERE index=_* earliest=-2d@d latest=@d BY host _time span=1d
| eval _time = if(_time < relative_time(now(), "-1d@d"), "YesterYesterDay", "YesterDay") 
| chart values(sourcetype) OVER host BY _time 
| nomv YesterYesterDay 
| nomv YesterDay 
| rex field=YesterYesterDay mode=sed "s/[\r\n\s]+/;/g" 
| rex field=YesterDay mode=sed "s/[\r\n\s]+/;/g" 
| eval setdiff = split(replace(replace(replace(replace(mvjoin(mvsort(mvappend(split(replace(YesterYesterDay, "(;|$)", "#1;"), ";"), split(replace(YesterDay, "(;|$)", "#0;"), ";"))), ";"), ";(\w+)#0\;\1#1", ""), ";\w+#1", ""), "#0", ""), ";(?!\w)|^;", ""), ";")
| makemv delim=";" YesterYesterDay
| makemv delim=";" YesterDay

Re: How to compare the same search from the previous day to current day using set diff?

woodcock — Mon, 18 Mar 2019 16:25:16 GMT

You can thank @martin_mueller for that setdiff line.

Re: How to compare the same search from the previous day to current day using set diff?

macadminrohit — Mon, 18 Mar 2019 16:58:02 GMT

@woodcock, whats this last eval setdiff is doing here ?

Re: How to compare the same search from the previous day to current day using set diff?

martin_mueller — Mon, 18 Mar 2019 18:18:37 GMT

It manually calculates the difference between two multivalue fields.

Re: How to compare the same search from the previous day to current day using set diff?

woodcock — Mon, 18 Mar 2019 18:55:59 GMT

Which was the core of OPs ask.

Re: How to compare the same search from the previous day to current day using set diff?

woodcock — Fri, 12 Apr 2019 21:28:42 GMT

Attention @darrenaefc, I had a mistake in my original answer. It works properly now.

Re: How to compare the same search from the previous day to current day using set diff?

woodcock — Sat, 13 Apr 2019 16:08:04 GMT

It had a bug and wasn't working right. Try it now.