adam-alphagen

SLM User Login Audit Custom Report

Discussion created by adam-alphagen on Jul 5, 2019
Latest reply on Aug 19, 2019 by kishan.pant@chemtrolsinfotech.com

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 @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!

 

Adam

Outcomes