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
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
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
See my updated answer.
Try a blank line above your code and then a leading 4 spaces on each line.
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
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.
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
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