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

SAM/APM SQL Report Writer Help

grantallenby

Hey All,

Hoping someone can help....

I have this so far..

SELECT TOP 10000 Nodes.Caption AS NodeName,

APM_AlertsAndReportsData.ApplicationName AS Application_Name,

APM_AlertsAndReportsData.ComponentName AS Component_Name,

FROM

Nodes INNER JOIN APM_AlertsAndReportsData ON (Nodes.NodeID = APM_AlertsAndReportsData.NodeId)

WHERE

(

(Nodes.Comments LIKE '%innovia%')

)

However I need to find away to count how many Components are in the report. I figured I could try a ComponentName COUNT() however it can't find away it will bring back a count. I can export the report into Excel and just check the last row number, however I really would like a running count field in the report; It just looks cleaner.

Any idea's?

Find more posts tagged with

report writer

sql

apm

solarwinds sam

Accepted answers

pavel.grim

So you want to have each component numbered?

Like this (?):

SELECT Nodes.Caption AS NodeName,

APM_AlertsAndReportsData.ApplicationName AS Application_Name,

APM_AlertsAndReportsData.ComponentName AS Component_Name,

ROW_NUMBER() OVER (ORDER BY Nodes.Caption, APM_AlertsAndReportsData.ApplicationName, APM_AlertsAndReportsData.ComponentName) as [Count]

FROM Nodes

INNER JOIN APM_AlertsAndReportsData ON Nodes.NodeID = APM_AlertsAndReportsData.NodeId

WHERE Nodes.Comments LIKE '%innovia%'

ORDER BY Nodes.Caption, APM_AlertsAndReportsData.ApplicationName, APM_AlertsAndReportsData.ComponentName

All comments

pavel.grim

What exactly are you trying to report? Number of components per application and per node? Something like this would do that:

SELECT TOP 10000 Nodes.Caption AS NodeName,

APM_AlertsAndReportsData.ApplicationName AS Application_Name,

COUNT(*) as ComponentCount

FROM

Nodes INNER JOIN APM_AlertsAndReportsData ON (Nodes.NodeID = APM_AlertsAndReportsData.NodeId)

WHERE

(

(Nodes.Comments LIKE '%innovia%')

)

GROUP BY Nodes.Caption, APM_AlertsAndReportsData.ApplicationName

If that is the case, I recommend removing the TOP 10000 from the select clause.

grantallenby

It wasn't, However that has proved very useful. So thank you for that as we did need a quick way of telling how many components are on each node.

I just need and running total field. IE every time a Component name is listed there will be a column that counts, so it will just go 1,2,3,4, ect

I have this at the moment..... it is just a snip it there are a few thousand

APM Components
NodeName	Application_Name	Component_Name
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Service: Windows Time
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Service: Cluster Service
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Messages Outstanding
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Resource Control Manager: Groups Online

What I'm after is a 4th column that is a running count, I have just edited the one below in excel to show you an example, however I ideally would want the report to do it for me.

APM Components
NodeName	Application_Name	Component_Name	Count
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Service: Windows Time	1
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Service: Cluster Service	2
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Messages Outstanding	3
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Resource Control Manager: Groups Online	4

Once again Thank you for your help. SQL is not my Specialty

pavel.grim

Okay, adding back the component name for you:

SELECT Nodes.Caption AS NodeName,

APM_AlertsAndReportsData.ApplicationName AS Application_Name,

APM_AlertsAndReportsData.ComponentName AS Component_Name,

COUNT(*) as [Count]

FROM Nodes

INNER JOIN APM_AlertsAndReportsData ON Nodes.NodeID = APM_AlertsAndReportsData.NodeId

WHERE Nodes.Comments LIKE '%innovia%'

GROUP BY Nodes.Caption, APM_AlertsAndReportsData.ApplicationName, APM_AlertsAndReportsData.ComponentName

And for better report on number of components on each node, you can use:

SELECT Nodes.Caption AS NodeName,

COUNT(*) as [Count]

FROM Nodes

INNER JOIN APM_AlertsAndReportsData ON Nodes.NodeID = APM_AlertsAndReportsData.NodeId

WHERE Nodes.Comments LIKE '%innovia%'

GROUP BY Nodes.Caption

Hope it helps .

grantallenby

Your a star, Thank you for your help Would be lost if people weren't so helpful on Thwack.

grantallenby

Just a quick one,

I've applied the top SQL you sent me however I don't get a running total. I have tried to have a look myself but failed to get it to keep a running total. I just get the below. Ideally the count needs to say 1,2,3,4 ect. Sorry to be a pain.

Test count

NodeName	Application_Name	Component_Name	Count
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Backup and restore functionality problems	1
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Cluster network connectivity problems	1
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Cluster service startup problems	1
LYNCSQL-CLUSTER	Microsoft Windows Server 2008 Failover Cluster	Cluster shared volume functionality problems	1