So, I need to compare counts over multiple days, but I also need to filter the results to only show the count difference for the current day (compared to yesterday). For some reason, I can't get Splunk to understand that "5 = 5" (today's date) so it won't filter the results. Here's a simplified version of the query that you can test:
earliest=-72h latest=now index=_internal log_level=ERROR
| stats count as ErrorCount by date_mday sourcetype
| sort sourcetype
| streamstats current=f last(ErrorCount) as LastErrorCount by sourcetype
| eval ErrorCountDiff=ErrorCount-LastErrorCount
| eval today_mday=strftime(now(), "%e")
What I want is only the rows where date_mday is equal to today_mday. However, if I add:
| search date_mday=today_mday
It returns zero results. As does:
| where date_mday=today_mday
And if I do:
| search date_mday!=today_mday
it returns ALL results (instead of just excluding today) (same result for where), so for some reason Splunk can't evaluate that both numbers are equal.
I have tried converting both fields "tostring", and both fields "tonumber", and then applying the "date_mday=today_mday" filter, but it still returns zero results.
Does anyone have an idea on how I can make Splunk understand that "5 = 5"? (today's date)
So, I finally got it. Splunk must have some kind of unchangeable format for the "date_mday" field, because no matter what I did, I couldn't make it match my today_mdate even though it had the same field value.
I ended up creating my own date_mday field using _time with strftime, AND using an eval, THEN it finally worked. I still think this is a bug, because it shouldn't be this hard to tell Splunk that I need results where 5=5.
Anyway, here's the query I built if anyone ever needs to do something similar:
earliest=-72h latest=now index=_internal log_level=ERROR
| eval new_date_mday=strftime(_time, "%e")
| stats count as ErrorCount by new_date_mday sourcetype
| eval today_mday=strftime(now(), "%e")
| sort sourcetype
| streamstats current=f last(ErrorCount) as LastErrorCount by sourcetype
| eval ErrorCountDiff=ErrorCount-LastErrorCount
| eval IsToday=if(like(new_date_mday, today_mday), "Yes", "No")
| where IsToday="Yes"
So, I finally got it. Splunk must have some kind of unchangeable format for the "date_mday" field, because no matter what I did, I couldn't make it match my today_mdate even though it had the same field value.
I ended up creating my own date_mday field using _time with strftime, AND using an eval, THEN it finally worked. I still think this is a bug, because it shouldn't be this hard to tell Splunk that I need results where 5=5.
Anyway, here's the query I built if anyone ever needs to do something similar:
earliest=-72h latest=now index=_internal log_level=ERROR
| eval new_date_mday=strftime(_time, "%e")
| stats count as ErrorCount by new_date_mday sourcetype
| eval today_mday=strftime(now(), "%e")
| sort sourcetype
| streamstats current=f last(ErrorCount) as LastErrorCount by sourcetype
| eval ErrorCountDiff=ErrorCount-LastErrorCount
| eval IsToday=if(like(new_date_mday, today_mday), "Yes", "No")
| where IsToday="Yes"
I opened a bug case 244781:
https://splunkcommunities.force.com/customers/5003300000nfPDb
Splunk has acknowledged that tonumber
should ignore bounding whitespace and bugifix SPL-102836 is being targeted for the 6.2.5 maintenance release which should be available Sept/Oct 2015.
The problem is that today_mday
is a string with a leading space; try this:
earliest=-72h latest=now index=_internal log_level=ERROR
| stats count as ErrorCount by date_mday sourcetype
| sort sourcetype
| streamstats current=f last(ErrorCount) as LastErrorCount by sourcetype
| eval ErrorCountDiff=ErrorCount-LastErrorCount
| eval today_mday=tonumber(strftime(now(), "%e")) | where today_mday=date_mday
This also returns 0 results
I don't think there is a leading space (I know the docs say there is, but maybe it's because we are still in the single digit days of the month), because even if I do...
earliest=-72h latest=now index=_internal log_level=ERROR
| stats count as ErrorCount by date_mday sourcetype
| sort sourcetype
| streamstats current=f last(ErrorCount) as LastErrorCount by sourcetype
| eval ErrorCountDiff=ErrorCount-LastErrorCount
| eval today_mday=strftime(now(), "%e")
| replace " " with "" in today_mday
| eval today_mday=tonumber(today_mday)
| where today_mday=date_mday
I still get 0 results. This is starting to look like a bug.
Yes, I am struggling to get Splunk to interpret today_mday
as a number/int but I am definitely correct in the "leading space" theory as you can see by this search:
earliest=-72h latest=now index=_internal log_level=ERROR
| stats count as ErrorCount by date_mday sourcetype
| sort sourcetype
| streamstats current=f last(ErrorCount) as LastErrorCount by sourcetype
| eval ErrorCountDiff=ErrorCount-LastErrorCount
| eval today_mday=strftime(now(), "%e") | eval combined="<" . date_mday . ":" . today_mday . ">" | stats count by date_mday,today_mday,combined
Here is a very interesting thing I noticed today: If a value has been created as a number, it will show right-justified in the column, but if it has been created as a string, it will show left-justified. HOW COOL IS THAT!!! I wonder how long that has been in Splunk and if it is actually documented anywhere?
http://answers.splunk.com/answers/241582/how-can-i-tell-a-field-values-type-number-or-strin.html
To compare two fields, you must use where
, not search
, like this:
... | where date_mday=today_mday
The reason is that the RHV for search
is always a string-literal.
This also returns zero results.
Then it is because the field date_mday
never has the same value as the field today_mday
.
I'm guessing you didn't run my test query, because it's pretty obvious that it does.
Just a side note, I realize I could limit the time range more and do a fillnull on ErrorCountDiff, but this is a simplified version of the search I'm doing, which does require multiple days of counts.