result in single row

[roopeshetty]

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	CC
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?

[johnhuang · Accepted solution]

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

[PickleRick]

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.

[roopeshetty]

thanks @johnhuang  it worked.