Splunk Search

Join two "stats values" columns by similar field

evan_roggenkamp
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
1 Solution

woodcock
Esteemed Legend

Like this:

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

View solution in original post

woodcock
Esteemed Legend

Like this:

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

evan_roggenkamp
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

woodcock
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

chrismcharvey
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

evan_roggenkamp
Path Finder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

How about this

index=cyan_varbind source=oriondb sourcetype=cyan_varbind | stats values(OIDValue) values(OID)  by TrapID
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...