topic Re: How to compare columns in a row in Splunk Search

How to compare columns in a row

ewanbrown — Tue, 29 Sep 2020 09:10:21 GMT

I have a query that produces some output like this:

ID server_a.1  server_a.2        server_b.1           server_b.2
1  0                   0            1                       0
2  1                   0            1                       1
3  1                   1            0                       0

I'd like to be able to add to it to tell me the number of ID's which are present in server_a*, but not in server_b* and visa versa. I think I can do it by making this an inner query, however I need to do it across millions of records, so that is not an option

Any help is appreciated!

Thanks

Re: How to compare columns in a row

lguinn2 — Wed, 23 Mar 2016 20:22:40 GMT

It would be helpful to see the search that produced this.
I have a feeling that this table was produced by a chart command - but by using a stats command then xyseries, I think you can easily get what you want.

Re: How to compare columns in a row

somesoni2 — Wed, 23 Mar 2016 20:40:04 GMT

If the name of servers are fixed/static, then something like this could work

your current search giving above output | addtotals server_a* fieldname=present_in_server_a | addtotals server_b* fieldname=present_in_server_b | eval status=case(present_in_server_a =0 AND present_in_server_b>0 ,"Present in server_b* only", present_in_server_a >0 AND present_in_server_b=0,"Present in server_a* only",1=1,"Present in both")

This will add a field status to specify which ID is present in which servers. You can further chart the count if you need

above search | stats count by status

Re: How to compare columns in a row

ewanbrown — Thu, 24 Mar 2016 10:30:16 GMT

Perfect, thanks

Re: How to compare columns in a row

ewanbrown — Thu, 24 Mar 2016 10:40:29 GMT

Hi,

I've the answer now, but the source query was:

index=beacons| spath | eval       temp=mvzip(mvzip(mvzip('bean.data.matches{}.clusterId','bean.data.matches{}.facility'),'bean.data.matches{}.matchStatus'), 'bean.data.matches{}.cluster') | mvexpand temp | eval x = split(temp,",") | eval clusterId=mvindex(x,0) | eval facility=mvindex(x,1) | eval match_status=mvindex(x,2) | eval cluster=mvindex(x,3) | search cluster=1 OR cluster=2 OR cluster=3 |  chart count by clusterId, facility

Re: How to compare columns in a row

woodcock — Thu, 24 Mar 2016 14:28:44 GMT

Append this to your search:

... | foreach server_a* [eval A = A + <<FIELD>>] | foreach server_b* [eval B = B + <<FIELD>>] | multireport | [stats dc(ID) AS dcIDbyA values(ID) AS valuesIDbyA BY A] [stats dc(ID) as dcIDbyB values(ID) AS valuesIDbyB BY B]