Obviously you can export the SLM user login audit information via the SLM SMACC tool, but I wanted a way to do this simply via SLM reporting so that we could provide this information automatically to the CISO to provide evidence of our data compliance. My DB wizard created a simple SQL script, which when executed on the SNOW DB server creates a new custom report in SLM which can then be customised, saved and scheduled as required. Much easier than having to do this manually in the SMACC. You can just set permissions in the SLM SMACC security options to set who can see this report.
Here's the SQL script you need to execute in SSMS.
DECLARE @NAME NVARCHAR(MAX) = 'User Logon Report'
DECLARE @uid uniqueidentifier = (SELECT NEWID())
DECLARE @Description NVARCHAR(MAX) = 'This report shows the logon count for users accessing Snow'
DECLARE @ColumnList NVARCHAR(MAX) = ' UserID,CID, Name,User Name,First Name,Last Name,Email,PasswordExpirationDate,Last Logon,Logon Count'
DECLARE @ColVis NVARCHAR(MAX) = '1,1,1,1,1,1,1,1,1,1'
DECLARE @KeyFieldName NVARCHAR(MAX) = 'UserID'
DECLARE @RowTargetLink NVARCHAR(MAX) = 'User.aspx?id='
DELETE FROM tblReport WHERE Name = @NAME
DECLARE @SQL NVARCHAR(MAX) = 'select DISTINCT su.[UserID], su.[CID] as "Company ID", CID.[Name] as "Company Name", su.[UserName] as "User Name", su.[FirstName] as "First Name", su.[LastName] as "Last Name",su.[Email] as "Email", su.[PasswordExpirationDate] as "Password Expiraion Date", su.[LogonDate] as "Last Logged On", su.[LogonCounter] as "Logon Count"
from tblSystemUser su
Join tblCID CID on CID.CID = su.CID
order by su.CID asc'
INSERT INTO tblReport (ReportID, StockReport, IsCustomReport, ReportType, ViewName, Name, Description, SQLQuery, ColumnList, ColumnVisibility, KeyFieldName, RowTargetLink, UsesCustomFields, CustomFieldCategoryID)
VALUES (@uid, 0, 1, 3 , 'QUERYBUILDER', @NAME, @Description, @SQL, @ColumnList, @ColVis, @KeyFieldName, @RowTargetLink, 1, 1)
INSERT INTO tblReportSecurity
SELECT ReportID, 1 FROM tblReport WHERE ReportID = @uid
Hope you find this helpful!