How to create a table with two stats list columns?

[stephenlclarke]

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>

[woodcock · Accepted solution]

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

[stephenlclarke]

Perfect.. ~Thanks very much

[stephenlclarke]

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.

[woodcock]

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.

[woodcock]

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