Documentation

Table of Contents


Integration Guides and Other Resources



This document contains specific information for SecureAuth IdP version 7.x. If using a different version of SecureAuth IdP, refer to the 8.x, 9.0.x, or 9.1 space accordingly.

Last modifed on Wednesday, 19 April 2017

Installation Guide
Full Package Download Location

1. Download the Full Package from the link below

This is a ~700MB download that includes SQL Express 2012 with Tools

https://downloads.secureauth.com/resources/Reporting/SAReportingDB.zip

2. Extract the contents of the SAReportingDB.zip package to a folder on the local D: drive

3. Open a Command Prompt with Administrator privileges (Run as Administrator)

4. Change the current directory to the new folder created in Step 2

5. Run the InstallSAReportingDB.bat script

6. Select Next when required, but do not change any settings

Scripts-only Download Location

1. Download the Scripts-only package from https://downloads.secureauth.com/resources/Reporting/SAReportingDB_ScriptsOnly.zip

2. Download Microsoft SQL Server 2012 Express with tools:

a. Browse the product download page: http://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx

b. Based on the Windows OS and language, select Express with Tools, and then click Download

c. Place the install file in the folder created when extracting the SAReportingDB_ScriptsOnly.zip file

3. Extract the contents of the SAReportingDB_ScriptsOnly.zip package to a folder on the local D: drive

4. Open a Command Prompt with Administrator privileges (Run as Administrator)

5. Change the current directory to the new folder created in Step 3

6. Run the InstallSAReportingDB.bat script

7. Select Next when required, but do not change any settings

SecureAuth IdP Configuration Guide

Install SQL Server 2012 Express and create a Logging database / user before or after update

However, after running the update process and updatewebconfig function, specify a connection string in the Web Admin's Logs tab and click Save to all realms

Do not use the Save button on the left while checking all realm checkboxes since the Log Instance ID must be different between each realm

 

Set up a Connection String

  1. In the Web Admin, navigate to the Logs tab
  2. Ensure that the Log Instance ID is unique and stands for that realm (this value is used as the value for the Realm column in each audit log event record)
  3. In the Log Database section, specify the credentials

Reference this link for each connection string property: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

    • Set Integrated Security to False to use SQL authentication
    • Set Persist Security Info to False
    • Set the Data Source to the sever machine name / SQL server instance name
    • Click Generate Connection String (no passwords are displayed in the string)
    • Click Test connection
    • Click Save to all realms button to apply that changes to web.config files of all realms

Troubleshooting/Common Issues
Running out of space on the physical drive

The log database continues to grow unless a backup/truncate procedure is put in place

The following section describes the commands that can be used to truncate the log table as well as shrink the actual database (mdf) and log (ldf) files

Truncating a table removes ALL rows and is not reversible; and since there is no rollback, it takes less time to execute and has no overhead

Deleting enables specifying a WHERE parameter, and execution time is directly proportional to the size of the table

Truncating the Log table:

Truncating the log table removes all rows from the table

To do this, run the following command in SQL Management Studio while connected as a db user that has either sysadmin or dbowner permissions

Truncate Log Table

Before truncating the table, all foreign keys that point to the table must be dropped

After truncation, those foreign keys must be recreated

USE [Logging]
TRUNCATE TABLE [CategoryLog]
TRUNCATE TABLE [Log]
GO
Drop FKs
USE [Logging]
GO
ALTER TABLE [dbo].[CategoryLog] DROP CONSTRAINT [FK_CategoryLog_Category]
GO
ALTER TABLE [dbo].[CategoryLog] DROP CONSTRAINT [FK_CategoryLog_Log]
GO
Re-create FKs
USE [Logging]
GO

ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Log] FOREIGN KEY([LogID])
REFERENCES [dbo].[Log] ([LogID])
GO

ALTER TABLE [dbo].[CategoryLog] CHECK CONSTRAINT [FK_CategoryLog_Log]
GO

ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO

ALTER TABLE [dbo].[CategoryLog] CHECK CONSTRAINT [FK_CategoryLog_Category]
GO

Deleting records from the Log table that are older than X amount of days:

The following command can be run to delete records older than 90 days (replace 90 with the desired number of days):
 

Delete Records
USE [Logging]
DELETE FROM [CategoryLog] WHERE LogID IN (SELECT [LogID] FROM [Log] WHERE DATEADD(day, 90, [TimeStamp]) < getdate())
GO
DELETE FROM [Log] WHERE DATEADD(day, 90, [TimeStamp]) < getdate()
GO

Shrinking the database files:

The following command shrinks the database to the last allocated extent:
 

Shrink Database Files
USE [Logging]
DBCC SHRINKDATABASE ([Logging], TRUNCATEONLY);
GO
Manually Creating the Database

To manually create the database from SQL Management Studio, run the below command while logged in as either a sysadmin or dbowner
 

Create Database

Change the file path to reflect the desired location of the database files

USE master;
GO
CREATE DATABASE Logging
ON 
( NAME = Logging_dat,
    FILENAME = 'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Logging.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Logging_log,
    FILENAME = 'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Logging.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO

Creating User and Roles
 

Create User

Replace [PasswordPlaceHolder] with the preferred password

USE master
CREATE LOGIN [SecureAuthSQLUser] WITH PASSWORD=N'[PasswordPlaceHolder]', 
DEFAULT_DATABASE=[Logging], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Use Logging
CREATE USER [SecureAuthSQLUser] FOR LOGIN [SecureAuthSQLUser] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember 'db_datareader', 'SecureAuthSQLUser'
GO
EXEC sp_addrolemember 'db_datawriter', 'SecureAuthSQLUser'
GO

Creating Reporting Tables and Stored Procedures
 

Create Tables and Stored Procedures
USE [Logging]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/****** Object:  Table [dbo].[Category]     ******/
CREATE TABLE [dbo].[Category](
	[CategoryID] [int] IDENTITY(1,1) NOT NULL,
	[CategoryName] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Log]     ******/
CREATE TABLE [dbo].[Log](
	[LogID] [bigint] IDENTITY(1,1) NOT NULL,
	[EventID] [int] NULL,
	[Priority] [int] NOT NULL,
	[Severity] [nvarchar](32) NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[Company] [nvarchar](1024) NOT NULL,
	[MachineName] [nvarchar](64) NOT NULL,
	[Appliance] [nvarchar](1024) NOT NULL,
	[Realm] [nvarchar](1024) NOT NULL,
	[ProcessID] [nvarchar](256) NOT NULL,
	[ProcessName] [nvarchar](512) NOT NULL,	
	[Win32ThreadId] [nvarchar](128) NULL,
	[UserID] [nvarchar](2048) NOT NULL,
	[UserAgent] [nvarchar](MAX) NOT NULL,
	[UserHostAddress] [nvarchar](64) NOT NULL,
	[BrowserSession] [nvarchar](128) NOT NULL,
	[Message] [nvarchar](MAX) NULL,
	[FormattedMessage] [nvarchar](MAX) NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CategoryLog]     ******/
CREATE TABLE [dbo].[CategoryLog](
	[CategoryLogID] [int] IDENTITY(1,1) NOT NULL,
	[CategoryID] [int] NOT NULL,
	[LogID] [bigint] NOT NULL,
 CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED 
(
	[CategoryLogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_CategoryLog_Category]     ******/
ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[CategoryLog] CHECK CONSTRAINT [FK_CategoryLog_Category]
GO
/****** Object:  ForeignKey [FK_CategoryLog_Log]     ******/
ALTER TABLE [dbo].[CategoryLog]  WITH CHECK ADD  CONSTRAINT [FK_CategoryLog_Log] FOREIGN KEY([LogID])
REFERENCES [dbo].[Log] ([LogID])
GO
ALTER TABLE [dbo].[CategoryLog] CHECK CONSTRAINT [FK_CategoryLog_Log]
GO
/****** Object:  Index [IDX_NC_CategoryLog_CategoryID]    ******/
CREATE NONCLUSTERED INDEX [IDX_NC_CategoryLog_CategoryID] ON [dbo].[CategoryLog] 
(
	[CategoryID] ASC
)
INCLUDE ( [CategoryLogID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Index [IDX_NC_CategoryLog_LogID]    Script Date: 07/09/2014 15:53:05 ******/
CREATE NONCLUSTERED INDEX [IDX_NC_CategoryLog_LogID] ON [dbo].[CategoryLog] 
(
	[LogID] ASC
)
INCLUDE ( [CategoryLogID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Settings]     ******/
CREATE TABLE [dbo].[Settings](
	[SettingID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[PropertyName] [nvarchar](128) NOT NULL,
	[PropertyValue] [ntext] NOT NULL,
	[TimeStamp] [timestamp] NULL,
 CONSTRAINT [Settings_PK] PRIMARY KEY CLUSTERED 
(
	[SettingID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Settings_Property] ON [dbo].[Settings] 
(
	[PropertyName] ASC
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Settings] ADD  CONSTRAINT [DF_Settings_SettingID]  DEFAULT (newid()) FOR [SettingID]
GO

----------------------------------------------------------------------
--
--	Create StoredProcedures
--
----------------------------------------------------------------------
/****** Object:  StoredProcedure [dbo].[WriteLog]     ******/
CREATE PROCEDURE [dbo].[WriteLog]
(
	@EventID int, 
	@Priority int, 
	@Severity nvarchar(32), 
	@Title nvarchar(256), 
	@Timestamp datetime,
	@Company nvarchar(1024) = '',
	@MachineName nvarchar(64), 
	@Appliance nvarchar(1024) = '',
	@Realm nvarchar(1024) = '',
	@ProcessID nvarchar(256),
	@ProcessName nvarchar(512),
	@Win32ThreadId nvarchar(128),
	@UserID nvarchar(2048) = '',
	@UserAgent nvarchar(MAX) = '',
	@UserHostAddress nvarchar(64) = '',
	@BrowserSession nvarchar(128) = '',
	@Message nvarchar(MAX),
	@FormattedMessage nvarchar(MAX),
	@LogId bigint OUTPUT
)
AS 
BEGIN
	INSERT INTO [Log] (
		EventID,
		Priority,
		Severity,
		Title,
		[Timestamp],
		Company,
		MachineName,
		Appliance,
		Realm,
		ProcessID,
		ProcessName,
		Win32ThreadId,
		UserID,
		UserAgent,
		UserHostAddress,
		BrowserSession,
		Message,
		FormattedMessage
	)
	VALUES (
		@EventID, 
		@Priority, 
		@Severity, 
		@Title, 
		@Timestamp,
		@Company,
		@MachineName,
		@Appliance,
		@Realm,
		@ProcessID,
		@ProcessName,
		@Win32ThreadId,
		@UserID,
		@UserAgent,
		@UserHostAddress,
		@BrowserSession,
		@Message,
		@FormattedMessage)
	SET @LogID = @@IDENTITY
	RETURN @LogID
END
GO
/****** Object:  StoredProcedure [dbo].[InsertCategoryLog]     ******/
CREATE PROCEDURE [dbo].[InsertCategoryLog]
	@CategoryID INT,
	@LogID bigint
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @CatLogID INT
	SELECT @CatLogID FROM CategoryLog WHERE CategoryID=@CategoryID and LogID = @LogID
	IF @CatLogID IS NULL
	BEGIN
		INSERT INTO CategoryLog (CategoryID, LogID) VALUES(@CategoryID, @LogID)
		RETURN @@IDENTITY
	END
	ELSE RETURN @CatLogID
END
GO
/****** Object:  StoredProcedure [dbo].[ClearLogs]     ******/
CREATE PROCEDURE [dbo].[ClearLogs]
AS
BEGIN
	SET NOCOUNT ON;
	DELETE FROM CategoryLog
	DELETE FROM [Log]
    DELETE FROM Category
END
GO
/****** Object:  StoredProcedure [dbo].[AddCategory]     ******/
CREATE PROCEDURE [dbo].[AddCategory]
	-- Add the parameters for the function here
	@CategoryName nvarchar(128),
	@LogID bigint
AS
BEGIN
	SET NOCOUNT ON;
    DECLARE @CatID INT
	SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
	IF @CatID IS NULL
	BEGIN
		INSERT INTO Category (CategoryName) VALUES(@CategoryName)
		SELECT @CatID = @@IDENTITY
	END
	EXEC InsertCategoryLog @CatID, @LogID 
	RETURN @CatID
END
GO
/****** Object:  StoredProcedure [dbo].[GetLog]   ******/
CREATE PROCEDURE [dbo].[GetLog]
	-- Add the parameters for the stored procedure here
	(
	@UserId nvarchar(2048),
	@StartDate datetime,
	@EndDate datetime,
	@EventID int,
	@GroupByUser bit,
	@GroupByCompany bit,
	@GroupByRealm bit,
	@GroupByEvent bit,
	@GroupByAppliance bit,
	@GroupByMachine bit,
	@BadUserID bit,
	@BadUserPwd bit,
	@BadOTP bit,
	@SearchMode int
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		
	DECLARE @NotEmpty bit = 0
	DECLARE @StartVarChar varchar(25) = convert(varchar, DATEADD(DAY, 0, @StartDate), 120)
	DECLARE @EndVarChar varchar(25) = convert(varchar, DATEADD(DAY, 1, @EndDate), 120)
	DECLARE @sql nvarchar(MAX) = 'SELECT '
	DECLARE @parameters nvarchar(MAX) =    '@UserId nvarchar(2048), 
											@StartVarChar varchar(25), 
											@EndVarChar varchar(25),	
											@EventID int,
											@GroupByUser bit,
											@GroupByCompany bit,
											@GroupByRealm bit,
											@GroupByEvent bit,
											@GroupByAppliance bit,
											@GroupByMachine bit,
											@BadUserID bit,
											@BadUserPwd bit,
											@BadOTP bit'
	IF @GroupByUser = 1
	BEGIN
		SET @sql += '[UserID]'
		SET @NotEmpty = 1
	END
	IF @GroupByCompany = 1
	BEGIN
		IF @NotEmpty = 1
			SET @sql += ', '
		SET @sql += '[Company]'
		SET @NotEmpty = 1
	END
	IF @GroupByRealm = 1
	BEGIN
		IF @NotEmpty = 1
			SET @sql += ', '
		SET @sql += '[Realm]'
		SET @NotEmpty = 1
	END
	IF @GroupByEvent = 1
	BEGIN
		IF @NotEmpty = 1
			SET @sql += ', '
		SET @sql += '[EventID]'
		SET @NotEmpty = 1
	END
	IF @GroupByAppliance = 1
	BEGIN
		IF @NotEmpty = 1
			SET @sql += ', '
		SET @sql += '[Appliance]'
		SET @NotEmpty = 1
	END
	IF @GroupByMachine = 1
	BEGIN
		IF @NotEmpty = 1
			SET @sql += ', '
		SET @sql += '[MachineName]'
		SET @NotEmpty = 1
	END
	IF (@NotEmpty = 1)
	BEGIN
		SET @sql += ', COUNT(*) AS Number FROM [Log] WHERE [UserID] Like ''%'' + @UserId + ''%'' AND [TimeStamp] BETWEEN @StartVarChar AND @EndVarChar AND [EventID] ' 
		IF @EventID = 0
			SET @sql += 'NOT' 
		SET @sql += ' IN (@EventID'
		IF @BadUserID = 1
			SET @sql += ',21070,21100' 
		IF @BadUserPwd = 1
			SET @sql += ',21090,21100,22010,22110,22210,22310,22910,22020'
		IF @BadOTP = 1
			SET @sql += ',24200,24210,24220'
		SET @sql += ') GROUP BY '
		SET @NotEmpty = 0
		IF @GroupByUser = 1
		BEGIN
			SET @sql += '[UserID]'
			SET @NotEmpty = 1
		END
		IF @GroupByCompany = 1
		BEGIN
			IF @NotEmpty = 1
				SET @sql += ', '
			SET @sql += '[Company]'
			SET @NotEmpty = 1
		END
		IF @GroupByRealm = 1
		BEGIN
			IF @NotEmpty = 1
				SET @sql += ', '
			SET @sql += '[Realm]'
			SET @NotEmpty = 1
		END
		IF @GroupByEvent = 1
		BEGIN
			IF @NotEmpty = 1
				SET @sql += ', '
			SET @sql += '[EventID]'
			SET @NotEmpty = 1
		END
		IF @GroupByAppliance = 1
		BEGIN
			IF @NotEmpty = 1
				SET @sql += ', '
			SET @sql += '[Appliance]'
			SET @NotEmpty = 1
		END
		IF @GroupByMachine = 1
		BEGIN
			IF @NotEmpty = 1
				SET @sql += ', '
			SET @sql += '[MachineName]'
			SET @NotEmpty = 1
		END
	END
	ELSE 
	BEGIN
		IF @SearchMode = 1
			SET @sql += '[EventID], convert(varchar,DATEADD(minute,DATEDIFF(minute,getutcdate(),getdate()),[Timestamp]),121) AS [Time], [Company], [MachineName], [Appliance], [Realm], [UserID], [Message] FROM [Log] WHERE [UserID] Like ''%'' + @UserId + ''%'' AND [TimeStamp] >= @StartVarChar AND  [TimeStamp] <= @EndVarChar AND [EventID] ' 
		ELSE
			SET @sql += '[LogID], [EventID], [Priority], [Severity], [Title], convert(varchar,DATEADD(minute,DATEDIFF(minute,getutcdate(),getdate()),[Timestamp]),109) AS [Time], [Company], [MachineName], [Appliance], [Realm], [ProcessID], [ProcessName], [Win32ThreadId], [UserID], [UserAgent], [UserHostAddress], [BrowserSession], [Message], [FormattedMessage] FROM [Log] WHERE [UserID] Like ''%'' + @UserId + ''%'' AND [TimeStamp] >= @StartVarChar AND  [TimeStamp] <= @EndVarChar AND [EventID] ' 
		IF @EventID = 0
			SET @sql += 'NOT' 
		SET @sql += ' IN (@EventID' 
		IF @BadUserID = 1
			SET @sql += ',21070,21100' 
		IF @BadUserPwd = 1
			SET @sql += ',21090,21100,22010,22110,22210,22310,22910,22020'
		IF @BadOTP = 1
			SET @sql += ',24200,24210,24220'
		SET @sql += ') ORDER BY [TimeStamp] DESC'
	END
	EXEC sp_executesql	@sql, 
						@parameters, 
						@UserId = @UserId,
						@StartVarChar = @StartVarChar,
						@EndVarChar = @EndVarChar,
						@EventID = @EventID,
						@GroupByUser = @GroupByUser,
						@GroupByCompany = @GroupByCompany,
						@GroupByRealm = @GroupByRealm,
						@GroupByEvent = @GroupByEvent,
						@GroupByAppliance = @GroupByAppliance,
						@GroupByMachine = @GroupByMachine,
						@BadUserID = @BadUserID,
						@BadUserPwd = @BadUserPwd,
						@BadOTP = @BadOTP
END
GO
----------------------------------------------------------------------
--
--	Set database schema initial version to 1000
--
----------------------------------------------------------------------
INSERT INTO dbo.Settings(PropertyName, PropertyValue)
  VALUES(N'DatabaseVersion', N'1000')
GO

Creating Chart Stored Procedures
 

Drop / Create Charts
USE [Logging]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'GetChart1', 'P' ) IS NOT NULL 
    DROP PROCEDURE GetChart1;
GO
CREATE PROCEDURE [dbo].[GetChart1]
	(
	@StartDate datetime,
	@EndDate datetime,
	@Realms varchar(250)
	)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @sql nvarchar(MAX) = '(SELECT @Result = COUNT(*) FROM Log WHERE [Realm] IN (' + @Realms + ') AND [EventID] = 20000 AND [TimeStamp] BETWEEN @StartDate AND @EndDate)'
	DECLARE @parameters nvarchar(MAX) =    '@StartDate datetime, 
											@EndDate datetime,
											@Result int out'
	DECLARE @SuccessCount int = 0
	EXEC sp_executesql @sql, @parameters, @StartDate, @EndDate, @SuccessCount out
	DECLARE @SuccessCountVarChar VARCHAR(25) = CONVERT(VARCHAR(25), @SuccessCount)
	SET @sql = '(SELECT @Result = COUNT(*) FROM Log WHERE [Realm] IN (' + @Realms + ') AND [EventID] IN (21070,21100,21090,21100,22010,22110,22210,22310,22910,22020,24200,24210,24220) AND [TimeStamp] BETWEEN @StartDate AND @EndDate)'
	DECLARE @FailedCount int = 0
	EXEC sp_executesql @sql, @parameters, @StartDate, @EndDate, @FailedCount out
	DECLARE @FailedCountVarChar VARCHAR(25) = CONVERT(VARCHAR(25), @FailedCount)
	
	CREATE TABLE #TempTable(
		[Status] varchar(25),
		[Total] int)
	INSERT INTO #TempTable VALUES ('Success (' + @SuccessCountVarChar + ')', @SuccessCount)
	INSERT INTO #TempTable VALUES ('Failed (' + @FailedCountVarChar + ')', @FailedCount)
	SELECT * FROM #TempTable
END
GO
IF OBJECT_ID ( 'GetChart2', 'P' ) IS NOT NULL 
    DROP PROCEDURE GetChart2;
GO
CREATE PROCEDURE [dbo].[GetChart2]
	(
	@StartDate datetime,
	@EndDate datetime,
	@PhoneMethod varchar(50),
	@EmailMethod varchar(50),
	@SMSMethod varchar(50),
	@Realms varchar(250)
	)
AS
BEGIN
	SET NOCOUNT ON;
	CREATE TABLE #TempTable2(
		[EmailStatus] varchar(25),
		[EmailTotal] int,
		[EmailTimeStamp] varchar(10),
		[PhoneStatus] varchar(25),
		[PhoneTotal] int,
		[PhoneTimeStamp] varchar(10),
		[SMSStatus] varchar(25),
		[SMSTotal] int,
		[SMSTimeStamp] varchar(10))
	DECLARE @sql nvarchar(MAX) = 'SELECT ''Email'', COUNT(*), CONVERT(VARCHAR(10), [TimeStamp], 101), NULL, NULL, NULL, NULL, NULL, NULL AS [TimeStamp] FROM [LOG] WHERE [Realm] IN (' + @Realms + ') AND [Message] like ''%'' + @EmailMethod + ''%'' AND [TimeStamp] BETWEEN @StartDate AND @EndDate GROUP BY CONVERT(VARCHAR(10), [TimeStamp], 101)'
	DECLARE @parameters nvarchar(MAX) = '@StartDate datetime, @EndDate datetime, @EmailMethod varchar(50)'
	INSERT INTO #TempTable2 EXEC sp_executesql @sql, @parameters, @StartDate, @EndDate, @EmailMethod
	SET @sql = 'SELECT NULL, NULL, NULL, ''Phone'', COUNT(*), CONVERT(VARCHAR(10), [TimeStamp], 101) AS [TimeStamp], NULL, NULL, NULL FROM [LOG] WHERE [Realm] IN (' + @Realms + ') AND [Message] like ''%'' + @PhoneMethod + ''%'' AND [TimeStamp] BETWEEN @StartDate AND @EndDate GROUP BY CONVERT(VARCHAR(10), [TimeStamp], 101)'
	SET @parameters = '@StartDate datetime, @EndDate datetime, @PhoneMethod varchar(50)'
	INSERT INTO #TempTable2 EXEC sp_executesql @sql, @parameters, @StartDate, @EndDate, @PhoneMethod
	SET @sql = 'SELECT NULL, NULL, NULL, NULL, NULL, NULL, ''SMS'', COUNT(*), CONVERT(VARCHAR(10), [TimeStamp], 101) AS [TimeStamp] FROM [LOG] WHERE [Realm] IN (' + @Realms + ') AND [Message] like ''%'' + @SMSMethod   + ''%'' AND [TimeStamp] BETWEEN @StartDate AND @EndDate GROUP BY CONVERT(VARCHAR(10), [TimeStamp], 101)'
	SET @parameters = '@StartDate datetime, @EndDate datetime, @SMSMethod varchar(50)'
	INSERT INTO #TempTable2 EXEC sp_executesql @sql, @parameters, @StartDate, @EndDate, @SMSMethod
	
	SELECT * FROM #TempTable2
END
GO
IF OBJECT_ID ( 'GetChart3', 'P' ) IS NOT NULL 
    DROP PROCEDURE GetChart3;
GO
CREATE PROCEDURE [dbo].[GetChart3]
	(
	@StartDate datetime,
	@EndDate datetime,
	@Realms varchar(250)
	)
AS
BEGIN
	SET NOCOUNT ON;
	
	CREATE TABLE #TempTable3(
		[Status] varchar(25),
		[Total] int)
	DECLARE @sql nvarchar(MAX) = 'SELECT DATEPART(HOUR, [TimeStamp]), COUNT(*) FROM [LOG] WHERE [Realm] IN (' + @Realms + ') AND EventID=20000 AND [TimeStamp] BETWEEN @StartDate AND @EndDate GROUP BY DATEPART(HOUR, [TimeStamp])'
	DECLARE @parameters nvarchar(MAX) = '@StartDate datetime, @EndDate datetime'
	INSERT INTO #TempTable3 EXEC sp_executesql @sql, @parameters, @StartDate, @EndDate
	
	SELECT * FROM #TempTable3 ORDER BY [Status] ASC
END
GO
IF OBJECT_ID ( 'GetChart4', 'P' ) IS NOT NULL 
    DROP PROCEDURE GetChart4;
GO
CREATE PROCEDURE [dbo].[GetChart4]
	(
	@StartDate datetime,
	@EndDate datetime,
	@Realms varchar(250)
	)
AS
BEGIN
	SET NOCOUNT ON;
	
	CREATE TABLE #TempTable4(
		[Status] varchar(25),
		[Total] int)
	DECLARE @sql nvarchar(MAX) = 'SELECT [Realm], COUNT(*) FROM [LOG] WHERE [Realm] IN (' + @Realms + ') AND EventID=20000 AND [TimeStamp] BETWEEN @StartDate AND @EndDate GROUP BY [Realm]'
	DECLARE @parameters nvarchar(MAX) = '@StartDate datetime, @EndDate datetime'
	INSERT INTO #TempTable4 EXEC sp_executesql @sql, @parameters, @StartDate, @EndDate
	
	SELECT * FROM #TempTable4 ORDER BY [Status] ASC
END
GO

Granting Permissions for Stored Procedures
 

Grant Permission
GRANT EXECUTE ON [Logging].dbo.GetChart1 TO SecureAuthSQLUser
GRANT EXECUTE ON [Logging].dbo.GetChart2 TO SecureAuthSQLUser
GRANT EXECUTE ON [Logging].dbo.GetChart3 TO SecureAuthSQLUser
GRANT EXECUTE ON [Logging].dbo.GetChart4 TO SecureAuthSQLUser
 
GRANT EXECUTE ON [Logging].dbo.AddCategory TO SecureAuthSQLUser 
GRANT EXECUTE ON [Logging].dbo.GetLog TO SecureAuthSQLUser 
GRANT EXECUTE ON [Logging].dbo.InsertCategoryLog TO SecureAuthSQLUser 
GRANT EXECUTE ON [Logging].dbo.WriteLog TO SecureAuthSQLUser