Service Properties Module : MinionWare

The ME Service Properties module collects information about service properties, including the startup account, service status, and the start mode. Minion Enterprise alerts on stopped services as part of this module. You can also use this information to assess the impact of an account password change.

IMPORTANT: By default, ME will alert on SQL services where the start mode is Auto, and status is not “Running” or “Unknown”. This means that any SQL service with a start mode of Manual or Disabled will not show up in the default alert. Likewise, services which are not SQL services are not alerted on automatically. You can of course change this behavior (by changing the Alert.ServiceStatus stored procedure call in the job), or devise your own alerts for non-SQL services. See the stored procedure "Alert.ServiceStatus", documented below.

The Service Properties module and service down alerts do not require any setup. Once you have entered instances into the dbo.Servers table, along with their associated service level, Minion Enterprise automatically collects service data and performs alerts as necessary.

For more information, see the “Service Down Alerts” article and "How To: Alert on non-SQL Server services".

Tables

Collector.ServiceProperties

Stores the collections of service properties data.

Column	Type	Description
ID	bigint	Primary key row identifier.
ExecutionDateTime	datetime	The execution date and time, common to the run of a collection for a single instance.
InstanceID	int	The instance ID of the instance in question, as defined in the table dbo.Servers. Note: Any time you see InstanceID = 0 in Minion Enterprise, it represents a global default.
ServiceName	varchar	The name of the service. Example: “SQL Server (MSSQLSERVER)”.
StartName	varchar	The service account name. Example: “NT Service\MSSQLSERVER”.
Status	varchar	The service status. Example: “Running”.
StartMode	varchar	The service startup mode. Example: “Auto”.

History.ServiceStatus

Holds a history of service status alerts.

Column	Type	Description
ID	bigint	Primary key row identifier.
ExecutionDateTime	datetime	The execution date and time, common to the run of a collection for a single instance.
InstanceID	int	The instance ID of the instance in question, as defined in the table dbo.Servers.
ServerName	Varchar	The name of the SQL Server instance.
ServiceName	varchar	The name of the service. Example: “SQL Server (MSSQLSERVER)”.
ServiceStartMode	Varchar	The service start mode. Example: “Automatic”.
ServiceStatus	varchar	The service status. Example: “Running”.
ServiceLevel	Varchar	A simple label for the level of the server’s importance, which determines the level of service that Minion Enterprise provides. Example: Gold.
AppName	varchar	Application name (as defined in dbo.Application, and tied to InstanceID in dbo.ServerAppRoleApp).

dbo.ServiceStatusExceptions

Holds custom exceptions to service level alerts.

Column	Type	Description
ID	bigint	Primary key row identifier.
InstanceID	Bigint	The instance ID of the instance in question, as defined in the table dbo.Servers.
ServiceName	Varchar	The name of the service. Example: “SQL Server (MSSQLSERVER)”.
IsActive	bit	Determines whether the current row is active, and should be used.
Comment	varchar	For your reference only. Label each row with a short description and/or purpose.

Alert.ServiceStatusDefer

Holds service status deferrment orders. Insert a row here to prevent ME from sending service down alerts for a particular drive, for a set period of time.

Column	Type	Description
ID	bigint	Primary key row identifier.
InstanceID	int	The instance ID of the instance in question, as defined in the table dbo.Servers.
ServiceName	varchar	The name of the service. Example: “SQL Server (MSSQLSERVER)”.
DeferDate	Date	The date on which the deferment begins.
DeferEndDate	Date	The date on which the deferment expires.
DeferEndTime	time	The time on which the deferment expires.

Views:

Collector.ServicePropertiesCurrent

Provides the most recent collection of service properties data.
Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:

ServerName
ServiceLevel
Version
Edition
Descr

Collector.ServicePropertiesPrevious

Provides the next-to-latest collection of service properties data.
Each “Previous” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:

ServerName
ServiceLevel
Version
Edition
Descr

Stored Procedures

Alert.ServiceStatus

Logs alerts and sends the alert email for downed SQL services.

Parameter	Type	Description
@EmailProfile	varchar	The name of the email profile to use, as defined in the SQL Server system table MSDB.dbo.sysmail_profile.
@ServiceLevel	varchar	A simple label for the level of the server’s importance, which determines the level of service that Minion Enterprise provides. Example: Gold.
@ServiceName	varchar	The name of the service to alert on. This can be ‘ALL’ or NULL to indicate all services; or, a single service name; or a comma-delimited list of service names, with or without wildcards. Example values: ALL %SQL% Service1, %SQL%, Service3 SQL Server (MSSQLSERVER)
@StartMode	varchar	The service startup mode to alert on. This means that only services with this start mode will generate an alert when they are not running. Note that “All” and NULL mean the same thing (i.e., all start modes). Valid values: All NULL Auto Manual Disabled
@IncludeDefer	bit	Flag that determines whether to include the T-SQL alert deferment statements at the end of the alert email, or not. By default, this is enabled.
@IncludeException	bit	Flag that determines whether to include the T-SQL alert exception statements at the end of the alert email, or not. By default, this is enabled.

Setup.ServiceStatusDefer

Allows you to set up a service down deferment for a specific service, on a specific instance, for a set period of time.

For example, if Server1 has a scheuled downtime over the weekend, you can defer alerts for Server1 services until Monday.

Parameter	Type	Description
@ServerName	varchar	The name of the server to receive the deferment.
@ServiceName	varchar	Service to defer alerts on. Example: “SQL Server (MSSQLSERVER)”.
@DeferDate	date	The date on which the deferment should begin.
@DeferEndDate	date	The date on which the deferment should end.
@DeferEndTime	time	The time at which the deferment should end.

Setup.ServiceStatusException

Allows you to set up an exception for service down alerts for a specific service, on a specific instance (or all instances).

Parameter	Type	Description
@ServerName	varchar	The name of the server to receive the exception.
@ServiceName	varchar	Service to except alerts on. Example: “SQL Server (MSSQLSERVER)”.

Jobs

CollectorServicePropertiesGet-GOLD

Calls the executable ServicePropertiesGET.exe for all servers with ServiceLevel = ‘Gold’, to retrieve service property information.

CollectorServicePropertiesGet-SILVER

Calls the executable ServicePropertiesGET.exe for all servers with ServiceLevel = ‘Silver’, to retrieve service property information.

CollectorServicePropertiesGet-BRONZE

Calls the executable ServicePropertiesGET.exe for all servers with ServiceLevel = ‘Bronze’, to retrieve service property information.

AlertServiceStatus-GOLD

Calls the proceure Alert.ServiceStatus to alert on downed services on Gold level servers.

AlertServiceStatus-SILVER

Calls the proceure Alert.ServiceStatus to alert on downed services on Silver level servers.
NOTE: ME does not provide service down alerts for Bronze servers by default, as they are considered lowest priority.

Executables

ServicePropertiesGET.exe

Collects service data for the instances in the specified service level, and logs the results to Collector.ServiceProperties.
Input parameters:

$Query – This parameter refers to the service level that the collector should operate on. Examples: Gold, Silver, Bronze. For more information on this topic, see the article “Executables and Service Levels”.

Example execution:
C:\MinionByMidnightDBA\Collector\ServicePropertiesGET.exe Gold

Service Properties: Use Cases

In this section:

Account password changes
Service account audit
Detect changes in the service settings

Account password changes: Use the collected service information to see the impact of an account password change. For example if it’s time to rotate the password for the account DOMN\SQLapp1, it would be an excellent idea to first check for SQL Server services that depend on that account:

SELECT  ExecutionDateTime
      , ServerName
      , ServiceLevel
      , ServiceName
      , StartName
      , Status
      , StartMode
FROM    [Collector].[ServicePropertiesCurrent]
WHERE   ServiceName LIKE '%SQL%'
        AND StartName = 'DOMN\SQLapp1';

We make sure to query the Collector.ServicePropertiesCurrent view, so we only pull up the most recent set of collected data.

Service account audit: The Service Properties collection makes it easy to research what accounts are in use for SQL services across your enterprise. The following query is a great place to start. It gives you a list of SQL services and their startup accounts:

SELECT  ServiceName
      , StartName
      , COUNT(*) AS [count]
FROM    [Collector].[ServicePropertiesCurrent]
WHERE   ServiceName LIKE '%SQL%'
GROUP BY ServiceName, StartName
ORDER BY StartName, ServiceName;

If something strange arises, we can drill down further into specifics:

SELECT  ServerName
      , ServiceName
      , StartName
FROM    [Collector].[ServicePropertiesCurrent]
WHERE   ServiceName LIKE '%SQL%'
        AND StartName = 'DOMN\SomeUser'
ORDER BY StartName, ServiceName;

Detect changes in the service settings: Minion Enterprise collects data at regular intervals and stores that data over time. So we have a built in way to examine changes in service settings
for example, discovering when a service’s start mode changed from Auto to Manual. We could write a query against the base table Collector.ServiceProperties, but it’s very handy to write change detection queries against the provided views:

SELECT  curr.ServerName
      , curr.ServiceLevel
      , curr.ServiceName
      , curr.StartName
      , curr.Status
      , curr.StartMode
      , prev.StartMode AS PreviousStartMode
FROM    [Collector].[ServicePropertiesCurrent] curr
INNER JOIN [Collector].[ServicePropertiesPrevious] prev
        ON curr.InstanceID = prev.InstanceID
           AND curr.ServiceName = prev.ServiceName
WHERE   curr.StartMode != prev.StartMode;

We can use a very similar query to detect when the service account has changed:

SELECT  curr.ServerName
      , curr.ServiceLevel
      , curr.ServiceName
      , curr.StartName
                  , prev.StartName AS PreviousStartName
      , curr.Status
      , curr.StartMode
FROM    [Collector].[ServicePropertiesCurrent] curr
INNER JOIN [Collector].[ServicePropertiesPrevious] prev
        ON curr.InstanceID = prev.InstanceID
           AND curr.ServiceName = prev.ServiceName
WHERE   curr.StartName != prev.StartName;

https://minionware.desk.com/customer/portal/articles/2376690-service-properties-module
http://www.MinionWare.net

Service Properties Module Print