Splunk Search
Highlighted

if an entry exists in stats, remove the entire line

Motivator

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?

0 Karma
Highlighted

Re: if an entry exists in stats, remove the entire line

Motivator

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

0 Karma
Highlighted

Re: if an entry exists in stats, remove the entire line

Motivator

Well hrumpf.... thats not better

The data is PREMISEFK, a PID, and the COMMAND. There is only 1 PREMISEFK 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.

0 Karma
Highlighted

Re: if an entry exists in stats, remove the entire line

Legend

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.

View solution in original post

Highlighted

Re: if an entry exists in stats, remove the entire line

Motivator

Hi Iguinn,

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

0 Karma