I have a search which gives me a whole range of timestamps (the usual date _ hour, date _ minute and date_second)
I want to populate a stacked bar chart with the those time stamps by re-arranging them in the following * bins *
i) 12-2 PM
ii) outside 12-2PM
iii) no timestamp found
The search I devised which is not working looks like this
sourcetype="syslog" | eval timestamp=strftime(_time,"%H:%M:%S") | eval range=case(date_hour>=23 AND date_hour<=24, "in",
date_hour<23 OR date_hour<=24, "out",
timestamp==null, "rest") | timechart count(timestamp) by range
Any idea so as to why this is not working ?
Any help is appreciated !
Hmm, a bit unclear. You are using the _time
field, which will always be present, even if there is no timestamp in the event itself (if all else fails, _time
will be set to the local time on the indexer).
What type of events do you have? Also, there seems to be some confusion regarding AM/PM. 12-2PM would be the hours 12-14, i.e. late lunch time.
So therefore I suggest that you use rex
instead of eval
to determine if there is a timestamp in the event itself. Assuming that you have events that have typical syslog formatting, e.g.
2013-01-04T13:14:15,231 blah blah blah
you could have the following regex to find it;
your search | rex "^\d+-\d+-\d+T(?<time_raw>[0-9:]+)" | rest of search here
which should capture the hour-minute-second part of the timestamp into to new field time_raw
. You need to change the regex to fit your particular type of timestamp.
To set your own date_hour etc fields, use rex
again;
...| rex field=time_raw "(?<my_hr>\d+):(?<my_min>\d+):(?<my_sec>\d+)"
Now you have your own home made date_* fields that only exist if there is a valid timestamp in the original event.
After that you can build your categories;
...| eval category = if(my_hr == 12 OR my_hr == 13, "in", if (my_hr < 12 OR my_hr > 13, "out", "no_timestamp"))
then you can add your charting commands, and I'm not sure timechart
is necessarily what you want. I'll leave that as an exercise though, just including a basic chart
below.
so putting it all together:
sourcetype=syslog | rex "^\d+-\d+-\d+T(?<time_raw>[0-9:]+)" | rex field=time_raw "(?<my_hr>\d+):(?<my_min>\d+):(?<my_sec>\d+)" | eval category = if(my_hr == 12 OR my_hr == 13, "in", if (my_hr < 12 OR my_hr > 13, "out", "no_timestamp")) | chart c by category
Hope this helps.
/Kristian
Hmm, a bit unclear. You are using the _time
field, which will always be present, even if there is no timestamp in the event itself (if all else fails, _time
will be set to the local time on the indexer).
What type of events do you have? Also, there seems to be some confusion regarding AM/PM. 12-2PM would be the hours 12-14, i.e. late lunch time.
So therefore I suggest that you use rex
instead of eval
to determine if there is a timestamp in the event itself. Assuming that you have events that have typical syslog formatting, e.g.
2013-01-04T13:14:15,231 blah blah blah
you could have the following regex to find it;
your search | rex "^\d+-\d+-\d+T(?<time_raw>[0-9:]+)" | rest of search here
which should capture the hour-minute-second part of the timestamp into to new field time_raw
. You need to change the regex to fit your particular type of timestamp.
To set your own date_hour etc fields, use rex
again;
...| rex field=time_raw "(?<my_hr>\d+):(?<my_min>\d+):(?<my_sec>\d+)"
Now you have your own home made date_* fields that only exist if there is a valid timestamp in the original event.
After that you can build your categories;
...| eval category = if(my_hr == 12 OR my_hr == 13, "in", if (my_hr < 12 OR my_hr > 13, "out", "no_timestamp"))
then you can add your charting commands, and I'm not sure timechart
is necessarily what you want. I'll leave that as an exercise though, just including a basic chart
below.
so putting it all together:
sourcetype=syslog | rex "^\d+-\d+-\d+T(?<time_raw>[0-9:]+)" | rex field=time_raw "(?<my_hr>\d+):(?<my_min>\d+):(?<my_sec>\d+)" | eval category = if(my_hr == 12 OR my_hr == 13, "in", if (my_hr < 12 OR my_hr > 13, "out", "no_timestamp")) | chart c by category
Hope this helps.
/Kristian
That was extremely helpful.
One more FINAL question. Its ok if you cant answer it.
lets just say I want to introduce a constant that is read from a lookup file and add it to the stats at the end.
How would i do that ?
instead of the final chart command, put in
stats count c(eval(category=="in") AS in_count c(eval(category=="out") AS out_count | eval ratio = in_count/out_count
The stats command gives you the total count as well in the field 'count' if you want to use that for your ratio.
You could also have a look at the top
command;
| top category
at the end instead. A little more basic, but may still be useful.
/k
i.e. consider this filter ->
eval category = if(my_hr == 12 OR my_hr == 13, "in", if (my_hr < 12 OR my_hr > 13, "out", "no_timestamp"))
Basically I want to apply a filter here to print a ratio
Hi,
That was the most useful response ever !
Just a quick followup.
What if I wanted to create a chart of the ratio of in/out ?
How would I go about that ?