topic Re: result in single row in Splunk Search

result in single row

roopeshetty — Sat, 22 Jan 2022 03:41:20 GMT

Hi Guys

I have a query like this

<query>| stats avg(CurrentConnections) as CC by host

And the output is as below with multiple rows

But we have a requirement to get all the results in a single row (all outputs are required but in a single row instead of multiple rows one after one) some thing like this;

host

server01

server02

server03

server04

server05

server06

368.333333333333

365.333333333333

345.333333333333

379.666666666666

356.333333333333

381.666666666666

Can someone please guide us how to do this?

Re: result in single row

johnhuang — Sat, 22 Jan 2022 06:46:43 GMT

<query>
| stats avg(CurrentConnections) as CC by host
| stats list(host) AS host list(CC) AS CC

Re: result in single row

roopeshetty — Sat, 22 Jan 2022 07:58:57 GMT

thanks @johnhuang it worked.

Re: result in single row

PickleRick — Sat, 22 Jan 2022 17:26:41 GMT

One big caveat though. Remember that list() doesn't care about "continuity" of entries before merging them. So if you have empty results (in your example - hosts without connections), you'll get "wrong" results.

Compare results of

| makeresults annotate=f count=100 
| streamstats count 
| table count 
| eval value=if(count%10!=3,count,null())
| stats avg(value) as avg by count

with

| makeresults annotate=f count=100 
| streamstats count 
| table count 
| eval value=if(count%10!=3,count,null())
| stats avg(value) as avg by count
| stats list(avg) list(count)

You could use fillnull to fill the "gaps"

| makeresults annotate=f count=100 
| streamstats count 
| table count 
| eval value=if(count%10!=3,count,null())
| stats avg(value) as avg by count
| fillnull value="N/A"
| stats list(avg) list(count)

But in general - it's best to avoid operating on such formed multivalue fields and don't rely on the order of values of those fields.