topic Re: Rex table format in Dashboards & Visualizations

Rex table format

nithys — Sun, 05 Nov 2023 02:42:43 GMT

There are three different events "input param" ,"sqs sent count", "total message published to SQS successfully"
with the first event "input param"- I am trying to fetch different entity say material/supplied material
with the 2nd event "sqs sent count"-getting the total sqs sent count for that particular material or supplied material
with the 3rd event "total message published to SQS successfully"-getting the count of total message published.
Now i want then to publish in a single row for all those count displayed in table for a single objectype to get in one dashboard panel
Then do a total counts for each columns and displays as a single row which will display in other panel of dashboard

Re: Rex table format

gcusello — Sun, 05 Nov 2023 09:13:38 GMT

Hi @nithys,

could you share some samples of your logs and your search (please both in text format, not screenshot)?

Ciao.

Giuseppe

Re: Rex table format

ITWhisperer — Sun, 05 Nov 2023 09:25:36 GMT

You can use addtotals to sum the numbers. Is this what you are after?

If not, how do you correlate different events to generate separate rows with their respective counts?

Re: Rex table format

nithys — Sun, 05 Nov 2023 14:01:31 GMT

Hi @gcusello
Below is the query.....Firstly I want them to get aligned all rows with each values in a single row.Currently there are 3 different rows for 1 particular result.....then apply sum of columns
index="" source IN "" "input params" OR "sqs sent count" OR "Total messages published to SQS successfully" | rex "\"objectType\":\"(?<objectType>[^\"]+)"
  | rex "\"objectIdsCount\":\"(?<objectIdsCount>[^\"]+)"
  | rex "\"sqsSentCount\":\"(?<sqsSentCount>[^\"]+)"
    | rex "\"totalMessagesPublishedToSQS\":\"(?<totalMessagesPublishedToSQS>[^\"]+)"
  | table objectType,objectIdsCount,sqsSentCount,totalMessagesPublishedToSQS

Re: Rex table format

gcusello — Mon, 06 Nov 2023 06:06:14 GMT

Hi @nithys ,

without a sample of your logs I cannot check yur regexes and I don't understand what't the key to correlate values because it seems that there isn't any common key to use in a stats command.

Ciao.

Giuseppe

Re: Rex table format

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

Hi @gcusello @ITWhisperer
I have same source and index for below two events
first event:
{ [-]
   awsRequestId:
   hostname:
   level: 30
   msg: data retrieved for Ids
   name:
   pid: 8
   time:
   uniqueRetrievedIds: [ [-

275649
   ]
   v: 0
}
second event:
{ [-]
   awsRequestId:
   hostname:
   level: 30
   msg: unique objectIds
   name:
   pid: 8
   time:
   uniqObjectIds: [ [-]
     275649
   ]
   uniqObjectIdsCount: 1
   v: 0
}
There is no common key in these two events,but want to have in table view
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: Rex table format

ITWhisperer — Wed, 08 Nov 2023 15:06:24 GMT

Please share the raw unformatted sample event in a code block </> to preserve the original formatting.

Re: Rex table format

nithys — Wed, 08 Nov 2023 15:19:04 GMT

Hi @ITWhisperer
Below are the raw events which are need to be displayed in table format in a single row for below events with no common key value

{"name":"","awsRequestId":"","hostname":"","pid":8,"level":30,"uniqObjectIds":["275649"],"uniqObjectIdsCount":1,"msg":"unique objectIds","time":"2023-11-03T19:26:43.672Z","v":0}

{"name":"","awsRequestId":"","hostname":"","pid":8,"level":30,"uniqueRetrievedIds":["275649"],"msg":"data retrieved for Ids","time":"2023-11-06T22:48:03.594Z","v":0}

{"name":"","awsRequestId":"","hostname":"","pid":8,"level":30,"eventBody":{"objectType":"material","objectIds":["275649","108795","1234567","1234568","99999999","888888888"],"version":"all"},"msg":"request body","time":"2023-11-03T05:25:33.508Z","v":0}

Re: Rex table format

ITWhisperer — Wed, 08 Nov 2023 15:59:56 GMT

OK so how do you want them combined e.g. do you want the times from the "unique objectIds" message and "data retrieved for Ids" message to be in the same row by object id?

| spath uniqObjectIds{} output=uniqObjectIds | spath uniqueRetrievedIds{} output=uniqueRetrievedIds | spath eventBody.objectIds{} output=eventBodyObjectIds | eval eventBodyObjectIds=if(eventBodyObjectIds=="",null(),eventBodyObjectIds) | eval objectId=coalesce(eventBodyObjectIds,coalesce(uniqueRetrievedIds,uniqObjectIds)) | eval retrievedTime=if(msg=="data retrieved for Ids",time,null()) | eval uniqueTime=if(msg=="unique objectIds",time,null()) | stats values(retrievedTime) as retrievedTime values(uniqueTime) as uniqueTime by objectId

Re: Rex table format

nithys — Thu, 09 Nov 2023 14:32:46 GMT

Hi @ITWhisperer
Thank for your reply and suggestions.Really appreciate !
Now I have added a log in all the below raw event which will have "entity":"suppliedMaterial"

{"name":"","awsRequestId":"","hostname":"","pid":8,"level":30,"supportType":"reprocess","entity":"suppliedMaterial","uniqObjectIds":["122598","268817","88888888888","99999999999999999","abc"],"uniqObjectIdsCount":5,"msg":"unique objectIds","time":"2023-11-09T13:56:36.559Z","v":0} 2nd event {"name":"","awsRequestId":"","hostname":"","pid":8,"level":30,"supportType":"reprocess","entity":"suppliedMaterial","uniqueRetrievedIds":[],"msg":"data retrieved for Ids","time":"2023-11-09T13:56:36.749Z","v":0} 3rd event {"name":"","awsRequestId":"","hostname":"","pid":8,"level":30,"supportType":"reprocess","entity":"suppliedMaterial","dataNotFoundIds":["122598","268817","88888888888","99999999999999999","abc"],"dataNotFoundIdsCount":5,"msg":"data not found for Ids","time":"2023-11-09T13:56:36.749Z","v":0}

Currently i have three query which fetch the result from _raw in below table format
Expected.I want all three different table combined and show their result based on common key value entity: "suppliedMaterial" from raw events .

uniqObjectIds	uniqObjectIdsCount	uniqObjectIds{}	PST_TIME
122598 268817 88888888888 99999999999999999 abc	5	122598 268817 88888888888 99999999999999999 abc	2023-08-11 06:38:01
122598 268817 88888888888 99999999999999999 abcdefg	5	122598 268817 88888888888 99999999999999999 abcdefg	2023-08-11 06:37:44
122598 268817 88888888888 99999999999999999 abcdefg	5	122598 268817 88888888888 99999999999999999 abcdefg	2023-08-11 06:35:40
122598 268817 88888888888 99999999999999999 abcdefg	5	122598 268817 88888888888 99999999999999999 abcdefg	2023-08-11 06:33:14

uniqueRetrievedIds{}	uniqueRetrievedIds	PST_TIME
122598 268817	122598 268817	2023-08-11 06:38:01
122598 268817	122598 268817	2023-08-11 06:37:44
122598 268817	122598 268817	2023-08-11 06:35:40
122598 268817	122598 268817	2023-08-11 06:33:14
122598 268817	122598 268817	2023-08-11 03:28:56
275649	275649	2023-06-11 06:49:10
275649	275649	2023-03-11 04:28:36
275649	275649	2023-03-11 03:13:07
108795	108795	2023-03-11 03:09:07
108795 275649	108795 275649	2023-02-11 14:44:04

dataNotFoundIds{}	dataNotFoundIdsCount	PST_TIME
88888888888 99999999999999999 abc	3	2023-08-11 06:38:01
88888888888 99999999999999999 abcdefg	3	2023-08-11 06:37:44
88888888888 99999999999999999 abcdefg	3	2023-08-11 06:35:40
88888888888 99999999999999999 abcdefg	3

Re: Rex table format

nithys — Thu, 09 Nov 2023 14:35:47 GMT

continue...

Hi @ITWhisperer The query used to get the above three table format

1st query: index= source IN ("") "uniqObjectIds" OR "data retrieved for Ids" | eval PST=_time-28800 | eval PST_TIME=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=uniqObjectIds path=uniqObjectIds{} |where isnotnull(uniqObjectIds) | spath output=uniqueRetrievedIds path=uniqueRetrievedIds{}| stats values(*) as * by _raw | table uniqObjectIds,uniqObjectIdsCount,uniqObjectIds{},PST_TIME | sort- PST_TIME 2nd query" index= source IN ("") "data retrieved for Ids"| eval PST=_time-28800 | eval PST_TIME=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=uniqueRetrievedIds path=uniqueRetrievedIds{} |where isnotnull(uniqueRetrievedIds)| stats values(*) as * by _raw | table uniqueRetrievedIds{},uniqueRetrievedIds, PST_TIME | sort- PST_TIME 3rd query: index= source IN ("") "data not found for Ids"| eval PST=_time-28800 | eval PST_TIME=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=dataNotFoundIds path=dataNotFoundIds{}|where isnotnull(dataNotFoundIds) | stats values(*) as * by _raw | table dataNotFoundIds{},dataNotFoundIdsCount, PST_TIME | sort- PST_TIME

Could you help

Re: Rex table format

ITWhisperer — Thu, 09 Nov 2023 14:52:11 GMT

What would your expected result look like?

Re: Rex table format

nithys — Thu, 09 Nov 2023 15:18:51 GMT

HI @ITWhisperer

I want all three query combined and display their results in one table based on entity:suppliedMaterial

uniqObjectIds	uniqObjectIdsCount	PST_TIME	uniqueRetrievedIds{}	uniqueRetrievedIds	PST_TIME	dataNotFoundIds	dataNotFoundIdsCount	PST_TIME
122598 268817 88888888888 99999999999999999 abc	5	122598 268817 88888888888 99999999999999999 abc	5	122598 268817 88888888888 99999999999999999 abc	2023-08-11 06:38:01	122598 268817	122598 268817	2023-08-11 06:38:01
88888888888 99999999999999999 abc	3	122598 268817 88888888888 99999999999999999 abc	5	122598 268817 88888888888 99999999999999999 abc	2023-08-11 06:38:01	122598 268817	122598 268817	2023-08-11 06:37:44
similar to above	similar to above	similar to above	similar to above	ssimilar to above	similar to above	similar to above	similar to above	similar to above	similar to above
		....

Re: Rex table format

ITWhisperer — Thu, 09 Nov 2023 15:48:35 GMT

You can't have multiple columns with the same name so try this

index= source IN ("") "uniqObjectIds" OR "data retrieved for Ids" | eval PST=_time-28800 | eval PST_TIME=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=uniqObjectIds path=uniqObjectIds{} |where isnotnull(uniqObjectIds) | spath output=uniqueRetrievedIds path=uniqueRetrievedIds{}| stats values(*) as * by _raw | table uniqObjectIds,uniqObjectIdsCount,uniqObjectIds{},PST_TIME | sort- PST_TIME | appendcols [search index= source IN ("") "data retrieved for Ids"| eval PST=_time-28800 | eval PST_TIME2=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=uniqueRetrievedIds path=uniqueRetrievedIds{} |where isnotnull(uniqueRetrievedIds)| stats values(*) as * by _raw | table uniqueRetrievedIds{},uniqueRetrievedIds, PST_TIME2 | sort- PST_TIME2 ] | appendcols [search index= source IN ("") "data not found for Ids"| eval PST=_time-28800 | eval PST_TIME3=strftime(PST, "%Y-%d-%m %H:%M:%S") | spath output=dataNotFoundIds path=dataNotFoundIds{}|where isnotnull(dataNotFoundIds) | stats values(*) as * by _raw | table dataNotFoundIds{},dataNotFoundIdsCount, PST_TIME3 | sort- PST_TIME3 ]

Re: Rex table format

nithys — Fri, 10 Nov 2023 08:41:07 GMT

Thanks much @ITWhisperer It really worked