topic Re: How to extract multiple field values for a field from single log event and cross check with the data from a file? in Splunk Search

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

raj_mpl — Mon, 18 Mar 2019 06:37:48 GMT

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

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

kamlesh_vaghela — Mon, 18 Mar 2019 07:15:15 GMT

@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

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

raj_mpl — Mon, 18 Mar 2019 09:41:50 GMT

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

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

kamlesh_vaghela — Mon, 18 Mar 2019 09:47:26 GMT

@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.

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

raj_mpl — Tue, 26 Mar 2019 05:07:19 GMT

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