topic Re: get the last element of repeating json payload in Splunk Search

get the last element of repeating json payload

sharathk0525 — Thu, 25 Jun 2020 04:43:27 GMT

I have a repeating j son payload appearing in my logs.
I am interested in capturing the last payload from the logs.
right now I am seeing 3 events with below search query, but I wanted the last event
here is my search query

search query

index=abc_applications cf_space_name=production cf_app_name="my-app-name" "\"newAction\":\"request-change"\" AND "Final obj-1----------" | rex field=_raw "Final obj-1----------(?P<json_data_1>\{.*\})" | eval json_data = mvindex(json_data_1, -1) | spath input=json_data | rename data.cRID as CRID | eval Attachment_Count = spath(json_data, "changeAttachment{}") | eval Approver_Count = spath(json_data, "changeApprover{}") | eval Config_Count = spath(json_data, "changeConfigItem{}")| stats count(Attachment_Count) as Attachment_Count, count(Approver_Count) as Approver_Count, count(Config_Count) as Config_Item_Count by CRID

payload is here

this is how my logs appear you will not see this text(====start====) (====end===) in the logs, just for understanding purpose I added this line, to differentiate repeating logs The logs are exactly identical and repeating in pattern ================start============= Final obj-1---------- { "action":"Waiting Approval", "changeConfigItem":[ {} ], "changeApprover":[ {} ], "changeAttachment":[ {}, {} ] "newAction":"request-change" } ================end============= ==========start================== Final obj-1---------- { "action":"Waiting Approval", "changeConfigItem":[ {} ], "changeApprover":[ {} ], "changeAttachment":[ {}, {} ], "data":{ "cRID":"1111"} "newAction":"request-change" } ==========end================== ==========start================== Final obj-1---------- { "action":"Waiting Approval", "changeConfigItem":[ {} ], "changeApprover":[ {} ], "changeAttachment":[ {}, {} ] "newAction":"request-change" }, "data":{ "cRID":"1111"} ==========end==================

Re: get the last element of repeating json payload

kamlesh_vaghela — Thu, 25 Jun 2020 06:33:42 GMT

@sharathk0525

Please provide valid sample JSON from your _raw and your expected output from that sample. That will make us clear understanding about your requirement. Please make sure _raw events should be the single liner JSON event.

Thanks
Kamlesh Vaghela

Re: get the last element of repeating json payload

sharathk0525 — Thu, 25 Jun 2020 13:33:21 GMT

this is how my logs appear
you will not see this text(====start====) (====end===) in the logs, just for understanding purpose I added this line, to differentiate repeating logs

The logs are exactly identical and repeating in pattern

================start============= Final obj-1---------- { "action":"Waiting Approval", "changeConfigItem":[ {"ciItem" : "1"} ], "changeApprover":[ {"name" : "test" } ], "changeAttachment":[ { "fileName" : "abc.txt"}, {"fileName" : "abc.txt"} ] "newAction":"request-change" } ================end============= ==========start================== Final obj-1---------- { "action":"Waiting Approval", "changeConfigItem":[ {"ciItem" : "1"} ], "changeApprover":[ {"name" : "test" } ], "changeAttachment":[ { "fileName" : "abc.txt"}, {"fileName" : "abc.txt"} ] "newAction":"request-change" } ==========end================== ==========start================== Final obj-1---------- { "action":"Waiting Approval", "changeConfigItem":[ {"ciItem" : "1"} ], "changeApprover":[ {"name" : "test" } ], "changeAttachment":[ { "fileName" : "abc.txt"}, {"fileName" : "abc.txt"} ] "newAction":"request-change" } ==========end==================

searchQuery

here is my search query search query index=abc_applications cf_space_name=production cf_app_name="my-app-name" "\"newAction\":\"request-change"\" AND "Final obj-1----------" | rex field=_raw "Final obj-1----------(?P<json_data_1>\{.*\})" | eval json_data = mvindex(json_data_1, -1) | spath input=json_data | rename data.cRID as CRID | eval Attachment_Count = spath(json_data, "changeAttachment{}") | eval Approver_Count = spath(json_data, "changeApprover{}") | eval Config_Count = spath(json_data, "changeConfigItem{}")| stats count(Attachment_Count) as Attachment_Count, count(Approver_Count) as Approver_Count, count(Config_Count) as Config_Item_Count by CRID

current output
I am getting output as, its giving cumulative results

CRID	Attachment_Count	Approver_Count	Config_Item_Count
1111	6	3	3

expected/desired output

CRID	Attachment_Count	Approver_Count	Config_Item_Count
1111	2	1	1

hope this helps your understanding

Re: get the last element of repeating json payload

kamlesh_vaghela — Thu, 25 Jun 2020 15:50:47 GMT

@sharathk0525

I hope data.cRID field will come in your event.

index=abc_applications cf_space_name=production cf_app_name="my-app-name" "\"newAction\":\"request-change"\" AND "Final obj-1----------" | rex field=_raw "Final obj-1----------(?P<json_data_1>\{.*\})" | eval json_data = mvindex(json_data_1, -1) | spath input=json_data | dedup data.cRID | rename data.cRID as CRID | eval Attachment_Count = spath(json_data, "changeAttachment{}") | eval Approver_Count = spath(json_data, "changeApprover{}") | eval Config_Count = spath(json_data, "changeConfigItem{}")| stats count(Attachment_Count) as Attachment_Count, count(Approver_Count) as Approver_Count, count(Config_Count) as Config_Item_Count by CRID

Can you please try this?

Re: get the last element of repeating json payload

sharathk0525 — Sun, 28 Jun 2020 22:47:34 GMT

Hello Kamlesh, thanks for your reply
I am interested in getting the last payload.
dedup would eliminate duplicates, but it does not ensure that it gets me the last payload.
Is there any way, that would get me the last payload from repeating payloads pattern?

Re: get the last element of repeating json payload

kamlesh_vaghela — Mon, 29 Jun 2020 05:03:42 GMT

@sharathk0525

Yes dedup will removes the events that contain an identical combination of values for the fields that you specify. dedup will gives you most recent event on the basis of data.cRID. if you looking for most recent event then dedup is best for you. In this case you can easily ignore stats also.

https://docs.splunk.com/Documentation/Splunk/8.0.4/SearchReference/Dedup

Can you please try this for validate data?

index=abc_applications cf_space_name=production cf_app_name="my-app-name" "\"newAction\":\"request-change"\" AND "Final obj-1----------" | rex field=_raw "Final obj-1----------(?P<json_data_1>\{.*\})" | eval json_data = mvindex(json_data_1, -1) | spath input=json_data | dedup data.cRID | rename data.cRID as CRID | eval Attachment_Count = spath(json_data, "changeAttachment{}") | eval Approver_Count = spath(json_data, "changeApprover{}") | eval Config_Count = spath(json_data, "changeConfigItem{}") | table _time CRID Attachment_Count, Approver_Count, Config_Item_Count