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

[ckunath]

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!

[HiroshiSatoh · Accepted solution]

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

[ckunath]

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

[abalogh_splunk]

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

[ckunath]

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)