topic Combining stats search results in Splunk Search

Combining stats search results

Thuan — Mon, 28 Sep 2020 16:34:37 GMT

I run a search on a field that has multiple values. For example the field quest_name has the following values

quest_name

1 wpad.TASCNET.tasc.com
2 wpad.tascnet.tasc.com
3 wpad.stafford.net

1). I can compute the number of records that exists for each field value with the following search

index=dns* quest_name=wpad* | stats count by quest_name | sort - count

The results are

quest_name      count

1 wpad.TASCNET.tasc.com 5777
2 wpad.tascnet.tasc.com 1324
3 wpad.stafford.net 225

2). I can compute the total number of records for all values for quest_name with the following search

index=dns* quest_name=wpad* | stats count(quest_name) AS total

The results are

total

1 9492

3). Now I want to obtain the percentage of each field value in relation to the "total" value using a single search to show the following calculations:

quest_name      count   percent

1 wpad.TASCNET.tasc.com 5777 5777/9492=
2 wpad.tascnet.tasc.com 1324 1324/9492=
3 wpad.stafford.net 225 225/9492=

This I have not been able to do. Your help is requested. Thank you.

Re: Combining stats search results

linu1988 — Fri, 09 May 2014 20:04:41 GMT

Hello Thuan,
Try this

index=dns quest_name=wpad | stats count by quest_name|eval a="a1"|join a[|search index=dns quest_name=wpad | stats count(quest_name) AS total|eval a="a1"|table a,total]|eval percent=(count/total)*100|eval percentage=percent.%|table quest_name,count,percentage

OR you may as look at the below simple one, i guess both give the same result.

index=dns quest_name=wpad*|top quest_name

Thanks

Re: Combining stats search results

somesoni2 — Mon, 12 May 2014 20:07:14 GMT

The top command can definitely give you the result.

  index=dns quest_name=wpad* | top limit=0 quest_name

Other option is as below:

index=dns quest_name=wpad | stats count by quest_name | sort - count | eventstats sum(count) as total | eval percent=round(count*100/total,2) | fields - total

Re: Combining stats search results

Thuan — Tue, 13 May 2014 16:01:54 GMT

Thank you for the diversity of answers.
I now get to know how to use subsearch, join, eventstats.
One note though about the captchas. I have tried to answer much earlier but I have so many captchas.