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
Try this for example:
sourcetype=serverevents [inputlookup your_lookup | fields servername] | stats count by servername | appendcols [| inputlookup your_lookup] | fillnull count | fields location count
Try this for example:
sourcetype=serverevents [inputlookup your_lookup | fields servername] | stats count by servername | appendcols [| inputlookup your_lookup] | fillnull count | fields location count
@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)
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. 🙂
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.