Help needed with a search and a lookup

GersonGarcia — Tue, 10 Oct 2017 16:21:49 GMT

All,

I have this search:

index=ssn sourcetype="agent" 
| rex field=_raw "Files:(?<customer>.*):/tmp/(?<filepath>.*):(?<filecount>.*)"  | stats sum(filecount) as filecount by customer host

It returns this data:

1 CUST001 host001 782
2 CUST002 host002 150
3 CUST003 host003 10
4 CUST004 host004 15
5 CUST005 host005 3
6 CUST006 host006 44
7 CUST007 host007 997
8 CUST008 host008 87
9 CUST009 host009 3587
10 CUST010 host010 18
11 CUST011 host011 273
12 CUST012 host012 20227
13 CUST013 host001 18

I need one alarm for hosts that are in a lookup table AND the filecount is 0. The lookup table is:

| inputlookup sldp-oo_customers

1   host001     CUST001
2   host001     CUST001
3   host001     CUST001
4   host020     CUST020

The output I need is:

1   CUST020     host020     0

As you may notice, the host in question does not have result in the first search, in this case it is missing data, but I want to be able to change the threshold (0 files) if needed.

The only way I found to achieve this result is to run it in 2 searches:

1) Generate another lookup file with the result of first search and schedule to run minutes before the second one:

 index=ssn sourcetype="agent" 
| rex field=_raw "Files:(?<customer>.*):/tmp/(?<filepath>.*):(?<filecount>.*)"  | stats sum(filecount) as filecount by customer host | outputlookup sldp-oo-filecount.csv createinapp=true

2) Run search using 2 lookup tables:

| inputlookup sldp-oo_customers | lookup sldp-oo-filecount.csv customer as customer host as host OUTPUT filecount | fillnull value=0 filecount | search filecount=0 | fields customer host filecount

Is there a better way to do this? Today is fine because the user want it to run every 24h, but it may became nightmare if I need to run it often.

Thank you very much for your help,

Gerson

Re: Help needed with a search and a lookup

kyaparla — Tue, 10 Oct 2017 22:41:09 GMT

You can use append command to concatenate results from your search and lookup.

Like this..

make sure the host fieldname in lookup is also host, otherwise rename it to host in second query

topic Help needed with a search and a lookup in Splunk Search

Help needed with a search and a lookup

Re: Help needed with a search and a lookup