topic Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?) in Splunk Search

Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

masonmorales — Mon, 28 Sep 2020 20:08:30 GMT

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)

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

masonmorales — Fri, 05 Jun 2015 16:08:09 GMT

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.

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

woodcock — Fri, 05 Jun 2015 16:23:49 GMT

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.

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

masonmorales — Fri, 05 Jun 2015 16:26:47 GMT

This also returns zero results.

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

woodcock — Fri, 05 Jun 2015 16:36:54 GMT

Then it is because the field date_mday never has the same value as the field today_mday.

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

masonmorales — Fri, 05 Jun 2015 16:53:26 GMT

I'm guessing you didn't run my test query, because it's pretty obvious that it does.

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

woodcock — Fri, 05 Jun 2015 17:20:06 GMT

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

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

masonmorales — Fri, 05 Jun 2015 17:28:56 GMT

This also returns 0 results

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

masonmorales — Fri, 05 Jun 2015 17:30:07 GMT

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.

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

woodcock — Fri, 05 Jun 2015 17:39:13 GMT

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

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

masonmorales — Mon, 28 Sep 2020 20:08:35 GMT

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"

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

woodcock — Fri, 05 Jun 2015 18:15:27 GMT

I opened a bug case 244781:
https://splunkcommunities.force.com/customers/5003300000nfPDb

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

woodcock — Mon, 08 Jun 2015 17:19:38 GMT

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

Re: Why doesn't Splunk believe that date_mday = today_mday? (5 is equal to 5, right?)

woodcock — Wed, 10 Jun 2015 20:41:47 GMT

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.