SQL to detect mobile login

[Tia_Brott-Silke]

During an upgrade, we need to know people who login using mobile. Any way to detect this? Reviewing securitylog, there doesn't seem to be a log for mobile. I only see Unity and core login logs.

Any creative ways to detect mobile logins?

[AdamShaneHyland]

Hi @Tia Brott-Silkett ,

As you notice, mobile devices are not logged to the hsi.securitylog in any unique way.  This feature was originally requested with an enhancement SCR: #251742 but has not be implemented.  If you would like this feature, please submit a Feedback request.

Best wishes.

[George_Sialmas · Accepted solution]

@Tia Brott-Silkett I recently used the query below as my customer wanted to know what client their users are using. Hopefully it helps with your query. Feel free to refactor it as you see fit, because you will notice that my query returns additional clients not just mobile. Note: sl.subactionnum = 9 refers to Login via Core, and I've updated it to refer to Mobile Device as when I tested logging into OnBase Mobile via Apple/Android device the subactionnum was recorded as '9' in the securitylog table.

DECLARE @startdate DATETIME = '2024-05-08'; --Replace with the desired start date.DECLARE @enddate DATETIME = '2024-06-01'; --Replace with the desired end date.SELECT DISTINCT    sl.usernum,    RTRIM(ua.username) AS 'User Name',    CASE         WHEN CHARINDEX('(', sl.messagetext) > 0 AND CHARINDEX(')', sl.messagetext) > 0         THEN SUBSTRING(sl.messagetext, CHARINDEX('(', sl.messagetext) + 1, CHARINDEX(')', sl.messagetext) - CHARINDEX('(', sl.messagetext) - 1)     END AS 'OnBase Version',    CASE        WHEN sl.subactionnum = 12 THEN 'HTML Web Client'        WHEN sl.subactionnum = 15 THEN 'Unity Client'        WHEN sl.subactionnum = 1 THEN 'Thick Client'        WHEN sl.subactionnum = 9 THEN 'Mobile Device'    END AS 'OnBase Client',    MAX(sl.logdate) AS LastLoginFROM     hsi.securitylog slINNER JOIN     hsi.useraccount ua ON sl.usernum = ua.usernumINNER JOIN     hsi.registeredusers ru ON sl.registernum = ru.registernumWHERE     actionnum IN (1)    AND sl.logdate BETWEEN @startdate AND @enddate    AND sl.usernum NOT IN (1, 2, 102, 813, 1074)    AND (        CASE             WHEN CHARINDEX('(', sl.messagetext) > 0 AND CHARINDEX(')', sl.messagetext) > 0             THEN SUBSTRING(sl.messagetext, CHARINDEX('(', sl.messagetext) + 1, CHARINDEX(')', sl.messagetext) - CHARINDEX('(', sl.messagetext) - 1)         END IS NOT NULL    )    AND (        CASE            WHEN sl.subactionnum = 12 THEN 'HTML Web Client'            WHEN sl.subactionnum = 15 THEN 'Unity Client'            WHEN sl.subactionnum = 1 THEN 'Thick Client'            WHEN sl.subactionnum = 9 THEN 'Mobile Device'        END IS NOT NULL    )GROUP BY    sl.usernum,    ua.username,     sl.subactionnum,    CASE         WHEN CHARINDEX('(', sl.messagetext) > 0 AND CHARINDEX(')', sl.messagetext) > 0         THEN SUBSTRING(sl.messagetext, CHARINDEX('(', sl.messagetext) + 1, CHARINDEX(')', sl.messagetext) - CHARINDEX('(', sl.messagetext) - 1)     ENDORDER BY    LastLogin DESC

Good luck,

George

[Ryan_Wakefield]

Hello @George Sialmas ,

I'm not sure your query will work since with the hsi.securitylog table you have to combine the actionnum and subactionnum columns in order to determine what the true action was.

So I am curious how you came to the conclusion you did to see subactionnum equal to 9 to be "Mobile Device". Can you elaborate further for me?

Thanks!

[George_Sialmas]

@Tia Brott-Silkett @Ryan Wakefield 

I'm retracting my earlier post regarding the detection of mobile logins using an SQL query, I must clarify that it is currently not feasible to distinguish OnBase Mobile logins from Core logins using SQL alone. My previous suggestion implied that such differentiation was possible with the provided query based on my testing when logging on to the OnBase mobile iOS and Android app. However, due to the lack of specific identifiers or distinct login attributes between the mobile and core systems in the existing database schema, the SQL query cannot accurately isolate mobile logins. I apologize for any confusion this may have caused and appreciate your understanding. Unfortunately, for whatever reason, I'm not permitted to delete my previous post.

Respectfully,

George