topic Re: count by percentage in Splunk Search

count by percentage

stwong — Mon, 28 Sep 2020 16:52:16 GMT

Hi, we're trying to find out windows XP users with some rules:

if mod=syn, get client ip (cli)
if mod=syn+ack, get server ip (server)
For each ip, regard as Windows XP if over 80% of OS shows os="Windows XP"

Logs look like following:

I use following search which seems to be a bit clumsy (I'm newbie to Splunk) and I'm finding the way to verify it:

search sourcetype=p0f ( mod=syn ) | rename cli AS ipaddr | fields mod, os, ipaddr 
 | append [ search sourcetype=p0f (mod="syn+ack" ) | rename srv AS ipaddr | fields mod, os, ipaddr ] 
  |  rex mode=sed field=ipaddr "s/\/.*//g" 
  | stats count, count(eval(match(os,"Windows XP"))) as XP, count(eval(NOT match(os, "Windows XP"))) as nonXP by ipaddr 
  | eval matched = XP/count * 100 | search matched >= 80 | fields ipaddr ]

I wonder if this can be achieved more efficiently. Would anyone please help? Thanks a lot.

Rgds

Re: count by percentage

martin_mueller — Tue, 17 Jun 2014 15:40:37 GMT

You can get rid of the append like this:

sourcetype=p0f mode=syn OR mod="syn+ack" | rename cli as ipaddr srv as ipaddr |  rex mode=sed field=ipaddr "s/\/.*//g"
| stats count count(eval(match(os,"Windows XP"))) as XP by ipaddr 
| eval matched = XP/count * 100 | search matched >= 80 | fields ipaddr

I've also dropped an unused field off the stats.

Re: count by percentage

stwong — Thu, 19 Jun 2014 03:26:17 GMT

We need to count cli of mod=syn and srv of mod=syn+ack, but mod=* have both cli/srv and thus we need to select only one of them depends on mod's value. Is this okay?

Thanks a lot.

Re: count by percentage

stwong — Thu, 19 Jun 2014 09:14:12 GMT

Thanks. We interested in cli of mod=syn and srv of mod="syn+ack", while cli and srv appears in mod=syn and mod=syn+ack. Seems the modification will stats cli and srv of all entries?

Re: count by percentage

somesoni2 — Thu, 19 Jun 2014 14:27:41 GMT

Try this

sourcetype=p0f mode=syn OR mod="syn+ack" | eval ipaddr=if(mod="syn+ack",srv,cli) |  rex mode=sed field=ipaddr "s/\/.*//g" | eventstats count as Total count(eval(match(os,"Windows XP"))) as XP by ipaddr | eval os=if(XP > 0.8*Total,"Windows XP",os) | stats count by ipaddr os

Re: count by percentage

martin_mueller — Thu, 19 Jun 2014 19:32:17 GMT

Ah. In that case, replace the rename with eval ipaddr = if(mod="syn+ack", srv, cli).

Re: count by percentage

stwong — Fri, 20 Jun 2014 01:21:36 GMT

That works for me. Thank you very much.