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!

.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 ...