Splunk Search

How to extract Json Object Property to Create Table?

PankajAgr
Loves-to-Learn

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. 

Labels (6)
Tags (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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 

ActivityStatusActivityTypeClientIdData.parentSpanIdData.pcm.nameData.pcm.user_idData.traceIdOriginCreationTimestampProperties.Activity      
COMPLETECreateCashTransactionType112688558259300b25e5Transaction_Type_289202314393684229b57deb074fd41df69f90226cb03f4992023-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 ```

 

 

Tags (1)
0 Karma

PankajAgr
Loves-to-Learn

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

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

ActivityStatusActivityTypeClientIdData.parentSpanIdData.pcm.user_idData.traceIdOriginCreationTimestampSourceSourcePathTenantCodeTenantContextId
COMPLETESendTemplateSettings9115766ea5ba2e592c6f23d0174bb033061b6ea293b4b694b539e2023-09-27T12:46:04.7371426+00:00Web Entry FormnullPcm.iLevelWebsite.Activities943fc4e0ab5f084274812d4d1ed045ef

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

 

 

0 Karma

PankajAgr
Loves-to-Learn

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

Tags (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.)

0 Karma

PankajAgr
Loves-to-Learn

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

PankajAgr_0-1696054971296.png


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"

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...