Splunk Search
Highlighted

Join two "stats values" columns by similar field

Path Finder

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:

alt text

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

How about this

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

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

Esteemed Legend

Like this:

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

View solution in original post

Highlighted

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

Path Finder

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.

0 Karma
Highlighted

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

Esteemed Legend

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
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

New Member

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.

0 Karma