Alter table column and search based on objecttype

[nithys]

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]

[gcusello]

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

[nithys]

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!

[nithys]

[gcusello · Accepted solution]

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

[nithys]

thank you