topic Re: Finding logs which don't yet exist in Getting Data In

Finding logs which don't yet exist

sanorthrup — Mon, 28 Sep 2020 13:48:38 GMT

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,

Re: Finding logs which don't yet exist

Ayn — Tue, 30 Apr 2013 20:24:48 GMT

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.

Re: Finding logs which don't yet exist

sanorthrup — Tue, 30 Apr 2013 21:51:18 GMT

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.

Re: Finding logs which don't yet exist

Ayn — Tue, 30 Apr 2013 21:56:15 GMT

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.

Re: Finding logs which don't yet exist

sanorthrup — Tue, 30 Apr 2013 22:33:49 GMT

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

Re: Finding logs which don't yet exist

kristian_kolb — Tue, 30 Apr 2013 23:28:00 GMT

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?

Re: Finding logs which don't yet exist

sanorthrup — Tue, 30 Apr 2013 23:47:28 GMT

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?

Re: Finding logs which don't yet exist

kristian_kolb — Wed, 01 May 2013 00:01:37 GMT

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.

Re: Finding logs which don't yet exist

sanorthrup — Mon, 28 Sep 2020 13:48:40 GMT

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?

Re: Finding logs which don't yet exist

kristian_kolb — Wed, 01 May 2013 08:33:07 GMT

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.

Re: Finding logs which don't yet exist

sanorthrup — Wed, 01 May 2013 11:31:06 GMT

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?

Re: Finding logs which don't yet exist

kristian_kolb — Wed, 01 May 2013 13:24:55 GMT

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.

Re: Finding logs which don't yet exist

kristian_kolb — Wed, 01 May 2013 17:52:04 GMT

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/