Login Auditing

@daniel.bouck 

That is a good question and outside of my scope of work. This is likely performed by our Records folks. 

I do know that iterative failed authentication attempts into the system result in account locking in compliance with CJIS 5.5.3, but I don’t know what is provided, logged, or performed with regard to unsuccessful login attempts. 

Sorry to be less than helpful in that regard. I would appreciate you sharing any insights you identify on this process though!

Cheers, 

Jaycee

WITH rms_session_profiles AS (SELECT sp.*,
        up.agency_name,
        up.full_name, up.first_name, up.middle_name, up.last_name,
        up.badge_number,
        up.department_id
        FROM rms.session_profiles sp
        inner join analytics_rms.vw_user_profiles up
          ON sp.user_id = up.user_id
      )
SELECT TOP (500)
    rms_session_profiles.full_name  AS [rms_session_profiles.badge_number],
    rms_session_profiles.operating_system  AS [rms_session_profiles.operating_system],
    rms_session_profiles.browser  AS [rms_session_profiles.browser],
    rms_session_profiles.browser_version  AS [rms_session_profiles.browser_version],
    rms_session_profiles.ip  AS [rms_session_profiles.ip],
    rms_session_profiles.successful_authentication_count  AS [rms_session_profiles.successful_authentication_count],
    rms_session_profiles.failed_authentication_count  AS [rms_session_profiles.failed_authentication_count],
        (CONVERT(VARCHAR(10),rms_session_profiles.updated_date_utc ,120)) AS [rms_session_profiles.updated_date_utc_date],
        (CONVERT(VARCHAR(5), CAST(rms_session_profiles.updated_date_utc  AS DATETIME), 108)) AS [rms_session_profiles.updated_date_utc_time_of_day]
FROM rms_session_profiles
WHERE ((((( rms_session_profiles.updated_date_utc  )) >= ((DATEADD(hour,-23, CONVERT(datetime, CONVERT(VARCHAR(13), CAST(CONVERT(DATETIME2,SYSDATETIMEOFFSET() AT TIME ZONE 'Greenwich Standard Time') AS DATETIME), 120) + ':00:00', 120) ))) AND (( rms_session_profiles.updated_date_utc  )) < ((DATEADD(hour,24, DATEADD(hour,-23, CONVERT(datetime, CONVERT(VARCHAR(13), CAST(CONVERT(DATETIME2,SYSDATETIMEOFFSET() AT TIME ZONE 'Greenwich Standard Time') AS DATETIME), 120) + ':00:00', 120) ) )))))) AND (rms_session_profiles.failed_authentication_count ) >= 3 AND (rms_session_profiles.department_id ) = [DEPT-DB-ID]
GROUP BY
    rms_session_profiles.full_name ,
    rms_session_profiles.ip ,
    rms_session_profiles.operating_system ,
    rms_session_profiles.browser ,
    rms_session_profiles.browser_version ,
    (CONVERT(VARCHAR(10),rms_session_profiles.updated_date_utc ,120)),
    (CONVERT(VARCHAR(5), CAST(rms_session_profiles.updated_date_utc  AS DATETIME), 108)),
    rms_session_profiles.successful_authentication_count ,
    rms_session_profiles.failed_authentication_count
ORDER BY
    8 DESC

 @Kevin M. 

I’ve attached the SQL query from analytics, note I replaced our Datalake ID with [DEPT-DB-ID] so you’ll need to change that to your agencies Datalake ID. I appreciate your assistance as I can do basic SQL queries but no SQL master.

I am hoping Mark43 has an analytics solution for this so I don’t need to access the datalake every time for this audit log.

@Kevin M. I appreciate you looking into it.

Unfortunately I have to disagree that analytics is doing what I want it to do as its reporting over 300+ failed attempts on multiple users “in the past 24 hours.”. This looks more like its reporting the total number of failed attempts ever not within the past 24 hours.

I may need to reach out to support or David directly to see if we can come up with a solution here.

Never mind, it wasn’t available.

Sounds Good @daniel.bouck. I think my comment mentioned the update date, though my snip showed I was using created date. Apologies for any confusion.

@Kevin M. we too do not have the rms_session_profiles table exposed or replicated in the data lake. We previously attempted to leverage that table to audit inactive users, but to no avail. I alos totally forgot that we could view the SQL script in analytics. Thanks for that reminder; will help for troubleshooting things in the future.

I am impressed by the community support here by @Jaycee.Elliott and @Kevin M. Thank you for sharing your knowledge! I received an email notice earlier today when @daniel.bouck posted this, but I have limited personal knowledge on the User Sessions explore. During the day I logged in to work with the explore, look at the underlying views, and look for any documentation to learn more. To supplement, I also reached out to engineering to get additional information and I am just able to catch up and reply on this thread now. Here is what I have learned:

I pretty quickly noticed the same data mapping issue @Jaycee.Elliott mentioned (User Badge Number returning User Full Name).

The included dimensions and measures are not intuitive. Everyone would benefit from better documentation in the explore explaining the purpose and/or source of each of them.

The major factor here is that the underlying data/view are not returning the results you need for your purpose. I am told the [Failed Authentication Count] is an aggregate that is not at the grain you would need for your purpose. Your use case is understandable and reasonable, however, this explore falls short of fulfilling that type of query.

I am going to go to the engineering team to see what we can do to improve this explore. As I understand from the discussion, the following would be valuable:

1. Fix issues where dimensions are returning incorrect data
2. Provide a failed authentication count at a grain where the user is able to solve the issue described by @daniel.bouck 
3. Add documentation to the dimensions/measures to assist the user in understanding the purpose/source/limitations
4. Follow up in this thread when I better understand what we can provide to improve this

I welcome and encourage feedback on additional items that would be valuable here. 

Dave