Splunk Search

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

hanikawadhwa
Explorer

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

Tags (1)
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

to4kawa
Ultra Champion

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

0 Karma

hanikawadhwa
Explorer

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

to4kawa
Ultra Champion
| 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.

0 Karma
Get Updates on the Splunk Community!

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...