Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Help with Monitoring Unmanage Windows

We've noticed on more than one occasion where devices do not properly come out of unmanage like they should. This can lead to some pretty serious problems, so I decided to build a monitor to make sure devices and applications don't overrun their normal unmanage windows. This can occur with both scheduled unmanage windows from the Unmanage Utility as well as manually entered windows from the Web Console.

I've created the following SQL Server User Experience Monitors for Node and Application windows, respectively.

SELECT CONVERT (INTEGER, unmanaged), caption

FROM dbo.Nodes 

WHERE (unmanaged=1 and UnManageUntil < GetDate())

SELECT CONVERT (INTEGER, unmanaged), name

FROM dbo.APM_Application

WHERE (unmanaged=1 and UnManageUntil < GetDate())

We currently have a node in an overrun state that I'm using for testing, and it does respond properly; however, when I attempt to test the APM version I receive the following message and the component showing DOWN:

Query must return at least 1 row

I'm assuming that the Node monitor will behave the same once the overrun is cleared. I don't know enough SQL to know how to setup a complicated If/Else statement inside the query. I thought that ORDER BY/Sort would do the trick, but I don't think that it will accomplish what I'm looking for.

What I'd like is to allow for an empty result set to return that the component is Available. I don't mind doing more research on my own, so I'm not necessarily looking for anyone to rewrite the script... but I'd appreciate being pointed in the right direction.

Find more posts tagged with

Accepted answers

Petr.Vilem

Following query selects all applications that needs to be re-managed (is it what you mean by overrun state?). Important update is using GETUTCDATE() instead of GETDATE().

SELECT a.ID, a.Name as ApplicationName, n.Caption as NodeName FROM APM_Application AS a
INNER JOIN Nodes AS n ON n.NodeID = a.NodeID
WHERE ((a.Unmanaged = '1') AND (a.UnmanageUntil IS NULL OR GETUTCDATE() > a.UnmanageUntil))
AND (n.Unmanaged IS NULL OR n.Unmanaged = '0')

I probably don't get what are trying to achieve... Select all applications which needs to be re-managed, and if there is no such application, select all applications? What would be this mixed result good for?

All comments

bobross

It seems to always happen this way, but I think I found the answer. These two queries seem to function properly. The Nodes query properly finds the node that is currently in an overrun state, and the Applications query properly responds with a valid field even though there are no Applications in an overrun state.

IF EXISTS (SELECT CONVERT (INTEGER, unmanaged), caption FROM dbo.Nodes WHERE (unmanaged=1 and UnManageUntil < GetDate()))
BEGIN
SELECT CONVERT (INTEGER, unmanaged), caption
FROM dbo.Nodes
WHERE (unmanaged=1 and UnManageUntil < GetDate())
END
ELSE

SELECT CONVERT (INTEGER, unmanaged), caption
FROM dbo.Nodes
ORDER BY unmanaged

IF EXISTS (SELECT CONVERT (INTEGER, unmanaged), name FROM dbo.APM_Application WHERE (unmanaged=1 and UnManageUntil < GetDate()))
BEGIN
SELECT CONVERT (INTEGER, unmanaged), name
FROM dbo.APM_Application
WHERE (unmanaged=1 and UnManageUntil < GetDate())
END
ELSE

SELECT CONVERT (INTEGER, unmanaged), name
FROM dbo.APM_Application
ORDER BY unmanaged

I'd still appreciate it if anyone that has more SQL experience can tell me if there is a way to make the queries more efficient, but I think that in the current state they will work.

Petr.Vilem

Following query selects all applications that needs to be re-managed (is it what you mean by overrun state?). Important update is using GETUTCDATE() instead of GETDATE().

bobross

This would be two separate components, one for Nodes (NPM) and one for Applications (APM). The reason for creating these monitors is that from time to time a monitor will remain unmanaged even though the original unmanage duration has been exceeded.

As an example, we have Device01 that we unmanaged from 4:00pm until 5:00pm. It is now 5:30pm and Device01 is still showing unmanaged. We need a way to be alerted when this happens so that we can manually remanage the monitor. We've contacted Solarwinds support a few times and general consensus is that it can happen. The major concern is the impact this has on monitoring and the questions that are inevitably raised when a device goes down that should have been managed but wasn't.

I have only seen this happen with Nodes, but we want to include an additional monitor for Applications as well just to be on the safe side.

I don't think that any Joins should be needed for this as the monitors for Node and Application unmanage windows are separate.

What exactly is the reason for suggesting GETUTCDATE over GETDATE? I've read the documentation for each and aside from using UTC time there seems to be absolutely no difference.

Petr.Vilem

The reason for using JOIN here when we are talking about applications is that application managed status depends on its node managed status. In other words, application cannot get managed unless its node is managed.

The reason for using GETUTCDATE over GETDATE is that applications UnmanagedFrom and UnmanageUntil times are stored in UTC in database so GETDATE would work correctly only for servers in GMT timezone.