Join two "stats values" columns by similar field

[evan_roggenkamp]

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:

[woodcock · Accepted solution]

Like this:

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

[evan_roggenkamp]

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.

[woodcock]

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

[chrismcharvey]

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.

[evan_roggenkamp]

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

[somesoni2]

How about this

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