Splunk Search

How to find latest events within multiple transactions?

brunton2
Path Finder

I have multiple transactions similar to the following:

<time> Event Start
<time> Motor 1, Steps 2345
<time> Motor 2, Steps 2232
<time> Motor 3, Steps 2235
<time> Motor 2, Steps 2532
<time> Motor 4, Steps 2342
<time> Motor 1, Steps 2642
<time> Event End

What I'd like as a result is to find the latest 'steps' for each distinct motor within each transaction. I've done numerous searches but drawn a blank on how to achieve it. Can anyone help suggest an approach?

Thanks

0 Karma
1 Solution

woodcock
Esteemed Legend

I am assuming that the 8 lines that you posted are a single event (which is the way that you should have it: if not, you should fix that first). If so...

This fakes the data:

| makeresults 
| eval _raw="Event Start
 <time> Motor 1, Steps 2345
 <time> Motor 2, Steps 2232
 <time> Motor 3, Steps 2235
 <time> Motor 2, Steps 2532
 <time> Motor 4, Steps 2342
 <time> Motor 1, Steps 2642
 <time> Event End"

This is your solution:

| rex mode=sed "s/\s*[\n\r]+\s*/::/g" 
| eval raw=_raw
| fields raw
| fields - _raw
| streamstats count AS serial 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "Motor\s*(?<Motor>\d+),\s+Steps\s*(?<Steps>\d+)"
| stats last(raw) AS raw BY Motor serial
| stats values(raw) BY serial
| fields - serial

View solution in original post

woodcock
Esteemed Legend

I am assuming that the 8 lines that you posted are a single event (which is the way that you should have it: if not, you should fix that first). If so...

This fakes the data:

| makeresults 
| eval _raw="Event Start
 <time> Motor 1, Steps 2345
 <time> Motor 2, Steps 2232
 <time> Motor 3, Steps 2235
 <time> Motor 2, Steps 2532
 <time> Motor 4, Steps 2342
 <time> Motor 1, Steps 2642
 <time> Event End"

This is your solution:

| rex mode=sed "s/\s*[\n\r]+\s*/::/g" 
| eval raw=_raw
| fields raw
| fields - _raw
| streamstats count AS serial 
| makemv delim="::" raw 
| mvexpand raw 
| rex field=raw "Motor\s*(?<Motor>\d+),\s+Steps\s*(?<Steps>\d+)"
| stats last(raw) AS raw BY Motor serial
| stats values(raw) BY serial
| fields - serial

brunton2
Path Finder

Thanks woodcock!

Although this wasn't used verbatim to solve my problem (largely because there were data complexities that I omitted from my description) it was exactly what I needed to resolve my needs. The consolidation of a transaction into a single event was the key part I needed and from there I could manipulate my data as I needed it. From there I found out that rex would allow me to pull a mvlist of events (learn something new every day!) and from this ordered list I could then find the latest moves of each motor.

Thanks again

0 Karma

lguinn2
Legend

This may work, or at least be a step in the right direction:

yoursearchhere "Event Start" OR "Event End" OR (Motor AND Steps)
| transaction startswith="Event Start" endswith="Event End" mvlist= true
| streamstats count as trans_sequence_number
| eval motorinfo = mvzip(motor,steps) 
| fields  trans_sequence_number motorinfo
| fields - _raw
| mvexpand motorinfo
| eval motor = mvindex(motorinfo,0)
| eval steps = mvindex(motorinfo,1)
| stats max(steps) as MaxSteps max(_time) as TransactionTime by trans_sequence_number motor
| rename motor as Motor trans_sequence_number as "Transaction Number"

In the search, I am trying to only include events that define the transaction and the motor information. The transaction command is memory-intensive, so don't include any other data that might logically be part of the transaction, but isn't going to be used in this search.
The streamstats command gives each transaction a unique number, which will be needed when we split up the transactions. If there is a different field that uniquely identifies the transaction, you could use that field instead.
I don't know that you need either of the fields commands, and I am not sure that the second one will work. But the purpose is to free up memory, because the mvexpand command might use a lot of memory.
Hopefully, the final output is close to what you need.

0 Karma

brunton2
Path Finder

Iguinn

This was close but I couldn't get the trans_sequence_number to expand with the motorinfo with this solution. Prior to expanding it was correctly set but after the mvexpand it somehow was lost and resulted in it being NULL for all rows. Thanks for the contribution though, it was largely the same as to above and I used bits of each of the proposed solutions to get to my end goal.

Thanks

0 Karma

koshyk
Super Champion

do you mean to say all the above sample is a "SINGLE" transaction? How many events are they in Splunk?

0 Karma

brunton2
Path Finder

I have multiple transactions, each one consists of a pattern of events similar to the example. The number of 'motor' events in each transaction varies from a dozen to a couple of hundred.

Thanks

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