Hi all
I'm looking to create a count of events that a list of strings appear in. So count the number events that Item1 appears in, how many events Item2 appears in etc. The query so far looks like this:
index=[index] message IN ("Item1*", "Item2*", "Item3") | stats count by message
For it to then produce the following
Item1 8
Item2 10
Item3 4
However Item2 has multiple slightly different variations meaning I get a bunch of individual items in the list alongside groupings looking like the following:
Item1 8
Item2 - /folder1/fileA error 1
Item2 - /folder2/fileB error 1
Issue with /folder2/fileC [Item2 error] 1
Is there a way to coalesce the results but still have the total number of events by search string?
I've tried searching but haven't managed to find anything on here yet. Any help would be appreciated!
Thank you
If you know all of the variations that the items can take, you can write a lookup table for it. I would get the values doing something like index=[index] message IN ("Item1*", "Item2*", "Item3") | table message |dedup message
and then manually coalesce the values in a lookup table (depending on the structure of the data, you may be able to use a wildcard lookup). Once you have the lookup defined, you can then do your search on the new coalesced field.
Not sure if all the variations can be known, the searched Item1, Item2 in the events often have differing messages attached depending on what's occurring that day. The data in the table (and the original search string itself) resembles this:
Item2 - /folder1/fileA error
Item2 - /folder2/fileB error
Issue with /folder2/fileC [Item2 error]
I'm not 100% sure if this will work, but I would try to build the lookup table something like this
in, out
Item1*, Item1
*Item2*, Item2
Item3, Item 3
and when you define the lookup check the advanced settings box, and under the match type box it would be something like WILDCARD(in)