how to get the string data as a json object for the below logstash logs?

[d942725]

{
@timestamp: 2020-02-04T13:46:41.274+00:00
domain: test
environment: dev
level: INFO
logger_name: com.test.practice.evthub.sse.impl.EventEncrypter
message: {"data":"6757", "key":"value"}
thread_name: main
}

For the above log, how to get the json inside the message field as a json object using spath. the output must be available to be reused for calculating stats. Finally i need to get the value available under the key. To get this task done first i need the json object to be created.

Tried using "spath input=message output=key" but didn't work for me.

[to4kawa]

props.conf

[encrypter_json]
SEDCMD-trim = s/(?s){.*?(\{.*}).*}/\1/g
KV_MODE=JSON
JSON_TRIM_BRACES_IN_ARRAY_NAMES = true

[to4kawa]

 domain="capem" environment="sit" logger_name="com.test.dna.evthub.sse.impl.EventEncrypter" message="Data =" message="" index="" index="amp-dnaeventhub" 
| rex "message: (?<message>{.*})" 
| spath input=message   path=eventName  
| table eventName

spath @ Splunk>docs
your first sample is not valid JSON. so spath is not work.
My query extracts valid JSON from message. so spath is work.

look like _raw is normal. maybe works.

---

| makeresults 
| eval _raw="{
@timestamp: 2020-02-05T09:41:19.486+00:00
domain: capem
environment: sit
level: INFO
logger_name: com.test.dna.evthub.sse.impl.EventEncrypter
message: {\"data\":{\"errorDetails\":[{\"system\":\"OCS\",\"responseCode\":404,\"request\":{\"url\":\"https://slot4.org008.t-dev.test.net/application/ocsia/v1/ocs-provisioning/service/61474817171/products\",\"body\":[\"fb92a747-1cf7-09c8-33fc-0da0d0c16d80\"]},\"response\":{\"statusCode\":404,\"error\":{\"error\":10004,\"message\":\"Service not found in OCS\"}}}],\"transactionDetails\":{\"id\":\"30d49584-76fe-4e9e-b7b4-0c3a819e432e\",\"groupId\":\"6e2d25f3-6e77-90a2-689d-1e1476e79c8b\",\"parentId\":\"fb92a747-1cf7-09c8-33fc-0da0d0c16d80\",\"serviceId\":\"61474817171\",\"downstreams\":[{\"name\":\"OCS\",\"status\":\"FAILED\",\"statusCode\":404,\"error\":{\"error\":10004,\"message\":\"Service not found in OCS\"}}],\"orderItemId\":\"5065705155871632216\",\"actionStatus\":\"FAILED\",\"dependencies\":[],\"chargingSpecId\":\"CS_SVCLSUB_001\",\"chargingSpecType\":\"SERVICE_SUBSCRIPTION\",\"productActionCode\":\"CEASE\",\"productActionType\":\"cease\",\"productInstanceId\":\"fb92a747-1cf7-09c8-33fc-0da0d0c16d80\",\"chargingSpecSubType\":\"HANDSET_CONNECTION\",\"customerAccountUuid\":\"6b1b147c-2b98-2489-cf92-cefab92a77cf\",\"orderItemActionType\":\"Create\",\"effectiveDate\":\"2020-02-05T09:40:14+00:00\",\"sourceSystem\":\"B2C-Vlocity\",\"orderId\":\"B20052034417634\"}},\"correlationId\":\"30d49584-76fe-4e9e-b7b4-0c3a819e432e\",\"eventName\":\"WTC_SubscriptionLineItemCompletion_Failed\",\"timestamp\":\"2020-02-05T09:40:14+00:00\",\"eventPublisher\":\"WTC\"}
thread_name: main
}" 
| rex "message: (?<message>{.*})" 
| spath input=message

what's data and key?

| rex "(?P<key>(?<=key\":\").*(?=\"))"

hi, how about this?
spath is not useful for you.

[d942725]

Hi tried this, but it is not returning anything. Atleast i must be able to see this key field being available in the splunk UI "Interesting fields" section so that i can use that field to show the stats in the dashboard.

[nickhills]

I think you need to give us some real example data, your sample above is obviously not representative of what it really looks like,

[d942725]

Query in use: domain="capem" environment="sit" logger_name="com.final.dna.evthub.sse.impl.EventEncrypter" message="Data =" message="" index="" index="amp-dnaeventhub" | rex field=_raw mode=sed "s/Data\s*=\s*//" | rex field=_raw mode=sed "s/,\sencrypted\susing\sEvent\sHub\skey\s=\sarn:aws:kms:ap-southeast-2:162316815215:key\/15225d7b-f71a-4c3d-bd84-24c5043c368e*//"

Data:
{ [-]
@timestamp: 2020-02-05T09:41:19.486+00:00
domain: capem
environment: sit
level: INFO
logger_name: com.final.dna.evthub.sse.impl.EventEncrypter
message: {"data":{"errorDetails":[{"system":"OCS","responseCode":404,"request":{"url":"https://slot4.org008.t-dev.final.net/application/ocsia/v1/ocs-provisioning/service/61474817171/produ... not found in OCS"}}}],"transactionDetails":{"id":"30d49584-76fe-4e9e-b7b4-0c3a819e432e","groupId":"6e2d25f3-6e77-90a2-689d-1e1476e79c8b","parentId":"fb92a747-1cf7-09c8-33fc-0da0d0c16d80","serviceId":"61474817171","downstreams":[{"name":"OCS","status":"FAILED","statusCode":404,"error":{"error":10004,"message":"Service not found in OCS"}}],"orderItemId":"5065705155871632216","actionStatus":"FAILED","dependencies":[],"chargingSpecId":"CS_SVCLSUB_001","chargingSpecType":"SERVICE_SUBSCRIPTION","productActionCode":"CEASE","productActionType":"cease","productInstanceId":"fb92a747-1cf7-09c8-33fc-0da0d0c16d80","chargingSpecSubType":"HANDSET_CONNECTION","customerAccountUuid":"6b1b147c-2b98-2489-cf92-cefab92a77cf","orderItemActionType":"Create","effectiveDate":"2020-02-05T09:40:14+00:00","sourceSystem":"B2C-Vlocity","orderId":"B20052034417634"}},"correlationId":"30d49584-76fe-4e9e-b7b4-0c3a819e432e","eventName":"WTC_SubscriptionLineItemCompletion_Failed","timestamp":"2020-02-05T09:40:14+00:00","eventPublisher":"WTC"}
thread_name: main
}

[nickhills]

Ok, so your example data is very different from what you really have,

just add |spath input=message and it should extract the contents of the message block for you.

[d942725]

tried that as well, but was not able to extract the attributes

[nickhills]

@vnravikumar Has nailed it if your source json data is quoted properly.

However in your question the quotes in the outer block are missing meaning the outer block is not valid json (please use the code formatter tool 101010 to prevent splunk answers stripping out punctuation/special characters)

In case your outer block is not valid (ie missing"s) something like this could work.

| makeresults |eval test="{
@timestamp: 2020-02-04T13:46:41.274+00:00
domain: test
environment: dev
level: INFO
logger_name: com.test.practice.evthub.sse.impl.EventEncrypter
message: {\"data\":\"6757\", \"key\":\"value\"}
thread_name: main
}"
|rex field=test "message: (?<message>\{[^\}]+\})"
|spath input=message
|table data key

[d942725]

Hi Thanks for ur reply.

I shouldn't be giving entire json in the query. Is it possible to pass that json dynamically to the existing query ?

I mean whatever u gave in eval test="{
@timestamp: 2020-02-04T13:46:41.274+00:00
domain: test
environment: dev
level: INFO
logger_name: com.test.practice.evthub.sse.impl.EventEncrypter
message: {\"data\":\"6757\", \"key\":\"value\"}
thread_name: main
}"

so instead of giving the json, i must be able to get the data inside that message field as it is a streaming application and can't try it for a single json. Any suggestions ?

[vnravikumar]

Hi

Check this

| makeresults 
| eval test="{ 
   \"@timestamp\":\"2020-02-04T13:46:41.274+00:00\",
   \"domain\":\"test\",
   \"environment\":\"dev\",
   \"level\":\"INFO\",
   \"logger_name\":\"com.test.practice.evthub.sse.impl.EventEncrypter\",
   \"message\":{ 
      \"data\":\"6757\",
      \"key\":\"value\"
       },
   \"thread_name\":\"main\"
    }" 
| spath input=test output=data path=message{}.data 
| spath input=test output=key path=message{}.key

[d942725]

Hi Thanks for ur reply.

I shouldn't be giving entire json in the query. Is it possible to pass that json dynamically to the existing query ?

I mean whatever u gave in eval test="{
@timestamp: 2020-02-04T13:46:41.274+00:00
domain: test
environment: dev
level: INFO
logger_name: com.test.practice.evthub.sse.impl.EventEncrypter
message: {\"data\":\"6757\", \"key\":\"value\"}
thread_name: main
}"

so instead of giving the json, i must be able to get the data inside that message field as it is a streaming application and can't try it for a single json. Any suggestions ?

[vnravikumar]

Hi
If your json is in _raw then you can try

index=yourindex
 | spath output=data path=message{}.data 
 | spath output=key path=message{}.key

[d942725]

can u give an example ? i didn't get the exact way to do it. take the payload mentioned above in the question and help me in framing a query to get the value in the field "key" and provide stats based on the key name.

[nickhills]

Can you post your actual event data - the whole event so we can see how its presented.
Maybe a screenshot?

[d942725]

yeah sure....

{ [-]
@timestamp: 2020-02-05T09:41:19.486+00:00
domain: capem
environment: sit
level: INFO
logger_name: com.telstra.dna.evthub.sse.impl.EventEncrypter
message: {"data":{"errorDetails":[{"system":"OCS","responseCode":404,"request":{"url":"https://slot4.org008.t-dev.telstra.net/application/ocsia/v1/ocs-provisioning/service/61474817171/pro... not found in OCS"}}}],"transactionDetails":{"id":"30d49584-76fe-4e9e-b7b4-0c3a819e432e","groupId":"6e2d25f3-6e77-90a2-689d-1e1476e79c8b","parentId":"fb92a747-1cf7-09c8-33fc-0da0d0c16d80","serviceId":"61474817171","downstreams":[{"name":"OCS","status":"FAILED","statusCode":404,"error":{"error":10004,"message":"Service not found in OCS"}}],"orderItemId":"5065705155871632216","actionStatus":"FAILED","dependencies":[],"chargingSpecId":"CS_SVCLSUB_001","chargingSpecType":"SERVICE_SUBSCRIPTION","productActionCode":"CEASE","productActionType":"cease","productInstanceId":"fb92a747-1cf7-09c8-33fc-0da0d0c16d80","chargingSpecSubType":"HANDSET_CONNECTION","customerAccountUuid":"6b1b147c-2b98-2489-cf92-cefab92a77cf","orderItemActionType":"Create","effectiveDate":"2020-02-05T09:40:14+00:00","sourceSystem":"B2C-Vlocity","orderId":"B20052034417634"}},"correlationId":"30d49584-76fe-4e9e-b7b4-0c3a819e432e","eventName":"WTC_SubscriptionLineItemCompletion_Failed","timestamp":"2020-02-05T09:40:14+00:00","eventPublisher":"WTC"}
thread_name: main
}

query in Use: domain="capem" environment="sit" logger_name="com.telstra.dna.evthub.sse.impl.EventEncrypter" message="Data =" message="" | rex field=_raw mode=sed "s/Data\s=\s*//" | rex field=_raw mode=sed "s/,\sencrypted\susing\sEvent\sHub\skey\s=\sarn:aws:kms:ap-southeast-2:162316815215:key\/15225d7b-f71a-4c3d-bd84-24c5043c368e*//"