Splunk Search

How to search the difference of a value between 2 searches by a certain field?

davdes44
New Member

So I want to find the difference of a value between 2 searches.

The first search grabs score by last name on 2 weeks prior:

score source=scores.py  latest=-20160m | stats count as previous by name

so the statistics table would look like this:
Name | Score
1. Smith | 76

2. Jackson | 80
3. Monroe | 95

The second search grabs the most recent score by last name:

score source=scores.py | stats count as previous by name

so the statistics table would look like this:
Name | Score
1. Smith | 79

2. Jackson | 85
3. Monroe | 90

I want it to ultimately look like this:

Name | previous | cur | Change
1. Smith | 76 | 79 | 3 |
2. Jackson | 80 | 85 | 5 |
3. Monroe | 95 | 90 | -5 |

Here is the search I have now:

score source=scores.py  latest=-20160m | stats count as previous by name | eval cur=[ search score source=scores.py  | stats count as cur by name | rename cur as query ]

I get this error "Error in 'eval' command: Typechecking failed. 'AND' only takes boolean arguments". Im thinking its because i have "by" in my eval. Anyone have any idea how I can achieve this? At my wits end >_____<

Tags (2)
0 Karma

MuS
SplunkTrust
SplunkTrust

Hi davdes44,

you cannot use a sub search inside an eval.

You can use this nice app https://apps.splunk.com/app/1645 which will handle such use cases or take a look at this run everywhere command, which will compare the event count of two days, one week ago and today:

index=_internal earliest=-1w@w sourcetype=splunkd date_wday=wednesday 
| bucket _time span=1d 
| stats last(_time) AS last_time count AS per_min_count by _time, host
| eval 1w_ago = if(last_time > exact(relative_time(now(),"-1w@w")) AND last_time <= exact(relative_time(now(),"-0w@w")) , per_min_count ,"0")
| eval current_count = if(last_time > exact(relative_time(now(),"-1d@d")) AND last_time <= exact(relative_time(now(),"-0d@d")) , per_min_count ,"0")
| stats max(last_time) AS _time, values(host) AS host, max(current_count) AS current_count, max(1w_ago) AS 1w_ago 
| eval diff = '1w_ago' - 'current_count'

First we get all events for wednesday over the last two weeks, then setup _time buckets per day, do some time based eval magic and count the results, display the result using stats and use a final eval to calculate the difference for the two results.

Hope this helps to get you started ...

cheers, MuS

ramdaspr
Contributor

Actually he can use a subsearch in the eval statement as long as he is trying to return only 1 record back to eval.

He is getting the error message because he has the by name added which then tried to return multiple records to the base query which causes the issue.

A join, i thought, would be a simpler solution in this case.

MuS
SplunkTrust
SplunkTrust

Thanks for the hint, but since I'm no big fan of any kind of sub search I was not aware of this 😉

0 Karma

ramdaspr
Contributor
 score source=scores.py  latest=-20160m | stats count as previous by name | join name [search score source=scores.py | stats count as current by name] | eval difference = current-previous

untested, but the logic should be ok. however this will ignore users which dont have a match in the subquery. If you dont want that, use type=outer on the join command

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