Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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

Highlighted

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

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.

Highlighted

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

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