topic Re: Join two "stats values" columns by similar field in Splunk Search

Join two "stats values" columns by similar field

evan_roggenkamp — Fri, 08 Jul 2016 20:04:24 GMT

I am trying to join two searches with a common TrapID field. The OIDValue column corresponds with the OID Column

The first:

index=cyan_varbind source=oriondb sourcetype=cyan_varbind | stats values(OIDValue) by TrapID

The second:

index=cyan_varbind source=oriondb sourcetype=cyan_varbind | stats values(OID) by TrapID

Visual:

Re: Join two "stats values" columns by similar field

somesoni2 — Fri, 08 Jul 2016 20:13:29 GMT

How about this

index=cyan_varbind source=oriondb sourcetype=cyan_varbind | stats values(OIDValue) values(OID)  by TrapID

Re: Join two "stats values" columns by similar field

woodcock — Fri, 08 Jul 2016 20:25:36 GMT

Like this:

index=cyan_varbind source=oriondb sourcetype=cyan_varbind | stats values(OIDValue) values(OID) BY TrapID

Re: Join two "stats values" columns by similar field

evan_roggenkamp — Fri, 08 Jul 2016 20:37:04 GMT

Thanks this is so simple I was over thinking it.

Question - does this respect the relationship between the values(OIDValue) column and the values(OID) column? In other words, does splunk know they correspond? It would appear that, although they line up, they aren't necessarily in rows as one would expect related data to be.

Re: Join two "stats values" columns by similar field

woodcock — Fri, 08 Jul 2016 20:40:21 GMT

No, to do that, you need to use list instead of values, like this:

index=cyan_varbind source=oriondb sourcetype=cyan_varbind | stats list(OIDValue) list(OID) BY TrapID

Or combine them first like this

index=cyan_varbind source=oriondb sourcetype=cyan_varbind | eval OIDcombo = OID . ":" . OIDValue | stats values(OIDCombo) BY TrapID

Re: Join two "stats values" columns by similar field

evan_roggenkamp — Fri, 08 Jul 2016 20:48:22 GMT

This has been so helpful to me. I cannot thank you enough.

Re: Join two "stats values" columns by similar field

chrismcharvey — Fri, 07 Jun 2019 19:19:19 GMT

This has been immensely helpful for me as well! Do you know how you would select just the most recent OIDValue for each OID? For my examples I've got:
main search | eval TransferResults = type ."=". status | stats values(TransferResults) by referenceId

but each type has multiple statuses:
IP=ERROR
IP=SUCCESS
NATIVE=ERROR
NATIVE=SUCCESS
X1=ERROR
X1=SUCCESS

and in this case, the most recent statuses are all =SUCCESS so I'm trying to show that.