topic How to use 2 timeranges in a single search? in Splunk Search

How to use 2 timeranges in a single search?

hulahoop — Tue, 16 Mar 2010 23:07:20 GMT

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?

Re: How to use 2 timeranges in a single search?

hulahoop — Tue, 16 Mar 2010 23:22:28 GMT

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"

Re: How to use 2 timeranges in a single search?

gkanapathy — Wed, 17 Mar 2010 01:06:20 GMT

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.