Splunk Search

How to add up the number of IPs in a row and output the count into a field within the same table

majek81
New Member

Good morning to all,

I want to add up the IPs in each row under the Affected_IPs field and output the count into the Ip_Count field of the associated row. Essentially, I want a new field (Ip_Count) with the count of the IPs of the Affected_IPs field &/ rows to display with all of the corresponding data within the resulting table.
My Ip_Count field comes up empty.

Base search:

|stats values(ip) as Affected_IPs
values(Ip_Count) as Ip_Count
by xref

I have an Excel image for visual aid.
Thanks in advance!

alt text

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

... | stats values(ip) AS Affected_IPs dc(Ip_Count) AS Ip_Count BY xref

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

... | stats values(ip) AS Affected_IPs dc(Ip_Count) AS Ip_Count BY xref
0 Karma

majek81
New Member

Thanks again but this didn't work, the Total field coming up empty or output 0. The solution I entered works apart from the double output which I was able to
...|stats values(ip) as Affected_IPs count as Ip_Count values(Affected_IPs)

then:
|fieldformat Ip_Count=Ip_Count/2

0 Karma

woodcock
Esteemed Legend

This is not making sense; we really should keep going and figure out why it is doubling and do it "right". Please post a few raw events to look at.

0 Karma

woodcock
Esteemed Legend

Add this:

... | eventstats dc(Affected_IP) AS Ip_Count BY xref
0 Karma

majek81
New Member

Thank you for this, I have tried this before and it did not work, the Ip_Count output was empty while all other field populate with data. I don't know if the placement of you code as any bearing on the outcome.
However, I have similar code which works:

...|stats values(ip) as Affected_IPs count as Total values(Affected_IPs)

But now the output of the count is doubled in the Ip_Count field. For instance, if Splunk finds one ip, the IP_Count field and rows counts output is two.

My next question is if you or any, know how to produce a true count doesn't double my actual ips count in the Affected_IP field?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You're close. Try

|stats values(ip) as Affected_IPs
count(Ip) as Ip_Count
values(*) as *
by xref
---
If this reply helps you, Karma would be appreciated.
0 Karma

majek81
New Member

Thank you for this, I have tried this before and it did not work. Maybe I should make myself a little more clear.
My table contains ip and other fields, I want to count the individual ip grouping for each row and put the resulting count back into the table as a Ip_Count. I hope that explanation provide more insight to my query.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try my updated answer.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...