topic Re: stats sum(duration) by user date_month date_year - Not really Addition of duration? in Splunk Search

stats sum(duration) by user date_month date_year - Not really Addition of duration?

starks951 — Sun, 19 Sep 2010 08:33:01 GMT

Splunkers... I am looking at a VPN logs from a Cisco ASA and trying to calculate the amount of time per day per user that a user spends logged into the VPN. I can run the following and the output looks right:

asa_logid="%ASA-6-602303" OR asa_logid="%ASA-6-602304" outbound sourcetype="syslog" earliest=-10d@d latest=+1d@d
| transaction startswith="has been created" endswith="has been deleted" by vpn_session

What I get in output is an event that is started and ended properly by the vpn_session id(an extracted value from the log) but user. I also get a value called duration (which seems to be the length of the event in seconds). Thus I figured I could sum the duration by user by date_month like below:

asa_logid="%ASA-6-602303" OR asa_logid="%ASA-6-602304" outbound sourcetype="syslog" source="/var/log/hosts/172.30.64.2/messages.log" earliest=-10d@d latest=+1d@d
| transaction startswith="has been created" endswith="has been deleted" by vpn_session
| stats sum(duration) by vpn_user date_month date_year

However this summation shows people logged into the VPN for hundreds of hours per day... clearly not possible...

What am I doing wrong here?

Re: stats sum(duration) by user date_month date_year - Not really Addition of duration?

Stephen_Sorkin — Mon, 20 Sep 2010 23:17:00 GMT

Your search syntax looks right, and the stats should calculate the sum of the duration field. You should note that your stats is calculating the total duration for the month, not for the day, so it's not unreasonable for it to be greater than 86,400s.

Another possibility is that vpn_user has become a multivalued field and certain sessions are counting against multiple users.

I'd pipe the output of transaction to stats sum(duration) count to see what the total duration and number of sessions is, as a sanity check.

Re: stats sum(duration) by user date_month date_year - Not really Addition of duration?

starks951 — Tue, 21 Sep 2010 01:56:10 GMT

Ok... so I ran some MORE analysis... and it looks like date_mday is a multi-value field (for those events that last over the break in the day, ie start at 1130pm and end at 0200am). At which point we are counting this event on BOTH days..

Going to have to figure out how to only report that ONCE.

Thoughts?

Re: stats sum(duration) by user date_month date_year - Not really Addition of duration?

Stephen_Sorkin — Mon, 28 Sep 2020 09:17:54 GMT

You can explicitly compute the equivalent of date_mday using eval: "... | eval mday = strftime(_time, "%m")"

Re: stats sum(duration) by user date_month date_year - Not really Addition of duration?

starks951 — Fri, 24 Sep 2010 00:10:12 GMT

I don't have any issues getting the month day, the problem is that the two events that make up the transaction have two different month days. How do I get it to evaluation only ONE of them.