How do you compare sets of field values from two searches?

[cinchnetops]

I'm basically trying to identify whether some of my hosts are not doing something successfully as it should be in a daily basis, and alert as needed.

The process would output specific line if the task is successful and I only need to match once.

so I'm been trying to do sub-search without much success.

my searches so far:
try 1:
sourcetype="myapp" | dedup host | eval allhost=host | eval joinf=1 | join max=0 joinf [search sourcetype="myapp" "Database updated" | dedup host | eval updatedhost=host | eval joinf=1] | eval match=if(allhost==updatedhost, 1,0)

try 2:
sourcetype="myapp" | dedup host | stats dc(host) as allhost | appendcols [search sourcetype="myapp" "Database updated" | dedup host | stats dc(host) as updatedhost ] | eval nodiff=if(match(allhost,updatedhost),"True","False") | table nodiff

^^^ this only match total host count which I need more details (ie. which host does NOT match)

try 3:
sourcetype="myapp" OR (sourcetype="myapp" "Database updated") | streamstats count by host | stats values(host) as host | mvexpand host | eval Status = if(match(host), "MATCH","NO MATCH") | table host,Status

^^^ not working since I don't know how to identify the second set of 'host' for the match

[DalJeanis · Accepted solution]

Try this...

sourcetype="myapp"
| rex "(?<updated>Database updated)"
| stats count values(updated) as updated by host

If there is a count but no 'updated', then it has not been updated.

[cinchnetops]

thanks! That's exactly the result I need. The takeaway is that I don't need to do two separate searches on this type of data gathering.

[DalJeanis]

Yes. Always think in terms of collecting ALL the data at one time, differentiating between the types of data, then chewing up the totals.

The tools for copying information from one type of record to another is the stats family... stats, eventstats, and streamstats. Use stats if there is a single key and the different kinds of records won't stomp on each other's data. Use eventstats if you need to generate and use group totals without destroying the underlying records. Use streamstats when you need to relate the records based on both key and order, for example when you need the last record of type X before type Y.

As an alternate technique, you can also use appendpipe to split off a group of records and analyze them, then use eventstats to roll the information back from the subset onto the regular records. (For example, if you wanted to have all the detail records for the groupid that had the five highest dollar totals, you use something like this...

| appendpipe [
    |stats sum(total) as sumtotal by groupid 
    |sort 5 - total  
    |table groupid sumtotal 
    ] 
 | eventstats values(sumtotal) as sumtotal by groupid
 | where isnotnull(sumtotal)