Help in extract data using rex in Splunk

anil1219 — Fri, 14 Jun 2024 17:10:03 GMT

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"}

Re: Help in extract data using rex in Splunk

gcusello — Fri, 14 Jun 2024 17:57:09 GMT

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

Re: Help in extract data using rex in Splunk

yuanliu — Fri, 14 Jun 2024 23:42:53 GMT

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

PaymentType

amount

currencyCode

fees

identifier

recipientHandle

senderHandle

status

statusChangedTimestamp

timestamp

transactionAmount

transferIdentifier

transferMode

type

version

receive

[REDACTED]

USD

0c4240e0-2c2c-6427-fb1f-71131029cd89

[REDACTED]

approved

2024-06-13T04:29:56.337+0000

2024-06-13T04:29:20.673+0000

[REDACTED]

cded3395-38f9-4258-90a5-9269abfa5536

contact

payment

send

[REDACTED]

USD

0c4240e0-2c2c-6427-fb1f-71131029cd89

[REDACTED]

approved

2024-06-13T04:29:56.337+0000

2024-06-13T04:29:20.673+0000

[REDACTED]

cded3395-38f9-4258-90a5-9269abfa5536

contact

payment

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

topic Re: Help in extract data using rex in Splunk in Splunk Search

Help in extract data using rex in Splunk

Re: Help in extract data using rex in Splunk

Re: Help in extract data using rex in Splunk