Forum Discussion
MarcusH
3 months agoContributor III
The MarketPlace solution Admin Solution Tools (AST) has examples in Dashboard Extender (log by date, by user etc):
''' <summary>
''' Performs a SQL query to retrieve a Log Analysis based on a date range
''' </summary>
''' <param name="si"></param>
''' <param name="dateRange"></param>
''' <returns></returns>
Private Function GetLogAnalysisByDate(ByVal si As SessionInfo, ByVal dateRange As Integer) As DataTable
Try
'Define the SQL Statement
Dim parameters As List(Of DbParamInfo) = New List(Of DbParamInfo) From {
New DbParamInfo("@dateRange", dateRange)
}
Dim newSql As String = "
DECLARE @LogAnalysisByDate TABLE (DaysOld VARCHAR(50), LogonCount BIGINT, ErrorCount BIGINT, TaskCount BIGINT)
-- Set the interval in days
DECLARE @DateInterval INT = @dateRange
--DECLARE @DaysOld INT = @DateInterval
DECLARE @CurrentDate DATETIME = GETUTCDATE()
--Set start date to number of days behind current date
DECLARE @StartDate DATETIME = DATEADD(d, (@DateInterval) * -1, GETUTCDATE())
DECLARE @EndDate DATETIME = GETUTCDATE()
DECLARE @TotalLogs BIGINT = (SELECT ((SELECT COUNT(*) FROM UserLogonActivity)+(SELECT COUNT(*) FROM ErrorLog)+(SELECT COUNT(*) FROM TaskActivity)))
DECLARE @Loop INT = 0
DECLARE @DaysFrom INT = 0
DECLARE @DaysTo INT = @DateInterval
DECLARE @LogonCount INT
DECLARE @ErrorCount INT
DECLARE @TaskActivityCount INT
WHILE (SELECT ISNULL(SUM(LogonCount) + SUM(ErrorCount) + SUM(TaskCount), 0) FROM @LogAnalysisByDate) < @TotalLogs
BEGIN
SET @LogonCount = (SELECT COUNT(*) FROM UserLogonActivity WHERE LogonTime BETWEEN @StartDate AND @EndDate)
SET @ErrorCount = (SELECT COUNT(*) FROM ErrorLog WHERE ErrorTime BETWEEN @StartDate AND @EndDate)
SET @TaskActivityCount = (SELECT COUNT(*) FROM TaskActivity WHERE QueuedTime BETWEEN @StartDate AND @EndDate)
IF @Loop > 0
BEGIN
-- Set the DaysOld column value
SET @DaysTo = @DaysTo + @DateInterval
SET @DaysFrom = @DaysTo - @DateInterval + 1
END
-- Update the start and end dates
SET @EndDate = @StartDate
SET @StartDate = DATEADD(d, (@DaysTo + @DateInterval) * -1, GETUTCDATE());
-- Insert the row
INSERT INTO @LogAnalysisByDate
SELECT (CONVERT(VARCHAR(50), @DaysFrom) + ' - ' + CONVERT(VARCHAR(50), @DaysTo)), @LogonCount, @ErrorCount, @TaskActivityCount
-- Increment the loop count
SET @Loop = @Loop + 1
END
SELECT * FROM @LogAnalysisByDate "
'Return the DataTable
Using DbConnFW As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
Return BRApi.Database.ExecuteSql(DbConnFW, newSql, parameters, False)
End Using
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, "Unhandled Exception in GetLogAnalysisByDate() function.", ex.Message, ex.InnerException))
End Try
End Function
Related Content
- 25 days ago
- 4 months ago
- 10 months ago
- 2 years ago