0

This is a follow-up to my earlier question Find max count of query (SQL Server 2016)

Q1) How do I get the NIC count per virtual machine? e.g.

VirtualMachineName  vCenter    Cluster      NicCount
------------------  ---------  -----------  --------
virtualMachine1     vCenter1   Cluster1a           1
virtualMachine2     vCenter1   Cluster1b           1
virtualMachine3     vCenter2   Cluster2a           1
virtualMachine4     vCenter1   Cluster1b           2

This is the query so far:

SELECT
  VirtualMachine.VirtualMachineName,
  Agent.EndpointName AS vCenter,
  Host.HostName AS [vSphere Cluster],
  (
    SELECT count( VMToNetwork.MacAddress )
    FROM   VirtualMachine AS VirtualMachineInner
            INNER JOIN VMToNetwork
              ON VirtualMachineInner.VirtualMachineID = VMToNetwork.VirtualMachineID
    WHERE VirtualMachineInner.VirtualMachineID = VirtualMachine.VirtualMachineID 
  ) AS NicCount
FROM
  VirtualMachine 
   INNER JOIN Host
    ON VirtualMachine.HostID = Host.HostID
     INNER JOIN Agent
      ON Host.ProvisioningAgentID = Agent.AgentID
GROUP BY
  Agent.EndpointName,
  Host.HostName,
  VirtualMachine.VirtualMachineName, 
  VirtualMachine.VirtualMachineID,
  VirtualMachine.IsManaged
HAVING
  (VirtualMachine.IsManaged <> 1)
ORDER BY
  VirtualMachine.VirtualMachineName,
  vCenter,
  [vSphere Cluster]

The problem is that NicCount has a value of zero for some rows. I know this is not correct because this query shows that all virtual machines have at least one NIC:

SELECT
  VirtualMachineInner.VirtualMachineName,
  count( VMToNetwork.MacAddress )
FROM
  VirtualMachine AS VirtualMachineInner
   INNER JOIN VMToNetwork
    ON VirtualMachineInner.VirtualMachineID = VMToNetwork.VirtualMachineID
--WHERE VirtualMachineInner.VirtualMachineID = VirtualMachine.VirtualMachineID 
GROUP BY
  VirtualMachineInner.VirtualMachineName

If the WHERE clause is uncommented NicCount = NULL instead NicCount = 0.

Thanks in advance.

Edit 2016-03-05 to change name from "How to get NIC count from query that includes two inner joins (SQL Server 2016)" which might have obscured the question.

Edit 2018-03-08: As suggested I am including sample data:

Table: VirtualMachine

VirtualMachineID   VirtualMachineName  HostId  IsManaged
----------------   ------------------  ------  ---------
               0   virtualMachine1          0          0
               1   virtualMachine2          1          0
               2   virtualMachine3          3          0
               3   virtualMachine4          1          0
               4   virtualMachine5          3          1

Table: Host

HostId  Hostname   ProvisioningAgentID
------  ---------- -------------------
     0  Cluster1a                    0
     1  Cluster1b                    0
     2  Cluster2a                    1

Table: VMToNetwork

VirtualMachineID  MacAddress
----------------  ----------
               0    MacAddr1
               1    MacAddr2
               2    MacAddr3
               3    MacAddr4
               3    MacAddr5
               4    MacAddr6

Table: Agent

AgentID  AgentName
--------  ---------
       0  vCenter1
       1  vCenter2
aenagy
  • 37
  • 6
  • The solutions provided in the other question don't work here which is why I asked this question separately. I believe that the difference is that the outer query, relative to the NIC count, is the result of two unrelated inner joins. As stated in this question, for some records the result is null which I know is not correct. – aenagy Mar 05 '18 at 17:00
  • Ah, yes, I tried several times to accept the first answer, The response I get back is: Thanks for the feedback! Votes case by those with less than 15 reputation are recorded, but do not change the publicly displayed post score. – aenagy Mar 05 '18 at 17:15
  • 1
    That worked. Answer now has green check-mark. Thanks. – aenagy Mar 05 '18 at 18:10
  • OK. Now that has been sored out, does anyone have a suggestion on how to fix this? – aenagy Mar 05 '18 at 19:33
  • Plz take time to go thru this link: https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – TheGameiswar Mar 05 '18 at 19:39
  • Pasting expected result,actual result along with test data and query helps you get upvotes and fast answers as well – TheGameiswar Mar 05 '18 at 19:40
  • Imagine yourself at the other end trying to answer the question with out any back ground info – TheGameiswar Mar 05 '18 at 19:42

0 Answers0