topic Re: Dividing a field with time into 2 hour slots to find matching events in each slot in Splunk Search

Dividing a field with time into 2 hour slots to find matching events in each slot

bharathdoitnow — Wed, 16 May 2018 04:48:08 GMT

Hi splunkers,

I am trying to solve an use case where I have to monitor some events occurance for every two hours. we have a input lookup which stores info like below:

Category Time
1 mango 2018-05-07 20:00:44
2 Apple 2018-05-07 14:58:04
3 Apple 2018-05-07 14:58:04
4 Apple 2018-05-07 14:58:04
5 Apple 2018-05-04 17:23:55
6 Orange 2018-05-08 18:21:16

I am trying to break the Time value into 2 hours slots and then check if apple,mango or orange exists within that slot. I tried to use splunk inbuilt timeline views but it did not help me to get a time table view like below:

today

8-10 AM 10-12PM 12-2PM 2-4PM 4-6PM
Apple yes no no yes no
orange no yes no yes no
Mango yes yes no no no

Re: Dividing a field with time into 2 hour slots to find matching events in each slot

niketn — Wed, 16 May 2018 09:48:08 GMT

@bharathdoitnow will you have at-least one fruit in all two hour slots? If not can the slot be removed? Also is it only for one day or can it span more than one day? How would the layout look like in that case? (Your data seems to be from multiple days).

Re: Dividing a field with time into 2 hour slots to find matching events in each slot

HiroshiSatoh — Wed, 16 May 2018 10:06:32 GMT

How's this?
I think that it is good to keep _time because slot is difficult to edit.

(your search)
|timechart span=2h sum(count) as exist by fruit
|fillnull value=0
|untable _time fruit exist
|eval exist=if(exist=0,"no","yes")
|eval slot=strftime(relative_time(_time,"-2h@h"),"%H")+"-"+strftime(_time,"%H")
|xyseries  fruit slot exist

Re: Dividing a field with time into 2 hour slots to find matching events in each slot

bharathdoitnow — Tue, 29 Sep 2020 19:33:52 GMT

@niketn we may not have at least one fruit every hour, for example if Apple is present between 8-10 i want mark it as available. if apple is not present during 10-12 window, I want to mark it as "Not available" and create an alret to place a order for apple. in similar way i have 6 different fruits which i have to monitor for "today" with 2 hour slots from 8AM to 10PM with an option to check previous days information.

I am unable to use "_time" feature as it is inputlookup which is having a field containing Time. So even if I apply a time picker for last 60 minutes, it will keep showing information of all previous days so I have to put a filter withing the Time field to pick only for the current date which is a string but in the [2018-05-07 20:00:44] YYYY-MM-DD hh:mm:ss sequence.

Results:
Category Time c_time
1 APPLE 1526434675.000000 2018-05-16 11:37:55
2 APPLE 1526451005.000000 2018-05-16 16:10:05
3 APPLE 1526448470.000000 2018-05-16 15:27:50
4 Banana 1526466669.000000 2018-05-16 20:31:09
5 Mango 1526438117.000000 2018-05-16 12:35:17
6 Mango 1526443223.000000 2018-05-16 14:00:23

Re: Dividing a field with time into 2 hour slots to find matching events in each slot

bharathdoitnow — Thu, 17 May 2018 01:39:23 GMT

Results are like this:

Category Time c_time
1 APPLE 1526434675.000000 2018-05-16 11:37:55
2 APPLE 1526451005.000000 2018-05-16 16:10:05
3 APPLE 1526448470.000000 2018-05-16 15:27:50
4 Banana 1526466669.000000 2018-05-16 20:31:09
5 Mango 1526438117.000000 2018-05-16 12:35:17
6 Mango 1526443223.000000 2018-05-16 14:00:23

Re: Dividing a field with time into 2 hour slots to find matching events in each slot

bharathdoitnow — Tue, 29 Sep 2020 19:33:55 GMT

I tried but unable to use _time as the field shows blank and I had to convert my Inputlook fields which has time string and use. i got stuck here at the moment. Thank you