topic Re: Getting full result in join/append in Splunk Search

Getting full result in join/append

nkumar6 — Wed, 30 Sep 2020 03:13:00 GMT

I have a index, where i store values of items and their count (pulled from SQL DB). I run a search to return me items of today and a sub search to return items of last week same day(-7d@d to -6d@d and @d to now).

for example:
Todays data
ITEMS COUNT
a1 2
a2 3
a3 4

LAST week data :
ITEMS COUNT
a1 5
a2 10
a4 8

I also want to run a calculation to calculate difference of both count.
So the expected would be:

ITEMS COUNT_TODAY COUNT_LASTWEEK Difference
a1 2 5 -3
a2 3 10 -7
a3 4 4
a4 8 -8

I have already tried append with stats, appendcols, join, but i always miss-out some data.
In append ans stats i can't do the calculation part, in appendcols lastweekdata merges with wrong set of todays data, and in join i miss out the blank part. Calculation for null values can be ignored, but if done it would be helpful.

Thanks in advance.

Re: Getting full result in join/append

woodcock — Fri, 29 Nov 2019 02:28:11 GMT

Like this:

index="YouShouldAlwaysSpecifyAnIndex" AND sourcetype="AndSourcetypeToo" ((earliest=-8d@d latest=-7d@d) OR (earliest=@d latest=now))
| timechart limit=0 useother=false cont=f span=1d count BY items
| where _time = relative_time(now(), "-8d@d") OR _time = relative_time(now(), "@d")
| untable _time items count
| eval _time = if(_time == relative_time(now(), "-8d@d"), "LASTWEEK", "TODAY")
| xyseries items _time count
| eval DIFFERENCE = TODAY - LASTWEEK

Here is a run-anywhere example:

|tstats count WHERE index="*" AND sourcetype="*" ((earliest=-8d@d latest=-7d@d) OR (earliest=@d latest=now)) BY sourcetype _time span=1h
| timechart limit=0 useother=false cont=f span=1h count BY sourcetype
| where _time = relative_time(now(), "-8d@d") OR _time = relative_time(now(), "@d")
| untable _time sourcetype count
| eval _time = if(_time == relative_time(now(), "-8d@d"), "LASTWEEK", "TODAY")
| xyseries sourcetype _time count
| eval DIFFERENCE = TODAY - LASTWEEK

Re: Getting full result in join/append

to4kawa — Fri, 29 Nov 2019 12:27:38 GMT

| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"+1d@d"), relative_time(_time,"-8d@d")) 
| makecontinuous span=5m
| where strftime(_time,"%d")==strftime(now(),"%d") OR strftime(_time,"%d")==strftime(relative_time(now(),"-7d@d"),"%d")
| eval ITEMS="a".(random() % 5 + 1)
| table _time ITEMS
`comment("this is sample data")`
| eval week=case(strftime(_time,"%d")==strftime(now(),"%d"),"COUNT_TODAY",strftime(_time,"%d")==strftime(relative_time(now(),"-7d@d"),"%d"),"COUNT_LASTWEEK")
| chart count over ITEMS by week
| eval Difference= COUNT_TODAY - COUNT_LASTWEEK
| table ITEMS COUNT_TODAY COUNT_LASTWEEK Difference

Hi , how about it?
When searching, please use first @woodcook 's search text.

Re: Getting full result in join/append

nkumar6 — Fri, 29 Nov 2019 20:21:20 GMT

While executing your suggestion i get error as "Error in 'makeresults' command: This command must be the first command of a search".

Did I miss out something?

Re: Getting full result in join/append

woodcock — Fri, 29 Nov 2019 21:05:00 GMT

You need the leading pipe as in | makeresults ...

Re: Getting full result in join/append

nkumar6 — Fri, 29 Nov 2019 21:38:48 GMT

I did like this
index=abc AND sourcetype=xyz ((earliest=-8d@d latest=-7d@d) OR (earliest=@d latest=now)) | makeresults count=2 | streamstats count | eval _time = if (count==2,relative_time(_time,"+1d@d"), relative_time(_time,"-8d@d")) | makecontinuous span=5m | where strftime(_time,"%d")==strftime(now(),"%d") OR strftime(_time,"%d")==strftime(relative_time(now(),"-7d@d"),"%d") | eval ITEM="a".(random() % 5 + 1) | table _time ITEM | eval week=case(strftime(_time,"%d")==strftime(now(),"%d"),"COUNT_TODAY",strftime(_time,"%d")==strftime(relative_time(now(),"-7d@d"),"%d"),"COUNT_LASTWEEK") | chart count over ITEM by week | eval Difference= COUNT_TODAY - COUNT_LASTWEEK | table ITEM, COUNT_TODAY, COUNT_LASTWEEK, Difference

Re: Getting full result in join/append

to4kawa — Fri, 29 Nov 2019 23:01:06 GMT

makeresults

This command must be written at the beginning of the search.

When searching, please use first @woodcook 's search text.

It was a remark when actually applying it to your query ...

Re: Getting full result in join/append

woodcock — Sat, 30 Nov 2019 03:59:36 GMT

Just try my top answer but change YouShouldAlwaysSpecifyAnIndex to your actual index value and AndSourcetypeToo to your actual sourcettype value. You have had working answers for days now.

Re: Getting full result in join/append

nkumar6 — Wed, 04 Dec 2019 11:47:30 GMT

Hi @to4kawa ,
can you help me with this, I am still failing to do so, neither I can find any other example like this.

Re: Getting full result in join/append

nkumar6 — Wed, 04 Dec 2019 11:50:48 GMT

I don't get any error but nor do I get any result. and eval ITEM="a".(random() % 5 + 1) wouldn't work in my case, as it was just example I used for a1,a2,a3 etc...

Re: Getting full result in join/append

to4kawa — Thu, 05 Dec 2019 14:37:26 GMT

 | makeresults count=2
 | streamstats count
 | eval _time = if (count==2,relative_time(_time,"+1d@d"), relative_time(_time,"-8d@d")) 
 | makecontinuous span=5m
 | where strftime(_time,"%d")==strftime(now(),"%d") OR strftime(_time,"%d")==strftime(relative_time(now(),"-7d@d"),"%d")
 | eval ITEMS="a".(random() % 5 + 1)
 | table _time ITEMS

please paste this query and search, check it.
This query is aim to create sample data.

and

| eval week=case(strftime(_time,"%d")==strftime(now(),"%d"),"COUNT_TODAY",strftime(_time,"%d")==strftime(relative_time(now(),"-7d@d"),"%d"),"COUNT_LASTWEEK")
 | chart count over ITEMS by week
 | eval Difference= COUNT_TODAY - COUNT_LASTWEEK
 | table ITEMS COUNT_TODAY COUNT_LASTWEEK Difference

This query is the logic of calculate what you want .

_time ITEM

If your log has two fields in this way, my logic should work as well.