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