cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Backups - and Reports

Jump to solution

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?

0 Kudos
1 Solution
Level 11

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.

 

View solution in original post

3 Replies
Level 11

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.

 

View solution in original post

Thank you @mjperkins I will give this a go later (probably next week now) and see how I get on

0 Kudos

And having made the necessary changes to the report this now works beautifully so will accept your answer as the solution.

Many thanks.

0 Kudos