Splunk Search
Highlighted

How to match up column values running a transaction search using startswith and endswith with appendcols?

New Member

(1) For every sourcetype, there are a set of serial numbers which are processed through segments. The segments are numbered from 0, and the last segment number can be anything depending on the size of the file. I have a search that identifies the maximum segment number for each device in the sourcetype, and I'm trying to calculate the duration from segmentId=0 to last segment for each. While I was able to put the max segment value into a field, I'm unable to implement it on the "endswith" portion of the transaction command:

sourcetype="joblog" "NTEST*" | stats max(segmentId) as lastSeg by serialNumber| eval maxSeg=lastSeg |transaction startswith=(segmentId=0 processingPhase=mapStart) endswith=(maxSeg processingPhase=mapEnd) by serialNumber| table duration

How do I change my query to be able to grab the largest segment value for each segment and calculate processing time from 0 to end for each serialNumber? The serialNumbers are in the format NTEST1234A NTEST2345B etc...

(2) As a second part to this, the serial numbers get processed through an integrator which is defined by phases "integrationStart" and "integrationEnd" which helps calculate the integration time.
I'm looking to add this to the value I get from one. I tried something like this:

sourcetype=algorithmjoblog serialNumber="NTEST*" |stats max(segmentId) as lastSeg by serialNumber| eval maxSeg=lastSeg |transaction startswith.... endswith..... | table  duration1| appendcols [search sourcetype=algorithmjoblog serialNumber="NTEST*"| transaction startswith=(phase=integrationStart) by serialNumber endswith=(phase=integrationEnd) by serialNumber|eval duration2=duration| table duration2]

The problem here is that the values are not matched up by SerialNumber. It just spits two columns with numbers which are not linked to the same serialNumber.

Could you please help me with both these situations? Thanks!

0 Karma
Highlighted

Re: How to match up column values running a transaction search using startswith and endswith with appendcols?

Builder

Hi,

Will the custom search command help? If you execute query like

.... | stats list(_raw) by serialNumber

You will get all the raw events list by serialNumber. Pass this information to custom search command to implement the business logic.

Thanks !!

0 Karma