Capturing multiple values and creating table from results - MVexpand?

[Barty]

Good afternoon guys & gals,

This on paper is a simple one, but it's absolutely escaping me. We have been asked to extract the most recent 3 entries for 2 different types of quote and then the data values that follow. The data looks like this:-

date=19-09-2018 startTime=00-00 endTime=01-00
 BI_FEED=Direct_DataFeed_20180918204501 QUOTE_TRANSACTIONS=53412 PROCESSING_TIME_SEC=987.504327 PROCESSING_STATE=complete
 BI_FEED=Direct_DataFeed_20180918213001 QUOTE_TRANSACTIONS=50096 PROCESSING_TIME_SEC=920.179029 PROCESSING_STATE=complete
 BI_FEED=CQ_DataFeed_201809190016 QUOTE_TRANSACTIONS=24 PROCESSING_TIME_SEC=54.824542 PROCESSING_STATE=complete
 BI_FEED=Direct_DataFeed_20180918204345 QUOTE_TRANSACTIONS=52312 PROCESSING_TIME_SEC=978.504327 PROCESSING_STATE=complete
 BI_FEED=CQ_DataFeed_201809190031 QUOTE_TRANSACTIONS=28 PROCESSING_TIME_SEC=65.140814 PROCESSING_STATE=complete
 BI_FEED=CQ_DataFeed_201809190045 QUOTE_TRANSACTIONS=196 PROCESSING_TIME_SEC=235.348442 PROCESSING_STATE=complete
 BI_FEED=CQ_DataFeed_201809190043 QUOTE_TRANSACTIONS=324 PROCESSING_TIME_SEC=355.376033 PROCESSING_STATE=complete
 BI_FEED=CQ_DataFeed_201809190049 QUOTE_TRANSACTIONS=188 PROCESSING_TIME_SEC=198.883841 PROCESSING_STATE=complete

So they would like the 3 recent Direct quotes and the most recent CQ quotes. Then, they would like to table to quote ID, the transaction number and the processing time. So far, I have been testing with simply getting the data for the Direct feed, but uncoupling the data is killing me. My thought process is as follows:-

base search here earliest=-15m@m
| rex max_match=3 field=_raw "BI_FEED=Direct_DataFeed_(?<dir>[0-9]*)\sQUOTE_TRANSACTIONS=(?<qt>[0-9]*)\sPROCESSING_TIME_SEC=(?<pts>[0-9.]*)"
| mvexpand dir
| table dir, qt, pts

This produces, as expected, a separate entry per dir value but then inputs all 3 entries for the qt and pts values. Obviously this is because I need to separate them out and keep them per quote string, but I'm struggling with that! If anyone is able to assist me in the above it would be much appreciated. Remember that I need to do this for both the direct and CQ values in a single table.

Thanks in advance!

Oh, and running splunk 6.6.4

[damiensurat · Accepted solution]

Hi. I think you should refer to using the mvzip command with Rex:

source="mvexpandData.csv"
| rex field=_raw "a=(?\d+)" max_match=5
| rex field=_raw "b=(?\d+)" max_match=5
| eval fields = mvzip(a,b)
| mvexpand fields
| rex field=fields "(?\d+),(?\d+)"
| table _time alpha beta

Check out this link:
http://docs.splunk.com/Documentation/Splunk/7.1.3/SearchReference/Mvexpand

[Barty]

Worked like a charm!! Thanks for that, much appreciated

[damiensurat]

My pleasure Barty! Happy Splunking!

[mstjohn_splunk]

@barty

Did you get a chance to try @daminesurat 's idea out? If it worked, would you go ahead and accept his answer? If not, would you let us know why it didn't work? Thanks!

[Barty]

Good morning, I am taking a look into this this morning, thank you in advance.