Splunk Search

How to use 2 timeranges in a single search?

Splunk Employee
Splunk Employee

I'd like to provide a table where the event count for today and yesterday are displayed. For example, count by status for access logs:

status | today's count | yesterday's count
------------------------------------------
 404          10               13
 500          20               24
 503          15               10

Is this possible?

Tags (1)
0 Karma
1 Solution

Splunk Employee
Splunk Employee

You know this is just a 2-day timechart. If you're not that picky about the table layout:

sourcetype="access_combined" earliest=-2d@d latest=@d | timechart span=1d count by status 

to which you can add:

... | eval dy=now() | bucket dy span=1d | eval dy=if(_time=dy,"today","yesterday")

or if you are picky, this is how can transpose:

sourcetype="access_combined" earliest=-2d@d latest=@d | chart count by status,_time span=1d

The column names will be wrong though, and they change every day, so you can't just use rename. But the above is equivalent to:

sourcetype="access_combined" earliest=-2d@d latest=@d | bucket _time span=1d | stats count by status,_time | xyseries status _time count

which you then can to expand to (to fix the names of the columns):

sourcetype="access_combined" earliest=-2d@d latest=@d  | bucket _time span=1d | stats count by status,_time | eval dy=now() | bucket dy span=1d | eval dy=if(_time=dy,"today","yesterday") | xyseries status dy count

And BTW, your "today" isn't today's count, it's yesterdays (midnight to midnight of the previous day), and your "yesterday" is two days ago. Adjust earliest and latest as appropriate.

View solution in original post

Splunk Employee
Splunk Employee

You know this is just a 2-day timechart. If you're not that picky about the table layout:

sourcetype="access_combined" earliest=-2d@d latest=@d | timechart span=1d count by status 

to which you can add:

... | eval dy=now() | bucket dy span=1d | eval dy=if(_time=dy,"today","yesterday")

or if you are picky, this is how can transpose:

sourcetype="access_combined" earliest=-2d@d latest=@d | chart count by status,_time span=1d

The column names will be wrong though, and they change every day, so you can't just use rename. But the above is equivalent to:

sourcetype="access_combined" earliest=-2d@d latest=@d | bucket _time span=1d | stats count by status,_time | xyseries status _time count

which you then can to expand to (to fix the names of the columns):

sourcetype="access_combined" earliest=-2d@d latest=@d  | bucket _time span=1d | stats count by status,_time | eval dy=now() | bucket dy span=1d | eval dy=if(_time=dy,"today","yesterday") | xyseries status dy count

And BTW, your "today" isn't today's count, it's yesterdays (midnight to midnight of the previous day), and your "yesterday" is two days ago. Adjust earliest and latest as appropriate.

View solution in original post

Splunk Employee
Splunk Employee

I guess I can use the appendcols command, something like:

sourcetype="access_combined" earliest=-1d@d | stats count as "today's count" by status | appendcols [search sourcetype=access_combined earliest=-2d@d latest=-1d@d | stats count as "yesterday's count" by status] | fields + status, "today's count","yesterday's count"