search language

[francly]

Hi, how do I get subtotal count for each Host and Total for all count, in additional count for all different status.

Host                            Status                             Count

HostA	Disconnected	1
HostA	Running	19
HostA	RunningWithErrors	2
HostA	BadConnectivity	2
HostB	Disabled	2
HostB	Disconnected	1
HostB	Running	17
HostB	RunningWithErrors	5
HostC	BadConnectivity	1
HostC	Running	7
HostC	RunningWithErrors	5

[PickleRick]

You need two different searches - one to sum count over hosts (and then do addtotals to get total sum) and another one to sum over statuses. That's the simplest solution I think

[francly]

thanks for response, can you provide the query.

[gcusello]

Hi @francly,

you could try something like this.

index=your_index
| stats count BY host Status
| append [ search 
     index=your_index
     | stats count BY host
     | eval Status="Total"
     ]
| sort host Status
| rename host AS Host

Ciao.

Giuseppe

[PickleRick · Accepted solution]

You're using count as a splunk function whereas in original post count is a field. So your count will just count the counts 😉 and what seems to be really needed is a sum of counts.

And your construction will yield some strange results.

What the OP wanted was simply one:

<original search> | stats sum(Count) by Host | addtotals row=f col=t labelfield=Host

and two:

<original search>| stats sum(Count) by Status | <optionaly addtotals as in example above>