This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

NodeID showing NULL in Cloud.Azure.Instances table

WinterSoldier over 2 years ago

Hi,

I am trying to join Orion.nodes table with Cloud.Azure.Instances on NodeID. However, in few of my nodes, the NodeID is showing null which leads to incorrect output. Here's my below query:

SELECT 
n.CloudInstance.DisplayName AS Nodename
,n.IPAddress
,n.CPULoad
,n.PercentMemoryUsed
,n.StatusDescription
,n.DetailsUrl
,n.CloudInstance.Region AS CloudInstance_Region
,n.CloudInstance.SubnetId AS CloudInstance_SubnetId
,n.StatusIcon
,n.Status
,n.StatusLED
,AI.Resourcegroup as ResourceGroup
,n.CloudInstance.Type
,n.CPUCount
,round((TotalMemory/1024/1024/1024),1) as [RAM] 

FROM Orion.Nodes n 
Join Orion.Cloud.Azure.Instances AI 
--WHERE (n.CloudInstance.CloudAccountId = 1)

If I put a where condition of n.IPAddress like 10.100.% the output gives me as total of 50 nodes.

But if I execute the query from table Orion.Nodes where IPAddress like 10.100.%, total nodes are 70. The reason behind this difference indicates that those 20 nodes have NULL values in NodeID column under Cloud.Azure.Instances table.

Can anyone please help me with getting the correct output?

Thanks in advance.

Parents

0 christopher.t.jones123 over 2 years ago

WinterSoldier, try doing a left join instead of a join. This should return even the NULL values that are currently being excluded
Cancel
Vote Up 0 Vote Down

Cancel
0 WinterSoldier over 2 years ago in reply to christopher.t.jones123

christopher.t.jones123, I am not good in SQL but I checked that primary key in Orion.Nodes table is NodeID and primary key on Orion.Cloud.Instances / Orion.cloud.Azure.Instances is VirtualmachineID.

I need help in joining tables. Tried the query mentioned in above post, however due to NULL values of NodeID getting excluded, output doesn't come as expected. Left Outer Join also didn't work. Please help me if you have any clue.

Thanks in advace.
Cancel
Vote Up 0 Vote Down

Cancel
0 christopher.t.jones123 over 2 years ago in reply to WinterSoldier

WinterSoldier, can you repost the latest query you are working with? I dont have any cloud instances so it's hard for me to test but maybe I can work something similar out with a different table or something.
Cancel
Vote Up 0 Vote Down

Cancel

0 christopher.t.jones123 over 2 years ago in reply to WinterSoldier

WinterSoldier, can you try the below query and let me know if it outputs correctly? I dont have a cloud instance in my SolarWinds to test with.

SELECT 
AI.Node.CloudInstance.DisplayName AS Nodename
,AI.Node.IPAddress
,AI.Node.CPULoad
,AI.Node.PercentMemoryUsed
,AI.Node.StatusDescription
,AI.Node.DetailsUrl
,AI.Node.CloudInstance.Region AS CloudInstance_Region
,AI.Node.CloudInstance.SubnetId AS CloudInstance_SubnetId
,AI.Node.StatusIcon
,AI.Node.Status
,AI.Node.StatusLED
,AI.Resourcegroup as ResourceGroup
,AI.Node.CloudInstance.Type
,AI.Node.CPUCount
,round((AI.Node.TotalMemory/1024/1024/1024),1) as [RAM] 

FROM Orion.Cloud.Azure.Instances AI

Reply

0 christopher.t.jones123 over 2 years ago in reply to WinterSoldier

WinterSoldier, can you try the below query and let me know if it outputs correctly? I dont have a cloud instance in my SolarWinds to test with.

SELECT 
AI.Node.CloudInstance.DisplayName AS Nodename
,AI.Node.IPAddress
,AI.Node.CPULoad
,AI.Node.PercentMemoryUsed
,AI.Node.StatusDescription
,AI.Node.DetailsUrl
,AI.Node.CloudInstance.Region AS CloudInstance_Region
,AI.Node.CloudInstance.SubnetId AS CloudInstance_SubnetId
,AI.Node.StatusIcon
,AI.Node.Status
,AI.Node.StatusLED
,AI.Resourcegroup as ResourceGroup
,AI.Node.CloudInstance.Type
,AI.Node.CPUCount
,round((AI.Node.TotalMemory/1024/1024/1024),1) as [RAM] 

FROM Orion.Cloud.Azure.Instances AI

Children

No Data