topic Splunk search to remove duplicated ips? in Splunk Search

Splunk search to remove duplicated ips?

kpavan — Wed, 11 Jan 2023 17:12:24 GMT

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!

Re: Splunk query to remove duplicated ips

gcusello — Tue, 10 Jan 2023 12:13:41 GMT

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

Re: Splunk query to remove duplicated ips

kpavan — Tue, 10 Jan 2023 13:01:05 GMT

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.

Re: Splunk query to remove duplicated ips

gcusello — Tue, 10 Jan 2023 13:20:19 GMT

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