SQL Web Report Syntax Help?

dc4networks

I trust all is well my SolarWinds THWACK Companions...

Are you able to help me with understanding and knowing what SQL Syntax I need to use to ideally get the results for Lines 11, 13 and or 14?

==============================================================

01 SELECT n.Location AS 'Site Location',

02    n.Caption AS 'Node Name',

03    n.IP_Address AS 'IP Address',

04    n.Vendor AS 'Vendor',

05    n.MachineType AS 'Machine Type',

06    nep.Model AS 'Node Model Type',

07    n.IOSImage AS 'IOS Image',

08    n.IOSVersion AS 'IOS Version',

09    ncc.chassisID AS 'Node Chassis ID',

10    ncc.chassisserialnumber AS 'Node Chassis Serial No.',

11    n.SystemUpTime AS 'Node Up Time',

12    n.LastBoot AS'Node Last Boot Up',

13    n.Status AS 'Node Status',

14    n.StatusLED AS 'Node Status LED',

15    n.Contact AS 'Support Team',

16    n.EngineID AS 'SWO Poller'

17    n.NodeID AS 'SWO Node ID'

18

19 FROM Nodes n

20

21 INNER JOIN NCM_NodeProperties ncm ON n.NodeID=ncm.CoreNodeID

22 INNER JOIN NCM_Cisco_Chassis ncc ON ncm.NodeID=ncc.NodeID

23 INNER JOIN NCM_Entity_Physical nep ON nep.NodeID=ncc.NodeID

24

25 ORDER BY

26 [Location], [Caption] ASC

==============================================================

Line 11;

How to display this as (YY:DD:)HH:MM:SS?

What SQL Syntax needs to added?

Line 13 and or 14;

What SQL Syntax can be used to show the Node Status Up, Down or Unmanaged replacing the 1, 2, 9

Or Line 14; Removing the *.gif?

Only 1 of these lines will be needed or wanted.

In the meantime I am now reading through the SAMs SQL in 24 Hours…

I will look forward to your feedback and recommendation to alternate SQL Syntax Code if necessary.

In addition, any recommendations on SQL Books or Commands Reference to help with the not so simple SolarWinds 'Out the Box' Reports.

Kind Regards,

DC4Networks

Confucius Says;

“The man who moves a mountain begins by carrying away small stones”.

ALL NODEs Inventory v0.2a.txt

simonp73

Ok so heres what I've managed to work out.

You need to Join the StatusInfo table to be able to pull back the status description rather than the value.

In order to get the system up time as days hours minutes second you need to do the bit of conversion on the seconds value contained in the table.  I've only managed to do this to days rather than years as i'm having difficulty working on the leap years part

So the query should be:

SELECT n.Location AS 'Site Location',

n.Caption AS 'Node Name',

n.IP_Address AS 'IP Address',

n.Vendor AS 'Vendor',

n.MachineType AS 'Machine Type',

nep.Model AS 'Node Model Type',

n.IOSImage AS 'IOS Image',

n.IOSVersion AS 'IOS Version',

ncc.chassisID AS 'Node Chassis ID',

ncc.chassisserialnumber AS 'Node Chassis Serial No.',

--n.SystemUpTime AS 'Node Up Time',

cast (floor(n.SystemUpTime /86400) As varchar(10))+'d ' +convert(varchar(8), dateadd(second, n.SystemUpTime, '19000101'), 8) AS 'Node Up Time'',

n.LastBoot AS'Node Last Boot Up',

--n.Status AS 'Node Status',

ns.ShortDescription AS 'Node Status',

--n.StatusLED AS 'Node Status LED',

n.Contact AS 'Support Team',

n.EngineID AS 'SWO Poller',

n.NodeID AS 'SWO Node ID'

FROM Nodes n

INNER JOIN NCM_NodeProperties ncm ON n.NodeID=ncm.CoreNodeID

INNER JOIN NCM_Cisco_Chassis ncc ON ncm.NodeID=ncc.NodeID

INNER JOIN NCM_Entity_Physical nep ON nep.NodeID=ncc.NodeID

INNER JOIN StatusInfo ns ON n.Status=ns.StatusId

ORDER BY

[Location], [Caption] ASC