How to read different time slots from lookup table

hanikawadhwa — Sat, 16 Nov 2019 02:34:53 GMT

Hi splunkers,

I have a situation to read different operational hours of same bin size for the last 3 days

Scenario:

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

Today 1 2 3 4 5
1 day before 1 2 3 4 5
2 day before 1 2 3 4 5

3 day before 1 2 3 4 5

As per Today's train scedule it will start at 10 operates till 12 after that it will take some rest and start again at 1 pm. Example: if today the train is in 1st hour of operation, i need to to count the alarmopened in 1st hour of operation for the last 3 days, divide it be 3 to compute average. If today's count of alarm opened is greater than the average, it will give alerts. The same happens for all hours of operation.

Question: My problem is how can I take the same time slot of previous last 3 days. If Now my train in 2nd hour of operation, how can I get the 2nd hour of operation for the last 3 days

Note: Bin size is same
everyday running for 5 hours

TIA

Re: How to read different time slots from lookup table

woodcock — Mon, 18 Nov 2019 20:41:36 GMT

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

topic How to read different time slots from lookup table in Splunk Search

How to read different time slots from lookup table

Re: How to read different time slots from lookup table