Host Metrics with associated indexes

[WebNerdNick]

We're using this query to retrieve metrics on our hosts:

index=_internal source=*metrics.log group=tcpin_connections 
| eval sourceHost=if(isnull(hostname), sourceHost,hostname) 
| rename connectionType as connectType
| eval connectType=case(fwdType=="uf","univ fwder", fwdType=="lwf", "lightwt fwder",fwdType=="full", "heavy fwder", connectType=="cooked" or connectType=="cookedSSL","Splunk fwder", connectType=="raw" or connectType=="rawSSL","legacy fwder")
| eval version=if(isnull(version),"pre 4.2",version)
| rename version as Ver
| dedup sourceIp
| table connectType, sourceIp, sourceHost, Ver

This gives us everything we need, except for what indexes these hosts are sending data to.

I'm aware of this query to retrieve the indexes and the hosts that are sending data to them:

|tstats values(host) where index=* by index

How can I combine the two, either with a join or a sub search where in the table output, we have a column for index, which would give us a list of indexes the hosts are sending to?  

[richgalloway · Accepted solution]

This should do it.  It just runs both queries and uses the stats command to regroup the results.

index=_internal source=*metrics.log group=tcpin_connections 
| eval sourceHost=if(isnull(hostname), sourceHost,hostname) 
| rename connectionType as connectType
| eval connectType=case(fwdType=="uf","univ fwder", fwdType=="lwf", "lightwt fwder",fwdType=="full", "heavy fwder", connectType=="cooked" or connectType=="cookedSSL","Splunk fwder", connectType=="raw" or connectType=="rawSSL","legacy fwder")
| eval version=if(isnull(version),"pre 4.2",version)
| rename version as Ver
| dedup sourceIp
| append [ | tstats values(host) as sourceHost where index=* by index | mvexpand sourceHost ]
| stats values(*) as * by sourceHost
| table connectType, sourceIp, sourceHost, Ver, index

[WebNerdNick]

I selected this answer as the solution. After coming back to this a few days later, it seems to be reporting what I was looking for. Not sure if there was some odd caching going on when I was testing over and over, but this at least gets me close to what I was looking for.

[WebNerdNick]

In my results under the index column, all I get is "_internal".

[richgalloway]

Interesting.  I see _internal and non-internal indexes when I run it on one of my sandboxes.

What do you see when you run the tstats query alone?

[WebNerdNick]

tstats alone gives me an index column with proper index names (not _index), and then I have a values(host) column that has large listings of the hostnames that send to the related index.

[richgalloway]

That sounds about right, although I would expect each index to be on a separate line with the corresponding index name.  Do you have a screenshot?