Dashboards & Visualizations

Extract the value from jsonstring in splunk

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

SplunkTrust
SplunkTrust

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

Explorer

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

Explorer

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

SplunkTrust
SplunkTrust

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

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

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

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

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

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

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

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

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

Super Champion

Hi @karthi25,
try this regex:

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

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

0 Karma