ApexSQL Monitor ad hoc reporting Helper functions (inventory, descriptions and examples)

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
Converts SQL Server agent status type to string
Converts source type to string
Converts system availability to string
Converts the metric name to Id
Converts the metric source name to Id
Converts the metric Id to name
Converts the metric source id to name
Converts SQL Server database status type to string (Restoring, Recovering, Recovery pending, Suspect, Emergency, Offline and Copying)
Convert alert severity to string (Low, Medium, High)
Convert alert state to string (Not resolved, Ignored, Fixed, Known issue)
Converts alert review state to string (Yes, No)
Convert alert severity string (Low, Medium, High) to Id (1, 2, 3)
Convert alert state string (Not resolved, Ignored, Fixed, Known issue) to Id (0, 1, 2, 3, 4)
Get all sources for Machine
Get all sources for SQL Server
Get all indexes for database
Get all indexes for server

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 report period that is up to 1 hour, the value returned will be 1 minute (when @interval is not set or is 0)
For report period between 1 hour and 1 day, the returned value will be 5 minutes (when @interval is not set or is 0)
For report period between 1 day and 7 days, the returned value will be 60 minutes (when @interval is not set or is 0)
For reporting period, larger than 7 days, the return value will be 240 minutes (when @interval is not set or is 0)

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

Stopped (@status = 1)
Start pending (@status = 2)
Stop pending (@status = 3)
Running (@status = 4)
Continue pending (@status = 5)
Pause pending (@status = 6)
Paused (@status = 7)

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

Machine (@object = 1)
SQL Server (@object = 2)
Database (@object = 3)
Device (@object = 4)
Index (@object = 5)
AlwaysOn replica (@object = 6)
AlwaysOn database (@object = 7)
Unknown (in case that parameter value is not one of above which is indication of error)

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

Offline (@status = 0)
Online (@status = 1)

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 input

The 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:

Example result: The function will return the following values depending on the value of the @status parameter

Stopped (@status = 1)
Start pending (@status = 2)
Stop pending (@status = 3)
Running (@status = 4)
Continue pending (@status = 5)
Pause pending (@status = 6)
Paused (@status = 7)

Definition:

CREATE FUNCTION [ApexSQL].[DatabaseStateTypeConverter] (@status INT)
RETURNS NVARCHAR(50)
AS
BEGIN
	IF @status = 1
		RETURN 'Restoring'

	IF @status = 2
		RETURN 'Recovering'

	IF @status = 3
		RETURN 'Recovery pending'

	IF @status = 4
		RETURN 'Suspect'

	IF @status = 5
		RETURN 'Emergency'

	IF @status = 6
		RETURN 'Offline'

	IF @status = 7
		RETURN 'Copying'

	RETURN 'Unknown';
END
GO

Convert alert severity to string (Low, Medium, High)

This function will return the alert severity as a string. Severity is stored in a database as a numerical value and by calling this function it will be returned as adequate text: Low, Medium or High

Example call: The function is designed to be called by the Report functions in the select statement

SELECT ApexSQL.AlertSeverityToStringConverter(X) AS Severity,Y
FROM [ApexSQL].[AlertSeverityPieChart](
    @StartDate, 
    @EndDate, 
    @MachineName)

Parameters:

@severity – this is the parameter that will accept the severity numerical value as stored in the repository database
Type: integer
Form: whole numerical value between 1 and 3

Example result: The function will return the following values depending on the value of the @status parameter

Low (@severity = 1)
Medium (@severity = 2)
High (@severity = 3)

Definition:

CREATE FUNCTION [ApexSQL].[AlertSeverityToStringConverter] (@Severity INT)
RETURNS NVARCHAR(50)
AS
BEGIN
	IF @Severity = 1
		RETURN 'Low'

	IF @Severity = 2
		RETURN 'Medium'

	IF @Severity = 3
		RETURN 'High'

	RETURN 'Unknown';
END

Convert alert state to string (Not resolved, Ignored, Fixed, Known issue)

Similar to the previous function, just this function will return the different alert resolution states of individual alerts as a readable text

Example call: The function is designed to be called by the Reporting script in the select statement

SELECT [ApexSQL].[AlertStateToStringConverter](A.[State]) AS [State]
FROM [ApexSQLMonitor].[ApexSQL].[MonitorAlerts]

Parameters:

@state – this is the parameter that cam have whole number value between 1 and 3 as those are used in the repository database for alert resolution status
Type: integer
Form: whole numerical value between 1 and 3

Example result: The function will return the following values depending on the value of the @status parameter

Ignored (@state = 1)
Fixed (@state = 2)
Known issue (@state = 3)
Not resolved (@state= 0)

Definition:

CREATE FUNCTION [ApexSQL].[AlertStateToStringConverter] (@value INT)
RETURNS NVARCHAR(50)
AS
BEGIN
	IF @state = 1
		RETURN 'Ignored'

	IF @state = 2
		RETURN 'Fixed'

	IF @state = 3
		RETURN 'Known issue'

	RETURN 'Not resolved';
END
GO

Converts alert review state to string (Yes, No)

This function will return information whether the alert is already reviewed by someone or not. In case that alert is already reviewed it will return Yes, otherwise it will return No

Example call: The function is designed to be called by the Reporting script in the select statement

SELECT [ApexSQL].[AlertCheckToStringConverter](A.[State]) AS [State]
FROM [ApexSQLMonitor].[ApexSQL].[MonitorAlerts]

Parameters:

@review – this is the parameter that cam have whole number value 0 or 1 those are used in the repository database for alert review state
Type: integer
Form: whole numerical value 0 or 1

Example result: The function will return the following values depending on the value of the @status parameter

No (@review = 0)
Yes (@Review = 1)

Definition:

CREATE FUNCTION [ApexSQL].[AlertCheckToStringConverter] (@ review INT)
RETURNS NVARCHAR(50)
AS
BEGIN
	IF @value = 0
		RETURN 'No'

	RETURN 'Yes';
END
GO

Convert alert severity string (Low, Medium, High) to Id (1, 2, 3)

This function will convert the alert severity string (Low, Medium and High) to an adequate numeric value used for alert severity in the repository database. This function is useful when writing the report script as the regular value can be used in the script instead of the non descriptive numerical values

Example call: The function is designed to be used in report script for setting the input parameter value for alert severity

SET @Severity = [ApexSQL].[AlertSeverityToNumberConverter]('High')

Parameters:

@severity – this is the parameter that will accept the severity numerical value as stored in the repository database
Type: NVARCHAR(50)
Form: Low, Medium, High or All (“All” is used when info for all three severity types is needed)

Example result: The function will return the following values depending on the value of the @status parameter

1 (@severity = ‘Low’)
2 (@severity = ‘Medium’)
3 (@severity = ‘High’)
4 (@severity = ‘All’)

Definition:

CREATE FUNCTION [ApexSQL].[AlertSeverityToNumberConverter] (@severity NVARCHAR(50))
RETURNS INT
AS
BEGIN
	IF @severity = 'Low'
		RETURN 1

	IF @severity = 'Medium'
		RETURN 2

	IF @severity = 'High'
		RETURN 3

	IF @severity = 'All'
		RETURN 4

	RETURN - 1;
END
GO

Convert alert state string (Not resolved, Ignored, Fixed, Known issue) to Id (0, 1, 2, 3, 4)

Example call: The function is designed to be used in report script for setting the input parameter value for alert severity

SET @Severity = [ApexSQL].[AlertStateToNumberConverter]('All')

Parameters:

Example result: The function will return the following values depending on the value of the @status parameter

0 (@state = ‘Not fixed)
1 (@state = ‘Ignored’)
2 (@state = ‘Fixed’)
3 (@state = Known issue’)
4 (@state = ‘All’)

Definition:

CREATE FUNCTION [ApexSQL].[AlertStateToNumberConverter] (@state NVARCHAR(50))
RETURNS INT
AS
BEGIN
	IF @ STATE = 'Not fixed'
		RETURN 0

	IF @ STATE = 'Ignored'
		RETURN 1

	IF @ STATE = 'Fixed'
		RETURN 2

	IF @ STATE = 'Known issue'
		RETURN 3

	IF @ STATE = 'All'
		RETURN 4

	RETURN - 1;
END
GO

Get all sources for Machine

Function will return all available sources for all performance metric (i.e indexes, Table name, database name etc.) for the specified monitored instance. This function allows for example to retrieve all sources for the metrics (for example getting all alerts regardless of what is the alert source or how many SQL Servers are hosted on the machine)

Example call: The function is designed to be used in report script for setting the input parameter value for alert severity

SELECT *
FROM ApexSQL.GetAllSourcesForMachine(ApexSQL.SourceNameToId('SERVER2012R2-L1'))

Parameters:

@MachineId – Unique ID of the monitored machine stored in the repository database
Type: uniqueidentifier

Example result:

Definition:

CREATE FUNCTION [ApexSQL].[AlertStateToNumberConverter] (@state NVARCHAR(50))
RETURNS INT
AS
BEGIN
	IF @ STATE = 'Not fixed'
		RETURN 0

	IF @ STATE = 'Ignored'
		RETURN 1

	IF @ STATE = 'Fixed'
		RETURN 2

	IF @ STATE = 'Known issue'
		RETURN 3

	IF @ STATE = 'All'
		RETURN 4

	RETURN - 1;
END
GO

Get all sources for SQL Server

Similar function as previous just designed to return all sources Ids for specified SQL Server only

Example call: The function is designed to be used as part of the select statement in the final report query for retrieving the requested data from the repository database

SELECT *
FROM ApexSQL.GetAllSourcesSQLServer(ApexSQL.SourceNameToId('SQL2016'))

Parameters:

@ServerId – Unique ID of the monitored SQL Server stored in the repository database
Type: uniqueidentifier

Example result:

Definition:

CREATE FUNCTION [ApexSQL].[GetAllSourcesSQLServer] (@ServerId UNIQUEIDENTIFIER)
RETURNS TABLE
AS
RETURN (
		SELECT [Id]
		FROM [ApexSQLMonitor].[ApexSQL].[MonitoredObjectsView]
		WHERE Type > 0
			AND ServerId = @ServerId
		)

Get all indexes for database

This function will return all existing indexes in the specified database

Example call: The function is designed to be used as part of the select statement in the final report query for retrieving the requested data from the repository database

SELECT *
FROM ApexSQL.GetAllIndexesForDatabase(@DatabaseId)

Parameters:

@DatabaseId – Unique ID of the database stored in the repository database
Type: uniqueidentifier

Example result:

Definition:

CREATE FUNCTION [ApexSQL].[GetAllIndexesForDatabase] (@DatabaseId UNIQUEIDENTIFIER)
RETURNS TABLE
AS
RETURN (
		SELECT [Id]
		FROM [ApexSQLMonitor].[ApexSQL].[MonitoredObjectsView]
		WHERE Type = 5
			AND DatabaseId = @DatabaseId
		)

Get all indexes for server

Similar to the previous function, just it will return all available indexes for SQL Server (for all databases in that server)

Example call: The function is designed to be used as part of the select statement in the final report query for retrieving the requested data from the repository database

SELECT *
FROM ApexSQL.GetAllIndexesForServer(@ServerId)

Parameters:

@DatabaseId – Unique ID of the database stored in the repository database
Type: uniqueidentifier

Example result:

Definition:

CREATE FUNCTION [ApexSQL].[GetAllIndexesForServer] (@ServerId UNIQUEIDENTIFIER)
RETURNS TABLE
AS
RETURN (
		SELECT [Id]
		FROM [ApexSQLMonitor].[ApexSQL].[MonitoredObjectsView]
		WHERE Type = 5
			AND ServerId = @ServerId
		)

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

Database administration
Automate critical tasks for database administration

Database development
Integrate database changes and automate deployments

ApexSQL Monitor ad hoc reporting Helper functions (inventory, descriptions and examples)

Downloads

Database administrationAutomate critical tasks for database administration

Database developmentIntegrate database changes and automate deployments

Downloads

Related posts:

Database administration
Automate critical tasks for database administration

Database development
Integrate database changes and automate deployments