Splunk Search

## Eval a distinct count

Path Finder

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(srcip) | eval freeleases = 100 - srcip | 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?

Tags (5)
1 Solution
Path Finder

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"

to

stats max(freeleases) as "Free Leases"

so the final search, for all interested parties:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i)lease (?P[^ ]+)" | dedup leaseip | search leaseip=192.168.201* | eventstats dc(lease_ip) as "distinctCount" | eval freeleases = 100 - distinctCount | stats max(freeleases) as "Free Leases"

(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

Path Finder

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"

to

stats max(freeleases) as "Free Leases"

so the final search, for all interested parties:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i)lease (?P[^ ]+)" | dedup leaseip | search leaseip=192.168.201* | eventstats dc(lease_ip) as "distinctCount" | eval freeleases = 100 - distinctCount | stats max(freeleases) as "Free Leases"

(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

Communicator

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().

Communicator

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"
``````
Path Finder

Changed my rex up a bit and while:

index=os host=dhcp* source="/var/lib/dhcp3/dhcpd.leases" | rex "(?i)lease (?P[^ ]+)" | dedup leaseip | search leaseip=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