topic Re: Eval a distinct count in Splunk Search

Eval a distinct count

Blu3fish — Mon, 28 Sep 2020 09:50:01 GMT

I've been trying to determine the # of free dhcp leases.

I can calculate the total current leases with:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i).*? (?P\d+.\d+.\d+.\d+)(?= )" | chart dc(src_ip)

But when I pipe this into an eval statement to subtract the dc of src_ip from the total # of free leases I always wind up with 0. Which means I'm doing something wrong.

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i).*? (?P\d+.\d+.\d+.\d+)(?= )" | chart dc(src_ip) | eval freeleases = 100 - src_ip | stats c(freeleases) as "Free Leases"

Also tried utilizing a subsearch but I receive an error that dc can't be used w/the eval function:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i).? (?P\d+.\d+.\d+.\d+)(?= )" | eval freeleases = 100 - [search src_ip= | chart dc(src_ip)] | stats c(freeleases) as "Free Leases"

Can the wise splunk jedis help this padawan learner?

Re: Eval a distinct count

acdevlin — Thu, 25 Aug 2011 18:28:03 GMT

I'm not exactly a Splunk guru myself and I don't currently live in a mud hut on Dagobah, but I do have a suggestion. Try using "eventstats" instead and saving the distinct count as its own field

index=os host=dhcp source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i).? (?P<src_ip>d+.d+.d+.d+)(?= )" | eventstats dc(src_ip) as "distinctCount" | eval freeleases = 100 - distinctCount | stats c(freeleases) as "Free Leases"

Also, depending on exactly what you're trying to see from the field "freeleases", you might want values() or sum() instead of count().

Re: Eval a distinct count

Blu3fish — Mon, 28 Sep 2020 09:50:07 GMT

Changed my rex up a bit and while:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i)lease (?P[^ ]+)" | dedup lease_ip | search lease_ip=192.168.201* | eventstats dc(lease_ip) as "distinctCount"

does return the correct # of leased IPs

| eval freeleases = 100 - distinctCount | stats c(freeleases) as "Free Leases"

returns the same result of leased IPs

Re: Eval a distinct count

acdevlin — Thu, 25 Aug 2011 20:31:39 GMT

I'm thinking the "stats" section right at the end is the problem. Try using values() instead of count() to display freeleases and see if anything comes out:

| eval freeleases=100-distinctCount | stats values(freeleases) as "Free Leases"

Re: Eval a distinct count

Blu3fish — Mon, 28 Sep 2020 09:50:12 GMT

eventstats was the right direction. But when we c(freeleases) it was counting every instance of distinctCount as freeleases. To fix this I just changed

stats c(freeleases) as "Free Leases"

stats max(freeleases) as "Free Leases"

so the final search, for all interested parties:

(I'm specifically curious about the 192.168.201 subnet above, you can nix that bit and search over all your subnets)

so problem solved! Thank you acdevlin for your help. W/o that mention of eventstats I would've been stuck in the mud.

Cheers