Inventory EOS and EOL Custom Table

CourtesyIT

All,  I am looking for some SWQL/SQL code that can pull out the following information from the database. 

Columns include:

• Device Name
• IP Address
• Make
• Model
• Serial Number
• IOS Version
• End Of Software Maintenance
• End of Life

m_roberts

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 <span class="s11">Cirrus</span><span class="s10">.</span>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

<span class="s11">WHERE [Device].EntityClass LIKE &#39;%chassis%&#39;</span>

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

<span class="s5">WHERE</span><span class="s0"> </span><span class="s10">[</span><span class="s11">Device</span><span class="s10">].</span><span class="s11">Serial</span><span class="s0"> </span><span class="s10"><></span><span class="s0"> </span><span class="s7">&#39;&#39;</span>

For this I would also add the following to the SELECT, so you can see what these elements are

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 <> ''