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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...