Ok, have been sick of the built-in "End-Of-Support" devices view, it's pretty worthless. Gives you the date and the # of devices, but nothing else about them! Worse yet, if you click on the "X devices" under "Target Node(s)", it just takes you the "End of Support & End of Sales Lookup", but doesn't show you what the "X" devices were! So, in the example below, the bottom one is the default view, the top one is my customized view. In the bottom one you have no idea that you have 62 WS-C2950T-24's that are End-Of-Support, you just know there are 62 devices.
Ok, my query gives you a bit more info. I haven't figured out how to get the "62" under Num_Devices to take you right to a page that shows you the qty-62 2950T-24's, so that link still just takes you to basically the same place the default view takes you. But, at least you have the switch model # known before you go there. Plus, click on the model # and if there was a URL associated with the EOS in the database, clicking on it will take you to that URL. I put in the Cisco EOS notice in the ones I did manually.
Bonus, its a more compact view also, takes up about 75% of what the default view did for the same # of items (5). If you increased the # of lines viewed in the Custom SQL, it would probably save even more room. I was going to put this in the "Content Exchange", but I didn't really see anything that applied under NCM. Nothing for putting up queries or views...
The query is really quite simple once you figure out where everything is. Here it is:
SELECT ToString(Year(NP.EndOfSupport)) + '-' + ToString(Month(NP.EndOfSupport)) + '-' + ToString(Day(NP.EndOfSupport)) AS End_Of_Support, EP.Model, COUNT(NP.NodeID) AS Num_Devices, MIN(NP.EosLink) AS [_LinkFor_Model], ToString('/Orion/NCM/Resources/Eos/EosMatching.aspx') AS [_LinkFor_Num_Devices]
FROM Cirrus.NodeProperties NP JOIN NCM.EntityPhysical EP ON (NP.NodeID = EP.NodeID)
WHERE (EP.ContainedIn = 0) AND (NP.EndOfSupport < (GetDate()+180))
GROUP BY EP.Model, NP.EndOfSupport
ORDER BY NP.EndOfSupport, EP.Model
For the newbies out there who don't know how to add a custom SQL to a page, just click on "Customize Page" (if you don't see it, you don't have permission). Go to the column you want the resource in and hit the green "+" icon. It will pop up the "Add Resource" screen, you want "Custom Query". You can get there by either scrolling down or typing "Custom Query" in the search box. Once you have the resource added to the page, you can then use the arrows to put it where you want in relation to the other items in that column. Once you're set, hit the "Preview" button at the bottom. That will bring up your view, but with a blank query. So you'll see the box you just added and it will say "No query specified. Click Edit to enter a SWQL query". Click EDIT on the box like it says...
Now just simply take the query above and paste it into the "Custom SWQL Query" box. In the "Title" Box, I put in "End of Support Devices", just like in the original resource. For "Subtitle" I put in "Next 6 months", just like in the original too. You can play around with the "Enable Search" and "Number of rows per page" to get it looking right for you, I left them default for my example.
You must have NCM for this to function. I believe you need to be running 10.7 of Orion at least and 7.3 of NCM, but I'm not positive of that. Feel free to play around with it. If you change the 180 in it, that will change the # of days its looking ahead (ie: 180 days = 6 months).
You're on your own figuring out how to assign EOS/EOL dates to your devices though! I've been struggling with it quite a bit, finally getting it somewhat to where its useful... Big hint, check to make sure your inventory jobs are completing!!
Where can this kind of data be found in the SQL DB. what Table.s
I would like to combine Nodes with the end of support information. I found these 2 table but can't figure a way to join them
With NCM the "base" table is NCM_NodeProperties, which has both the NCM "NodeID" that is used in all things NCM, and the "CoreNodeID", which corresponds to the NPM side of things. So, something like this works:
SELECT DISTINCT TOP 100 EMQ.NodeID, ND.Caption
FROM [dbo].[NCM_EosMatchQueue] EMQ
JOIN [dbo].[NCM_NodeProperties] NP ON (EMQ.NodeID = NP.NodeID)
JOIN [dbo].[NodesData] ND ON (NP.CoreNodeID = ND.NodeID)
thank for this statement, But i may have chosen the wrong table for End of Support dates. I cannot find these (see screenshot) in EosMatchQueue table I tried searching for 2027 with this statement but i get noting. Is there another table with this end of support data? The servers were manually entered.
where EndOfSupport LIKE '%2027%'
Try looking at what the actual values are in the database. I don't see anything set to 2027 in my table, but lots of other dates like 2017 and such. There are also quite a few "NULL" values in EndOfSupport. Very possible that if its a null value that Solarwinds might just display 1/12/2027 for whatever reason? ie: a default date for a NULL value?
The reason you don't see 2027 is because its something we added for the windows 2016 servers. What mentioned above. Now that i found the [dbo].[NCM_NodeProperties] that has the End of support data and the NodeID to join the tables and information for my report.
Really good thread
cnorborg would it be possible for you to help me change the SQL code or refer me to a SAL manual that lists the table name for PART number?
my default EoL table lists my switches models by "Part Number" instead of "Model"
My SW Default NCM table
Your Query results:
Hmm... That depends on where that info comes from. Try this query and let me know if the MachineType column has the info you want?
SELECT ToString(Year(EP.NodeProperties.EndOfSupport)) + '-' + ToString(Month(EP.NodeProperties.EndOfSupport)) + '-' + ToString(Day(EP.NodeProperties.EndOfSupport)) AS End_Of_Support, EP.Model, EP.Node.MachineType, COUNT(EP.NodeProperties.NodeID) AS Num_Devices, MIN(EP.NodeProperties.EosLink) AS [_LinkFor_Model], ToString('/Orion/NCM/Resources/Eos/EosMatching.aspx') AS [_LinkFor_Num_Devices]
FROM NCM.EntityPhysical EP
WHERE (EP.ContainedIn = 0) AND (EP.NodeProperties.EndOfSupport < (GetDate()+180))
GROUP BY EP.Model, EP.Node.MachineType, EP.NodeProperties.EndOfSupport
ORDER BY EP.NodeProperties.EndOfSupport, EP.Model
cnorborg Thank you for that SQL code
this is what I get with that:
I think part number is what we need to display and sort by, or sort by date but display part number, since NCM detects the model of the DELL as Part Number.
DELL switches are a bit different in that the default fields often list something else for them, and some of the fields are inconsistent like Machine Type
SW are a bit Cisco centric
There is no database column named "Part Number", so I was taking a longshot. I can't tell though, are you saying that worked, or that it didn't?
If not, I'll probably need you to comb some of the database fields to see if you can find the values you're looking for. If you have the SDK loaded, you can do it via that, or it might be easier to use the Database Manager since its loaded with NPM. Go to the server, load it, and click on "Add default server". Go to your database, probably NetPerfMon, and try looking through some tables. I'm using the NCM_Entity_Physical, NCM_NodeProperties and NPM.Nodes (which is now NodesData and NodesSettings) tables. I doubt Dell would be in NCM_Entity_Physical_Juniper, but you might look there if you haven't found it. Otherwise I'm not really sure, you'd have to comb some of the tables to find those values...
thank you for that suggestion.
I would say it worked ~50%.
I would love to get it where yours is at, displaying the model numbers, EoL dates and a count
your code still works better than the default SW out-of-the-box chart
I'll comb the fields and get back to you
Hello Craig Norborg,
Thanks for an excellent piece of information. We will definitely use it as inspiration for improvements of the EoL/EoS feature.
I sent you an e-mail as I would like to discuss your feedback in detail.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.