topic Re: Alter table column and search based on objecttype in Dashboards & Visualizations

Alter table column and search based on objecttype

nithys — Mon, 06 Nov 2023 05:17:27 GMT

Hi Team,
I have a table which has counts for these attributes Re-ProcessRequest count,objectType,objectIdsCount,uniqObjectIdsCount,sqsSentCount,dataNotFoundIds
1.How can i make table column arrange as my needs,currently dataNotFoundIds shows in second coluld ,,,rather i want to display in last column.similary want to do for other columns too?
2.How can i filter based on the objecttype and do the addcolumntotal and gisplay total count?
index="" source IN "" "support request details" |stats count | rename count as Re-ProcessRequest
| join left [ search
index="" source IN "" "input params" OR "sqs sent count" OR "Total messages published to SQS successfully" OR "unique objectIds" OR "data not found for Ids"
| rex "\"objectType\":\"(?<objectType>[^\"]+)"
| rex "\"objectIdsCount\":\"(?<objectIdsCount>[^\"]+)"
| rex "\"uniqObjectIdsCount\":\"(?<uniqObjectIdsCount>[^\"]+)"
| rex "\"sqsSentCount\":\"(?<sqsSentCount>[^\"]+)"
| rex "\"dataNotFoundIds\":\"(?<dataNotFoundIds>[^\"]+)"
| rex "\"totalMessagesPublishedToSQS\":\"(?<totalMessagesPublishedToSQS>[^\"]+)"
| table objectType,objectIdsCount,sqsSentCount,totalMessagesPublishedToSQS,uniqObjectIdsCount,dataNotFoundIds | addcoltotals labelfield=total label="Total" | tail 1| stats list(*) as * ]
| join [ search
index=""source IN "" "dataNotFoundIds" | spath output=payload path=dataNotFoundIds{} | spath input=_raw | stats count by payload | addcoltotals labelfield=total label="Total" | tail 1 | fields - payload,total | rename count as datanotfound]

Re: Alter table column and search based on objecttype

gcusello — Mon, 06 Nov 2023 06:00:35 GMT

Hi @nithys ,

Let meunderstand: your issue is the fields order at the end of your search?

if this is your issue, use table at the end of your search listing fields in the wanted order.

About the filter, you can add a search command after the objectType extraction.

At least one hint: try to avoid to use join command: Splunk isn't a database and join command is very slow and resource consuming! in Community you can find many sampleas about replace join with stats.

I could be more detailes. if you could share your search using the Insert/Edit Code Sample button (<>) because the search parameters aren't clear.

Ciao.

Giuseppe

Re: Alter table column and search based on objecttype

nithys — Mon, 06 Nov 2023 06:40:10 GMT

Thanks for your reply .

index=dummyIndex source IN ("/dummy/Source")"support request details" |stats count | rename count as Re-ProcessRequest | appendcols [ search index=dummyIndex source IN ("/dummy/Source") "input params" OR "sqs sent count" OR "Total messages published to SQS successfully" OR "unique objectIds" OR "data not found for Ids" OR "dataNotFoundIds" | rex "\"objectType\":\"(?<objectType>[^\"]+)" | rex "\"objectIdsCount\":\"(?<objectIdsCount>[^\"]+)" | rex "\"uniqObjectIdsCount\":\"(?<uniqObjectIdsCount>[^\"]+)" | rex "\"sqsSentCount\":\"(?<sqsSentCount>[^\"]+)" | rex "\"totalMessagesPublishedToSQS\":\"(?<totalMessagesPublishedToSQS>[^\"]+)" | table objectType,objectIdsCount,sqsSentCount,totalMessagesPublishedToSQS,uniqObjectIdsCount | addcoltotals labelfield=total label="Total" | tail 1| stats list(*) as * ] | appendcols [ search index=dummyIndex source IN ("/dummy/source") "dataNotFoundIds" | spath output=payload path=dataNotFoundIds{} | spath input=_raw | stats count by payload | addcoltotals labelfield=total label="Total" | tail 1 | fields - payload,total | rename count as datanotfoundbynewway]

While above is my query ,Let me first ask this: Can you please elaborate more on your statement
"if this is your issue, use table at the end of your search listing fields in the wanted order."

I am looking to modify the above query such a way the column "datanotfoundbynewway" should appear at last.

Actual: It always displayed as a second column
Expected : I wanted that column to appear as the last column .

# also how can i make use of stats in the above query instead of join

Thanks again!

Re: Alter table column and search based on objecttype

nithys — Mon, 06 Nov 2023 06:42:26 GMT

Re: Alter table column and search based on objecttype

gcusello — Mon, 06 Nov 2023 06:58:29 GMT

Hi @nithys,

at the ned of your search you can use the table command to define the order of fields in output, in your case:

<your_search> | table field1 field2 field3 datanotfoundbynewway

then you are using three very similar searches as subsearches: this isn't very efficient because every subsearch takes a CPU.

In you case you couls use something like this: (please adapt my approach to your requirement):

index=dummyIndex source IN ("/dummy/Source")("support request details" OR "input params" OR "sqs sent count" OR "Total messages published to SQS successfully" OR "unique objectIds" OR "data not found for Ids" OR "dataNotFoundIds" OR "dataNotFoundIds") | rex "\"objectType\":\"(?<objectType>[^\"]+)" | rex "\"objectIdsCount\":\"(?<objectIdsCount>[^\"]+)" | rex "\"uniqObjectIdsCount\":\"(?<uniqObjectIdsCount>[^\"]+)" | rex "\"sqsSentCount\":\"(?<sqsSentCount>[^\"]+)" | rex "\"totalMessagesPublishedToSQS\":\"(?<totalMessagesPublishedToSQS>[^\"]+)" | spath output=payload path=dataNotFoundIds{} | rename dataNotFoundIds{}AS datanotfoundbynewway | stats values(objectType) AS objectType values(objectIdsCount) AS objectIdsCount values(sqsSentCount) AS sqsSentCount values(totalMessagesPublishedToSQS) AS totalMessagesPublishedToSQS values(uniqObjectIdsCount) AS uniqObjectIdsCount count AS Re-ProcessRequest values(datanotfoundbynewway) AS datanotfoundbynewway | addcoltotals labelfield=total label="Total" | table objectType objectIdsCount sqsSentCount totalMessagesPublishedToSQS uniqObjectIdsCount datanotfoundbynewway

probably this search will not work as is, but see my approach.

Ciao.

Giuseppe

Re: Alter table column and search based on objecttype

nithys — Tue, 07 Nov 2023 04:38:57 GMT

thank you