I have a Cisco ACS serving radius requests for VPN users. The syslog is configured for splunk and is able to receive data and index all fields. Following are the sample log texts shown for a particular user (XXXX_user93) (only interesting fields)
a) RADIUS Start record
Feb 21 10:32:34 2012-02-21 10:32:34.134 +05:30 NOTICE Radius-Accounting: RADIUS Accounting start request, NetworkDeviceName=XXXXRoamConnect, User-Name=XXXX_user93, Framed-IP-Address=10.32.38.93, Calling-Station-ID=18.104.22.168, NAS-Identifier= YYYY-FG-MUMENT, Acct-Status-Type=Start, Acct-Session-Id=00a2fc9c, AcsSessionID= INMAA-TDL-ACS-I/112925452/1282834,
b)Corresponding RADIUS Stop record
Feb 21 10:32:41 2012-02-21 10:32:41.127 +05:30 NOTICE Radius-Accounting: RADIUS Accounting stop request, NetworkDeviceName=XXXXRoamConnect, User-Name=XXXX_user93, Framed-IP-Address=10.32.38.93, Calling-Station-ID=22.214.171.124, NAS-Identifier=YYYY-FG-MUMENT, Acct-Status-Type=Stop, Acct-Session-Id=00a2fc9c, Acct-Session-Time=468, Acct-Terminate-Cause=NAS Error, AcsSessionID=INMAA-TDL-ACS-I/112925452/1282838
The start and stop requests are correlated by the field Acct-Session-Id for which the value would be the same for a particular users start and stop record.
What we are looking is a daily, weekly, monthly report in a tabular format, something similar to this
NetworkDeviceName | Username | Starttime | Endtime | Acct-Session-Id | Acct-Session-Time
I have tried with no success
We are currently evaluating Splunk and would like help in achieving the above. Have searched for apps in Splunk base and couldnt find any.
Thanks in advance.
What ways have you tried without success? I can think of two ways you could achieve this fairly easily: either using
transaction or using
transaction: the only thing that might be a bit tricky is to get the start time and end time right.
transaction always produces the field
duration that is exactly what it says, the duration of the transaction (= the difference between the timestamps of the first and last event in the transaction), so adding that value to the transaction's
_time gives you the last time. I also did some time output formatting using
strftime in order to make it human readable. The
duration field could also be used instead of the
Acct-Session-Time field, in case you don't trust the duration calculated by the ACS 🙂
... | transaction Acct-Session-Id | eval Starttime=strftime(_time,"%+") | eval Endtime=strftime(_time+duration,"%+") | table NetworkDeviceName User-Name Starttime Endtime Acct-Session-Id Acct-Session-Time
The drawback with using
transaction is it can be pretty resource intensive.
2) You can use
stats and split on
Acct-Session-Id because it is a unique identifier for each session.
stats always requires a statistical operation to be run on each field it takes as an input, so with field carrying absolute values, just use
first() to get this. NOTE that I use
first(_time) for getting the Starttime and Lasttime, respectively - this might seem confusing, but has to do with how results arrive in reverse chronological order from the search pipeline, so the latest events comes first and therefore
first(_time) grabs the latest event it comes across. Also the
eval/strftime statements are again done in order to get human readable timestamps.
... | stats first(NetworkDeviceName) as NetworkDeviceName,first(Username) as Username,last(_time) as Starttime,first(_time) as Endtime,first(Acct-Session-Time) as Acct-Session-Time by Acct-Session-Id | eval Starttime=strftime(Starttime,"%+") | eval Endtime=strftime(Endtime,"%+") | table NetworkDeviceName Username Starttime Endtime Acct-Session-Id Acct-Session-Time
Thanks Ayn, have been trying the transaction method without any success. Anyway applied your searches and here are the results
a) The transaction method works perfect. ONe question I have though is how can I group the table by username such as a single username has multiple records. I would want to publish a report by username and all associated records
b) The stats method works but is there a way I can get the duration as in transaction than AccSessionTime as reported by ACS
a) A mix of
transaction and stats might be a good idea - use the basic command in 1) and then do something like in 2), adding a "count" to the stats.
b) You can
eval the difference between the last and first timestamp for each stats entry, which will give you the duration.
For doing this in a production situation - especially with a monthly report - I would look into summary indexing. Also, depending on how long someone is connected, it may be better to maintain connection state in a lookup table, ala http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/