In the current version of K1000 is not possible to creare this kind of notification using the wizard but we need to use a custom SQL to achieve what we need.
A bit of background before to put the hands on the SQL code:
The following SQL uses many different tables and the most important to consider are:
MACHINE contains all the machines that are in our inventory
NTSERVICE contains information about the services discovered on the machine, their name, version, status and other details about them.
The most important field in this table are:
NAME represents the name of the service
STARTUP_TYPE represents the service startup type (if starts automatically, manually or disabled).
the most common status types are:SERVICE_AUTO_START
SERVICE_DEMAND_START
SERVICE_DISABLED
STATUS represents the current status of the service. The most common values are:SERVICE_RUNNING
SERVICE_STOPPED
MACHINE_NTSERVICE_JT It is used to link the MACHINE table with the NTSERVICE table
In the following example we want to monitor the DHCP Server (DHCPServer) and the DNS server status and send an email notification to the administrator when one of these two services is stopped.
First of all we need to create our notification using the wizard:
- Under Reporting click on Notifications
- Click on Choose Action -> New -> Device Notification
- Enter the title, the recipients (at least one) and the frequency and press Create
- Click on the name of the notification that you just created and, as editor option, select : "To edit the Notification using this editor, Click Here"
- Remove all the SQL code and replace it with the following one:
SELECT MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID AS TOPIC_ID
FROM MACHINE
LEFT JOIN ORG1.MACHINE_NTSERVICE_JT
ON ORG1.MACHINE.ID = ORG1.MACHINE_NTSERVICE_JT.MACHINE_ID
LEFT JOIN NTSERVICE
ON ORG1.MACHINE_NTSERVICE_JT.NTSERVICE_ID = NTSERVICE.ID
WHERE NTSERVICE.STATUS = 'SERVICE_STOPPED'
AND NTSERVICE.NAME IN ('DHCPServer', 'DNS')
Changing the where clause and modifying the IN clause adding other services you can keep an eye on different situations.
Some important points to remember:
- It is recommended that you test your SQL code before to sue in a notification or in a report: you can easily connect to the internal database of the K1000 using TOAD for MySql or the native tools of MySQL
- Remember that the data is collected thought the Inventory and so it is not realtime: by default the inventory is sent to the K1000 every 2 hours. So do not exaggerate with the frequency of the notification
- the field NAME of the table NTSTATUS contains the real name of the service and not the descriptive one. To find out the real name of a service open the services.msc snap-in and double click on the service
The real name of the service in the one stated in the General tab, Service name
Comments