topic Re: How to create total average/median/max of field in a separate table? in Splunk Search

How to create total average/median/max of field in a separate table?

LearningGuy — Mon, 23 Oct 2023 14:06:45 GMT

How to create total average/median/max of field in a separate table?
Thank you in advance

| index=testindex
| table company, ip, Vulnerability, Score

company	ip	Vulnerability	Score
CompanyA	ip1	Vuln1	2
CompanyA	ip1	Vuln2	0
CompanyA	ip2	Vuln3	4
CompanyA	ip2	Vuln4	2
CompanyA	ip3	Vuln5	3
CompanyA	ip3	Vuln6	5

Group by IP => This worked just fine
| stats values(company), avg(Score) as AvgScore by ip

company	ip	AvgScore
CompanyA	ip1	1
CompanyA	ip2	3
CompanyA	ip3	4

Group by Company => how do I group by company after group by ip (using stats) and put it on a separate table?
| stats avg(AvgScore) as Average, avgAvgScore) as Median, max( AvgScore) as Max by company

Company	Average	Median	Max
CompanyA	2.7	3	4

Re: How to create total average/median/max of field in a separate table?

richgalloway — Mon, 23 Oct 2023 15:03:59 GMT

A separate table requires a separate search.

If this is in a dashboard then consider making the first table a base search and the second table a post-processing of the first. That will save you time and resources when the dashboard runs.

... <search id="base">  <query>| index=testindex | table company, ip, Vulnerability, Score | stats values(company), avg(Score) as AvgScore by ip</query> ... </search> ... <search base="base">  <query>| stats avg(AvgScore) as Average, avgAvgScore) as Median, max( AvgScore) as Max by company</query> </search> ...

Re: How to create total average/median/max of field in a separate table?

LearningGuy — Mon, 23 Oct 2023 18:26:41 GMT

Hello,
I tried your suggestion and it worked fine. I am accepting this as a solution
Can you also suggest how to put the average, median and max on the bottom of the table?
Thank you again
Below is the example:

company	ip	AvgScore
CompanyA	ip1	1
CompanyA	ip2	3
CompanyA	ip3	4
	Average	2.7
	Median	3
	Max	4

Re: How to create total average/median/max of field in a separate table?

richgalloway — Tue, 24 Oct 2023 00:24:12 GMT

I think you can do that with the appendpipe command, which processes the current results and adds new results to bottom.

| stats values(company), avg(Score) as AvgScore by ip | appendpipe [ stats avg(AvgScore) as Average, median(AvgScore) as Median, max(AvgScore) as Max by company ]

Re: How to create total average/median/max of field in a separate table?

LearningGuy — Tue, 24 Oct 2023 00:11:44 GMT

Hello,

I tried the command you suggested and it did not show any effects
Please suggest.

Thanks

Re: How to create total average/median/max of field in a separate table?

richgalloway — Tue, 24 Oct 2023 00:24:56 GMT

I used the wrong case in the field name. Try my edited answer.

Re: How to create total average/median/max of field in a separate table?

LearningGuy — Tue, 24 Oct 2023 01:31:42 GMT

Hello,
There would be no difference because I converted your suggestion to my real data, so I already fixed any details
Please suggest.

Thanks,

Marius