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.

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

Tags (2)
0 Karma
1 Solution

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

View solution in original post

0 Karma

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.

Thanks in advance for your help.

0 Karma

Explorer

Thanks. Exactly what I was looking for.

0 Karma

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

0 Karma

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

View solution in original post

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!