7 Replies Latest reply on May 16, 2017 1:44 PM by ryan.davis26

    Adding DetailsURL to web-based SQL report

    mr.e

      I am having a bit of a hard time with the DetailsURL variable on web-based reports.  When I add it to my SQL script, I got Invalid Query error message. I already checked Thwack and the Orion online help and documents but I could not find the answer.  Is there a video or a document that shows several examples of how to use DetailsURL on a web-based SQL report?  Else, does anyone have any guidance? Thanks in advance. 

        • Re: Adding DetailsURL to web-based SQL report
          njoylif

          give us some more info...what is the sql script you are working with?

          check out this post

            • Re: Adding DetailsURL to web-based SQL report
              mr.e

              Here is the script that our developers created, which is based on one I found in Thwack:

               

              SET NOCOUNT OFF 

              SET ROWCOUNT 0 

               

              SELECT 

              nodes.NodeID,

                Nodes.IP_Address as 'IP Address',

                Nodes.Caption AS Hostname,

                Nodes.Region,

                Nodes.Status,

                Nodes.SITE_ID


                into #nodetemp


                FROM Nodes

              where  Nodes.Region NOT LIKE 'INDIA' AND

              Nodes.Region NOT LIKE 'DU' AND

              Nodes.Region NOT LIKE 'Internet' AND

              Nodes.Region NOT LIKE 'Datacenters' AND

              Nodes.Caption NOT LIKE 'usPSC%'

              AND  (Nodes.Status != '9')

               

              select n.NodeID,

                n.[IP Address],

                n.Hostname,

                n.Region,

                n.Status,

                n.SITE_ID,

              I.Caption AS Interface_Caption,

                I.InterfaceSpeed,

                I.interfaceid,

                (dbo.GetInBps95th30(InterfaceID) )AS Maxbps_In95,

                dbo.GetOutBps95th30(InterfaceID) AS Maxbps_Out95,

                dbo.GetMaxBps95th30(InterfaceID) AS Maxbps_95

               

                from Interfaces I, #nodetemp n

               

              where n.nodeid = i.NodeID

              and I.Caption LIKE '%MPLS%'

              ORDER BY Maxbps_In95 DESC

               

              drop table #nodetemp

               

              The report works great and it comes up fairly quickly (20 - 40 seconds).  Still, I would like to add a DetailsURL link to the hostname as well as the Interface_Caption fields.  That way, our network teams could get more details on the routers themselves or on the circuits for each of the routers.  Do you know how I could do this?

               

              Thanks!

                • Re: Adding DetailsURL to web-based SQL report
                  njoylif

                  you'd add a line under select statement like:

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


                  it really depends on what you are going to do with this.  If this is going to be report in browser, I'm not sure if that works.  if not, try:

                  '<a href="/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+CAST(Nodes.NodeID as varchar(256))'">'+Nodes.Caption+'</a>' as HostName,


                    • Re: Adding DetailsURL to web-based SQL report
                      mr.e

                      Sorry, I tried both statements and I am still getting the Query is not valid error.    

                       

                      Is it possible that the DetailsURL variable is only available if I was using SWQL code instead?  My script is SQL and I don't think that SWQL has compatible variables for the ones currently in the SQL script.  I so wish that SW would release a detailed video, using both SWQL and SQL examples for using the DetailsURL variable.  I'd think there are many other Thwackers who would really appreciate that.

                       

                      Anyway, let me know if you have any other ideas for me to try.  Else, thanks and have a great weekend. 

                • Re: Adding DetailsURL to web-based SQL report
                  mr.e

                  I was wondering if any there were any other ideas on how to get DetailsURL added to my SQL report.  I would very much like to make this happen, with your help.  Thanks again.

                  • Re: Adding DetailsURL to web-based SQL report
                    jvaughan@connectria.com

                    I know this is an old thread, but I was able to add the following (adapted to your query) to get a DetailsURL field in a SQL query:

                     

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