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 >_____<
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
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.
Thanks for the hint, but since I'm no big fan of any kind of sub search I was not aware of this 😉
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