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

Node Details URL in custom SQL Report

Jump to solution

I make a lot of custom SQL reports now that the Web Report Designer allows me to publish them with more style than before.  However, I am not able to get the nice automatic Node Details links to appear for each returned Node.  I looked at several .OrionReport files and these all appear to have some complex XML formatting to handle the URL.  Thing is, the Custom Table editor has a "Display Setting" for "Display Page Link", but I keep getting the following error...


URL - 'Details Page Link' data presenter requires 'db|DetailsUrl' field(s) to be present within the datasource


What should I query and how should I format it to get this column to do what was available in the original Report Writer?

Tags (1)
1 Solution

getting warmer - I was able to get yours to work with

SELECT DISTINCT

   

   nodes.caption

   ,'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+CAST(Nodes.NodeID as varchar(256)) as 'DetailsURL'


Then when you select columns skip the detailsurl or you can hide it - add Details Page Link display setting to your caption column and it should no longer complain since it finds a detailsURL column to pull from. LMK

View solution in original post

29 Replies
Level 10

Resurrecting an old topic.

I'm so close to getting this working with all the help found on here.

The results preview great!

Results work.jpg

But when adding the details link to the table I get this:

Ordinal.jpg

Any ideas?

0 Kudos

It looks like you enabled the Details Page URL on the Interface column.  This line is specific to NODES ONLY.

,'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+CAST(Nodes.NodeID as varchar(256)) as 'DetailsURL'

You could replace it with this if your report is for Interfaces.

,'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=I:'+CAST(Interfaces.InterfaceID as varchar(256)) as 'DetailsURL'

Should be noted that the "DetailsURL" can only be set to one element type.

Hi,

I also wanted to have hyperlink which direct to node details page, I tried it but did not worked for me.

Could you please tell me which element IP to use for node details.

Should be noted that the "DetailsURL" can only be set to one element type.

0 Kudos

Hi gunjan1402

If you are using a Custom SQL Query Report then you need to take care of the following :-

1.) Use Distinct while using the Select statment.

2.) for enabling the link to the nodes in the SQL report you need to add Details URL to your DataSource.

3.) For Adding the Details URL page you simple need to Add the following in the SQL Query in your Select Statement

     '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+CAST([NodesData].[NodeID] AS varchar(256)) As DetailsURL

Note: Here you need to pick the NodeID from Nodes.Data Table.

Hope This Helps

I think that I'm very close, but I'm getting the following Error:  "Ordinal '7' exceeds the maximum number"

Any ideas?  BTW, I'm loving your post.

pastedImage_0.png

pastedImage_1.png

This is my sql:

SELECT  distinct

  swNodes.Caption as Name,

    Vendor,

    swNodes.Address_Locations,

interfaces.statusLED,

  InterfaceName,

  interfaces.caption as NodeName,

    '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=I:'+CAST(interfaces.InterfaceID AS varchar(256)) As DetailsURL,

  Interfaces.InterfaceSpeed / 1000000 as "Interface Speed"

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

join [SolarWindsOrion].[dbo].[Nodes] swNodes on [SolarWindsOrion].[dbo].[Interfaces].NodeID = swNodes.NodeId

 

  where

        (swNodes.Caption like 'AFGGA12%' or

        swNodes.Caption like 'AFGGA23%' or

        swNodes.Caption like 'AFGGA24%' or

        swNodes.Caption like 'AFGGA37%' or

        swNodes.Caption like 'AFGGA51%' or

        swNodes.Caption like 'AFGGA57%' )

        and (interfaces.Caption = 'GigabitEthernet0/0 · level 3 circuit'

         or interfaces.Caption = 'sp_wan'

         or interfaces.Caption = 'GigabitEthernet0/0 · Level 3 Circuit'

         or interfaces.Caption = 'GigabitEthernet0/2 · Level 3 Circuit')

           

Order by swnodes.Caption

0 Kudos

johnnygeesw​, off top of my head, I want to say that has to do with some math...i.e., remove your /1000000 for interface speed and see if query works then.  after figuring out where issue is, you can then figure out how to fix the issue.

0 Kudos

The query runs fine in my SQL Studio.  It was blank due to the WHERE clause, but even commenting those out, I get data back, no errors.  Does your work in Studio, but just not in report writer?

0 Kudos

This worked, exactly as expected.!

0 Kudos
Level 9

Use the DetailsURL as sugested in this thread I can generate the column with the URL without issue, however, when I actually apply the detail link to done of the columns I recieve and error. The details of the error are as follows.

Anyone have any ideas on how to get this working for me?

Thank you!

0 Kudos

you can also use concat...basically you just add the HTML code into the SQL query code

CONCAT('<a href="https://thwack.solarwinds.com/Orion/View.aspx?NetObject=N:',n.nodeid,'">',n.caption,'</a>') as Caption,

Cool stuff njoylif

Here is what I used to show the notes on my nodes as a resource in summary page:

--------------------------------------------

SELECT CONCAT('<a href="https://thwack.solarwinds.com/Orion/View.aspx?NetObject=N:',a.nodeid,'">',b.caption,'</a>') as 'Caption',

a.Note,a.AccountID,a.TimeStamp

FROM NodeNotes a

INNER JOIN NodesData b on a.NodeID=b.NodeID

ORDER BY a.TimeStamp DESC

--------------------------------------------

njoylif,

I'm hoping you can answer this question.  Do you know how to create a DetailsURL for EOC Nodes and Interfaces in SQL or SWQL?  I can't seem to figure it out.

Orion allows you to Cast the NodeID onto the end of the URL but EOC requires the Orion ID in the URL (see below)

Nodes - https://eoc.xxxxxx.com/Orion/37/Orion/View.aspx?NetObject=N:174

Interfaces - https://eoc.xxxxx.com/Orion/37/Orion/View.aspx?NetObject=I:16193

Unfortunately, I don't know how to add the OrionID (37).  Would appreciate anyone that can show me how this is done.  Thanks much!

0 Kudos

I think, basically, you'd use concat...see below

CONCAT('https://[your URL]/Orion/',${NodeID},'/Orion/View.aspx?NetObject=N:',${[objectID macro]}) as DetailsURL

0 Kudos

Hey njoylif,

I tried that on an EOC SWQL window and it's not working.

0 Kudos

SWQL does not handle concat but if they are the type of object then a simple + will work.

Example- constructing a SWQL field for node url or such

,'http://yourserverip/Orion/whatever.aspx?N:' + ToString(NodeID) AS

The ToString function will allow the integer node ID to be concatenated with the string you type in, this case the beginning of a URL

Work for you?

0 Kudos


bluefunelemental - Thanks for the response but unfortunately EOC SWQL doesn't support ToString functions.  I wish SolarWinds would advise exactly what functions it does support though.  It sure would make things easier.

0 Kudos

Detrioter,

when available add in the nodes.detailsurl column then your page link will magically work. Nodes and interfaces has it but others might not- I have had some luck in building my own column and calling it detailsurl and working.

Which table is your custom SQL coming from?

0 Kudos

Is that a macro?  Nodes.DetailsURL is not a column in my Nodes table.  Below is a section on my query.  I union that with several other for Node alerts, APM alerts, etc.  i even built my own correct URL, but still cannot get it act as a hyperlink.

SELECT DISTINCT 

     

   nodes.caption

   --,'https://ORION-Web-Server/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+CAST(Nodes.NodeID as varchar(256)) as 'URL'

      ,[ObjectName]

   ,AlertDefinitions.AlertName

      ,[TriggerTimeStamp]

   ,nodes.Platform_Node_Contact as 'Platform Contact'

   ,volumes.Additional_Volume_Contact_1 as 'Additional Contact 1'

   ,volumes.Additional_Volume_Contact_2 as 'Additional Contact 2'

   ,volumes.Additional_Volume_Contact_3 as 'Additional Contact 3'

   ,convert(char(10), [TriggerTimeOffset] / (60 * 60 * 24))+':'+convert(char(10), dateadd(s, [TriggerTimeOffset], convert(datetime2, '0001-01-01')), 108)

  FROM

  [DL-Orion].[dbo].[AlertStatus]

  ,[DL-Orion].dbo.Volumes

  ,[DL-Orion].dbo.AlertDefinitions

  ,[DL-Orion].dbo.Nodes

  where

  (volumes.VolumeID = AlertStatus.ActiveObject

    AND AlertStatus.ObjectType = 'Volume'

  AND Nodes.NodeID = Volumes.NodeID

  AND AlertStatus.AlertDefID = AlertDefinitions.AlertDefID

  AND AlertStatus.Acknowledged != 1

  )

0 Kudos

getting warmer - I was able to get yours to work with

SELECT DISTINCT

   

   nodes.caption

   ,'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+CAST(Nodes.NodeID as varchar(256)) as 'DetailsURL'


Then when you select columns skip the detailsurl or you can hide it - add Details Page Link display setting to your caption column and it should no longer complain since it finds a detailsURL column to pull from. LMK

View solution in original post