topic Re: How to determine if event is in list of outages? in Splunk Search

How to determine if event is in list of outages?

apps_inpaytech — Mon, 24 Oct 2022 23:55:33 GMT

I have a seemingly simple request: list the events and indicate if it occurred during an outage.

I have been trying for ages and I cannot get it to work, can anyone please help?

Base search for events:

index=api_calls

CSV lookup to record the outage windows, called 'outages.csv' (UK style dates):

DateFrom	DateTo	Reason
01/09/2022 09:00:00	30/09/2022 23:00:00	Testing 1
01/10/2022 09:00:00	31/10/2022 09:00:00	Testing 2

This produces the correct outage row:

| inputlookup outages.csv 
| eval t=now()
| eval DateFromEpoch=strptime(DateFrom, "%d/%m/%Y %H:%M:%S") 
| eval DateToEpoch=strptime(DateTo, "%d/%m/%Y %H:%M:%S")
| where DateFromEpoch <= t and DateToEpoch >= t
| table Reason

Output is: Testing 2

I would have expected this to add the Reason field to the base results:

index=api_calls
| append 
  [ inputlookup outages.csv 
  | eval t=_time
  | eval DateFromEpoch=strptime(DateFrom, "%d/%m/%Y %H:%M:%S") 
  | eval DateToEpoch=strptime(DateTo, "%d/%m/%Y %H:%M:%S")
  | where DateFromEpoch <= t and DateToEpoch >= t
  | table Reason ] 
| table _time Reason *

But for some reason I cannot get anything to add to the search, not even

index=api_calls
| append [ | makeresults
  | eval Reason="hello" | table Reason ]
| table _time Reason *

Ideally, I would like this to be as a macro so I can re-use it easily:

index=api_calls
| `is_outage(_time)`
| table _time Reason *

I'm doing something wrong, any help appreciated.

Re: How to determine if event is in list of outages?

johnhuang — Mon, 24 Oct 2022 23:57:33 GMT

Time based lookup may work. Few questions:

Can outages overlap, e.g. multiple outages.

Can we put a constraint around the max length of outages, e.g. all outages are resolved in x time?

Re: How to determine if event is in list of outages?

richgalloway — Tue, 25 Oct 2022 00:12:48 GMT

It would help to see a sample expected output, but it looks like you've omitted a key step.

The append command runs a search and puts the results after the result obtained up to that point. Think of the results as rows in a spreadsheet - append adds rows on the bottom. So the example queries may produce many rows of events from the api_calls index followed by a single row with "Reason 2" at the bottom.

The trick is to get the Reason value at the bottom distributed to all of the rows above. Here's one way.

See my BSides 22 talk on append and appendcols (https://www.youtube.com/watch?v=m8YLT8eM1k4) for more information.

Re: How to determine if event is in list of outages?

apps_inpaytech — Tue, 25 Oct 2022 00:45:41 GMT

I can ensure outages do not overlap if it's easier.

Max length would be unknown, could be for days or minutes.

Re: How to determine if event is in list of outages?

apps_inpaytech — Tue, 25 Oct 2022 00:51:44 GMT

The expected output would be something like:

Date	Outage Reason	Client	Duration(ms)	Parameters
20/08/2022		Company A	100	blah
20/09/2022	Testing 1	Company A	60000	fred
20/10/2022	Testing 2	Company B	60000	foo
20/11/2022		Company A	150	goldfish

With Date, Client, Duration, Parameters coming from index api_calls

With this data I can filter and produce the reports the business needs.

Thanks

Re: How to determine if event is in list of outages?

johnhuang — Tue, 25 Oct 2022 00:53:43 GMT

This is how you could configure a time based lookup which should be magnitudes faster than using append.

1. Create outages_time_lookup.csv from your original lookup:

2. Configure a new lookup definition (use default value for anything not defined below):

Name: outages_time_lookup
Lookup file: outages_time_lookup.csv
Name of time field: DateFromEpoch
Minimum offset: 0
Advanced Options -> Maximum matches: 1

3. Sample query using the time based lookup

index=api_calls | eval lookup_value=1 | lookup outages_time_lookup lookup_value OUTPUT DateToEpoch Reason | eval is_outage=IF(_time<DateToEpoch, "Y", "N") | eval outage_reason=IF(_time<DateToEpoch, Reason, "")

Re: How to determine if event is in list of outages?

apps_inpaytech — Tue, 25 Oct 2022 02:46:16 GMT

Genius!!

Thankyou, that worked a treat 😀