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

[jkfierro]

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

[jkfierro]

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

[woodcock]

See my updated answer.

[woodcock]

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

[woodcock · Accepted solution]

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

[jkfierro]

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.

[DalJeanis]

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

[javiergn]

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