retrieve result from array and load in table

[nithys]

Hi Team,
I have two event , attaching screenshot for reference
1.how to retrieve the uniqObjectIds and display in table form
2.how to retrieve the objectIds,version and display their value in different table column form
first event:
msg: unique objectIds
name: platform-logger
pid: 8
uniqObjectIds: [ [-]
     275649

    108976
   ]
   uniqObjectIdsCount: 1
second event:
 event: { [-]
body: { "objectType": "material", "objectIds": [ "275649" ], "version": "latest" }
msg: request body

The query i came closest is below but still unable to get what i wanted.
Actual :
Expected: in a table , i get the each object in different row .ex
|uniqueIds|
|275649|
||108976

index="" source IN ("") | eval PST=_time-28800 | eval PST_TIME=strftime(PST, "%Y-%d-%m %H:%M:%S") | eval split_field= split(_raw, "Z\"}") | mvexpand split_field | rex field=split_field "objectIdsCount=(?<objectIdsCount>[^,]+)" | rex field=split_field "uniqObjectIdsCount=(?<uniqObjectIdsCount>[^,]+)" | rex field=split_field "recordsCount=(?<recordsCount>[^,]+)" | rex field=split_field "sqsSentCount=(?<sqsSentCount>[^,]+)"|where objectType="material" | table_time,PST_TIME,objectType,objectIdsCount,uniqObjectIdsCount,recordsCount,sqsSentCount | sort _time desc

[ITWhisperer]

This looks like your events might be in JSON format. Please share your unformatted raw events in a code block </> to preserve the formatting in your events, so we might be able to assist you.

[nithys]

Hi Team,
First Event where i need to retrieve the uniqObjectIds

{"name":"","awsRequestId":"","hostname":"","pid":8,"level":30,"uniqObjectIds":["275649"],"uniqObjectIdsCount":1,"msg":"unique objectIds","time":"","v":0}

below is one event where i want the fields objecttype,objectids,version to retrieve

	
{"name":"","awsRequestId":"","hostname":"","pid":8,"level":30,"eventBody":{"objectType":"material","objectIds":["275649"],"version":"latest"},"msg":"request body","time":"2023-11-06T22:48:03.330Z","v":0}

Wanted to retrieve above two events data in the below query

index="" source IN ("") | eval PST=_time-28800 | eval PST_TIME=strftime(PST, "%Y-%d-%m %H:%M:%S") | eval split_field= split(_raw, "Z\"}") | mvexpand split_field | rex field=split_field "objectIdsCount=(?<objectIdsCount>[^,]+)" | rex field=split_field "uniqObjectIdsCount=(?<uniqObjectIdsCount>[^,]+)" | rex field=split_field "recordsCount=(?<recordsCount>[^,]+)" | rex field=split_field "sqsSentCount=(?<sqsSentCount>[^,]+)"|where objectType="material" | table_time,PST_TIME,objectType,objectIdsCount,uniqObjectIdsCount,recordsCount,sqsSentCount | sort _time desc

[ITWhisperer]

Since this is JSON, if you haven't already ingested it as JSON, you can extract the fields with the spath command

| spath
| spath input=eventBody

[nithys]

Hi @ITWhisperer 
THANKS for the above query which worked to get the data from that json in a table form.but data are displayed as duplicate/doble

index="" source IN ("") "request body"| spath
| spath input=eventBody,eventBody.objectIds{}

 

[ITWhisperer]

Your sample events didn't have duplicates in. Please share some representative unformatted events and explain what your expected results would be from those events.

[nithys]

Thanks @ITWhisperer the above spath query which worked and was able to form a table view without duplicate.
How can i combine two events results in a single row rather than display in two rows ,there is no common key to do stats by it has same source and index only the msg. is different
1.Currently uniqObjectIds,uniqueRetrievedIds are displayed in two rows in a table view,wanted as a single row
2.How to combine multiple event in a single query if there is no common key
.

index= ""    source IN ("")  "uniqObjectIds"  OR "data retrieved for Ids"
 | spath output=uniqObjectIds path=uniqObjectIds{}  | spath output=uniqueRetrievedIds path=uniqueRetrievedIds{} |  eval PST=_time-28800 | eval PST_TIME=strftime(PST, "%Y-%d-%m %H:%M:%S") | eval split_field= split(_raw, "Z\"}") | mvexpand split_field | rex field=split_field "objectIdsCount=(?<objectIdsCount>[^,]+)" | rex field=split_field "uniqObjectIdsCount=(?<uniqObjectIdsCount>[^,]+)" | rex field=split_field "recordsCount=(?<recordsCount>[^,]+)" | rex field=split_field "sqsSentCount=(?<sqsSentCount>[^,]+)" | table_time,PST_TIME,objectType,objectIdsCount,uniqObjectIdsCount,recordsCount,sqsSentCount,uniqObjectIds,uniqueRetrievedIds | sort _time desc

  

[ITWhisperer]

Since these come from the same raw event(?) you could regather the fields with a stats command

| stats values(*) as * by _raw

You may need to add _raw to your list of fields in the table command or use another field which is unique to the original event, e.g. _time

[nithys]

thank you @ITWhisperer  Above solution worked