Splunk Search

How to use mstats and inputlookup command

abhi04
Communicator

Hi,

I have a alert query that uses mstats, I want this query to not throw alert during public holidays (from 9 AM to 5 PM). I have created a lookup holidays.csv with columns "Date","Description". How can i use this lookup with the already mstats command to check for the date and time in the lookup file and if its in the timerange in the file then not trigger the alert or probably not search. Thanks in advance.

Lookup file:

abhi04_0-1696527434097.png

 

Labels (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

Matching hour of day is exactly why you cannot use inputlookup subsearch.  But now you want 9 - 5:30 instead of 9 - 5, this means that we need to consider %M as well.

| mstats some filters
| <some more sophisticated filters>
| eval Date = split(strftime(_time, "%m/%d/%Y:%H%M"), ":")
| eval tod = mvindex(Date, 1), Date = mvindex(Date, 0)
| lookup mylookup Date
| where NOT (Description == "Bank holiday" AND tod > 900 AND tod < 1730)

View solution in original post

0 Karma

yuanliu
SplunkTrust
SplunkTrust

As you said "I" (you) have created the lookup table, my advice is to enter Date as epoch time if possible. (Splunk itself can help with that using commands like outputlookup.)  If you must use a human readable format such as "%m/%d/%Y" as your screenshot suggests, you need to convert your event _time to that same format.  Something like

| mstats some filters
| <some more sophisticated filters>
| eval Date = split(strftime(_time, "%m/%d/%Y:%H"), ":")
| eval hour = mvindex(Date, 1), Date = mvindex(Date, 0)
| lookup mylookup Date
| where NOT (Description == "Bank holiday" AND hour > 8 AND hour < 18)

Hope this helps.

Tags (1)
0 Karma

abhi04
Communicator

@yuanliu Thanks for the query, just wanted to update that I was able to get the results that are not in the holiday date but got stuck on how to also put a filter on the hour (9 Am - 5:30 PM on holiday)

The query I used to exclude events on holiday

| search NOT
[| inputlookup holidays.csv
| rename HolidayDate as Date
| fields Date ]

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Matching hour of day is exactly why you cannot use inputlookup subsearch.  But now you want 9 - 5:30 instead of 9 - 5, this means that we need to consider %M as well.

| mstats some filters
| <some more sophisticated filters>
| eval Date = split(strftime(_time, "%m/%d/%Y:%H%M"), ":")
| eval tod = mvindex(Date, 1), Date = mvindex(Date, 0)
| lookup mylookup Date
| where NOT (Description == "Bank holiday" AND tod > 900 AND tod < 1730)
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...