topic Re: How to use mstats and inputlookup command in Splunk Search

How to use mstats and inputlookup command

abhi04 — Thu, 05 Oct 2023 17:38:20 GMT

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:

Re: How to use mstats and inputlookup command

yuanliu — Thu, 05 Oct 2023 17:53:57 GMT

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.

Re: How to use mstats and inputlookup command

abhi04 — Thu, 05 Oct 2023 19:29:49 GMT

@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 ]

Re: How to use mstats and inputlookup command

yuanliu — Thu, 05 Oct 2023 19:45:43 GMT

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)