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.
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:
Give this a try
my search ... | stats count by client, value | eventstats count as clientcount by value | where value<yourLimitNumberHere | xyseries client value count
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:
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.
Try this
my search ... | stats count by client value | where value<2 | xyseries client value count
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
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.
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
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
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
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.
oh, ok ok..
check this one
mysearch | transaction client| where eventcount=1 | chart count by client, value
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