Splunk Search

Getting full result in join/append

nkumar6
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

to4kawa
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

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

nkumar6
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

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

nkumar6
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

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

nkumar6
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

woodcock
Esteemed Legend

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

0 Karma

nkumar6
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

woodcock
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

woodcock
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...