Applies to
ApexSQL Monitor
Summary
This article shows the helper functions needed for ad hoc reporting including what each function is, why the function is needed, when each function should be used and how those functions should be used
Description
The following Helper functions are used for ad hoc reporting. All the below functions, once executed and created in the ApexSQL Monitor central repository database will serve as data converters, and they are not database and/or table dependent
Helper functions inventory:
Calculate default interval - [ApexSQL].[CalculateInterval]
Description: This main purpose of the function is to return the time interval that will be used for interpolation of the metric values. The final result when this function is implemented is to reduce the number of the measurement values that will be displayed in the chart to acceptable level. For example, for the metric which values are retrieved each 15 second, displaying that metric in the 1 day chart would require displaying 5,760 values in the chart. In the limited chart space, this will render it as unreadable. For 7-day chart it is even more prominent readability problem.
This function will calculate the total time period for the report as difference between the start and end date of the report, and depending on the time period size it will return the time value in minutes for which the data values will be interpolates into a single value
Example call: The function is designed to be called by the Reporting function as the parameter in the select statement
SELECT MAX(ApexSQL.FTruncateDate(MeasuredAt, [ApexSQL].[CalculateInterval](@StartDate, @EndDate, @Interval))) AS X ,AVG(Value) AS Y FROM [ApexSQL].[MonitorMeasuredValues]
Parameters:
@From – Start period of the report. Type: datetimeoffset Form: '2016-11-01 00:00:00.0000000 +01:00@To - End period of the report. Type: datetimeoffset Form: '2016-11-01 00:00:00.0000000 +01:00
@Interval – Interval in minutes that will be used for calculation. This parameter allows user to set its own time interval that will be used for calculation, regardless of the report time period. If not set it will be considered as 0 Type: integer
Example result: The function will return the following values (time interval in minutes) depending on the report time period
For manually entered interval, the function will return that value as is
For example:
SELECT MAX(ApexSQL.FTruncateDate(MeasuredAt, [ApexSQL].[CalculateInterval](@StartDate, @EndDate, 100))) AS X ,AVG(Value) AS Y FROM [ApexSQL].[MonitorMeasuredValues]
Since the value 100 is set manually by the user, regardless of the time interval used for report the function will return the value 100 (100 minutes) as interpolation period.
Note: setting the period manually is intended for advanced users. If not sure, do not enter the value manually and function will use the most optimal values
Definition:
CREATE FUNCTION [ApexSQL].[CalculateInterval] ( @From DATETIMEOFFSET ,@To DATETIMEOFFSET ,@Interval INT ) RETURNS INT AS BEGIN DECLARE @period INT; SET @period = DATEDIFF(Minute, @From, @To) IF @Interval <> 0 RETURN @Interval -- Period less than hour interval is one minute IF @period < 1 * 60 RETURN 1; -- Period less than 24-hour interval is five minute IF @period < 24 * 60 RETURN 5; -- Period less than 7-day interval is one hour IF @period < 7 * 24 * 60 RETURN 60; -- Period bigger than 7-day interval is four hour RETURN 240; END GO
Converts SQL Server agent status type to string (Stopped, Start pending, Stop pending, Running, Continue pending, Pause pending and Paused) - AgentStateTypeConverter
Description: The status of SQL Server Agent is stored in a form of the numerical values in the ApexSQL monitor repository database. This is the function that will decipher those numerical values into proper name of specific SQL Server agent status as used by SQL Server, that can be then directly used for displaying in report
Example call: The function is designed to be called by the Reporting function in the select statement
SELECT ApexSQL.AgentStateTypeConverter(X) AS STATUS ,Y FROM [ApexSQL].[MeasuredValuePieChart]
Parameters:
@status – this is the parameter that cam have whole number value between 1 and 7 as those are used in the repository database for SQL Server Agent Status Type: integer Form: whole numerical value between 1 and 7
Example result: The function will return the following values depending on the value of the @value parameter
Definition:
CREATE FUNCTION [ApexSQL].[AgentStateTypeConverter] (@status INT) RETURNS NVARCHAR(50) AS BEGIN IF @status = 1 RETURN 'Stopped' IF @status = 2 RETURN 'Start pending' IF @status = 3 RETURN 'Stop pending' IF @status = 4 RETURN 'Running' IF @status = 5 RETURN 'CONTINUE pending' IF @status = 6 RETURN 'Pause pending' IF @status = 7 RETURN 'Paused' RETURN 'UNKNOWN'; END GO
Converts source type to string (Machine, SQL Server, Database, Device, Index, Replica, AG Database) - SourceTypeConverter
This function allows the user to have the human readable name of the actual source type of the specific metric data
Example call: The function is designed to be called by the Reporting function or Report query in the select statement
SELECT [Name] ,ApexSQL.SourceTypeConverter([Type]) AS [Type] FROM [ApexSQLMonitor].[ApexSQL].[MonitoredSourcesView]
Parameters:
@object – this is the parameter that can accept any whole number value between 1 and 7, as these are used in the repository database for different source types
Type: integer
Form: whole numerical value between 1 and 7
Example result: The function will return the following values depending on the value of the @value parameter
Definition:
CREATE FUNCTION [ApexSQL].[SourceTypeConverter] (@object INT) RETURNS NVARCHAR(50) AS BEGIN IF @object = 1 RETURN 'Machine' IF @object = 2 RETURN 'SQL Server' IF @object = 3 RETURN 'Database' IF @object = 4 RETURN 'Device' IF @object = 5 RETURN 'Index' IF @object = 6 RETURN 'AlwaysOn Replica' IF @object = 7 RETURN 'AlwaysOn Database' RETURN 'Unknown'; END GO
Converts system availability to string (Offline, Online) - StatusConverter
The function serves to provide the readable status information for the system availability. The function is designed to work specifically in interaction with the MeasuredValuePieChart reporting function
Example call: The function is designed to be called by the Report query in the select statement
SELECT ApexSQL.StatusConverter(X) AS STATUS ,Y FROM...
Parameters:
@status – this is the parameter of the float type that will be retrieved from the MeasuredValuePieChart reporting function Type: float
Example result: The function will return the following values depending on the @value parameter
Definition:
CREATE FUNCTION [ApexSQL].[StatusConverter] (@status FLOAT) RETURNS NVARCHAR(50) AS BEGIN IF @status = 0 RETURN 'Offline' RETURN 'Online'; END GO
Converts the metric name to Id
This function allows use of regular metric names when writing report queries, and function will convert it in the adequate unique identifier id that is in use in the repository database
Example call: The function is designed to be called by the Report query in the select statement
SELECT X,Y FROM [ApexSQL].[MeasuredValueLineChart]( ApexSQL.SourceNameToId(@MachineName), ApexSQL.MetricNameToId('% Processor Time'))
Parameters:
@MetricName – this is the parameter that will accept the metric name as inputThe list of the exact metric names can be retrieved from the repository database using the following query:
-- Displays all available Metric names used in ApexSQL Monitor SELECT [Name] FROM [ApexSQLMonitor].[ApexSQL].[MonitorMeasurements]
Type: NVARCHAR(500)
Example result: The function will return the following values if % of Processor time is used as input: 99CAE97A-5ABE-4C18-972E-6463114FC4F9
Definition:
CREATE FUNCTION [ApexSQL].[MetricNameToId] (@MetricName NVARCHAR(500)) RETURNS UNIQUEIDENTIFIER AS BEGIN DECLARE @Id UNIQUEIDENTIFIER; SELECT TOP 1 @Id = Id FROM ApexSQL.MonitorMeasurements WHERE NAME = @MetricName RETURN @Id END GO
Converts the metric source name to Id
This function allows using regular names of the sources when writing custom SQL Server performance report queries. The function will convert the metric source name in the adequate uniqueidentifier id of that source that is in use in the repository database
Example call: The function is designed to be called by the Report query in the select statement
SELECT X,Y FROM [ApexSQL].[MeasuredValueLineChart]( ApexSQL.SourceNameToId(@MachineName), ApexSQL.MetricNameToId('% Processor Time'))
Parameters:
@SourceName – this is the parameter that will accept the metric’s source name as input
The list of the exact metric names can be retrieved from the repository database using the following query:
-- Displays all available names of metric sources and the source types SELECT [Name] ,ApexSQL.SourceTypeConverter([Type]) AS [Type] FROM [ApexSQLMonitor].[ApexSQL].[MonitoredSourcesView]
Type: NVARCHAR(500)
Example result: The function will return the following values if Intel[R] 82574L Gigabit Network Connection is used as input: 1C1719D8-B1A1-4B21-859A-4756561A78FF
Definition:
CREATE FUNCTION [ApexSQL].[SourceNameToId] (@SourceName NVARCHAR(500)) RETURNS UNIQUEIDENTIFIER AS BEGIN DECLARE @Id UNIQUEIDENTIFIER; SELECT TOP 1 @Id = Id FROM ApexSQL.MonitoredSourcesView WHERE NAME = @SourceName RETURN @Id END GO
Converts the metric Id to name
This is the function that will convert the uniqueidentifier id of the metrics and return it as the human readable name of the metric for displaying in reports. This helper function is complementary to the previously described Converts the metric name to Id function
Example call: The function is designed to be called by the Report functions in the select statement
DECLARE @serie NVARCHAR(256); SELECT @serie = [ApexSQL].[MetricIdToName](@MeasurmentId)
Parameters:
@MeasurementId – this is the parameter that will accept the metric unique Id as Type: uniqueidentifier
Example result: The function will return the following values if 99CAE97A-5ABE-4C18-972E-6463114FC4F9 is used as input: % of Processor time
Definition:
CREATE FUNCTION [ApexSQL].[MetricIdToName] (@MeasurmentId UNIQUEIDENTIFIER) RETURNS NVARCHAR(500) AS BEGIN DECLARE @MeasurmentName NVARCHAR(500); SELECT TOP 1 @MeasurmentName = NAME FROM ApexSQL.MonitorMeasurements WHERE Id = @MeasurmentId RETURN @MeasurmentName END GO
Converts the metric source id to name
The function serves to convert the unique identifier id used in the central repository database to a human readable name of the metric source that can be used in final reports. This helper function is complementary to the previously described Converts the metric source name to Id function
Example call: The function is designed to be called by the Reporting queries in the select statement
SELECT A.[Id] ,ApexSQL.SourceIdToName(A.[SourceId]) AS Source ,
Type: uniqueidentifier
Example result: The function will return the following values if 99CAE97A-5ABE-4C18-972E-6463114FC4F9 is used as input: % of Processor time
Definition:
CREATE FUNCTION [ApexSQL].[SourceIdToName] (@SourceId UNIQUEIDENTIFIER) RETURNS NVARCHAR(500) AS BEGIN DECLARE @SourceName NVARCHAR(500); SELECT TOP 1 @SourceName = NAME FROM ApexSQL.MonitoredSourcesView WHERE Id = @SourceId RETURN @SourceName END GO
Converts SQL Server database status type to string (Restoring, Recovering, Recovery pending, Suspect, Emergency, Offline and Copying)
This function provides the readable names for the SQL Server database status that can be used directly for displaying in the user report
Example call: The function is designed to be called by the Reporting function in the select statement
SELECT ApexSQL.AgentStateTypeConverter(X) AS STATUS,Y FROM [ApexSQL].[MeasuredValuePieChart]
Parameters:
@status – this is the parameter that cam have whole number value between 1 and 7 as those are used in the repository database for SQL Server Agent Status
Type: integer
Form: whole numerical value between 1 and 7
Example result: The function will return the following values depending on the value of the @status parameter
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center