if an entry exists in stats, remove the entire line

[dbcase]

Hi,

I have this work in progress query

index=betats source="*top.csv" | dedup PREMISE_FK COMMAND PID | where COMMAND!="" OR COMMAND!="top -n 1"|bin _time span=1d | table PREMISE_FK COMMAND PID|stats list(COMMAND) count(PREMISE_FK) as count by PREMISE_FK PID|where count>1|sort -count |eval commandname=if(COMMAND='com.icontrol.startupService',count=0,count)

it produces a table that looks like this

1000627 2624    
com.icontrol.activation
com.icontrol.serviceBlock1
com.icontrol.screen
3   3
1000627 2638    
com.icontrol.startupService
com.icontrol.serviceBlock1
com.icontrol.security.converge
3   3

What I'd like to do is if the table row contains com.icontrol.startupService then I'd like to remove the entire row. From the example above it would now only contain

1000627 2624    
com.icontrol.activation
com.icontrol.serviceBlock1
com.icontrol.screen
3   3

I've been attempting to reset the count to 0 if it equals com.icontrol.startupService but no luck. Thoughts?

[lguinn2 · Accepted solution]

This should work

index=betats source="*top.csv"
| dedup PREMISE_FK COMMAND PID
| where COMMAND!="" OR COMMAND!="top -n 1"
| stats list(COMMAND) as COMMAND count(PREMISE_FK) as count by PREMISE_FK PID
| where count > 1
| sort -count
| search NOT COMMAND="com.icontrol.startupService"

First, I removed the table and bin commands, which were not contributing to the final output.
But the interesting part is that I used a search command at the end, instead of where. The reason this works is because
1 - the output of the stats command was a field named list(COMMAND). I fixed that.
2 - the output of the list function is a multi-valued field. search can handle the multi-valued field better than where.

[dbcase]

Hi Iguinn,

That is pretty slick! Many thanks, worked like a charm

[dbcase]

Hmmm the formatting of the data makes it difficult. this is hopefully better
PREMISE_FK PID COMMAND Count
1000627 2624 com.icontrol.activation 3
com.icontrol.serviceBlock1
com.icontrol.screen

1000627 2638 com.icontrol.startupService 3
com.icontrol.serviceBlock1
com.icontrol.security.converge

[dbcase]

Well hrumpf.... thats not better

The data is PREMISE_FK, a PID, and the COMMAND. There is only 1 PREMISE_FK and 1 PID per row, but there can be multiple commands. If any of the commands equals com.icontrol.startupService then the entire row should be removed.