05-30-2024 09:12 AM
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?
05-30-2024 05:43 PM
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
05-30-2024 09:29 AM
Hi
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.
05-30-2024 05:43 PM
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
05-31-2024 05:59 AM
Hello
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!
06-02-2024 01:56 AM
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
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.