topic Re: How to create a table with two stats list columns? in Splunk Search

How to create a table with two stats list columns?

stephenlclarke — Fri, 10 Jul 2015 11:34:15 GMT

I have two queries that I want to merge into one.

First query:

<pre>
sourcetype="sourceType1"
rex "Application=(?+)" |
dedup appname |
sort appname |
stats list(appname) Column1 by host
</pre>

Returns:

<pre>
host Column1
srv16 APP1
APP2
srv19 APP3
APP4
srv26 APP5
APP6
srv27 APP7
APP8
</pre>

Second query:

<pre>
sourcetype="sourceType2"
rex "Application=(?+)" |
dedup appname |
sort appname |
stats list(appname) Column2 by host
</pre>

Returns:

<pre>
host Column2
srv15 APP1
APP2
srv16 APP1
APP2
srv19 APP3
APP4
srv26 APP5
APP6
srv27 APP7
APP8
</pre>

How do I merge these two queries to get something that looks like this:

<pre>
host Column1 Column2
srv15 APP1
APP2
srv16 APP1 APP3
APP2 APP4
srv19 APP3
APP4
srv25 APP5
APP6
srv26 APP5
APP6
srv27 APP7 APP7
APP8 APP8
</pre>

Re: How to create a table with two stats list columns?

woodcock — Fri, 10 Jul 2015 14:03:23 GMT

Like this:

sourcetype="sourceType1" OR sourcetype="sourceType2" | rex "Application=(?+)" | stats values(appname) by host sourcetype

You don't need the sort because values sorts automatically. With values you don't need the dedup either.

Re: How to create a table with two stats list columns?

stephenlclarke — Fri, 10 Jul 2015 14:38:38 GMT

Update:

I have a server estate that is running both DEV and TEST instances of a number of applications. One source type has all the logs for the DEV instances and the other has all the logs for the TEST instances.

What I need is a report that lists all the DEV instances per host in one column and all the TEST instances per host in a separate column. Some hosts will only have DEV while other will only have TEST apps, but a handful will be running both DEV and TEST.

Re: How to create a table with two stats list columns?

woodcock — Fri, 10 Jul 2015 15:25:13 GMT

Even with your "update", my answer should still give you what you desire, but doubled-up in rows instead of in separate columns.

Re: How to create a table with two stats list columns?

woodcock — Fri, 10 Jul 2015 15:32:04 GMT

To get the columns the way you like, use chart instead of stats like this:

 sourcetype="sourceType1" OR sourcetype="sourceType2" | rex "Application=(?+)" | chart values(appname) by host sourcetype

Re: How to create a table with two stats list columns?

stephenlclarke — Fri, 10 Jul 2015 15:38:18 GMT

Perfect.. ~Thanks very much