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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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.

mariannedave
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...