topic Re: retrieve result from array and load in table in Dashboards & Visualizations

retrieve result from array and load in table

nithys — Tue, 07 Nov 2023 07:20:06 GMT

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

Re: retrieve result from array and load in table

ITWhisperer — Tue, 07 Nov 2023 12:54:59 GMT

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.

Re: retrieve result from array and load in table

nithys — Tue, 07 Nov 2023 14:04:47 GMT

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

Re: retrieve result from array and load in table

ITWhisperer — Tue, 07 Nov 2023 14:16:52 GMT

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

Re: retrieve result from array and load in table

nithys — Tue, 07 Nov 2023 14:44:29 GMT

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

Re: retrieve result from array and load in table

ITWhisperer — Tue, 07 Nov 2023 14:55:47 GMT

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.

Re: retrieve result from array and load in table

nithys — Wed, 08 Nov 2023 14:44:07 GMT

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

Re: retrieve result from array and load in table

ITWhisperer — Wed, 08 Nov 2023 15:12:28 GMT

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

Re: retrieve result from array and load in table

nithys — Fri, 10 Nov 2023 08:38:30 GMT

thank you @ITWhisperer Above solution worked