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 thanks, Chart is slow on my data, after several try and error find solution. first using “stats” to extract count, then use “xyseries”.
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
@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. -
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?
@bowesmana thanks, Chart is slow on my data, after several try and error find solution. first using “stats” to extract count, then use “xyseries”.