How to remove clients with more than one hits?

[lakromani]

I have data in this format:

client=green value=house
client=yellow value=appartement 
client=black value=bungalow
client=blue value=flat
client=yellow value=house

Using a search like this my search ... | chart count by client, value I get Statistics result like this:

         appartement    bungalow    flat    house
black   0           1           0       0
blue    0           0           1       0
green   0           0           0       1
yellow  1           0           0       1

If I would like to remove the column value with more that one hit in the table, how to do it.
In this example house should be removed, since it is listed in both green and yellow.
Or even better setting a number as a limit. If more than x hit, remove it from the table.

[somesoni2]

Give this a try

my search ... | stats count by client, value | eventstats count as clientcount by value | where value<yourLimitNumberHere | xyseries client value count

[javiergn · Accepted solution]

Another one for you:

your base search
| eventstats count by value
| eval x = 2
| eval value = if(count < x, value, null())
| chart count by client, value
| fields - NULL

And the way I tested this:

| stats count | fields - count
| eval raw = "
 client=green value=house;
 client=yellow value=appartement;  
 client=black value=bungalow;
 client=blue value=flat;
 client=yellow value=house;
"
| eval raw = split(raw, ";")
| mvexpand raw
| rename raw as _raw
| extract
| eventstats count by value
| eval x = 2
| eval value = if(count < x, value, null())
| chart count by client, value
| fields - NULL

Output:

[lakromani]

Thanks
I will accept this, since this gave me the idea for my solution.

  my search
 | eventstats count by client 
 | where count<3 
 | table client value count 
 | chart count over value by client limit=0 
 | addtotals fieldname=Total 
 | table channel Total * 
 | sort - Total | head 10 
 | fields - Total

The last part from addtotals, is just to get the value with most hit.

[sundareshr]

Try this

my search ... | stats count by client value | where value<2 | xyseries client value count

[bchung_splunk]

using addtotals will add another column named Total for total numbers,
so try:

my search ... | chart count by client, value | addtotals | search Total<x

x is the number for your limit

[lakromani]

This was close. But after testing it out, I did see that I have mixed up row and column in my example. This has now been edited. So I like to get the sum of column, and then remove it if its larger than x not sum the row. Sorry for my mistake.

[bchung_splunk]

adding tranpose before addtotals then?

 my search ... | chart count by client, value | transpose header_field=client column_name=value| addtotals | search Total<x

the table are transposed, if you'd like, you can transpose it back.

HTH,
Bill

[lakromani]

Thanks, I did give you +1 for this, since it work as well.
Only pitfall that I needed to add 0 to the transpose or I lost rows. (default 5 rows)

transpose 0 header_field=client column_name=value

[inventsekar]

In this example yellow should be removed, since it is listed in both appartement and house
my search ... | dedup client | chart count by client, value

For events that have the same 'client', keep the first 3 that occur and remove all subsequent events.
my search ... | dedup 3 client | chart count by client, value

http://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/dedup

[lakromani]

This does not work. It only remove all yellow after the first found, so there will be one yellow in the table. I would like to remove yellow 100% if it occurs more than one time, or more than x time.

PS I already have:

my search ..  | dedup value client | chart count by client, value

This then only show 0 hits or 1 hits in the table.

[inventsekar]

oh, ok ok..
check this one

mysearch | transaction client| where eventcount=1 | chart count by client, value

[lakromani]

This does not work, since for the search I like to see a graph like this:

yellow XX 
black X
blue x

Yellow 2 hits
Black 1 hits
Blue 1 hits
No green, since column house is removed