Splunk Search

How to get transaction results in table with "transacted" items in separate rows?

splunk_thunk
Explorer

Hello Experts,

I have a transaction query that I am displaying in a table. I am able to get results in a table, however, the results tied in a single transaction appear as single row in the table. I would like to have them displayed in separate rows, as if they are individual search results.

Here's an example:

Log Data:

  Transaction Id=1, step=1, data_x=dataX1, data_y=dataY1

  Transaction Id=1, step=2, data_x=dataX2, data_y=dataY2

How results look like

Transaction Id data_x data_y

1

1

dataX1

dataX2

dataY1

dataY2

2

2

... ...

 

Need it to look like

Transaction Id data_x data_y
1 dataX1 dataY1
1 dataX2 dataY2
2 ... ...
2 ... ...

 

Any help appreciated.

Thanks!

 

Labels (2)
0 Karma
1 Solution

splunk_thunk
Explorer

Thanks @ITWhisperer . Trying it out in prod before I mark resolved.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Please share the SPL that produces the current results.

---
If this reply helps you, Karma would be appreciated.
0 Karma

splunk_thunk
Explorer

@richgalloway 

| makeresults count=4
| streamstats count
| eval transactionId = case(count=1 OR count=2, 1, count=3 OR count=4, 2) | eval step = case(transactionId=1 AND count<=2,count, transactionId=2,count-2) | eval data_x="dataX".step | eval data_y="dataY".step | eval val=case(step=1,step,step>1,"null. So how to print value_1 here as well?") | transaction transactionId | table transactionId, step, val, data_x, data_y | sort transactionId

 

Please try the above.

I have another question (you will notice it in search results from above query). For the same transaction, how can I print the value of a field from step=1 of the transaction into other steps in the result, when that field is not available in the other search results within same transaction.

Thanks much!

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I assume you used transaction to gather the events together so you could try using stats and list()

The main thing you need to do is zip all the fields together, mvexpand, then "unzip" them. This does rely on there being values in each of the fields, although that would be true for transaction as well

| makeresults count=4
| streamstats count
| eval transactionId = case(count=1 OR count=2, 1, count=3 OR count=4, 2) | eval step = case(transactionId=1 AND count<=2,count, transactionId=2,count-2) | eval data_x="dataX".step | eval data_y="dataY".step | eval val=case(step=1,step,step>1,"null. So how to print value_1 here as well?")
| stats list(data_*) as data_* list(step) as step list(val) as val by transactionId
| eval data=mvzip(data_x,mvzip(data_y,val,":"),":")
| mvexpand data
| eval data_x=mvindex(split(data,":"),0)
| eval data_y=mvindex(split(data,":"),1)
| eval val=mvindex(split(data,":"),2)
| table transactionId data_x data_y val

splunk_thunk
Explorer

Thanks @ITWhisperer . That solves one problem. 

Any suggestions to copy value from step-1 of the transaction into step-2 of the same transaction when it is missing in step-2?

I have added  this table to a dashboard that has filter on val from my example. When val field is not available in step-2 of the same transaction, the filter condition removes that result from the table, which is not desired. 

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults count=4
| streamstats count
| eval transactionId = case(count=1 OR count=2, 1, count=3 OR count=4, 2) | eval step = case(transactionId=1 AND count<=2,count, transactionId=2,count-2) | eval data_x="dataX".step | eval data_y="dataY".step | eval val=case(step=1,step,step>1,null())
| streamstats latest(val) as val by transactionId
| stats list(data_*) as data_* list(step) as step list(val) as val by transactionId
| eval data=mvzip(data_x,mvzip(data_y,val,":"),":")
| mvexpand data
| eval data_x=mvindex(split(data,":"),0)
| eval data_y=mvindex(split(data,":"),1)
| eval val=mvindex(split(data,":"),2)
| table transactionId data_x data_y val

splunk_thunk
Explorer

Thanks @ITWhisperer . Trying it out in prod before I mark resolved.

0 Karma
Get Updates on the Splunk Community!

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...