Custom stats

As well as it is possible to create custom alerts, it is possible to create custom stats. The difference is:

• Custom alerts: Can alert on if something have specific values, e.g. if CPU usage is above 80% or if a database is created that does not conform to a naming standard etc.
• Custom stats: Collects information about delta values between samples. Is used for e.g. when a sysadmin is added or removed to or from a SQL Server.

Custom stats works by comparing snapshots of information between samples. What information is compared can be defined. E.g. a custom stat is created that contains SQL Server login information. One snapshot containing login information on SQL Servers are kept and compared to the previous snapshot containing login information on SQL Servers. If there is a difference related to the sysadmin role, an entry for the custom stat will be logged. This entry can then be used for a custom alert to create an alert.

In the above example, the custom stat is defined in the dbo.custom_stats table:

Snapshot values for the custom value can be seen in the dbo.v_custom_stats_results view:

As seen, the view contains information for two concurrent samples.

When there is a difference in the two snapshots, they can be seen in the dbo.v_custom_stats_history view:

Now a custom alert can be made to trigger if a sysadmin is added or removed:

create table #temp
(
	server_name nvarchar(128) not null,
	database_name nvarchar(128) null,
	object_name nvarchar(128) null,
	count int not null,
	action varchar(7) not null
)

insert into #temp (server_name, database_name, object_name, count, action)
exec dbo.s_get_custom_stats_results @name = 'Sysadmins'

select t.server_name, t.object_name, t.count
from #temp t
where t.action = 'Added'

drop table #temp