Matching event results to a lookup field with a wildcard.

[joeybagofdonuts]

Hello!

I'm trying to build out a lookup of services on specific servers that I want to know when they've stopped. But I wanted to use wildcards for servers so I didn't need to type out a lot of servers.

This is the some sample data and the base of the search that I've been playing with.

host	Name	severity	failuresAllowed
server1234	service1	low	3
server1*	service2	high	1
server2*	service3	medium	2

index=windows source=service earliest=-20m
[inputlookup Windows_App_Services.csv | table host Name ]
| stats count(eval(if(State!="Running",1,null()))) as failureCount by host Name
| join host Name type=outer [inputlookup Windows_App_Services.csv]


The first inputlookup pulls in just the server name and service we're looking at so that I can search only those events. Then I count how many of those events have a State of not running so I know how many times in the 20 minute lookup back period they haven't been running. Then I'd like to pull in severity and failuresAllowed so that I can use those to calculate severity in ITSI, but when I try to do the join it does not work because the host doesn't match what's in the lookup since it's wildcarded.

I've tried creating a wildcard match_type on that lookup, but that doesn't seem to help me. Anyone have any ideas?

Thanks for your help!

[richgalloway · Accepted solution]

Create a new lookup definition called "Windows_App_Services" that references Windows_App_Services.csv.  In the Advanced Options section, set the Match type field to "WILDCARD(host)".

Then replace the join in the query with a lookup.

index=windows source=service earliest=-20m
[inputlookup Windows_App_Services.csv | table host Name ]
| stats count(eval(if(State!="Running",1,null()))) as failureCount by host Name
| lookup Windows_App_Services host OUTPUT severity failuresAllowed

[joeybagofdonuts]

Thanks! That worked for me, I was messing up the syntax of calling the lookup. I've accepted this as the solution, but I have a follow up question if you'd be so inclined.

Say I want to take it a step further and be able to list multiple hosts and be able to wild card. Like this for example:

host	Name	severity	failuresAllowed
server123::server234::server345	Service1	low	3
server1*	Service2	high	1
server123	Service3	medium	2

This works for the 2nd and 3rd row. Note that I'm doing my lookup by host and Name because certain servers could be looking at the same services with different severities resulting if it's stopped.

index=windows source=service earliest=-20m
[inputlookup Windows_App_Services.csv | table host Name | makemv delim=:: host | mvexpand host]
| stats count(eval(if(State!="Running",1,null()))) as failureCount by host Name
| lookup Windows_App_Services host Name OUTPUT severity failuresAllowed

However I'm not sure how to split out the hosts in the first row as this doesn't quite do it.

index=windows source=service
[inputlookup Windows_App_Services.csv | table host Name | makemv delim=:: host | mvexpand host]
| stats count(eval(if(State!="Running",1,null()))) as failureCount by host Name
| lookup Windows_App_Services_Lookup host Name | makemv delim=:: host | mvexpand host

I'm at a point where I could probably bang my head against a wall for a little and figure it out, but any advice would be much appreciated!

[joeybagofdonuts]

I did some headslamming and realized I could just do this. Might not be the most efficient so still open to critiques, but this got me the results I wanted!

index=windows source=service
[inputlookup Windows_App_Services.csv | table host Name | makemv delim=:: host | mvexpand host]
| stats count(eval(if(State!="Running",1,null()))) as failureCount by host Name
| lookup Windows_App_Services_Lookup host Name
| join host Name type=outer [inputlookup Windows_App_Services.csv | makemv delim=:: host | mvexpand host]