How to see also events with no findings based on lookup

[fred1455]

Hello,

as a Lookup I definded a List of locations and servers

location, servername
Paris, Server1
Paris, Server2
Madrid, Server3
Madrid, Server4
Milano, Server 5

in my sourcetype=serverevents I do have events including the field servername.

How can I make a query based on inputlookup to count and see also locations with no events.

So like

Paris=4 Events
Madrid=0 Events
Milano= 3 Events

Can you helo me please?

Thanks

MIke

[knielsen · Accepted solution]

Try this for example:

sourcetype=serverevents [inputlookup your_lookup | fields servername] | stats count by servername | appendcols [| inputlookup your_lookup] | fillnull count | fields location count

[DalJeanis]

@knielsen - appendcols won't work for that - it will add columns to the existing sums, not add events where there wasn't a match. See if you can figure out another way. (hint - append, coalesce, stats)

[knielsen]

Well, I honestly tried that out with one of our own lookup files, where I have ~100 lines in the lookup. I got a count > 0 for like 20 of those on a limited data set without the appendcols, and adding the appendcols added the unused entries with count=0 as well.

Don't know why it doesn't work for you, Sorry. 🙂

edit: actually, I shut up on this one. I didn't check my ids reappearing, and that even makes sense. So yeah, I am wrong. 🙂

[knielsen]

And then again, it works as expected when I do it the other way round, start with the inputlookup for the whole table, then add the count for things that are found, then fill count with 0. I got no more duplicated ids then, as the count is correct for the ids found, 0 for non found. No appendcols, just append...

 | inputlookup your_lookup | append [ search sourcetype=serverevents [| inputlookup your_lookup | fields servername ] |stats count by servername] | stats first(*) as * by servername | fillnull count

I double checked the result of that now within my data sets, this should be fine.