How to extract Json Object Property to Create Table?

[PankajAgr]

I have event Logs Similar to this.

{Level: Information
MessageTemplate: Received Post Method for activity: {Activity}
Properties: { [-]
ActionId: 533b531b-3078-448f-a054-7f54240962af
ActionName: Pcm.ActivityLog.ActivityReceiver.Controllers.v1.ActivitiesController.Post (Pcm.ActivityLog.ActivityReceiver)
Activity: {"ClientId":"1126","TenantCode":"BL.Activities","ActivityType":"CreateCashTransactionType","Source":"Web Entry Form","SourcePath":null,"TenantContextId":"00-9b57deb074fd41df69f90226cb03f499-353e17ffab1a6d25-01","ActivityStatus":"COMPLETE","OriginCreationTimestamp":"2023-09-28T11:39:48.4840749+00:00","Data":{"traceId":"9b57deb074fd41df69f90226cb03f499","parentSpanId":"88558259300b25e5","pcm.user_id":2,"pcm.name":"Transaction_Type_2892023143936842"}}
Application: ActivityLogActivityReceiver
ConnectionId: 0HMU00KGAKUBJ
CurrentCorrelationId: 95c2f966-1110-405b-ae9a-47a024343b6c
Environment: AWS-OB-DEV5
OriginCorrelationId: 95c2f966-1110-405b-ae9a-47a024343b6c
ParentCorrelationId: 95c2f966-1110-405b-ae9a-47a024343b6c
RequestId: 0HMU00KGAKUBJ:00000003
RequestPath: /api/activitylog/v1/activities
SourceContext: ActivityLog.ActivityReceiver.Controllers.v1.ActivitiesController
TenantContextId: 00-9b57deb074fd41df69f90226cb03f499-353e17ffab1a6d25-01
XRequestId: 3ba2946fa8cc0e5d5e3e82f27f566dd4
}
}

I want to create a table from Properties.Activity with some specific fields.

"ActivityType", "Source","OriginCreationTimestamp"
"CreateCashTransactionType","Web Entry Form","2023-09-28T11:39:48.4840749+00:00"

Can you help me to write the query, I tried spath/mvexpand but was not able to find it. 

[yuanliu]

Do not treat structured data as text; regex is not an appropriate tool.  I suspect that the text you posted is copied from Splunk's structured viewer, not in "RAW Text" format.  Is this correct?

If it is the case, Splunk would have already given you a field named Properties.Activity, whose value is itself an escaped, but fully compliant JSON string. (This is not a preferred method to log data.  Developers usually resort to escaped JSON when the field has combined JSON and non-JSON content.)  All you should need to do is spath.

| spath input=Properties.Activity

Your sample data should give you these fields 

ActivityStatus

ActivityType

ClientId

Data.parentSpanId

Data.pcm.name

Data.pcm.user_id

Data.traceId

OriginCreationTimestamp

Properties.Activity

COMPLETE

CreateCashTransactionType

1126

88558259300b25e5

Transaction_Type_2892023143936842

2

9b57deb074fd41df69f90226cb03f499

2023-09-28T11:39:48.4840749+00:00

{"ClientId":"1126","TenantCode":"BL.Activities","ActivityType":"CreateCashTransactionType","Source":"Web Entry Form","SourcePath":null,"TenantContextId":"00-9b57deb074fd41df69f90226cb03f499-353e17ffab1a6d25-01","ActivityStatus":"COMPLETE","OriginCreationTimestamp":"2023-09-28T11:39:48.4840749+00:00","Data":{"traceId":"9b57deb074fd41df69f90226cb03f499","parentSpanId":"88558259300b25e5","pcm.user_id":2,"pcm.name":"Transaction_Type_2892023143936842"}}

If Splunk doesn't give you Properties.Activities, please click "Raw Text" in Splunk search window and post in text.

The following is a partial emulation based on your sample data and my assumption.  You can play with it and compare with real data.

| makeresults
| eval _raw = "{\"Properties\": {
\"ActionId\": \"533b531b-3078-448f-a054-7f54240962af\",
\"ActionName\": \"Pcm.ActivityLog.ActivityReceiver.Controllers.v1.ActivitiesController.Post (Pcm.ActivityLog.ActivityReceiver)\",
\"Activity\": \"{\\\"ClientId\\\":\\\"1126\\\",\\\"TenantCode\\\":\\\"BL.Activities\\\",\\\"ActivityType\\\":\\\"CreateCashTransactionType\\\",\\\"Source\\\":\\\"Web Entry Form\\\",\\\"SourcePath\\\":null,\\\"TenantContextId\\\":\\\"00-9b57deb074fd41df69f90226cb03f499-353e17ffab1a6d25-01\\\",\\\"ActivityStatus\\\":\\\"COMPLETE\\\",\\\"OriginCreationTimestamp\\\":\\\"2023-09-28T11:39:48.4840749+00:00\\\",\\\"Data\\\":{\\\"traceId\\\":\\\"9b57deb074fd41df69f90226cb03f499\\\",\\\"parentSpanId\\\":\\\"88558259300b25e5\\\",\\\"pcm.user_id\\\":2,\\\"pcm.name\\\":\\\"Transaction_Type_2892023143936842\\\"}}\"
}}"
| spath
``` data emulation above ```

[PankajAgr]

Please find the raw text for one record

{"Level":"Information","MessageTemplate":"Received Post Method for activity: {Activity}","RenderedMessage":"Received Post Method for activity: \"{\\\"ClientId\\\":\\\"9115\\\",\\\"TenantCode\\\":\\\"Pcm.iLevelWebsite.Activities\\\",\\\"ActivityType\\\":\\\"SendTemplateSettings\\\",\\\"Source\\\":\\\"Web Entry Form\\\",\\\"SourcePath\\\":null,\\\"TenantContextId\\\":\\\"943fc4e0ab5f084274812d4d1ed045ef\\\",\\\"ActivityStatus\\\":\\\"COMPLETE\\\",\\\"OriginCreationTimestamp\\\":\\\"2023-09-27T12:46:04.7371426+00:00\\\",\\\"Data\\\":{\\\"traceId\\\":\\\"3d0174bb033061b6ea293b4b694b539e\\\",\\\"parentSpanId\\\":\\\"766ea5ba2e592c6f\\\",\\\"pcm.user_id\\\":2,\\\"pcm.field_changes\\\":[[[[[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]]]]]]}}\"","Properties":{"Activity":"{\"ClientId\"😕"9115\",\"TenantCode\"😕"Pcm.iLevelWebsite.Activities\",\"ActivityType\"😕"SendTemplateSettings\",\"Source\"😕"Web Entry Form\",\"SourcePath\":null,\"TenantContextId\"😕"943fc4e0ab5f084274812d4d1ed045ef\",\"ActivityStatus\"😕"COMPLETE\",\"OriginCreationTimestamp\"😕"2023-09-27T12:46:04.7371426+00:00\",\"Data\":{\"traceId\"😕"3d0174bb033061b6ea293b4b694b539e\",\"parentSpanId\"😕"766ea5ba2e592c6f\",\"pcm.user_id\":2,\"pcm.field_changes\":[[[[[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]]]]]]}}","SourceContext":"Pcm.ActivityLog.ActivityReceiver.Controllers.v1.ActivitiesController","ActionId":"512bd8da-6d33-43fa-bdea-98aec8557fbc","ActionName":"Pcm.ActivityLog.ActivityReceiver.Controllers.v1.ActivitiesController.Post (Pcm.ActivityLog.ActivityReceiver)","RequestId":"0HMTV8DM8SU7U:00000002","RequestPath":"/api/activitylog/v1/activities","ConnectionId":"0HMTV8DM8SU7U","TenantContextId":"943fc4e0ab5f084274812d4d1ed045ef","XRequestId":"5166ba8338c9671d9003c1d698d0e5aa","CurrentCorrelationId":"25a0fd9f-163d-493e-905d-6e296af0e776","ParentCorrelationId":"25a0fd9f-163d-493e-905d-6e296af0e776","OriginCorrelationId":"25a0fd9f-163d-493e-905d-6e296af0e776","Application":"ActivityLogActivityReceiver","Environment":"AWS-DEV6"}}

I used this query to filter the record till now, please help me to complete this

index=activitylog_activityreceiver Environment="AWS-DEV6" | spath MessageTemplate | search MessageTemplate="Received Post Method for activity: {Activity}"

[yuanliu]

Pro tip: When posting complex source text, use the code block. (</> icon).  This way your post will not have so many frowning faces 🙂

Can you confirm whether Splunk gives you a field like Properties.Activity? If it does, spath MessageTemplate would do nothing because MessageTemplate is already a top-level scalar.  If no, spath MessageTemplate would do nothing because the path MessageTemplate does not exist.

I still assume that Splunk already gives you fields Properties.Activity and MessageTemplate because your index search already invokes a field named "Environment" which is at the same top level as MessageTemplate and Properties.  All you need to do to extract values of interest from Properties.Activity

index=activitylog_activityreceiver Environment="AWS-DEV6"
 MessageTemplate="Received Post Method for activity: {Activity}"
| spath input=Properties.Activity

Your sample data should give

ActivityStatus	ActivityType	ClientId	Data.parentSpanId	Data.pcm.user_id	Data.traceId	OriginCreationTimestamp	Source	SourcePath	TenantCode	TenantContextId
COMPLETE	SendTemplateSettings	9115	766ea5ba2e592c6f	2	3d0174bb033061b6ea293b4b694b539e	2023-09-27T12:46:04.7371426+00:00	Web Entry Form	null	Pcm.iLevelWebsite.Activities	943fc4e0ab5f084274812d4d1ed045ef

This is the emulation for the data you show. (Attempt to correct those frowning faces rendered embedded Properties.Activity somewhat noncompliant, but Splunk dealt with fine.)

| makeresults
| eval _raw = "{\"Level\":\"Information\",\"MessageTemplate\":\"Received Post Method for activity: {Activity}\",\"RenderedMessage\":\"Received Post Method for activity: \\\"{\\\\\\\"ClientId\\\\\\\":\\\\\\\"9115\\\\\\\",\\\\\\\"TenantCode\\\\\\\":\\\\\\\"Pcm.iLevelWebsite.Activities\\\\\\\",\\\\\\\"ActivityType\\\\\\\":\\\\\\\"SendTemplateSettings\\\\\\\",\\\\\\\"Source\\\\\\\":\\\\\\\"Web Entry Form\\\\\\\",\\\\\\\"SourcePath\\\\\\\":null,\\\\\\\"TenantContextId\\\\\\\":\\\\\\\"943fc4e0ab5f084274812d4d1ed045ef\\\\\\\",\\\\\\\"ActivityStatus\\\\\\\":\\\\\\\"COMPLETE\\\\\\\",\\\\\\\"OriginCreationTimestamp\\\\\\\":\\\\\\\"2023-09-27T12:46:04.7371426+00:00\\\\\\\",\\\\\\\"Data\\\\\\\":{\\\\\\\"traceId\\\\\\\":\\\\\\\"3d0174bb033061b6ea293b4b694b539e\\\\\\\",\\\\\\\"parentSpanId\\\\\\\":\\\\\\\"766ea5ba2e592c6f\\\\\\\",\\\\\\\"pcm.user_id\\\\\\\":2,\\\\\\\"pcm.field_changes\\\\\\\":[[[[[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]]]]]]}}\\\"\",\"Properties\":{\"Activity\":\"{\\\"ClientId\\\":\\\"9115\\\",\\\"TenantCode\\\":\\\"Pcm.iLevelWebsite.Activities\\\",\\\"ActivityType\\\":\\\"SendTemplateSettings\\\",\\\"Source\\\":\\\"Web Entry Form\\\",\\\"SourcePath\\\":null,\\\"TenantContextId\\\":\\\"943fc4e0ab5f084274812d4d1ed045ef\\\",\\\"ActivityStatus\\\":\\\"COMPLETE\\\",\\\"OriginCreationTimestamp\\\":\\\"2023-09-27T12:46:04.7371426+00:00\\\",\\\"Data\\\":{\\\"traceId\\\":\\\"3d0174bb033061b6ea293b4b694b539e\\\",\\\"parentSpanId\\\":\\\"766ea5ba2e592c6f\\\",\\\"pcm.user_id\\\":2,\\\"pcm.field_changes\\\":[[[[[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]]]]]]}}\",\"SourceContext\":\"Pcm.ActivityLog.ActivityReceiver.Controllers.v1.ActivitiesController\",\"ActionId\":\"512bd8da-6d33-43fa-bdea-98aec8557fbc\",\"ActionName\":\"Pcm.ActivityLog.ActivityReceiver.Controllers.v1.ActivitiesController.Post (Pcm.ActivityLog.ActivityReceiver)\",\"RequestId\":\"0HMTV8DM8SU7U:00000002\",\"RequestPath\":\"/api/activitylog/v1/activities\",\"ConnectionId\":\"0HMTV8DM8SU7U\",\"TenantContextId\":\"943fc4e0ab5f084274812d4d1ed045ef\",\"XRequestId\":\"5166ba8338c9671d9003c1d698d0e5aa\",\"CurrentCorrelationId\":\"25a0fd9f-163d-493e-905d-6e296af0e776\",\"ParentCorrelationId\":\"25a0fd9f-163d-493e-905d-6e296af0e776\",\"OriginCorrelationId\":\"25a0fd9f-163d-493e-905d-6e296af0e776\",\"Application\":\"ActivityLogActivityReceiver\",\"Environment\":\"AWS-DEV6\"}}"
| spath
``` emulates
index=activitylog_activityreceiver Environment="AWS-DEV6"
  MessageTemplate="Received Post Method for activity: {Activity}"
```

If by any chance Splunk hasn't extracted Properties.Activity for obscure reasons, you can add an spath to extract it, then filter for MessageTemplate, then extract from Properties.Activity.

index=activitylog_activityreceiver Environment="AWS-DEV6"
| spath
| search MessageTemplate="Received Post Method for activity: {Activity}"
| spath input=Properties.Activity

[PankajAgr]

<>{"Level":"Information","MessageTemplate":"Received Post Method for activity: {Activity}","RenderedMessage":"Received Post Method for activity: \"{\\\"ClientId\\\":\\\"9115\\\",\\\"TenantCode\\\":\\\"Pcm.iLevelWebsite.Activities\\\",\\\"ActivityType\\\":\\\"SendTemplateSettings\\\",\\\"Source\\\":\\\"Web Entry Form\\\",\\\"SourcePath\\\":null,\\\"TenantContextId\\\":\\\"943fc4e0ab5f084274812d4d1ed045ef\\\",\\\"ActivityStatus\\\":\\\"COMPLETE\\\",\\\"OriginCreationTimestamp\\\":\\\"2023-09-27T12:46:04.7371426+00:00\\\",\\\"Data\\\":{\\\"traceId\\\":\\\"3d0174bb033061b6ea293b4b694b539e\\\",\\\"parentSpanId\\\":\\\"766ea5ba2e592c6f\\\",\\\"pcm.user_id\\\":2,\\\"pcm.field_changes\\\":[[[[[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]]]]]]}}\"","Properties":{"Activity":"{\"ClientId\"😕"9115\",\"TenantCode\"😕"Pcm.iLevelWebsite.Activities\",\"ActivityType\"😕"SendTemplateSettings\",\"Source\"😕"Web Entry Form\",\"SourcePath\":null,\"TenantContextId\"😕"943fc4e0ab5f084274812d4d1ed045ef\",\"ActivityStatus\"😕"COMPLETE\",\"OriginCreationTimestamp\"😕"2023-09-27T12:46:04.7371426+00:00\",\"Data\":{\"traceId\"😕"3d0174bb033061b6ea293b4b694b539e\",\"parentSpanId\"😕"766ea5ba2e592c6f\",\"pcm.user_id\":2,\"pcm.field_changes\":[[[[[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]],[[[]],[[]],[[]]]]]]]}}","SourceContext":"Pcm.ActivityLog.ActivityReceiver.Controllers.v1.ActivitiesController","ActionId":"512bd8da-6d33-43fa-bdea-98aec8557fbc","ActionName":"Pcm.ActivityLog.ActivityReceiver.Controllers.v1.ActivitiesController.Post (Pcm.ActivityLog.ActivityReceiver)","RequestId":"0HMTV8DM8SU7U:00000002","RequestPath":"/api/activitylog/v1/activities","ConnectionId":"0HMTV8DM8SU7U","TenantContextId":"943fc4e0ab5f084274812d4d1ed045ef","XRequestId":"5166ba8338c9671d9003c1d698d0e5aa","CurrentCorrelationId":"25a0fd9f-163d-493e-905d-6e296af0e776","ParentCorrelationId":"25a0fd9f-163d-493e-905d-6e296af0e776","OriginCorrelationId":"25a0fd9f-163d-493e-905d-6e296af0e776","Application":"ActivityLogActivityReceiver","Environment":"AWS-DEV6"}}</>

As i mentioned this is one of the record lists that is generated by Splunk query 

index=activitylog_activityreceiver Environment="AWS-DEV6" | search MessageTemplate="Received Post Method for activity: {Activity}"

I want to extend this query to generate a table from Properties.Activity field of records by extracting there some keys for each record.

if I try this query it will give a list of JSON with a single column, But i need some column extract from json

index=activitylog_activityreceiver Environment="AWS-DEV6" | search MessageTemplate="Received Post Method for activity: {Activity}" | table Properties.Activity

Hope i was able to convey what is needed

[yuanliu]

You are still throwing frowning faces😀

Anyway, have you tried this

index=activitylog_activityreceiver Environment="AWS-DEV6"
 MessageTemplate="Received Post Method for activity: {Activity}"
| table Properties.Activity
| spath input=Properties.Activity

(There should be no need to run MessageTemplate="Received Post Method for activity: {Activity}" in a second search because it is at the same level as Environment="AWS-DEV6". Running a restriction in second search is less efficient.)

[PankajAgr]

This query extracts all keys in JSON, after Properties.Activity column, In results first column in Properties.Activity and rest all are extracted from json

is it possible to get the table data in the below-mentioned columns? If so can you help to complete only for these 4 column

"ActivityType, ClientId, Source, Properties.Activity"

[yuanliu]

You mean

index=activitylog_activityreceiver Environment="AWS-DEV6"
  MessageTemplate="Received Post Method for activity: {Activity}"
| spath input=Properties.Activity
| table ActivityType, ClientId, Source, Properties.Activity