Splunk Search

How to use 2 timeranges in a single search?

hulahoop
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

gkanapathy
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

gkanapathy
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.

hulahoop
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"

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...