Dashboards & Visualizations

Extract the value from jsonstring in splunk

karthi25
Path Finder

I am having the field "transactionid" in the splunk log as follows:

 ***** "thread_name":"pool-2-thread-13","level":"ERROR","level_value":40000,"stack_trace":"com.fasterxml.jackson.databind.exc.InvalidFormatException: Can not deserialize value of type java.time.Instant from String \"test\": Text 'test' could not be parsed at index 0\n at [Source: {\"commisionEvent\":{\"channel\":\"testString\",\"orderType\":\"testString\",\"eventSource\":\"testString\",\"eventCreationDate\":\"2018-02-10T00:30:21.298Z\",\"orderDate\":\"2018-02-10T00:30:21.298Z\",\"dealerCode\":\"testString\",\"ban\":\"123\",\"orderNo\":\"testString\",\"originalOrderNo\":\"testString\",\"customerName\":\"testString\",\"msisdn\":\"testString\",\"orderDetails\":[{\"sku\":\"testString\",\"imei\":\"testString\",\"msisdnLineLevel\":\"testString\",\"msrp\":\"1000.00\",\"customerPaidAmount\":\"1200.00\",\"sellingPrice\":\"1000.00\",\"jumpIndicator\":\"testString\",\"eipIndicator\":\"123\",\"eip1stPayment\":\"1100.00\",\"eipPlanId\":\"testString\",\"eipInitialAmount\":\"1000.00\",\"discount\":\"100.00\",\"transactionid\":\"testString\",\"shippedDate\":\"test\",\"priceoverrideamount\":\"1000.00\",\"priceOverrideCode\":\"testString\",\"overrideReason\":\"testString\",\"originalTransactionId\":\"testString\",\"lineType\":\"testString\",\"transactionType\":\"testString\"}]}}; line: 1, column: 676] (through reference chain: com.tmobile.tfb.commissions.model.TbcUpgradesFeedPayload[\"commisionEvent\"]-com.fasterxml.jackson.datatype.jsr310.deser.InstantDeserializer.deserialize(InstantDeserializer.java:206)\n\t... 90 common frames omitted\n","APP_NAME":"tfb_hardGoods_SCMS","eventID: ":"123","eventType: ":"TBCCommissionUpgradeOrderFeed"}

I tried the below query

index=**** sourcetype=*"cf_foundation=" "cf_org_name=" "cf_space_name=Test-" "cf_app_name=***-test" | rex field=_raw ".*transactionid\\":\\"(?[^]+)"|table transactionid

but it shows the error "Error in 'rex' command: Encountered the following error while compiling the regex '.*transactionid\":\"(?[^]+)': Regex: missing terminating ] for character class"

Can anyone please suggest me the correct solutions for it.

0 Karma
1 Solution

mayurr98
Super Champion

Can you please try this

| makeresults  | eval _raw="100.00\\\",\\\"transactionid\\\":\\\"testString\\\"," | rex field=_raw "transactionid\\\\\"\:\\\\\"(?<Transaction_id>[^\\\\]+)"

In your environment, you should try

| rex field=_raw "transactionid\\\\\"\:\\\\\"(?<Transaction_id>[^\\\\]+)"

let me know if this helps!

View solution in original post

0 Karma

rupkumar4sec
Path Finder

Hello, I am facing the same problem. I tried all the solutions provided here but i am not able to extract itas needed. Just wanted to know, do these solutions worked for you?

0 Karma

rupkumar4sec
Path Finder

Hello, I am facing the same problem. I tried all the solutions provided here but i am not able to extract itas needed. Just wanted to know, do these solutions worked for you?

0 Karma

mayurr98
Super Champion

Can you please try this

| makeresults  | eval _raw="100.00\\\",\\\"transactionid\\\":\\\"testString\\\"," | rex field=_raw "transactionid\\\\\"\:\\\\\"(?<Transaction_id>[^\\\\]+)"

In your environment, you should try

| rex field=_raw "transactionid\\\\\"\:\\\\\"(?<Transaction_id>[^\\\\]+)"

let me know if this helps!

0 Karma

tiagofbmm
Influencer

Hi

I used as test only the json part of the above string:

| makeresults 
| eval sample="{\"commisionEvent\":{\"channel\":\"testString\",\"orderType\":\"testString\",\"eventSource\":\"testString\",\"eventCreationDate\":\"2018-02-10T00:30:21.298Z\",\"orderDate\":\"2018-02-10T00:30:21.298Z\",\"dealerCode\":\"testString\",\"ban\":\"123\",\"orderNo\":\"testString\",\"originalOrderNo\":\"testString\",\"customerName\":\"testString\",\"msisdn\":\"testString\",\"orderDetails\":[{\"sku\":\"testString\",\"imei\":\"testString\",\"msisdnLineLevel\":\"testString\",\"msrp\":\"1000.00\",\"customerPaidAmount\":\"1200.00\",\"sellingPrice\":\"1000.00\",\"jumpIndicator\":\"testString\",\"eipIndicator\":\"123\",\"eip1stPayment\":\"1100.00\",\"eipPlanId\":\"testString\",\"eipInitialAmount\":\"1000.00\",\"discount\":\"100.00\",\"transactionid\":\"testString\",\"shippedDate\":\"test\",\"priceoverrideamount\":\"1000.00\",\"priceOverrideCode\":\"testString\",\"overrideReason\":\"testString\",\"originalTransactionId\":\"testString\",\"lineType\":\"testString\",\"transactionType\":\"testString\"}]}}; line: 1, column: 676] (through reference chain: com.tmobile.tfb.commissions.model.TbcUpgradesFeedPayload[\"commisionEvent\"]"
| rex field=sample "transactionid\"\:\"(?<transactionid>[^\"]*)"
| table transactionid

It is extracting the transcation id with value testString.

Is that what you are looking for?

0 Karma

karthi25
Path Finder

I tried it..but just a two empty result is coming up : my query was index=*** sourcetype=cloudfoundry_apps "cf_foundation=*** " "cf_org_name=" "cf_space_name= " "cf_app_name=tfb_hardGoods_SCMS-test" | rex field= "transactionid\":\"(?[^\"]*)"
| table transaction_id

0 Karma

tiagofbmm
Influencer

Is the event you put on the top of the page exactly the one that you have in Splunk? Can you post one event in FULL please?

0 Karma

karthi25
Path Finder

following is the full event

2018-03-01T05:29:43.817263+00:00 EQM-SCMS.Test-SCMS-qlab02.tfbhardGoodsSCMS-test fa4cbb7b-26fa-425e-968d-05dabde7c79a[[APP/PROC/WEB/0]]: cf_foundation=px-npe01 cf_app_name=tfb_hardGoods_SCMS-test cf_app_id=fa4cbb7b-26fa-425e-968d-05dabde7c79a cf_org_name=EQM-SCMS cf_org_id=56f5bed9-cbdc-4ae3-a1e8-73072442a1fe cf_space_name=Test-SCMS-qlab02 cf_space_id=ba8816e1-36d0-4857-9396-87dbf162aead .source.s_cf_apps {"@timestamp":"2018-02-28T21:29:43.816-08:00","@version":1,"message":"Retry will not be attempted on this message : {}","logger_name":"com.tmobile.deep.AMQPWaitExchangePublisher","thread_name":"pool-2-thread-13","level":"ERROR","level_value":40000,"stack_trace":"com.tmobile.deep.exceptions.DEEPException: Can not deserialize value of type java.time.Instant from String \"test\": Text 'test' could not be parsed at index 0\n at [Source: {\"commisionEvent\":{\"channel\":\"testString\",\"orderType\":\"testString\",\"eventSource\":\"testString\",\"eventCreationDate\":\"2018-02-10T00:30:21.298Z\",\"orderDate\":\"2018-02-10T00:30:21.298Z\",\"dealerCode\":\"testString\",\"ban\":\"123\",\"orderNo\":\"testString\",\"originalOrderNo\":\"testString\",\"customerName\":\"testString\",\"msisdn\":\"testString\",\"orderDetails\":[{\"sku\":\"testString\",\"imei\":\"testString\",\"msisdnLineLevel\":\"testString\",\"msrp\":\"1000.00\",\"customerPaidAmount\":\"1200.00\",\"sellingPrice\":\"1000.00\",\"jumpIndicator\":\"testString\",\"eipIndicator\":\"123\",\"eip1stPayment\":\"1100.00\",\"eipPlanId\":\"testString\",\"eipInitialAmount\":\"1000.00\",\"discount\":\"100.00\",\"transactionid\":\"testString\",\"shippedDate\":\"test\",\"priceoverrideamount\":\"1000.00\",\"priceOverrideCode\":\"testString\",\"overrideReason\":\"testString\",\"originalTransactionId\":\"testString\",\"lineType\":\"testString\",\"transactionType\":\"testString\"}]}}; line: 1, column: 676] (through reference chain: com.tmobile.tfb.commissions.model.TbcUpgradesFeedPayload[\"commisionEvent\"]->com.tmobile.tfb.commissions.model.CommissionEvent[\"orderDetails\"]->java.util.ArrayList[0]->com.tmobile.tfb.commissions.model.OrderDetail[\"shippedDate\"])\n\tat com.tmobile.deep.scms.TfbHardGoodsProcessor.process(TfbHardGoodsProcessor.java:84)\n\tat com.tmobile.deep.scms.TfbHardGoodsProcessor.process(TfbHardGoodsProcessor.java:33)\n\tat com.tmobile.deep.scms.TfbHardGoodsProcessor$$EnhancerBySpringCGLIB$$36f9f84d.process()\nCaused by: com.fasterxml.jackson.databind.exc.InvalidFormatException: Can not deserialize value of type java.time.Instant from String \"test\": Text 'test' could not be parsed at index 0\n at [Source: {\"commisionEvent\":{\"channel\":\"testString\",\"orderType\":\"testString\",\"eventSource\":\"testString\",\"eventCreationDate\":\"2018-02-10T00:30:21.298Z\",\"orderDate\":\"2018-02-10T00:30:21.298Z\",\"dealerCode\":\"testString\",\"ban\":\"123\",\"orderNo\":\"testString\",\"originalOrderNo\":\"testString\",\"customerName\":\"testString\",\"msisdn\":\"testString\",\"orderDetails\":[{\"sku\":\"testString\",\"imei\":\"testString\",\"msisdnLineLevel\":\"testString\",\"msrp\":\"1000.00\",\"customerPaidAmount\":\"1200.00\",\"sellingPrice\":\"1000.00\",\"jumpIndicator\":\"testString\",\"eipIndicator\":\"123\",\"eip1stPayment\":\"1100.00\",\"eipPlanId\":\"testString\",\"eipInitialAmount\":\"1000.00\",\"discount\":\"100.00\",\"transactionid\":\"testString\",\"shippedDate\":\"test\",\"priceoverrideamount\":\"1000.00\",\"priceOverrideCode\":\"testString\",\"overrideReason\":\"testString\",\"originalTransactionId\":\"testString\",\"lineType\":\"testString\",\"transactionType\":\"testString\"}]}}; line: 1, column: 676] (through reference chain: com.tmobile.tfb.commissions.model.TbcUpgradesFeedPayload[\"commisionEvent\"]->com.tmobile.tfb.commissions.model.CommissionEvent[\"orderDetails\"]->java.util.ArrayList[0]->com.tmobile.tfb.commissions.model.OrderDetail[\"shippedDate\"])\n\tat com.fasterxml.jackson.databind.exc.InvalidFormatException.from(InvalidFormatException.java:74)\n\tat com.fasterxml.jackson.databind.DeserializationContext.weirdStringException(DeserializationContext.java:1410)\n\tat com.fasterxml.jackson.datatype.jsr310.deser.JSR310DeserializerBase._rethrowDateTimeException(JSR310DeserializerBase.java:81)\n\tat com.fasterxml.jackson.datatype.jsr310.deser.InstantDeserializer.deserialize(InstantDeserializer.java:212)\n\tat com.fasterxml.jackson.datatype.jsr310.deser.InstantDeserializer.deserialize(InstantDeserializer.java:50)\n\tat com.fasterxml.jackson.databind.deser could not be parsed at index 0\n\tat java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1949)\n\tat java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1777)\n\tat com.fasterxml.jackson.datatype.jsr310.deser.InstantDeserializer.deserialize(InstantDeserializer.java:206)\n\t... 90 common frames omitted\n","APP_NAME":"tfb_hardGoods_SCMS","eventID: ":"123","eventType: ":"TBCCommissionUpgradeOrderFeed"}
0 Karma

tiagofbmm
Influencer
| makeresults 
| eval sample="{\"@timestamp\":\"2018-02-28T21:29:43.816-08:00\",\"@version\":1,\"message\":\"Retry will not be attempted on this message : {}\",\"logger_name\":\"com.tmobile.deep.AMQPWaitExchangePublisher\",\"thread_name\":\"pool-2-thread-13\",\"level\":\"ERROR\",\"level_value\":40000,\"stack_trace\":\"com.tmobile.deep.exceptions.DEEPException: Can not deserialize value of type java.time.Instant from String \"test\": Text 'test' could not be parsed at index 0\n at [Source: {\"commisionEvent\":{\"channel\":\"testString\",\"orderType\":\"testString\",\"eventSource\":\"testString\",\"eventCreationDate\":\"2018-02-10T00:30:21.298Z\",\"orderDate\":\"2018-02-10T00:30:21.298Z\",\"dealerCode\":\"testString\",\"ban\":\"123\",\"orderNo\":\"testString\",\"originalOrderNo\":\"testString\",\"customerName\":\"testString\",\"msisdn\":\"testString\",\"orderDetails\":[{\"sku\":\"testString\",\"imei\":\"testString\",\"msisdnLineLevel\":\"testString\",\"msrp\":\"1000.00\",\"customerPaidAmount\":\"1200.00\",\"sellingPrice\":\"1000.00\",\"jumpIndicator\":\"testString\",\"eipIndicator\":\"123\",\"eip1stPayment\":\"1100.00\",\"eipPlanId\":\"testString\",\"eipInitialAmount\":\"1000.00\",\"discount\":\"100.00\",\"transactionid\":\"testString\",\"shippedDate\":\"test\",\"priceoverrideamount\":\"1000.00\",\"priceOverrideCode\":\"testString\",\"overrideReason\":\"testString\",\"originalTransactionId\":\"testString\",\"lineType\":\"testString\",\"transactionType\":\"testString\"}]}}; line: 1, column: 676] (through reference chain: com.tmobile.tfb.commissions.model.TbcUpgradesFeedPayload[\"commisionEvent\"]->com.tmobile.tfb.commissions.model.CommissionEvent[\"orderDetails\"]->java.util.ArrayList[0]->com.tmobile.tfb.commissions.model.OrderDetail[\"shippedDate\"])\n\tat com.tmobile.deep.scms.TfbHardGoodsProcessor.process(TfbHardGoodsProcessor.java:84)\n\tat com.tmobile.deep.scms.TfbHardGoodsProcessor.process(TfbHardGoodsProcessor.java:33)\n\tat com.tmobile.deep.scms.TfbHardGoodsProcessor$$EnhancerBySpringCGLIB$$36f9f84d.process()\nCaused by: com.fasterxml.jackson.databind.exc.InvalidFormatException: Can not deserialize value of type java.time.Instant from String \"test\": Text 'test' could not be parsed at index 0\n at [Source: {\"commisionEvent\":{\"channel\":\"testString\",\"orderType\":\"testString\",\"eventSource\":\"testString\",\"eventCreationDate\":\"2018-02-10T00:30:21.298Z\",\"orderDate\":\"2018-02-10T00:30:21.298Z\",\"dealerCode\":\"testString\",\"ban\":\"123\",\"orderNo\":\"testString\",\"originalOrderNo\":\"testString\",\"customerName\":\"testString\",\"msisdn\":\"testString\",\"orderDetails\":[{\"sku\":\"testString\",\"imei\":\"testString\",\"msisdnLineLevel\":\"testString\",\"msrp\":\"1000.00\",\"customerPaidAmount\":\"1200.00\",\"sellingPrice\":\"1000.00\",\"jumpIndicator\":\"testString\",\"eipIndicator\":\"123\",\"eip1stPayment\":\"1100.00\",\"eipPlanId\":\"testString\",\"eipInitialAmount\":\"1000.00\",\"discount\":\"100.00\",\"transactionid\":\"testString\",\"shippedDate\":\"test\",\"priceoverrideamount\":\"1000.00\",\"priceOverrideCode\":\"testString\",\"overrideReason\":\"testString\",\"originalTransactionId\":\"testString\",\"lineType\":\"testString\",\"transactionType\":\"testString\"}]}}; line: 1, column: 676] (through reference chain: com.tmobile.tfb.commissions.model.TbcUpgradesFeedPayload[\"commisionEvent\"]->com.tmobile.tfb.commissions.model.CommissionEvent[\"orderDetails\"]->java.util.ArrayList[0]->com.tmobile.tfb.commissions.model.OrderDetail[\"shippedDate\"])\n\tat com.fasterxml.jackson.databind.exc.InvalidFormatException.from(InvalidFormatException.java:74)\n\tat com.fasterxml.jackson.databind.DeserializationContext.weirdStringException(DeserializationContext.java:1410)\n\tat com.fasterxml.jackson.datatype.jsr310.deser.JSR310DeserializerBase._rethrowDateTimeException(JSR310DeserializerBase.java:81)\n\tat com.fasterxml.jackson.datatype.jsr310.deser.InstantDeserializer.deserialize(InstantDeserializer.java:212)\n\tat com.fasterxml.jackson.datatype.jsr310.deser.InstantDeserializer.deserialize(InstantDeserializer.java:50)\n\tat com.fasterxml.jackson.databind.deser could not be parsed at index 0\n\tat java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1949)\n\tat java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1777)\n\tat com.fasterxml.jackson.datatype.jsr310.deser.InstantDeserializer.deserialize(InstantDeserializer.java:206)\n\t... 90 common frames omitted\n\",\"APP_NAME\":\"tfb_hardGoods_SCMS\",\"eventID: \":\"123\",\"eventType: \":\"TBCCommissionUpgradeOrderFeed\"}"
| rex field=sample "transactionid\"\:\"(?<transactionid>[^\"]*)" 
| table transactionid
0 Karma

karthi25
Path Finder

Its working with the above sample makeresult which you have created but when I use my search query like
index=cloudfoundry sourcetype=cloudfoundry_apps "cf_foundation=px-npe01" "cf_org_name=EQM-SCMS" "cf_space_name=Test-SCMS-qlab02" "cf_app_name=tfb_hardGoods_SCMS-test" transactionid |rex field=_raw "eventType:\s\":\"(?[^\"]+)"
| rex field= "transactionid\":\"(?[^\"])"
| table eventType,transactionid

it's written the same empty result 😞

0 Karma

tiagofbmm
Influencer

Notice that you're syntax in the rex is not correct, this is how it should be:

| rex field=sample "transactionid\"\:\"(?<transactionid>[^\"]*)" 
| rex field=sample "eventType\:\s\:(?<eventType>[^\"]*)"

and I copied the exact same event you posted and got your desired results. Please recheck with this

index=cloudfoundry sourcetype=cloudfoundry_apps "cf_foundation=px-npe01" "cf_org_name=EQM-SCMS" "cf_space_name=Test-SCMS-qlab02" "cf_app_name=tfb_hardGoods_SCMS-test" transactionid
| rex field=_raw "transactionid\"\:\"(?<transactionid>[^\"]*)" 
| rex field=_raw "eventType\:\s\:(?<eventType>[^\"]*)"
| table eventType,transactionid
0 Karma

tiagofbmm
Influencer

Please let me know if the answer was useful for you. If it was, accept it and upvote. If not, give us more input so we can help you with that

0 Karma

493669
Super Champion

Hi @karthi25,
try this regex:

...|rex field=_raw "\"transactionid\":\"(?<transactionid>[^\"]+)"

also check in regex101: https://regex101.com/r/1nFZuR/1

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...