Application Status Report - Need to select most recent notes

Question

We have a report that lists Applications that are in any state other than 'Up' (ie. Warning, Critical, Down); and includes any notes that might be in the "Last XX Notes" field from the Summary page of the affected node. Unfortunately, this creates duplicate listings on the report because it's listing the application/node for each of the notes listed. I need to be able to change this so that it only shows the application/node one time with the most recent note, however, I'm not very proficient with SQL/SWQL. I was hoping someone might be able to suggest how to modify the report's SQL statement to achieve this goal.

Here is the SQL statement being used:

SELECT  Name,  Status, ISNull(NN.Note, '<none>') AS  NodeNotes, DetailsUrl,  Application.Node.DisplayName,  ToLocal(AStat.LastSuccessfulPoll) AS LastGoodPoll,  Application.Node.NodeID,   Application.Node.DetailsUrl  AS  NodeDetailsUrl,  Application.Node.Status  AS  NodeStatus,  Application.Node.ChildStatus 
FROM  Orion.APM.Application  
LEFT JOIN  Orion.NodeNotes   NN  ON  NN.NodeID  =  Application.Node.NodeID  
LEFT JOIN  Orion.APM.CurrentApplicationStatus   AStat  ON  AStat.ApplicationID  =  Application.ApplicationID 
WHERE  Status  <>  1

HillyBilly · Answer

Hi tvminser,

I hope I have understood your requirements. I could not work out if you wanted a Custom Query/Table on your Classic view or a Report.

The reason why you are getting duplicates is due to the node might have more than 1 Application assigned that could have a problem (not up)

Either Way, here is my solution for everything.

Custom Query in Classic View. Because of the Custom query limitations you can not use a group by Node and then the sub-applications per node.

Here is the SWQL query, I have set a Number of row to 5 in my demo.

SELECT n.Caption AS [Node]
    , n.DetailsUrl AS [_LinkFor_Node]
    , concat('/Orion/images/StatusIcons/Small-', n.StatusIcon ) AS [_IconFor_Node]
    , apma.Name AS [AppName]
    , apma.DetailsUrl AS [_LinkFor_AppName]
    , concat('/Orion/images/StatusIcons/Small-', apma.StatusDescription ,'.gif' ) AS [_IconFor_AppName]
    , ISNull(nn.Note, '<none>') AS NodeNotes
    , ToLocal(apmcas.LastSuccessfulPoll) AS LastGoodPoll

FROM  Orion.APM.Application AS apma
LEFT JOIN Orion.Nodes n ON n.NodeID = apma.NodeID 
LEFT JOIN Orion.NodeNotes nn ON nn.NodeID = n.NodeID
LEFT JOIN Orion.APM.CurrentApplicationStatus apmcas ON apmcas.ApplicationID = apma.ApplicationID 
WHERE apma.Status <> 1
Order BY [Node]

The Result.

If you create a Custom Table, you are able to group the Applications by Node, the down side to this is that the query Displays ALL the nodes.

In the Custom Query, edit the Data Source and select Advanced Database Query with the option of SWQL.

SELECT n.Caption AS [Node]
    , apma.DetailsUrl AS [_LinkFor_AppName]
    , apma.Status AS [ChildStatus]
    , n.DetailsUrl AS [_LinkFor_Node]
    , n.Status
    , n.ChildStatus
    , n.DetailsUrl
    , apma.Name AS [AppName]
    , apma.StatusDescription AS [AppDescription]
    , ISNull(nn.Note, '<none>') AS NodeNotes
    , ToLocal(apmcas.LastSuccessfulPoll) AS LastGoodPoll

FROM  Orion.APM.Application AS apma
LEFT JOIN Orion.Nodes n ON n.NodeID = apma.NodeID 
LEFT JOIN Orion.NodeNotes nn ON nn.NodeID = n.NodeID
LEFT JOIN Orion.APM.CurrentApplicationStatus apmcas ON apmcas.ApplicationID = apma.ApplicationID 
WHERE apma.Status <> 1
Order BY [Node]

in the Table Layout, select Node, AppName, AppDescription (Optional, if the icon is no good for you), NodeNote, LastGoodPoll.

click on the Advanced Option for both NODE and AppName and add 2x Display settings 1st Details Page Link with Enable tool tips, 2nd Status Icon (watermark)

Then Sort by Node and then AppName.

and Finally Group By Node.

The Result.

As for generating a report, as far as I am aware, you use the same code and technique as the Custom Table process.

Have fun