Inventory EOS and EOL Custom Table

Isn't almost all of that information in Cirrus.Nodes?

SELECT [Nodes].NodeCaption
      , [Nodes].AgentIP
      , [Nodes].Vendor
      , [Nodes].MachineType
      , [Nodes].OSImage
      , [Nodes].OSVersion
      , [Nodes].EndOfSupport
      , [Nodes].EndOfSales
      , [Nodes].EndOfSoftware
      , [Device].Serial
FROM Cirrus.Nodes AS [Nodes]
LEFT JOIN Cirrus.EntityPhysical AS [Device]
      ON [Nodes].NodeID = [Device].Serial

I am getting an error when I cut and paste into a Custom Query. (SWQL)

The JOIN was not correctly mapped, try this

SELECT [Nodes].NodeCaption
      , [Nodes].AgentIP
      , [Nodes].Vendor
      , [Nodes].MachineType
      , [Nodes].OSImage
      , [Nodes].OSVersion
      , [Nodes].EndOfSupport
      , [Nodes].EndOfSales
      , [Nodes].EndOfSoftware
      , [Device].Serial
FROM Cirrus.Nodes AS [Nodes]
LEFT JOIN Cirrus.EntityPhysical AS [Device]
      ON [Nodes].NodeID = [Device].NodeID

thanks, sample output.  It will list the device several times.  

I didn't look at the output or what you were trying to do, just fix the query to allow it to output.

If you look at the Cirrus.EntityPhysical entity, you will see there are multiple rows for each of the physical elements on the node. Therefore, you simply need to add a WHERE clause to restrict what you actually want to see. For example, you may only be interested in the Chassis, in which case the clause would be

WHERE [Device].EntityClass LIKE '%chassis%'

More likely, just interested in devices and the elements that have serial numbers, so this would be 

SELECT [Nodes].NodeCaption
      , [Nodes].AgentIP
      , [Nodes].Vendor
      , [Nodes].MachineType
      , [Nodes].OSImage
      , [Nodes].OSVersion
      , [Nodes].EndOfSupport
      , [Nodes].EndOfSales
      , [Nodes].EndOfSoftware
      , [Device].Name
      , [Device].Serial
FROM Cirrus.Nodes AS [Nodes]
RIGHT JOIN Cirrus.EntityPhysical AS [Device]
      ON [Nodes].NodeID = [Device].NodeID
WHERE [Device].Serial <> ''

Thanks m_roberts.  That was obviously a bad edit considering I don't have any matching data at all in my lab and I was trying to remember the linkages.

Thanks, Works like a champ. 

Sidebar: You might want to change the WHERE clause (to trap for NULLs).  It's generally best practices because NULL is not the same as an empty string ('').

WHERE [Device].Serial <> ''

to

WHERE IsNull([Device].Serial, '') <> ''

Will do. thanks,

I have a variation on the code here I would like to make but have trouble sorting out the AND's and NOT's.  

I was able to do this on a per site basis, but still need a full list.  Because I have so many devices the list only include something like the first 100 devices and does not provide any additional data.  I though if I pulled out my core site, then I could do two separate lists.  Datacenter and remote offices.  Thoughts?  christopher.t.jones123 sum_giais John W Johnson

SELECT [Nodes].NodeCaption
      , [Nodes].AgentIP
      , [Nodes].Vendor
      , [Nodes].MachineType
      , [Nodes].OSImage
      , [Nodes].OSVersion
      , [Nodes].EndOfSupport
      , [Nodes].EndOfSales
      , [Nodes].EndOfSoftware
      , [Device].Serial
FROM Cirrus.Nodes AS [Nodes]
RIGHT JOIN Cirrus.EntityPhysical AS [Device]
      ON [Nodes].NodeID = [Device].NodeID
WHERE [Device].EntityClass LIKE '%chassis%' AND (NOT [Nodes].NodeCaption = '%ENAU%')