A potentially simple question that i'm just missing the obvious answer to 😉
Say for example we have the following events.
line 1 : time, fred, wilma
line 2 : time,fred, barney,
line 3 : time,fred, barney, wilma, bam
line 4 : time,barney, wilma, bam
We want the following counts of events creating columns of total, bam, barney, fred and wilma in a specific order and once counted these are not to be counted again even if the field exists.
Column : # (Line where it should have come from.)
Total : 4 (lines 1 to 4)
bam : 2 (lines 3,4)
barney : 1 (line 2)
fred : 1 (line 1).
wilma : 0 (no lines remaining).
I was also thinking if there was a precedence/priotity order to eventtypes or tags that I could utilise to get the same result.
You could do something like this:
| stats count | eval dummy = "line 1 : time, fred, wilma
line 2 : time,fred, barney,
line 3 : time,fred, barney, wilma, bam
line 4 : time,barney, wilma, bam" | makemv dummy delim="
" | mvexpand dummy | rename dummy as _raw
| eval dominant_character = case(searchmatch("bam"), "bam", searchmatch("barney"), "barney", searchmatch("fred"), "fred", searchmatch("wilma"), "wilma")
| stats count by dominant_character
| addcoltotals count
| fillnull dominant_character value="Total"
Do you need the result list to include zeros and follow the desired order as well?
You could do something like this:
| stats count | eval dummy = "line 1 : time, fred, wilma
line 2 : time,fred, barney,
line 3 : time,fred, barney, wilma, bam
line 4 : time,barney, wilma, bam" | makemv dummy delim="
" | mvexpand dummy | rename dummy as _raw
| eval dominant_character = case(searchmatch("bam"), "bam", searchmatch("barney"), "barney", searchmatch("fred"), "fred", searchmatch("wilma"), "wilma")
| stats count by dominant_character
| addcoltotals count
| fillnull dominant_character value="Total"
Do you need the result list to include zeros and follow the desired order as well?
If your fields are properly extracted then isnotnull(field)
should work as well. For the default case at the end I usually use 1=1
, that's universally recognizable as not doing anything special by everyone looking at it later.
Ah yes searchmatch!
As I was looking for the existance of a fieldname so searchmatch("myfield=*") works like a charm 🙂
I did need a zero's filling the rest of the non-dominant character.
Resulting example search for multiple hosts. Iwill actually need around 30 columns but this gives a good idea of how it will work.
index=blah host=blah*
| eval dominant_character = case(searchmatch("auth="), "auth", searchmatch("ssh="), "ssh", searchmatch("trap="), "trap", searchmatch(""), "other"))
| stats count(dominant_character) AS Count by host, dominant_character
| chart sum(Count) AS Count over host by dominant_character
| addtotals | fillnull value=0 | fields host Total * other
Result:
host Total auth ssh trap other
blah1 10452 362 0 21 10069
blah2 1796 629 590 3 574
blah3 7970 362 0 3 7605
blah4 5042 362 0 3 4677
Thank you very much Martin! I was hoping to get an answer from you 🙂
I did find an issue where if there isn't a single host with a particular field it will not show at all.
I could create a single evaled host event that contains a single value for every column and then just remove this at the end just prior to it being displayed.
edit:
| addtotals
| append [search | stats count
| eval host="dummy_host" | eval auth=0 | eval ssh=0 | eval underObs=0 | eval chatter=0 | eval authFail=0 | eval telnetErr=0 | eval backupFail=0 | eval trap=0
| eval pmd=0 | eval swo=0 | eval acl=0 | eval disPkt=0 | eval LBerr=0 | eval errConn=0 | eval getPeer=0 | eval disk=0 | eval hm=0 | eval lcnm=0 | eval ctp=0
| eval crc=0 | eval cnmMgr=0 | eval cardSeat=0 | eval tools=0 | eval underObs=0 | eval chatter=0| fields - count]
| fillnull value=0 | fields host Total auth * other
| search host!=dummy_host
| addcoltotals