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

[bharathdoitnow]

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

[HiroshiSatoh]

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

[bharathdoitnow]

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

| inputlookup test
| fields Category,Time
| eval Time= strptime(Time, "%Y-%m-%d %H:%M:%S")
| where Time>=relative_time(now(),"-1d")
| eval c_time=strftime(Time,"%F %T")

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

[niketn]

@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).

[bharathdoitnow]

@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.

Current Query:
| inputlookup test
| fields Category,Time
| eval Time= strptime(Time, "%Y-%m-%d %H:%M:%S")
| where Time>=relative_time(now(),"-1d")
| eval c_time=strftime(Time,"%F %T")

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

[bharathdoitnow]

my Query and results
| inputlookup test
| fields Category,Time
| eval Time= strptime(Time, "%Y-%m-%d %H:%M:%S")
| where Time>=relative_time(now(),"-1d")
| eval c_time=strftime(Time,"%F %T")
:

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