Splunk Search

Getting full result in join/append

Explorer

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.

0 Karma
1 Solution

Ultra Champion
| 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.

View solution in original post

Ultra Champion
| 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.

View solution in original post

Explorer

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?

0 Karma

Ultra Champion

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

Explorer

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

0 Karma

Ultra Champion
 | 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.

Explorer

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

0 Karma

Esteemed Legend

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

0 Karma

Explorer

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

0 Karma

Esteemed Legend

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

Esteemed Legend

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!