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