AnsweredAssumed Answered

SLM User Audit Custom Report

Question asked by adam-alphagen on Jul 5, 2019
Latest reply on Sep 19, 2019 by Connor_Clarke

Hi All


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 @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 @RowTargetLink NVARCHAR(MAX) = 'User.aspx?id='

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!