topic Re: eval and range in Splunk Search

eval and range

asarolkar — Thu, 03 Jan 2013 17:50:16 GMT

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 !

Re: eval and range

kristian_kolb — Thu, 03 Jan 2013 22:24:46 GMT

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

Re: eval and range

asarolkar — Thu, 03 Jan 2013 22:27:11 GMT

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 ?

Re: eval and range

asarolkar — Mon, 28 Sep 2020 13:02:45 GMT

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

Re: eval and range

kristian_kolb — Mon, 28 Sep 2020 13:02:48 GMT

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.

Re: eval and range

asarolkar — Fri, 04 Jan 2013 00:07:06 GMT

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 ?