Splunk search to remove duplicated ips?

[kpavan]

Hi All,

Greetings!

Need help on splunk query,

I have 2 indexes assets and vulns, am trying to build report to analyze percent % assets are not scanned, with below query am getting results percent, but some of the ip's are having duplicate entries which are showing as as scanned and not scanned for same ip, i need query to remove from SCANNED =0 if the same ip SCANNED=1, I tried using dedup but since its removing randomly so scanned ip's also getting removed. 

Please help me with correct query

((index=index1 sourcetype=asset) OR (index=index1 sourcetype=vulns))
| eval vuln=if('sourcetype'="vulns","yes","no")
| eval assets=if('sourcetype'="asset","yes","no")
| stats max(eval(if(vuln="yes",1,0))) AS SCANNED max(eval(if(assets="yes",1,0))) AS ASSETS latest(ip) as ip by uuid
| search ASSETS=1
| stats count(eval( SCANNED > 0)) AS scanned, count(uuid) as total
| eval percent = round((scanned/(total))*100,2)

Result example

scanned	ASSETS	ip
0	1	192.168.1.1
1	1	192.168.1.1

Thanks!

[gcusello]

Hi @kpavan,

please try this:

((index=index1 sourcetype=asset) OR (index=index1 sourcetype=vulns))
| stats count(eval(if(sourcetype="vulnes",1,0))) AS SCANNED count(eval(if(sourcetype="asset",1,0))) AS ASSETS count AS total BY ip
| where ASSETS>0
| eval percent = round((SCANNED /(total))*100,2)

Ciao.

Giuseppe

[kpavan]

Hi @gcusello,

Thanks for the response!

I tried with your query, since uuid is the unique identifier and ip's are overlap with different network ranges. So if I use stats by ip the % are dropping from actual percent when do manual calculation.

So I wanted to remove ip's where it was already mentioned as scanned.

[gcusello]

HI @kpavan,

you must use uuid as identifier, could you have more IPs for the same IP?

if yes, how do you want to manage this situation?

maybe you could try something like this:

((index=index1 sourcetype=asset) OR (index=index1 sourcetype=vulns))
| stats count(eval(if(sourcetype="vulnes",1,0))) AS SCANNED count(eval(if(sourcetype="asset",1,0))) AS ASSETS values(ip) AS ip count AS total BY uuid
| mvexpand ip
| stats 
   max(SCANNED) AS SCANNED 
   max(ASSETS) AS ASSETS 
   max(total) AS total BY ip 
| where ASSETS>0
| eval percent = round((SCANNED /(total))*100,2)

Ciao.

Giuseppe