Help needed with a search and a lookup

[GersonGarcia]

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

[kyaparla · Accepted solution]

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

Like this..

index=ssn sourcetype="agent"
| rex field=_raw "Files:(?.*):/tmp/(?.*):(?.*)" | stats sum(filecount) as filecount by customer host | append [| inputlookup sldp-oo_customers] | stats values(*) as * by host | fillnull value=0 | where filecount=0

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