Timetable/Schedule is been given in lookup table, how to use it in splunk query

[hanikawadhwa]

Hi Splunkers,

I am stuck in a situation where I have been provided an input lookup file containing operational hours of a train.

          9-10      10-11   11-12   12-13   13-14   14-15  15-16    16-17 ...................23-24

Today 1 2 3 4 5
T-1 1 2 3 4 5
T-2 1 2 3 4 5
T-3 1 2 3 4 5

Bin Size is 1 hour in this case and schedule of the same train for the previous 3 days has been provided with the same bin size. Scenario: Today's schedule is that the train's 1st hour of operation is 9-10 and 2nd hour of operation is 10-11 and so on. everyday train is running for 5 hours. so in the table 5 hours of operations are mentioned.
Let's say as per current time I am in the 1st hour of operation so I need to consider the 1st hour of operation for the last 3 days count their alarmopened and divide it by 3 to get the average. If today, number of alarm opened in 1st hour of operation is more than the average calculated on the basis of 1st hour of operation for the last 3 days, it will give alerts.

Question: How I can mark the hour of operations of previous days. If today I am in 2nd hour of operation, how to get the count of alarm opened in 2nd hour of operation in previous 3 days?

Logically I am able to understand the scenario but can't think of implement in splunk. Please guide.
Hope my question is clear.
TIA

[woodcock]

It seems to me that the lookup table is irrelevant. Let us assume that your alarms data has a valid _time and ASSET_NAME value. If so, you can do what you asked like this:

|makeresults
| eval _raw = "sourcetype,ASSET_NAME,DateTime
Alarm,A,2019-10-26T08:01:01
Alarm,A,2019-10-26T08:02:02
Alarm,A,2019-11-02T08:01:01
Alarm,A,2019-11-02T08:02:02
Alarm,A,2019-11-09T08:01:01
Alarm,A,2019-11-09T08:02:02
Alarm,A,2019-11-16T08:01:01
Alarm,A,2019-11-16T08:02:02
Alarm,A,2019-11-16T08:02:03
Alarm,A,2019-11-16T08:02:04"
| multikv forceheader=1 
| eval _time = strptime(DateTime, "%Y-%m-%dT%H:%M:%S")

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval DayOfWeek = strftime(_time, "%a")
| eval HourOfOperation = tonumber(strftime(_time, "%H")) - 7
| eval week = _time
| bin week span=1w
| stats count AS alarms max(_time) AS _time BY ASSET_NAME HourOfOperation DayOfWeek week
| tail 4
| multireport
[ tail 3 | stats avg(alarms) AS alarm_threshold BY ASSET_NAME HourOfOperation ]
[ head 1 ]
| selfjoin ASSET_NAME HourOfOperation
| where alarms > alarm_threshold

[to4kawa]

Hi,I understand what I want to do, but I don't understand the contents of CSV well.
Is there no sample log?

[hanikawadhwa]

ASSET_NAME Date Starttime Endtime Hour of operation

A 2019-11-16 08:00:00 08:59:59 1
A 2019-11-16 09:00:00 09:59:59 2
A 2019-11-16 10:00:00 10:59:59 3
A 2019-11-16 13:00:00 14:00:59 4
B 2019-11-15 08:00:00 08:59:59 1
B 2019-11-15 11:00:00 11:59:59 2
B 2019-11-15 12:00:00 12:59:59 3
B 2019-11-15 13:00:00 14:59:59 4
C 2019-11-14 09:00:00 09:59:59 1
C 2019-11-14 10:00:00 10:59:59 2
C 2019-11-14 11:00:00 11:59:59 3
C 2019-11-14 17:00:00 17:59:59 4

My lookup table is something like this

[woodcock]

This is a good start. Now that I see the lookup table/data, what do the events themselves look like. And once we have that create a mockup of your desired final output. Now add some text to describe what transformation (in plain english) moves from data to mockup.

[to4kawa]

| makeresults
| eval _raw="ASSET_NAME,Date,Starttime,Endtime,HourOfOperation
A,2019-11-16,08:00:00,08:59:59,1
A,2019-11-16,09:00:00,09:59:59,2
A,2019-11-16,10:00:00,10:59:59,3
A,2019-11-16,13:00:00,14:00:59,4
B,2019-11-15,08:00:00,08:59:59,1
B,2019-11-15,11:00:00,11:59:59,2
B,2019-11-15,12:00:00,12:59:59,3
B,2019-11-15,13:00:00,14:59:59,4
C,2019-11-14,09:00:00,09:59:59,1
C,2019-11-14,10:00:00,10:59:59,2
C,2019-11-14,11:00:00,11:59:59,3
C,2019-11-14,17:00:00,17:59:59,4"
| multikv forceheader=1
| table ASSET_NAME Date Starttime Endtime HourOfOperation
`comment("this is sample data, please use inputlookup your_csv")`
| eval earliest=strptime(Date." ".Starttime,"%Y-%m-%d %H:%M:%S")
| eval latest=strptime(Date." ".Endtime,"%Y-%m-%d %H:%M:%S")
| fields earliest latest
| format "(" "(" "" ")" "OR" ")"

I don't know the status of the main search, such as the number of alerts, so I made it for sub-search.

If you use where to limit the number of HourOfOperation, you can count alerts in that period in the main search.