Here is the script that our developers created, which is based on one I found in Thwack:
SET NOCOUNT OFF
SET ROWCOUNT 0
Nodes.IP_Address as 'IP Address',
Nodes.Caption AS Hostname,
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')
I.Caption AS Interface_Caption,
(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?
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,
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.
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.
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'
Oh that's beautiful! Going to have to try that...