Nodes with Threshold and Custom Properties

SW_United

Looking to create a report that will grab node information, cusom properties and threshold values;

I've got this far;

SELECT cp.Environment, n.Caption, n.Description, n.DetailsUrl, n.DisplayName, n.IP_Address                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
FROM Orion.Nodes AS n
JOIN Orion.NodesCustomProperties AS cp ON n.NodeID = cp.NodeID
WHERE cp.Environment = 'Production'

and did try to just add this line, but hasn't worked, it shows no results. But when I remove this I get results, so something to do with this JOIN and I think it might be field DisplayName?? not sure

JOIN Orion.NodesThresholds AS th ON n.DisplayName = th.DisplayName

Anyone got any ideas?

I would like to see node info, custom property and threshold warning and critical values for cpu and memory to start with (will look at volume later )

Lofstrand

Created below, hope that works for you. 

I don't have your custom property so I added "city" instead. Just change that to environment if you want that instead.

SELECT 
    n.CustomProperties.city 
    ,n.Caption
    ,n.Description
    ,n.DetailsUrl
    ,n.DisplayName
    ,n.IP_Address                                                              
    , CASE (TH_CPU.ThresholdType)
        WHEN 0 THEN 'Default'
        WHEN 1 THEN 'Custom'
    END AS [CPU_ThresholdType]
    ,TH_CPU.Level1Value AS CPUWarningThrehold
    ,TH_CPU.Level2Value AS CPUCriticalThrehold
    ,CASE (TH_MEM.ThresholdType)
        WHEN 0 THEN 'Default'
        WHEN 1 THEN 'Custom'
    END AS [MEM_ThresholdType]
    ,TH_MEM.Level1Value AS MemWarningThrehold
    ,TH_MEM.Level2Value AS MemCriticalThrehold
FROM Orion.Nodes AS n
INNER JOIN Orion.Thresholds AS TH_CPU ON N.nodeid=TH_CPU.InstanceId AND TH_CPU.Name='Nodes.Stats.CpuLoad'
INNER JOIN Orion.Thresholds AS TH_MEM ON N.nodeid=TH_MEM.InstanceId AND TH_MEM.Name='Nodes.Stats.PercentMemoryUsed'
WHERE 
    n.CustomProperties.City='Örebro'