I need a report on of systems with no System Application Monitors assigned. Have spent a week on it and running out of brain.. Any hints, help, at all will be sincerely appreciated.

RobSchlick

Can't find a report of Nodes with no System Application Monitors assigned. Not even any that I can fudge/modify. UGH.

Find more posts tagged with

sam

report.

Accepted answers

rmcder

Here is what I use for similar report:

SELECT     Nodes.Caption
FROM       APM_Application RIGHT OUTER JOIN
           Nodes ON APM_Application.NodeID = Nodes.NodeID
WHERE      (Nodes.NetworkCategory = 'Servers') AND (APM_Application.Name IS NULL)
ORDER BY   Nodes.Caption

This works for me. The Nodes.NetworkCategory is a custom property we assign to servers. You may or may not have the same criteria where you can filter out non-server nodes.

Hope this helps some...

All comments

RobSchlick

SELECT n.NodeID, n.Caption as Node, a.ID as ApplicationID, a.Name as [Application], c.ID as ComponentId, c.Name as Component, cts.Value as TemplateCredId, cs.[Value] as OverridenCredId, cred.Name as CredName FROM APM_Component c Right JOIN APM_Application a ON c.ApplicationID = a.ID LEFT JOIN Nodes n ON a.NodeID = n.NodeID LEFT JOIN APM_ComponentSetting cs ON cs.ComponentID = c.ID AND cs.[Key] = '__CredentialSetId' LEFT JOIN APM_ComponentTemplate ct ON ct.ID = c.TemplateID LEFT JOIN APM_ComponentTemplateSetting cts ON cts.ComponentTemplateID = ct.ID AND cts.[Key] = '__CredentialSetId' LEFT JOIN [Credential] cred ON cred.ID = ISNULL(cs.Value, cts.Value) This is what I have so far, still plugging away. Still muted progress.

rmcder

Here is what I use for similar report:

This works for me. The Nodes.NetworkCategory is a custom property we assign to servers. You may or may not have the same criteria where you can filter out non-server nodes.

Hope this helps some...

RobSchlick

Awesome. A tweak here and there and it snapped right in. I was looking in all the wrong places and joining backwards.

Thanks again!

rmcder

You are most welcome - thwack is a great resource.

AlexSoul

Hi Rob,

You may find the following helpful as well.

In your example this will only return nodes with no apps assigned at all, but what if you need to know which nodes do not have specific application assigned? This will not work if some other applications are assigned to node already?

The below solution is what I use for compliance reporting on unassigned applications:

SELECT * FROM NODES WHERE APPLICATION_TEMPLATE <> BLA_BLA_BLA (for compliance reasons)

Hope this helps,

Alex