compare two result

[indeed_2000]

Hi

I have a query that need to compare count of PF field for two log file:

on splunk I have two query that create this table, the issue is need to "PF" that equal in query1 and query2 show in same row:

current result:

hostname1 PF1    count1 hostname2 PF2    count2
host1     red    50     host2     yellow 90
host1     green  40     host2     green  90
host1     purple 50     host2     red    90

expected result:

hostname1 PF1    count1 hostname2 PF2    count2
host1     red    50     host2     red    90
host1     green  40     host2     green  90
host1     purple 50     host2     -      -
host1     -      -      host2     yellow 90

here is the query:

index="myindex" "mymodule*:" AND "P[" AND "F[" source="/tmp/*/log.srv23.*.bz2"
| rex field=source "\/.*\/log\.(?<servername>\w+)."
| rex "P(?<PF>\[\d+\]\[\d+\])"
| stats count as _PF by PF,servername | stats list(_PF) as count list(PF) as PF by servername

| appendcols
  [search index="myindex" "mymodule*:" AND "P[" AND "F[" 
  source="/tmp/*/log.srv24.*.bz2"
  | rex field=source "\/.*\/log\.(?<servername>\w+)."
  | rex "P(?<PF2>\[\d+\]\[\d+\])"
  | stats count as _PF2 by PF2,servername | stats list(_PF2) as count 
  list(PF2) as PF2 by servername ]

Any idea?
Thanks

[bowesmana]

You could do this

index="myindex" "mymodule*:" AND "P[" AND "F[" source="/tmp/*/log.srv23.*.bz2" OR source="/tmp/*/log.srv24.*.bz2" 
| rex field=source "\/.*\/log\.(?<servername>\w+)." 
| rex "P(?<PF>\[\d+\]\[\d+\])" 
``` count by colour and server name ```
| stats count as _PF by PF servername 
``` now collect by colour ```
| stats list(servername) as servername list(_PF) as count by PF

which would give you something like this - does this work

PF  hostname count
red host1      50
        host2      90
green host1 40
            host2  90
purple host1 50
yellow host1 90

[indeed_2000]

@bowesmana Thanks, Try what you mentioned but not work as I expected,

Change my mind, Is it possible to create table like this?

PF              Host1      Host2      Host3

red.            50.              20.           89

purple.      30.              80.          1

green.        80.             12.           -

[bowesmana]

Yes, that type of table can be done with chart, so

...
| chart count over PF by servername

what that won't do is distinguish between which source it came from, which may or may not be relevant to your use case. Do you care if the count is combined between source 1 and source 2?

[indeed_2000 · Accepted solution]

@bowesmana thanks, Chart is slow on my data, after several try and error find solution. first using “stats” to extract count, then use “xyseries”.