Splunk Search

Create a table that shows last 5 days and timestamps of found events each day

ckunath
Communicator

Hello,

I want to create a search that looks for events that contain a value for a field, and then show the timestamp of it in a column that shows the day that it was found in (e.g today, yesterday). The table should look like this:

VALUE  | day-5 | day-4 | day-3 | day-2 | day-1 | today 
"text" |       |       |       |       |       | 07:32
"num"  |       | 11:09 |       | 11:10 |       | 
       |       | 11:12 |       |       |       | 
"bool" |       |       |       | 03:50 |       | 

How can I do this?

I can get the values and the timestamps with "stats count by value, _time", but I don't know how to seperate the timestamps into the different columns. Any help is highly appreciated!

0 Karma
1 Solution

HiroshiSatoh
Champion

It seems to be possible except in the order of the columns.
※Please correct the main search.

index=_internal earliest=-6d@d latest=+1d@d|rename log_level as VALUE|stats max(_time) as time by VALUE,date_mday|eval time=strftime(time ,"%H:%M")
| join date_mday [search
| noop|stats count
 |eval today=relative_time(now(), "+0d@d"),
    "day-1"=relative_time(now(), "-1d@d"),
    "day-2"=relative_time(now(), "-2d@d"),
    "day-3"=relative_time(now(), "-3d@d"),
    "day-4"=relative_time(now(), "-4d@d"),
    "day-5"=relative_time(now(), "-5d@d")
 |fields - count
 |transpose
 |rename column as Date,"row 1" as date_mday
 |eval date_mday=strftime(date_mday,"%d")]
|chart first(time) by  VALUE,Date

alt text

View solution in original post

HiroshiSatoh
Champion

It seems to be possible except in the order of the columns.
※Please correct the main search.

index=_internal earliest=-6d@d latest=+1d@d|rename log_level as VALUE|stats max(_time) as time by VALUE,date_mday|eval time=strftime(time ,"%H:%M")
| join date_mday [search
| noop|stats count
 |eval today=relative_time(now(), "+0d@d"),
    "day-1"=relative_time(now(), "-1d@d"),
    "day-2"=relative_time(now(), "-2d@d"),
    "day-3"=relative_time(now(), "-3d@d"),
    "day-4"=relative_time(now(), "-4d@d"),
    "day-5"=relative_time(now(), "-5d@d")
 |fields - count
 |transpose
 |rename column as Date,"row 1" as date_mday
 |eval date_mday=strftime(date_mday,"%d")]
|chart first(time) by  VALUE,Date

alt text

ckunath
Communicator

Thank you! This was exactly what I was looking for.

0 Karma

abalogh_splunk
Splunk Employee
Splunk Employee

Hi,

I think you are looking for the transpose command.
Read more about it at http://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Transpose

0 Karma

ckunath
Communicator

Hi,

thanks for your answer! unfortunately i am still stuck with finding a way to split the timestamps where day=x into their columns. Can you help me in that regard?

Something like eval day-1=_time where date_mday= (now().date_mday-1) (Doesn't work of course, but that's how I'd imagine it off the top of my head)

0 Karma
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 ...