Automatically updating time slots and case for filling values accordingly.

[avraw]

Hi,

I have a DB Input with three columns Name, Timestamp and PENDING.
So there are a limited amount of names, and each name has a particular value for pending at a particular timestamp.

NAME              Time Stamp                PENDING
NAME 1      Tue Apr 3 21:25:54 2018        5
NAME 2         Tue Apr 3 21:25:26 2018     0
NAME 1         Tue Apr 3 21:23:04 2018     13
NAME 2      Tue Apr 3 21:21:20 2018        3
NAME 1       Tue Apr 3 21:17:14 2018       1

I want to create a dashboard with time slots of 5 minute each as the columns and NAME values as rows, but PENDING should be filled if it falls under that timestamp and name. The time slots will keep on updating in 5 minute intervals so will the value of PENDING as the cross section cell ( case of NAME and timestamp slot category)

It should look like this

           21:25      21:20    21:15
NAME1      5            13       1
NAME2      0            3        NA

I have looked up on this already a lot of times but couldn't get an exact answer as of now, I hope you could understand my requirements here and help me out with this.

[adonio · Accepted solution]

Hello there,

you can use the following search:
| bin span=5m _time
| eval hour_minute = strftime(_time, "%H:%M")
| chart values(PENDING) as pend over NAME by hour_minute
| fillnull value=NA

see screenshot:

hope it helps

[avraw]

This perfect! Thank you so much adonio!

Could you help me out with one more thing as well. I need to color cells red when they go over a certain threshold for each name.

Is it possible ?

[adonio]

you can use the heatmap built into table visualization, or you can modify xml of the dashboard.
will recommend to download the dashboards examples app: https://splunkbase.splunk.com/app/1603/
navigate to table elements and use the formula to apply colors of your choice.
glad it works for you

[avraw]

Thanks adonio. Looking into the examples right now. Just another question. Is there any way I could setup alert conditions on this chart. Like if I could put up case statements against a name and the pending count against that name exceeds a threshold then It would trigger an alert. I used case statements for my previous dashboards but they were actually tables and if it returned a row it triggered an alert. I am not sure how to implement the same for a chart. Any help would be appreciated.

[adonio]

if you are after the pending value, i will suggest to only search for that. for example:
... your search ... PENDING > X
X represent some numeric value
another option, is to save the alert and add condition to it based on the field and value you are looking for

[avraw]

Yeah I tried using 'if' statement as well as 'where' statements. It seems like the search is not able to detect the PENDING variable or value.

[adonio]

if you want to alert on a high / low value, i will recommend to put it in the search first.
not after your stats or chart command
just capture the event / value you want to alert upon