Getting Data In

How to delete rows in a table based on a field value?

leandrot
Explorer

Hi all,

I have a table which displays data from a query, what I want to achieve is to delete entire rows if the value of a particular field is zero

Thanks in advance

Best regards

0 Karma
1 Solution

kmaron
Motivator

I think it might have to do with your field name. I used a query of mine and when I renamed the field to a field name that required quotes the Where clause no longer worked.

Can you try changing your query to this:

index=a source=ab sourcetype=qm DESK=$desk_token$ 
|stats sum(PNR_COUNT) as TotalTicketsonQueue sum(AGENT_COUNT) as "Total Agents on Queue" max(OLDEST_Ticket) as "Oldest Ticket on Queue (mins)" by DESK,QUEUE_CITY,QUEUE_NUMBER 
| replace "0" WITH " " IN "Total of Agents on Queue" "Oldest PNR on Queue (mins)" 
|rename QUEUE_CITY as "Queue city", QUEUE_NUMBER as "Queue Number" 
|rangemap field="Oldest PNR on Queue (mins)" low=1-22000 elevated=22001-35000 severe=35001-99999999 default=" "
|sort "Oldest PNR on Queue (mins)" desc
| where NOT TotalTicketsonQueue == "0"
| rename TotalTicketsonQueue as "Total Tickets on Queue"

View solution in original post

kmaron
Motivator

I think it might have to do with your field name. I used a query of mine and when I renamed the field to a field name that required quotes the Where clause no longer worked.

Can you try changing your query to this:

index=a source=ab sourcetype=qm DESK=$desk_token$ 
|stats sum(PNR_COUNT) as TotalTicketsonQueue sum(AGENT_COUNT) as "Total Agents on Queue" max(OLDEST_Ticket) as "Oldest Ticket on Queue (mins)" by DESK,QUEUE_CITY,QUEUE_NUMBER 
| replace "0" WITH " " IN "Total of Agents on Queue" "Oldest PNR on Queue (mins)" 
|rename QUEUE_CITY as "Queue city", QUEUE_NUMBER as "Queue Number" 
|rangemap field="Oldest PNR on Queue (mins)" low=1-22000 elevated=22001-35000 severe=35001-99999999 default=" "
|sort "Oldest PNR on Queue (mins)" desc
| where NOT TotalTicketsonQueue == "0"
| rename TotalTicketsonQueue as "Total Tickets on Queue"

leandrot
Explorer

Many thanks!!! It worked!!

0 Karma

kmaron
Motivator

Glad to hear it!!

0 Karma

shivamgoyal23
New Member
0 Karma

kmaron
Motivator

You just need to add a where clause.

| where NOT field==0

leandrot
Explorer

Here is the query:

index=a source=ab sourcetype=qm DESK=$desk_token$
|stats sum(PNR_COUNT) as "Total Tickets on Queue" sum(AGENT_COUNT) as "Total Agents on Queue" max(OLDEST_Ticket) as "Oldest Ticket on Queue (mins)" by DESK,QUEUE_CITY,QUEUE_NUMBER
| replace "0" WITH " " IN "Total of Agents on Queue" "Oldest PNR on Queue (mins)"
|rename QUEUE_CITY as "Queue city", QUEUE_NUMBER as "Queue Number"
|rangemap field="Oldest PNR on Queue (mins)" low=1-22000 elevated=22001-35000 severe=35001-99999999 default=" "
|sort "Oldest PNR on Queue (mins)" desc
| where NOT "Total Tickets on Queue"=="0"

I already tried that but was not working

0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...