cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Customized "End-Of-Support" view query

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

CustomEOS.png

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

12 Replies
Level 11

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

dbo_NCM_EosMatchQueue

dbo_NodesData

0 Kudos

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)

HTH!

0 Kudos

Hi Craig,

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.

SELECT *

  FROM [SolarWindsOrion].[dbo].[NCM_EosMatchQueue]

  where EndOfSupport LIKE '%2027%'

pastedImage_1.png

0 Kudos

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?

0 Kudos

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.

0 Kudos

I found the correct data in [dbo].[NCM_NodeProperties] table. if anyone else is looking

thanks for the help

0 Kudos
Level 12

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

part number.JPG

Your Query results:

Custom Query.JPG

0 Kudos

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

0 Kudos

cnorborg‌ Thank you for that SQL code

this is what I get with that:

Custom Query2.JPG

Custom Query3.JPG

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

0 Kudos

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

0 Kudos

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

Level 18

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.

Regards,

Jiri

0 Kudos