How to list the difference between two searches

[bgill0123]

I currently have two searches that produce two different numbers:
|metadata type=hosts |search host=abc1* or host=abc2* | stats count as Total

index=main host=abc1* or host=abc2* | timechart span=1d dc(host)

the first search is for 30 days the second search is for one day.

can I produce a search that will tell me the difference between the two?

[DalJeanis]

Not sure why you are comparing the results of those particular searches. Metadata is not always going to be consistently the same as the detailed event data on the actual index, so if you're using metadata for one side, you should use it for the other. You can also get that information in a single pass at the metadata, since you are not counting anything, just checking for the presence of records. All you need is the earliest and latest _time values in the 30 day window.

I'm assuming you are looking for hosts that have not reported today. Here's how I'd do that.

| metadata type=hosts 
| search host=abc1* or host=abc2* 
| eval host=upper(host) 
| stats max(lastTime) as lastTime, max(recentTime) as recentTime, min(firstTime) as firstTime by host
| rename COMMENT as "now we have one record for each host, no matter what the hostname capitalization may have been"
| rename COMMENT as "with the first and last event _times in that range, plus the _time of the most recent event"

| rename COMMENT as "anything after midnight yesterday will be considered to be 'today' for comparison purposes"
| rename COMMENT as "we will set one counter field for 'existed yesterday to 30 days ago' and one for 'existed today'"
| rename COMMENT as "since each record is a different host at this point, summing up the values gets you the distinct count."
| eval startofday=relative_time(now(),"-1d@d")
| eval yesterday=case(firstTime<startofday,1)
| eval today=case(lastTime>=startofday,1)
| eval oldmissing=case(isnull(today),host)
| eval newtoday=case(isnull(yesterday),host)
| stats count as hostsTotal 
    sum(yesterday) as hostsPrior 
    sum(today) as hostsToday 
    count(oldmissing) as hostsMissingCount
    values(oldmissing) as hostsMissingList
    count(newtoday) as hostsNewCount
    values(newtoday) as hostsNewList

[mayurr98]

Try this

index=main host=abc1* or host=abc2* | timechart span=1d dc(host) as total_host 
|  appendcols 
    [|metadata type=hosts |search host=abc1* or host=abc2* | stats count as Total] 
|  eval difference=Total-total_host

and thereafter if you are just interested in difference then append | fields difference at the end of the query.

let me know if this helps!

[DalJeanis]

@mayurr98 - appendcols is only useful when you can be absolutely certain that the two result sets will line up. In this case, the very point of the search assumes they will not. In this case, done similar to that, you'd need a join instead.

[mayurr98]

From the explanation he is just interested in single value difference between two searches. that is why I suggested appnedcols.