count by percentage

[stwong]

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

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

Logs look like following:

[2014/05/19 10:40:01] mod=syn|cli=192.168.133.251/36360|srv=192.168.188.98/80|subj=cli|os=Windows NT kernel 5.x|dist=5|params=generic fuzzy|raw_sig=4:59+5:0:1460:65535,8:mss,nop,ws,nop,nop,sok:df,id+:0
[2014/05/19 10:35:28] mod=syn+ack|cli=192.168.94.71/49771|srv=192.168.11.122/80|subj=srv|os=Windows 7 or 8|dist=3|params=none|raw_sig=4:125+3:0:1460:8192,8:mss,nop,ws,sok,ts:df,id+:0

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

[somesoni2]

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

[stwong]

That works for me. Thank you very much.

[stwong]

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.

[martin_mueller]

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.

[martin_mueller]

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

[stwong]

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?