Splunk Search

Count of each field value (I only get total counts)

jkfierro
Explorer

I have:

sourcetype=squid_proxy | stats count, values(url) as url, sum(bytes) as bytes by client_ip

Which almost get's me the result I need. It produces:

client_ip | count | url | bytes
| <#> | |

| | |

etc

What I am really looking for is:

client_ip | count | url | bytes
| <#> | |

| <#> | |

etc

Where the count value corresponds with the url value.

I have managed to get this far with the count manipulation:

sourcetype=squid_proxy | stats count(url) as "urlcount" by client_ip, url | stats values(url) as url, values(urlcount) as urlcount, sum(bytes) as bytes by client_ip

But now, bytes sum doesn't appear (column empty) and the count column is only displaying the count values rather than count of each url

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

index="YouShouldAlwaysSpecifyAnIndex" sourcetype="squid_proxy"
| stats count sum(bytes) as bytes BY client_ip url
| stats list(url) list(bytes) list(count) sum(bytes) sum(count) BY client_ip

View solution in original post

0 Karma

jkfierro
Explorer

Not sure how to properly format my post. Sorry!

I am looking for:

Column 1 - client_ip
Column 2 - count of how many times url appears for client_ip
Column 3 - each url visited by client_ip
Column 4 - sum of total bytes from all urls visited

The below starter query gets me a total count of all the url entries. For instance, a single value of "12" meaning "12 total occurrences" of "6 urls".

| stats count, values(url) as url, sum(bytes) as bytes by client_ip

The output would list out all 6 URLs in one field (column 3), and the total count of 12 in the other field (column 2).

What I'm really looking for, is, for the 6 URL listed in field, the corresponding field would have 6 counter values, sort of like below, if it is readable.

client_ip----count-----url-----sumbytes
ip1--------------2-------url1--------sum1
------------------4--------url2-------------
------------------3--------url3-------------
------------------1--------url4-------------
------------------1--------url5-------------
------------------1--------url6-------------
ip2-------------20------url1---------sum2
etc
etc
0 Karma

woodcock
Esteemed Legend

See my updated answer.

0 Karma

woodcock
Esteemed Legend

Try a blank line above your code and then a leading 4 spaces on each line.

0 Karma

woodcock
Esteemed Legend

Like this:

index="YouShouldAlwaysSpecifyAnIndex" sourcetype="squid_proxy"
| stats count sum(bytes) as bytes BY client_ip url
| stats list(url) list(bytes) list(count) sum(bytes) sum(count) BY client_ip
0 Karma

jkfierro
Explorer

Hey! I like that! Thanks very much. This gets me more than desired, but is useful stats, and the query reference will help me develop more useful reports in the future.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Be sure to mark code, and formatted output, as code, so that the interface doesn't delete anything that looks like html.

Try this -

sourcetype=squid_proxy | stats count, sum(bytes) as bytes by client_ip url
0 Karma

javiergn
SplunkTrust
SplunkTrust

Hi,

If you want the count by URL you need to group by URL as in:

sourcetype=squid_proxy 
| stats count sum(bytes) as bytes by client_ip, url

If you still want the total by url ignoring the client_ip then use eventstats afterwards:

sourcetype=squid_proxy 
| stats count, sum(bytes) as bytes by client_ip, url
| eventstats sum(count) as total_count_url by url

Or group only by url:

sourcetype=squid_proxy 
| stats count, values(client_ip) as client_ip, sum(bytes) as bytes by url

There are multiple ways of doing this. Hope that helps.
If not please provide a more detailed example.

Thanks,
J

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