Splunk Search

How to read different time slots from lookup table

hanikawadhwa
Explorer

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

0 Karma

woodcock
Esteemed Legend

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
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...