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

[sflisher]

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.

I already have the following:

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

[sflisher]

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.

Thanks in advance for your help.

[sflisher]

Thanks. Exactly what I was looking for.

[gkanapathy]

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.

[gkanapathy · Accepted solution]

sourcetype="squid_access" sq_user="username" | timechart span=5m count | where count > 0 | stats count