Splunk Search

Return top item in a transaction, with fields appended

msarro
Builder

Greetings everyone. I am working with call records, and any particular record represents a call leg. Everything done to create a new call (a transfer, call forwarding, etc) creates a new record. All of the records can be tied together by a common "local id" field. The first item is usually the call which kicked off the entire process, so in a dashboard that will be the one we want users to see.

So, imagine we have the following records:

time,local_id,termination_description,direction
000001.1,123456:0,good,Terminating
000001.2,123456:1,bad,Originating
000001.3,123456:2,bad,Originating

So here's the search as I'm running it now:

index=cdrs (simple search to return all records here)
|rex Local_ID_Trimmed(get the base local ID without the :N section)
|transaction Local_ID_Trimmed

However this will show all of the items in the transaction. What I want to display to a searching user is:
time the call happened (from the first record)
total number of call legs
whether or not a bad call leg existed in the transaction

Then I'll do a drilldown when this is clicked on to show all of the related call legs.

Any advice on how to do this would be great. I've tried using stats instead of transaction but it's not coming out right. My big questions are:

  • A) how do I only return the first record out of the transaction
  • B) how do I append to the end the
    eventcount of the transaction

    c) How do I search for the appearance of "bad" in the transaction, and just show it as a "bad" field

    D) Is there a way to do this without actually running the transaction in the beginning? For instance, searching for any record ending in :0, then do a subsearch by passing the local ID?

I've been wracking my brain over this for the past few hours.

What I'd like to see would look like this:

time,local_id_trimmed,hasbad,numlegs,direction
000001.1,123456,True,3,Terminating
Tags (1)
0 Karma
1 Solution

jonuwz
Influencer

Assuming the direction in the output is the directio in the 1st record, and the results are returned in chronological order

base_search | rex field=local_id (?P<local_id_trimmed>.*?):.* | stats first(time) as time min(termination_description) as goodorbad count as numlegs first(direction) as direction by local_id_trimmed | eval hasbad=if(goodorbad=="bad","True","False") | table time local_id_trimmed hasbad numlegs direction

untested, but should work ..

since the "good or bad" field is seemingly limited to good or bad, the minimum value of that field will be bad if any of the events are "bad" (alphabetical order)

if first() doesn't work for you, try earliest() instead

If you're going to do row drilldown by local_id_trimmed - its worth putting the local_id_trimmed in the 1st column

View solution in original post

jonuwz
Influencer

Assuming the direction in the output is the directio in the 1st record, and the results are returned in chronological order

base_search | rex field=local_id (?P<local_id_trimmed>.*?):.* | stats first(time) as time min(termination_description) as goodorbad count as numlegs first(direction) as direction by local_id_trimmed | eval hasbad=if(goodorbad=="bad","True","False") | table time local_id_trimmed hasbad numlegs direction

untested, but should work ..

since the "good or bad" field is seemingly limited to good or bad, the minimum value of that field will be bad if any of the events are "bad" (alphabetical order)

if first() doesn't work for you, try earliest() instead

If you're going to do row drilldown by local_id_trimmed - its worth putting the local_id_trimmed in the 1st column

msarro
Builder

That works brilliantly, thank you!

0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...