To Get sum of hosts

[xvxt006]

Hi,

i have a simple query where i am getting response times by host. i want to get the sum of hosts as a filed. I have to use that field in Slack.

host=xxx* sourcetype=yyyyy | stats avg(time) by host

So i have tried to use eventstats but that did not accept host as an argument. Any suggestions please?

[jkat54 · Accepted solution]

If you mean a distinct count of hosts:

 host=xxx* sourcetype=yyyyy | stats avg(time) dc(host) by host

However, with the by clause dc(host) will always equal 1 so you'll need something like appendpipe:

 host=xxx* sourcetype=yyyyy | stats avg(time) by host | appendpipe [stats dc(host)]

Maybe you're looking to addcoltotals?

 host=xxx* sourcetype=yyyyy  | stats avg(time) by host | addcoltotals fieldname=avg(time))

If you mean a sum of time by hosts:

 host=xxx* sourcetype=yyyyy | stats avg(time) sum(time) by host

If you meant something else, please explain.

There's also commands called addtotals, appendcols, append, etc. which you may be interested in.

[jkat54]

Hey, you gave me some points, but did I answer the question? If so, can you let us know which search worked?

[xvxt006]

Hi, Thanks for your responses. Those were helpful in finding out different options. i have used appendcols and in combination with eventstats count(eval(match(host, "^x.*"))) as TotalHosts

[ppablo]

Hi @xvxt006

Glad you were able to find a solution through @jkat54 🙂 Please don't forget to resolve the post by clicking "Accept" directly below his answer. This will make it easier to find for other users with similar questions.

[xvxt006]

Hi, Thank you for the reply. Say there are 4 distinct hosts, i need a filed which shows that count as 4. As i mentioned, i want to use that in Slack.

[jkat54]

Ok you want appendcols most likely:

 host=xxx* sourcetype=yyyyy | stats avg(time) by host | appendcols [search host=xxx* sourcetype=yyyyy |stats dc(host)]

Or maybe remove the by clause:

 host=xxx* sourcetype=yyyyy | stats avg(time) dc(host)

Unfortunately I have no idea what is meant by "want to use that in Slack" as I have no experience with "Slack".

[jkat54]

This is probably worth a good read too as it goes into differences between chart and stats:

https://answers.splunk.com/answers/32001/difference-between-stats-and-chart.html