Backups - and Reports

stuartd

I need some SQL help.

The default report "Backup Status of Running Config" utilises an 'advanced DB query' that goes beyond my current very low level coding skills. I understand what it is doing, but I don't know how to amend it.

The default one shows this:

SELECT OrionNodes.Caption, OrionNodes.DetailsUrl, OrionNodes.Status, OrionNodes.ChildStatus, OrionNodes.Vendor, OrionVendors.Icon, OrionNodes.IP_Address, OrionNodes.IPAddressType,OrionNodes.MachineType, NcmConfigArchive.DownloadTime   FROM NCM.NodeProperties AS NcmNodeProperties   INNER JOIN Orion.Nodes AS OrionNodes ON NcmNodeProperties.CoreNodeID=OrionNodes.NodeID  INNER JOIN Orion.Vendors AS OrionVendors ON OrionNodes.Vendor=OrionVendors.Name  LEFT JOIN NCM.ConfigArchive AS NcmConfigArchive ON NcmNodeProperties.NodeID=NcmConfigArchive.NodeID AND NcmConfigArchive.ConfigType='Running'

But I need to create a duplicate of this but that includes a filter to show only nodes that match a certain custom property. That CP is a Yes/No and is called "Node_in_BAU" - thoughts, help?

mjperkins

It looks like you will need two things:

1. an INNER JOIN section added to the end of the existing FROM clause. It would need to be something like:

   INNER JOIN Orion.NodesCustomProperties AS OrionNodesCustomProperties ON OrionNodes.NodeID = OrionNodesCustomProperties.NodeID

This will add the table with the custom properties to the query. Using an INNER JOIN will only bring in records from OrionNodesCustomProperties for nodes already in the query result.

2. After the SWQL query quoted, you will need a WHERE clause to define only the 'Node_in_BAU' = YES nodes:

   WHERE OrionNodesCustomProperties.Node_in_BAU = True

(This assumes a Boolean True/False property. If you use the words Yes and No, then replace True with 'Yes'.) This WHERE clause will restrict the results to only the records having your custom property set to 'Yes'/True.