topic Re: Counting events only once using different fields in specific orders. in Splunk Search

Counting events only once using different fields in specific orders.

Lucas_K — Thu, 27 Nov 2014 23:47:48 GMT

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.

Re: Counting events only once using different fields in specific orders.

martin_mueller — Sat, 29 Nov 2014 14:03:52 GMT

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?

Re: Counting events only once using different fields in specific orders.

Lucas_K — Mon, 28 Sep 2020 18:18:00 GMT

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 🙂

Re: Counting events only once using different fields in specific orders.

Lucas_K — Sun, 30 Nov 2014 23:41:31 GMT

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

Re: Counting events only once using different fields in specific orders.

martin_mueller — Sun, 30 Nov 2014 23:44:08 GMT

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.