Extract the value from jsonstring in splunk

[karthi25]

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.

[rupkumar4sec]

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?

[rupkumar4sec]

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?

[mayurr98 · Accepted solution]

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!

[tiagofbmm]

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?

[karthi25]

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

[tiagofbmm]

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?

[karthi25]

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

[tiagofbmm]

| 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

[karthi25]

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 😞

[tiagofbmm]

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

[tiagofbmm]

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

[493669]

Hi @karthi25,
try this regex:

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

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