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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...