How to extract multiple field values for a field from single log event and cross check with the data from a file?

[raj_mpl]

Hi All , Good Day

My log will generate 2 types of log events
1)tid and mid in single log event
2)multiple field values for a single fields (tid and mid) from a single log event

Now I have a list of tid or mid values with me in an excel sheet , How to compare whether the values are present in the splunk output output with the values I have in excel sheet?

below are the sample events

Event 1:

<From *********>
    <To *********</To>
    <MID>ABC</MID>

  <Transactions>
    <Transaction tID="123" transactionDate="date"> 
    <From ***/From>
    **
    <MID>DEF</MID>

  <Transactions>

  <Transaction tID="456" transactionDate="**********>

    <To *****/To>
    <MID>GHI</MessageID>

 <Transactions>

  <Transaction tID="789" transactionDate="**********>
    <To *****/To>
    <MID>JKL</MessageID>

Event 2:

 <Transactions>

  <Transaction tID="zyxy" transactionDate="**********>

    <To *****/To>
    <MID>a2128u</MessageID>

How to extract the fields first using regex ? And how to cross check with the TID values in excel sheet with splunk output?

Please help . Thanks

[raj_mpl · Accepted solution]

Hi All, I found the solution for this , Will take a chance to update the answer here

Firstly I had written the regex to extract the multiple values of MID's and TID's from the raw logs

sourcetype=mysourcetype TID MID | rex max_match=50 "<MID>(?P<mid_extracted>[^\<]+)"|rex max_match=50 "TID\=\"(?P<tid_extracted>[^\"]+)" |table mid_extracted , tid_extracted

Now to split the multiple values in a singe event used > MVEXPAND and finally performed the join with the externally uploaded lookup file .Lookup table contains 2 columns . one is MID Values/TID Values second one is Status .Under MID Values/Tid Values columns we have all the values to be checked and in second column all the values were written as MATCHED

The final query having join condition is as below

sourcetype=mysourcetype TID MID |rex max_match=50 "TID\=\"(?P<tid_extracted>[^\"]+)" |mvexpand tid_extracted |table tid_extracted |join type=left tid_extracted [| inputlookup tid_test.csv]

Now If any value in Splunk extracted output matches the value in Lookup file , The status field value displays as MATCHED
else
displays empty value

Note: In lookup file the column heading should be exactly as splunk output field heading means tid_extraced should be same

[kamlesh_vaghela]

@raj_mpl

Have you tried with xpath?? https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/xpath
You can use xpath for extracting all transactions from the 1st event.

You can use join command to relate those transactions with the 2nd event. https://docs.splunk.com/Documentation/Splunk/7.2.4/SearchReference/Join

If you want help in search design then share both valid XML events. So we can help you batter.

Thanks

[raj_mpl]

Hi @kamlesh_vaghela , Thanks for your response on this
I tried using xpath command but no luck
below is the event

<Header>
<To>xxx</To>
<MID>CBC</MID>
<TransactionGroup>NEEY</TransactionGroup>
<Priority>Low</Priority>
</Header>

And tried like below

sourcetype=something tID MID| xpath "//Header/MID" outfield=MID

I formed a query using regex to extract all the values by using below one.its extracting all the values from events which are having more number of values in them.

sourcetype=something tID MID | rex max_match=50 "<MID>(?P<mid_extract>[^\<]+)"|rex max_match=50 "tID\=\"(?P<tid_extract>[^\"]+)" |table mid_extract,tid_extract

If my approach and the output is correct ? Then my requirement is to validate the output values with my tid's or mid's in external file
please help

[kamlesh_vaghela]

@raj_mpl

Can you please share XML Event 1 and Event 2 mentioned in the question? I think I can help you with the search. Mask original values with the sample values in the event.