Splunk Search

## Squid Log Analysis - Calculate total number of 'timespans' that have events

Explorer

Hi All,

I am using splunk to analyse squid logs and my goal is to identify how many minutes of the day a client ip or user is accessing the Internet (via squid) and report against it.

sourcetype="squid_access" sq_user="username" | eval mb = round(sq_bytes/1024/1024) | timechart span=5m count sum(mb)

This will report the number of events found in the log over a 5 min time span and the amount of MB downloaded.

I would like to summarise this report further and calculate how many of the '5 minute timespans' have events. (The timespan could of course be 1 minute which may make the maths easier.). This will allow me to report how many minutes as user has been browsing.

So the calculation would need to be a sum of all timespans that have events > 0. I am not necessarily interested in the number of events because the user is ether surfing or not...

I may also choose a low number of events as a threshold to exclude open web pages that have some background activity, e.g. events > 5 or events > 10.

Any idea if this is possible.

Thanks

Tags (2)
1 Solution Splunk Employee
``````sourcetype="squid_access" sq_user="username" | timechart span=5m count | where count > 0 | stats count
``````
Explorer

cool, thanks that is working well. And so simple 🙂

So for a single user it works. If I want a table of all users (I have less than 40) then I can use the following:

sourcetype="squid_access" | timechart span=1m count by sq_user limit=40

But again if I want a total of the number of minutes using '| where count > 0 | stats count' then it returns 0 results. Actually the '| where count > 0' itself returns 0 results.

Should there be a solution to reporting on all users together then I would want to chart sq_user and the total number of minutes per day, possible by a 24 hour period over a given date range.

Explorer

Thanks. Exactly what I was looking for. Splunk Employee

I'm not sure I fully understand what you're asking, but it's basically because of the way timechart formats results when you have a "by" field. You'll get what you want with: `sourcetype=squid_access | bucket _time span=1m | stats count by _time,sq_user | where count > 0 | stats count by sq_user`. Splunk Employee
``````sourcetype="squid_access" sq_user="username" | timechart span=5m count | where count > 0 | stats count
`````` State of Splunk Careers