Splunk Search

Multiple same fields in one event with different data

mariannedave
Explorer

I have this XML data in one event but there are multiple transactions with same fieldnames . We need to display them all but group per transaction.

Sample Data in Splunk:

 

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
	<TransactionMetaData xmlns="">
		<UniqueTransactionID>8G1F1R1C-701G16EC0F32</UniqueTransactionID>
		<TransactionDateTime>2021-04-16T03:11:26.031+02:00</TransactionDateTime>
	</TransactionMetaData>
	<Payload xmlns="">
		<ValidatedSalesTransactions>
			<Transaction>
				<RetailStoreID>PHP6666</RetailStoreID>
				<BusinessDayDate>2021-04-15</BusinessDayDate>
				<BeginDateTime>2021-04-15T07:21:22</BeginDateTime>
				<CurrencyCode>PHP</CurrencyCode>
				<ReceiptNumber>8565</ReceiptNumber>
				<RetailTransaction TransactionStatus="Totaled">
						<SaleReturn ReturnFlag="true">
							<ItemID>7481036706423787</ItemID>
							<Quantity>1</Quantity>
								<Amount>1.67</Amount>
							<OnlineOrderID>5516054561</OnlineOrderID>
							<OnlineItemID>6430081131637851</OnlineItemID>
						</SaleReturn>
				</RetailTransaction>
				<Country>Philippines</Country>
			</Transaction>
			<Transaction>
				<RetailStoreID>PHP6666</RetailStoreID>
				<BusinessDayDate>2021-04-15</BusinessDayDate>
				<BeginDateTime>2021-04-15T07:30:11</BeginDateTime>
				<CurrencyCode>PHP</CurrencyCode>
				<ReceiptNumber>8566</ReceiptNumber>
				<RetailTransaction TransactionStatus="Totaled">
						<SaleReturn ReturnFlag="true">
							<ItemID>59874451032500</ItemID>
							<Quantity>1</Quantity>
								<Amount>2.84</Amount>
							<OnlineOrderID>8549756244420</OnlineOrderID>
							<OnlineItemID>64385647545125144</OnlineItemID>
						</SaleReturn>
				</RetailTransaction>
				<Country>Philippines</Country>
			</Transaction>
		</ValidatedSalesTransactions>
	</Payload>

 

My Search Query:

 

index=transaction_index sourcetype=ST_source
| xmlkv
| table RetailStoreID CurrencyCode ReceiptNumber ItemID Quantity OnlineOrderID OnlineItemID Country

 

I'm only getting the 1st transaction.

mariannedave_0-1618567518117.png

But, this is my expected result:

Expected_Search_Result.jpg

what can I add on my search to get all the transaction?

Thank you!

Labels (4)
Tags (1)
0 Karma
1 Solution

manjunathmeti
Champion

hi @mariannedave,

You need to break these multiple transactions into separate events. Try this:

index=transaction_index sourcetype=ST_source 
| rex max_match=0 "(?<event>\<Transaction\>[^\{]+?\<\/Transaction\>)" 
| mvexpand event 
| xmlkv event
| table RetailStoreID CurrencyCode ReceiptNumber ItemID Quantity OnlineOrderID OnlineItemID Country

 

If this reply helps you, a like would be appreciated.

View solution in original post

manjunathmeti
Champion

hi @mariannedave,

You need to break these multiple transactions into separate events. Try this:

index=transaction_index sourcetype=ST_source 
| rex max_match=0 "(?<event>\<Transaction\>[^\{]+?\<\/Transaction\>)" 
| mvexpand event 
| xmlkv event
| table RetailStoreID CurrencyCode ReceiptNumber ItemID Quantity OnlineOrderID OnlineItemID Country

 

If this reply helps you, a like would be appreciated.

View solution in original post

mariannedave
Explorer

It works! This is what I need. Thank you so much 😆

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.