Go to Manage Reports, then create a new report, when the thing pops up add a Custom Table to it and then under selection method chose the Advanced Database Query (SQL,SWQL).
Change the query type to SQL and add the following SQL code:
WHERE [LastLogin] >= GETUTCDATE()
--List Orion users online now
--Checks for last login and last logout, if the login is more recent than the logout then they are still online
SELECT x.* FROM (
SELECT MAX(A1.TimeLoggedUTC) AS [Login]
,A1.AccountID AS [AccountID]
,A1.AuditEventMessage AS [AuditEventMessage]
FROM AuditingEvents A1
INNER JOIN AuditingEvents A2 ON A1.AccountID = A2.AccountID
WHERE A1.ActionTypeID = 1 AND A2.ActionTypeID = 2 --1 = Logged in or 2 =Logged out
GROUP BY A1.AccountID, A1.AuditEventMessage
HAVING (MAX(A2.TimeLoggedUTC ) < MAX(A1.TimeLoggedUTC)) ) x
ORDER BY x.[Login] DESC
This appeared to work , but it's detailing users from yesterday.... Grrr
Hit test results and you should see the raw SQL results.
Give it a meaningful name and click Add To Layout.
The table wizard thing appears next, click Add Columns and AccountID and Last Login and click Add Column.
Clicking Preview Resource will show the first few lines in the table as it would appear in the report.
Adjust the column names by expanding the Advanced option under each column name.
Give the table a name and Submit.
Make sure you populate the Report Title (ands it must be unique) and follow the wizard through, under the Properties tab you can change which folder it appears in, which will help you find it easily later.
Hope it helps
Hi Yuquaholic, I tried this and it returns only admin accounts for me - can you explain the '[LastLogin] >= GETUTCDATE()' logic. Isn't getutcdate just the current date/time? Would it be possible to use SWQL to take the lastlogon for each user from the orion.accounts table then compare against the latest logoff event in the orion.auditingEvents table to give a true count? I think it was you who introduced me to SWQL at the London SWUG but I must admit I've still not got around to reading up on it much since (sorry)... I already have a report to show me last logon by username but I'd love to get a single numeric on my dashboards to keep an eye on current logon count.
Yes, I can see that it doesn't reflect the users online.
Your are spot on with the AuditingEvents table, which contains far better data.
And from this table, the AuditEventMessage also includes where they logged from as well.
Leave it with me, I will get some time this afternoon to have a look at it.
After a bit more time playing with this, I have realised that the table doesn't seem to provide the details of all user log outs, unless they hit the logout button.
For example here are my activities for the past 2 days:
ActionType TimeLoggedUtc UserName Log In 11/05/2018 08:52 me Log In 11/05/2018 08:32 me Log In 11/05/2018 08:32 me Log Out 10/05/2018 17:33 me Log In 10/05/2018 17:26 me Log In 10/05/2018 13:38 me Log In 10/05/2018 11:37 me Log In 10/05/2018 11:10 me Log In 10/05/2018 11:10 me Log In 10/05/2018 09:25 me Log In 10/05/2018 08:45 me Log In 10/05/2018 08:36 me Log In 10/05/2018 08:36 me Log Out 09/05/2018 17:27 me Log In 09/05/2018 15:36 me
As you can see, I have logged in my Orion boxes multiple times, but there are quite obviously only two logout events.
There are only two user ActionTypes, 1 = Log in and 2 = Log Out, with nothing obvious for session timeouts. (For ActionTypeID translations, see dbo.AuditingActionTypes table)
From the example above, both of my logout entries were from 'end of day, close my browser before I shut down for the evening' events, where I would always click the Orion log out button by default.
Yet, as you can see, I logged in multiple times a day, due to my Orion session timing out.
So it appears that the session timeouts are not recorded on this table, and without these, it's rather difficult to say who is online or not (from this table anyway).
Add to this, that if a user is running a report, they also appear in this table, again with no logout event either.
Sorry, but back to the drawing board....
Aww, that is a shame. Thanks a lot for spending your time looking into this, much appreciated. I'll be sure to upvote your feature request.
If you don't have SAM module: Logs in inetpub folder capture web requests/ dates/ times. You could infer that accounts who have made recent requests are still logged in. Accounts table in database will tell you last login. You could assume very recent logins haven't yet logged out if that assumption is permissible for your purposes.
I found actiontypeID '1' under 'Orion.AuditingEvents' signifies a login and actiontypeID '2' signifies a logout. My SWQL is nothing more than absolute basic but I imagine it would be possible to compare 'account id' along with 'actiontypeid' to count login's and logout's of each individual then return anyone with 1 less logout than login... Can any SWQL bods jump in to join up these dots?
If you have SAM apply the AppInsight for IIS if not already assigned and review the connection table:
There is also an old IIS template which has this in as well.
If you do not, use PerfMon on the Orion server - http://woshub.com/how-to-monitor-active-sessions-on-iis/
Installation | Consultancy | Training | Licenses