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]
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
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
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!
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
thank you