ServiceNav offers 2 service models to allow you to execute your own SQL queries MS-MSSQL-request-integer and MS-MSSQL-request-integer.
By default these templates work with Microsoft SQLServer and Sybase.
We will focus here on the MS-MSSQL-Request-Integer model
Using the MS-MSSQL-request-integer model
This model allows you to execute a query that will return an integer value on a single line.
The returned value can be compared to thresholds.
Configuration requirements :
Supervisory account type MS SQL with the right to run the command on the SQL Server or Sybase instance and database.
File configuration freetds.conf. To consult the documentation on this link : https://coservit.com/servicenav/fr/documentation/configurer-la-supervision-pour-une-base-ms-sql-server/
Model parameter :
- The server name: This is the name defined in the freetds.conf file: [LOGIN_NAME]
- The name of the database to query
- The SQL query
- The text to display as a prefix to the returned value
- The alert threshold and the critical integer threshold
Use Case - Controlling an application scenario
Objective I have an application that manages application scenarios. I don't have a model for this application in ServiceNav, but I can control the execution time of the scenario directly in the database.
Using the MS-MSSQL-request-integer present in ServiceNav, so I will create my own service model to monitor my application scenario.
From its SQL server
In your Microsoft SQL Server Management console, prepare your query to return only one value on a row.
select top 1 status from SchedItemResult where webscriptName='ServiceNav - Demo' and DATEDIFF(minute, PollTime, getdate()) < 5 order by PollTime desc;
The result is this:
Here, I have a result with the column "Totaltime" which will be used to know the execution time of my scenario.
Now we just need to implement it in a ServiceNav service model.
Create a new unit service from the template MS-MSSQL-Query_Integer
In the Configuration module / Services menu / Lists
Click on the Add button:
Choose the Service Model
Instantiate fields :
The query will return an integer value that I will compare to the defined thresholds.
Add this service to your equipment that manages the scenarios.
Click on Validate and reload its configuration.
Verification of this new model in supervision
In the Supervision / Technical Operation Module
Find its control point and check its operation.
There is also access to performance data.
Click on the service
This model opens many possibilities that can be exposed in its MAP module.