12 Replies Latest reply on Aug 23, 2019 6:36 AM by bman6074

    Customized "End-Of-Support" view query

    Craig Norborg

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

        • Re: Customized "End-Of-Support" view query

          Hello cnorborg,


          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.




          • Re: Customized "End-Of-Support" view query

            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

              • Re: Customized "End-Of-Support" view query
                Craig Norborg

                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

                  • Re: Customized "End-Of-Support" view query

                    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

                      • Re: Customized "End-Of-Support" view query
                        Craig Norborg

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

                  • Re: Customized "End-Of-Support" view query

                    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