Splunk Search

Help in extract data using rex in Splunk

anil1219
Engager

I have 2 records for PaymentType as send and receive. I would like to extract PaymentType as receive only so that I can further compare.

Could you please let me know how do I can extract PaymentType as receive only.

transaction: {"version":1,"status":"approved","identifier":"0c4240e0-2c2c-6427-fb1f-71131029cd89","amount":"[REDACTED]","transactionAmount":"[REDACTED]","timestamp":"2024-06-13T04:29:20.673+0000","statusChangedTimestamp":"2024-06-13T04:29:56.337+0000","type":"payment","transferIdentifier":"cded3395-38f9-4258-90a5-9269abfa5536","currencyCode":"USD","PaymentType":"receive","senderHandle":"[REDACTED]","recipientHandle":"[REDACTED]","fees":[],"transferMode":"contact"}

transaction: {"version":1,"status":"approved","identifier":"0c4240e0-2c2c-6427-fb1f-71131029cd89","amount":"[REDACTED]","transactionAmount":"[REDACTED]","timestamp":"2024-06-13T04:29:20.673+0000","statusChangedTimestamp":"2024-06-13T04:29:56.337+0000","type":"payment","transferIdentifier":"cded3395-38f9-4258-90a5-9269abfa5536","currencyCode":"USD","PaymentType":"send","senderHandle":"[REDACTED]","recipientHandle":"[REDACTED]","fees":[],"transferMode":"contact"}

Labels (4)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

As @gcusello points out, the data you illustrated is suspiciously close to JSON.  Are you sure that your data is not like this instead?

 

{"transaction": {"version":1,"status":"approved","identifier":"0c4240e0-2c2c-6427-fb1f-71131029cd89","amount":"[REDACTED]","transactionAmount":"[REDACTED]","timestamp":"2024-06-13T04:29:20.673+0000","statusChangedTimestamp":"2024-06-13T04:29:56.337+0000","type":"payment","transferIdentifier":"cded3395-38f9-4258-90a5-9269abfa5536","currencyCode":"USD","PaymentType":"receive","senderHandle":"[REDACTED]","recipientHandle":"[REDACTED]","fees":[],"transferMode":"contact"}
}

 

Or is it possible that you are simply illustrating an extracted field named transaction whose values are

 

{"version":1,"status":"approved","identifier":"0c4240e0-2c2c-6427-fb1f-71131029cd89","amount":"[REDACTED]","transactionAmount":"[REDACTED]","timestamp":"2024-06-13T04:29:20.673+0000","statusChangedTimestamp":"2024-06-13T04:29:56.337+0000","type":"payment","transferIdentifier":"cded3395-38f9-4258-90a5-9269abfa5536","currencyCode":"USD","PaymentType":"receive","senderHandle":"[REDACTED]","recipientHandle":"[REDACTED]","fees":[],"transferMode":"contact"}

 

and

 

{"version":1,"status":"approved","identifier":"0c4240e0-2c2c-6427-fb1f-71131029cd89","amount":"[REDACTED]","transactionAmount":"[REDACTED]","timestamp":"2024-06-13T04:29:20.673+0000","statusChangedTimestamp":"2024-06-13T04:29:56.337+0000","type":"payment","transferIdentifier":"cded3395-38f9-4258-90a5-9269abfa5536","currencyCode":"USD","PaymentType":"send","senderHandle":"[REDACTED]","recipientHandle":"[REDACTED]","fees":[],"transferMode":"contact"}

 

If not, your developers are really doing a deservice to everyone downstream, not just Splunkers.  But if raw data  is indeed as you originally posted, you can first extract the valid JSON into a field, let's call it transaction, then extract key-value pairs from this object.

 

| rex "transaction: *(?<transaction>{.+)"
| fromjson transaction

 

This is what you should get

PaymentTypeamountcurrencyCodefeesidentifierrecipientHandlesenderHandlestatusstatusChangedTimestamptimestamptransactionAmounttransferIdentifiertransferModetypeversion
receive[REDACTED]USD 0c4240e0-2c2c-6427-fb1f-71131029cd89[REDACTED][REDACTED]approved2024-06-13T04:29:56.337+00002024-06-13T04:29:20.673+0000[REDACTED]cded3395-38f9-4258-90a5-9269abfa5536contactpayment1
send[REDACTED]USD 0c4240e0-2c2c-6427-fb1f-71131029cd89[REDACTED][REDACTED]approved2024-06-13T04:29:56.337+00002024-06-13T04:29:20.673+0000[REDACTED]cded3395-38f9-4258-90a5-9269abfa5536contactpayment1

Here is an emulation you can play with and compare with real data

 

| makeresults
| eval data = mvappend("transaction: {\"version\":1,\"status\":\"approved\",\"identifier\":\"0c4240e0-2c2c-6427-fb1f-71131029cd89\",\"amount\":\"[REDACTED]\",\"transactionAmount\":\"[REDACTED]\",\"timestamp\":\"2024-06-13T04:29:20.673+0000\",\"statusChangedTimestamp\":\"2024-06-13T04:29:56.337+0000\",\"type\":\"payment\",\"transferIdentifier\":\"cded3395-38f9-4258-90a5-9269abfa5536\",\"currencyCode\":\"USD\",\"PaymentType\":\"receive\",\"senderHandle\":\"[REDACTED]\",\"recipientHandle\":\"[REDACTED]\",\"fees\":[],\"transferMode\":\"contact\"}",
"transaction: {\"version\":1,\"status\":\"approved\",\"identifier\":\"0c4240e0-2c2c-6427-fb1f-71131029cd89\",\"amount\":\"[REDACTED]\",\"transactionAmount\":\"[REDACTED]\",\"timestamp\":\"2024-06-13T04:29:20.673+0000\",\"statusChangedTimestamp\":\"2024-06-13T04:29:56.337+0000\",\"type\":\"payment\",\"transferIdentifier\":\"cded3395-38f9-4258-90a5-9269abfa5536\",\"currencyCode\":\"USD\",\"PaymentType\":\"send\",\"senderHandle\":\"[REDACTED]\",\"recipientHandle\":\"[REDACTED]\",\"fees\":[],\"transferMode\":\"contact\"}")
| mvexpand data
| rename data AS _raw
``` data emulation above ```

 

Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @anil1219 ,

this seeems to be a json format so you could use the INDEXED_EXTRACTION = JSON in the sourcetype definition in props.conf (https://docs.splunk.com/Documentation/Splunk/9.2.1/Admin/Propsconf) or the spath command (https://docs.splunk.com/Documentation/Splunk/9.2.1/SearchReference/Spath).

Otherwise, you could use a regex like the following:

 rex "\"PaymentType\":\"(?<PaymentType>[^\"]+)"

the you can test at https://regex101.com/r/VEeiyG/1

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...