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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...