topic Re: Extract the value from jsonstring in splunk in Dashboards & Visualizations

Extract the value from jsonstring in splunk

karthi25 — Tue, 29 Sep 2020 18:24:34 GMT

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.

Re: Extract the value from jsonstring in splunk

493669 — Mon, 12 Mar 2018 07:45:06 GMT

Hi @karthi25,
try this regex:

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

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

Re: Extract the value from jsonstring in splunk

tiagofbmm — Mon, 12 Mar 2018 07:46:53 GMT

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?

Re: Extract the value from jsonstring in splunk

karthi25 — Tue, 29 Sep 2020 18:24:37 GMT

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

Re: Extract the value from jsonstring in splunk

tiagofbmm — Mon, 12 Mar 2018 08:38:51 GMT

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?

Re: Extract the value from jsonstring in splunk

karthi25 — Mon, 12 Mar 2018 08:45:02 GMT

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

Re: Extract the value from jsonstring in splunk

tiagofbmm — Mon, 12 Mar 2018 08:53:07 GMT

| 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

Re: Extract the value from jsonstring in splunk

karthi25 — Tue, 29 Sep 2020 18:24:43 GMT

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 😞

Re: Extract the value from jsonstring in splunk

mayurr98 — Mon, 12 Mar 2018 10:18:29 GMT

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!

Re: Extract the value from jsonstring in splunk

tiagofbmm — Mon, 12 Mar 2018 10:27:53 GMT

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

Re: Extract the value from jsonstring in splunk

tiagofbmm — Wed, 21 Mar 2018 16:58:51 GMT

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

Re: Extract the value from jsonstring in splunk

rupkumar4sec — Tue, 04 Dec 2018 19:17:39 GMT

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?

Re: Extract the value from jsonstring in splunk

rupkumar4sec — Tue, 04 Dec 2018 19:17:39 GMT

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?