8 Replies Latest reply on Aug 21, 2014 3:59 PM by njoylif

Retrieving NodeID from the URL to match in a Custom Table's SQL Statement

nhess80 Aug 20, 2014 1:39 PM

I have a custom query that I would like to display the results based on the node I am currently viewing. Not all of them at once. I am having trouble with the syntax. Any help would be great

The syntax in red is what I am having an issue with. I can't seem to dynamically get the NodeID from the URL

SELECT c.NodeID,c.SysName,c.CustomPollerID,c.CustomPollerAssignmentID,d.RawStatus,RowID

FROM [SolarWindsOrion].[dbo].[CustomPollerStatus] d

LEFT JOIN(

SELECT b.*,InterfaceName

FROM [SolarWindsOrion].[dbo].[Interfaces] c

LEFT JOIN(

Select a.*, b.CustomPollerID,b.CustomPollerAssignmentID

FROM [SolarWindsOrion].[dbo].[CustomPollerAssignment] b

LEFT JOIN(

SELECT NodeID,SysName

FROM [SolarWindsOrion].[dbo].[Nodes] a

Where Vendor = 'Accedian'

) a

ON b.NodeID = a.NodeID

Where b.CustomPollerID IN ('6A74556A-96F5-47EE-8B06-6C66E883FDE3','3D843627-92A1-4840-B5C4-9846F072CB12')

) b

ON b.NodeID = c.NodeID

Where b.NodeID IS NOT NULL

) c

ON c.CustomPollerAssignmentID = d.CustomPollerAssignmentID

Where c.NodeID IS NOT NULL AND RawStatus IS NOT NULL AND NodeID = Request.QueryString("NetObject")

Group by NodeID, SysName, CustomPollerID,c.CustomPollerAssignmentID, RawStatus,RowID

Order by c.CustomPollerID,c.CustomPollerAssignmentID,d.RawStatus,d.RowID

Re: Retrieving NodeID from the URL to match in a Custom Table's SQL Statement

njoylif Aug 20, 2014 1:43 PM (in response to nhess80)

try NodeID=${NodeID}
Like (0)

Actions
- Re: Retrieving NodeID from the URL to match in a Custom Table's SQL Statement
  
  nhess80 Aug 20, 2014 1:47 PM (in response to njoylif)
  
  Already tried that... Keeps giving me query not valid.... Seems like this should be simple to do but cannot figure it out
  
  Like (0)
  
  Actions
  - Re: Retrieving NodeID from the URL to match in a Custom Table's SQL Statement
    
    HolyGuacamole Aug 21, 2014 4:17 AM (in response to nhess80)
    
    From the admin guide
    Note: Web-based reports do not allow the use of variables that are created by preceding a property or database column name with a dollar sign ($) and an open brace ({) and then following it with a close brace (}), as in ${Node.LastBoot}.
    So, you will not be able to reference macros in a Custom Table.
    
    You can add a 'Custom Query' resource to a Node Details View, where you should be able to display this information using SWQL (SolarWinds Query Language). SWQL is slightly different from SQL
    
    SELECT Caption FROM Nodes
    
    in SQL
    would be
    
    SELECT Caption FROM Orion.Nodes
    
    in SWQL
    
    To show this information just for the node in the node details view, the SWQL would be
    
    SELECT Caption FROM Orion.Nodes WHERE NodeID='${NodeID}'
    
    Like (1)
    
    Actions
    - Re: Retrieving NodeID from the URL to match in a Custom Table's SQL Statement
      
      nhess80 Aug 21, 2014 10:45 AM (in response to HolyGuacamole)
      
      Thank you for the response. I was confused about the SQL vs SWQL. I tried what you said and just used the query you provided but it just returns all nodes. If I am viewing the details page of Node #1 I don't want it to display anything but Node #1. The query that I ran below displays all nodes no matter which node I am viewing.. Hope that makes sense.. You have definitely helped me take a step in the right direction
      
      SELECT Caption
      FROM Orion.Nodes
      WHERE NodeID=${NodeID}
      
      Like (0)
      
      Actions
      - Re: Retrieving NodeID from the URL to match in a Custom Table's SQL Statement
        
        HolyGuacamole Aug 21, 2014 10:58 AM (in response to nhess80)
        
        Are you sure you added that test Custom Query object in a Node Details view? Anyway, I thought you are trying to show the data for a specific custom interface poller in a node details view. If you are happy to see data for all the interface pollers for any given node, there shouldn't be a need to use SQL / SWQL. Simply add a Custom Table resource to your Node Details view. The default selection for the data source is the node you would be looking at (i.e.,NodeID filter will be applied automatically). Simply choose the columns you need to see from the Custom Interface Pollers category of column names.
        
        Like (0)
        
        Actions
        
        Re: Retrieving NodeID from the URL to match in a Custom Table's SQL Statement
        
        nhess80 Aug 21, 2014 11:12 AM (in response to HolyGuacamole)
        
        I was using a custom table and writing the query in there.. But you are correct, when I added a custom query to the details page it worked as it should.. Do you know why it would work differently with the custom table vs custom query? Thanks again.. now I just need to rewrite my statement a bit so much appreciated
        1 of 1 people found this helpful
        
        Like (0)
        
        Actions
        
        Re: Retrieving NodeID from the URL to match in a Custom Table's SQL Statement
        
        nhess80 Aug 21, 2014 12:18 PM (in response to HolyGuacamole)
        
        Is there a reason that I am only able to query the Nodes Table or am I wrong. Why does the first query work but the second does not? Thanks
        
        SELECT NodeID
        FROM Orion.Nodes
        
        SELECT CustomPollerAssignmentID
        FROM Orion.CustomPollerAssignment
        
        Like (0)
        
        Actions
        
        Re: Retrieving NodeID from the URL to match in a Custom Table's SQL Statement
        
        njoylif Aug 21, 2014 3:59 PM (in response to nhess80)
        
        you have to look at the SWQL schema docs to figure out how to query correctly....I think...
        i.e.:
        CustomPollerAssignmentOnNode Orion.NPM.CustomPollerAssignmentOnNode Defined by relationship Orion.NodesHostsCustomPollerAssignmentOnNode (System.Hosting)
        1 of 1 people found this helpful
        
        Like (0)
        
        Actions

Go to original post

CustomPollerAssignmentOnNode	Orion.NPM.CustomPollerAssignmentOnNode	Defined by relationship Orion.NodesHostsCustomPollerAssignmentOnNode (System.Hosting)