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.
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.
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
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