Finding logs which don't yet exist

[sanorthrup]

I have radius logs and I'm looking for a way to find out which users have logged in, but not yet logged out. Every VPN session has a start message and a corresponding stop message, both share a unique Session-ID. I'd love to know which users have logged in, but not logged out in the past 24 hours. Is this even possible?

Apr 29 22:59:13 192.168.6.238 CisACS_03_RADIUSAcc 14ax4k174 1 0 User-Name=domain\user,NAS-IP-Address=192.168.2.197,NAS-Port=134664192,Group-Name=SSL_VPN,Service-Type=Framed,Framed-Protocol=PPP,Calling-Station-Id=108.206.8.210,Acct-Status-Type=Stop,Acct-Input-Octets=91504,Acct-Output-Octets=863314,Acct-Session-Id=3F0154A4,Acct-Session-Time=2361,Acct-Input-Packets=1,Acct-Output-Packets=4,

Apr 29 22:19:52 192.168.6.238 CisACS_03_RADIUSAcc oh7mmpl 1 0 User-Name=domain\user,NAS-IP-Address=192.168.2.197,NAS-Port=134664192,Group-Name=SSL_VPN,Service-Type=Framed,Framed-Protocol=PPP,Calling-Station-Id=108.206.8.210,Acct-Status-Type=Start,Acct-Session-Id=3F0154A4,

[Ayn · Accepted solution]

Are these two events the only ones you ever get, so that you only ever expect two events per Acct-Session-Id? In that case, you could do

... | stats count by Acct-Session-Id | where count<2

to find session ID's where only one event occurred.

[kristian_kolb]

when you get more up to speed, you could have look at this blog post. most useful in your type of case (since you dont know just how far back to look for those Start messages):

http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/

/k

[kristian_kolb]

You could try a subsearch;

source=your_source Acct_Status_Type = Start NOT [search source=your source Acct_Status_Type = Stop | fields + Acct_Session_Id]

The inner search (in square brackets) will return the session id's for all Stop events, so that the outer search will look for all Start events, but not those that have a corresponding Stop event.

Read up on subsearches in the docs.

[sanorthrup]

You are correct about only having one username for each session. To make matters more complicated, I also need to see the date, time (ideally I could see the entire message) and I need to filter so that I'm only seeing the messages which have "Acct-Status-Type=Start". Otherwise I see users/sessions that logged in yesterday, but logged out today. Is it possible to see the messages in their original format rather than just a column of session ID's/usernames?

[kristian_kolb]

There is a line in your event that says User-Name, possibly you have a field called User_Name? Currently that field has values like domainuser, but perhaps that is your editing/masking of real user-ids?

Then you can craft searches like;

... | stats count first(User_Name) as User by Acct_Session_Id | where count<2

There can only be one username for each session, right, so therefore you can use the first() function to stats.

/k

[sanorthrup]

You were right about the field being called Acct_Session_ID, when I made that change I received some results. But the result was a full listing of all the session ID's that I want to see, but only the session ID's. Ultimately I need a list of the usernames associated with the session_ids on the list. Is that possible?

[kristian_kolb]

Yes. That would help. But are you sure that it's not a field under a different name, like Acct_Session_Id? Check the field list to the left of the events listing.

Also, I think that you should check that you run in 'Smart' or 'Verbose' mode. Just above the time-picker/search button.

[sanorthrup]

For a 24 hour period I get 7,394 "matching events" but 0 results. I don't think Acct-Session-ID is actually a field, I think it's just text in the event. Can I/Should I make it a field? Would that help?

[kristian_kolb]

What type of output do you get when you search for;

sourcetype=ssl_vpn | stats count by Acct-Session-Id

What are the counts?
Is the field called Acct-Session-Id? Or is it just the text in the event that looks like that?

/k

[sanorthrup]

Sorry, for every unique Acct-Session-Id=xxx123 there will never be more than 2 events. They are Acct-Status-Type=Start and Acct-Status-Type=Stop.
I see what you're trying to do in your query and it looks like exactly what I need, but unfortunately it's got 7,000+ events, but 0 results whether I include "| where count<2" or not.
Here's my query:
sourcetype="ssl_vpn" | stats count by Acct-Session-Id | where count<2

[Ayn]

Yes, that's what I'm trying to address in my answer. My question was if there are two events per VALID SESSION or if there are more.

[sanorthrup]

Unfortunately there will be thousands of logs. Many of them will have both start messages and corresponding stops. I only want to see the ones which have a start, but no stop.