Splunk Search

Finding users currently logged in to my app

cjmckenna
New Member

For some reason I am having a real hard time wrapping my head around something..... We have an application where we need to track who is currently logged in. The application writes a log entry when they login and when they logoff. They can login concurrently from multiple stations as well.

So what I need to figure out is if time of login for that user/station combo is higher than the logout time for that user/station combo then we assume they are still logged. So the outcome will need to be a table that shows the ID, Station and time of the current logged in users.

Here is a sample of the data:

ACTION                  STATION                UID                   au_Time
Login                  1.2                                 123456            2017-10-19 09:15:45.0
Login                  1.3                                 987654            2017-10-19 09:24:35.0
Login                  1.4                                 ABCDEF            2017-10-19 09:40:27.0
Login                  1.3                                 XYZPDQ            2017-10-19 10:10:34.0
Login                  1.6                                 XYZPDQ            2017-10-19 10:11:48.0
Login                  1.5                                 XXX111            2017-10-19 09:40:38.0
Logoff                 1.3                                 987654            2017-10-19 09:44:40.0
Logoff                 1.2                                 123456            2017-10-18 14:57:12.0
Logoff                 1.6                                 XYZPDQ            2017-10-19 10:38:52.0

In the example above you see UID 987654 logged in at 9:24 and logged out at 9:44 so they are not currently logged in
- User 123456 last logout was on the 18th at 14:57 and they logged in again on the 19th at 9:15. So they are currently logged in
- User XYZPDQ logged in to station 1.6 at 10:11 on the 19th and logged of of station 1.6 at 1:38 but this user XYZPDQ is still looed in on station 1.3

I just cant figure out the best way to see if that user and station has a greater time for logout than they do for login to decide if they are currently logged in or not.

Thanks for any help !!!

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

You can do that by filtering out all but the most recent event for each station/user pair. That will leave either a Login or a Logout for each. Then display just the Logins and you'll have your list of those you are still logged in.

<your search> | dedup STATION UID | where ACTION=Login | table ACTION STATION UID _time
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

You can do that by filtering out all but the most recent event for each station/user pair. That will leave either a Login or a Logout for each. Then display just the Logins and you'll have your list of those you are still logged in.

<your search> | dedup STATION UID | where ACTION=Login | table ACTION STATION UID _time
---
If this reply helps you, Karma would be appreciated.

DalJeanis
SplunkTrust
SplunkTrust

@richgalloway - Put quotes around "Login".

0 Karma

elliotproebstel
Champion

First, convert the timestamps from strings to epoch time:
| eval _time=strptime(au_Time, "%F %X.%1Q")
Then add a field called latest_time to each event. This field will contain the timestamp of the latest event for that given station:
| eventstats latest(_time) AS latest_time BY STATION
Then search for only events where the timestamp of the entry is the same as the latest timestamp:
| where latest_time=_time
This will give you only the lines containing the latest event for each station. From there, you can manipulate the data to output exactly what you need.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@elliotproebstel - Acceptable, and gets the right result. However, overriding the builtin _time field with different data should be avoided, in my opinion, other than for presentation such as in timechart.

Also, note @richgalloway's answer as slightly more elegant, since dedup will automatically select the first event, which in the default order will be the most recent.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...