17 Replies Latest reply on Jul 21, 2017 2:33 PM by rschroeder

    Create Report Summary for Port status Admin-Up Down Inactive Last used etc

    phillsb

      NPM NCM UDT IPAM NTA

       

      Hi

       

      Would someone please provide some assistance please.....

       

      I seem to be going round in circles with creating a summary report for providing a weekly / monthly report with not too heavy on the layout but enough information ports.

       

      I am new to SW and I have been tasked to produce a report for all sites Approximately 600 Cisco nodes.

       

      I have no knowledge of SQL/SWSQL

       

      The task is to provide a report that shows -

      Device site

      Device name

      Toal ports

      Used ports

      Available ports

      Operationaly status

      Last Active

       

      I have found the following code and applied it and it looks good but the code is not pulling in any data. To keep things simple I only discovered 1 site for its ports, but nothing is showing in the table.

      i have modified the below code to include some more columns and info, but still no data. i will add the modified code when i logged in.

       

      What do I need to do,to make this happen?

       

      SELECT NodeID, Caption,

      COUNT (*) AS "Total Ports", SUM (IsUp) AS "Used Ports", SUM (IsDown) AS "Available Ports"

      FROM (

      SELECT ND.Caption, ND.NodeID,

      CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,

      CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown

      FROM NCM_Interfaces NCMI INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)

      INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)

      WHERE (InterfaceTypeDescription  like '%ethernet%')

      ) T1

      GROUP BY NodeID, Caption

      ORDER BY Caption DESC

       

      image.jpeg

      Both code and image is from Port count report

       

       

      if anyone is able to proide some advice it would be appreciated.

       

      many thanks in advance

       

      Message was edited by: Phillsb Reference to SQL Code used.

        • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
          phillsb

          Hi wluther

          as mentioned.
          below is the code that i would like to use but it is not pulling in the data.

          SELECT T1.NodeID, T1.Caption, n.n_site, n.Vendor, n.Description,

          COUNT (*) AS "Total Ports", SUM (IsUp) AS "Used Ports", SUM (IsDown) AS "Available Ports",

          CAST (ROUND ((1.0 * SUM (IsDown)/COUNT (*)) *100, 0) as float) AS "% Available"

          FROM (

          SELECT ND.Caption, ND.NodeID,

          CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,

          CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown

          FROM NCM_Interfaces NCMI INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)

          INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)

          WHERE (InterfaceTypeName like '%ethernet%')

          ) T1

          JOIN Nodes n ON n.NodeID = T1.NodeID

          WHERE

          n_site = 'WM%'

          GROUP BY T1.NodeID, T1.Caption, n_site, n.Vendor, n.Description

          ORDER BY "Available Ports" DESC

           

           

          it would also be a nice to have where it selects all n_sites but be able to use a drop down menu, so either 1 or 2 sites could come up with the table then export?

           

          Thanks

            • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
              wluther

              phillsb

               

              Are you using "n_site" as a custom property, or does it represent something else?

               

              On the presumption "n_site" is a custom property, and you are wanting to filter for "n_site" starts with "WM", then you will need the following:

               

              Joining the "NodesCustomProperties" table to the query:

              JOIN NodesCustomProperties NCP ON T1.NodeID=NCP.NodeID

               

              Filtering by the "n_site" values contained within the "NodesCustomProperties" table:

              WHERE

              NCP.n_site LIKE 'WM%'

               

              Adding the "n_site" column to the SELECT statement, making the values visible in the returned results:

              SELECT

              NCP.n_site

               

              Adding the "n_site" custom property to the GROUP BY statement:

              GROUP BY

              NCP.n_site

               

               

              The query below is actually the same as the one you previously provided. I simply spaced it out a bit more to make it easier to see what should go where.

              Also, since I do not have a custom property named "n_site" in my environment, I used "area" instead, and the query seemed to work properly for my results.

               

              SELECT
              T1.NodeID
              ,T1.Caption
              
              --THIS IS WHERE YOU ADD THE CUSTOM PROPERTY TO BE VIEWED IN THE RESULTS
              ,NCP.n_site
              --,NCP.area
              
              ,n.Vendor
              ,n.Description
              ,COUNT (*) AS "Total Ports"
              ,SUM (IsUp) AS "Used Ports"
              ,SUM (IsDown) AS "Available Ports"
              ,CAST (ROUND ((1.0 * SUM (IsDown)/COUNT (*)) *100, 0) as float) AS "% Available"
              
              FROM (
              SELECT ND.Caption, ND.NodeID,
              CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,
              CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown
              FROM NCM_Interfaces NCMI
              INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
              INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
              WHERE (InterfaceTypeName like '%ethernet%')
              ) T1
              
              JOIN Nodes n ON T1.NodeID=n.NodeID
              
              --THE FOLLOWING LINE JOINS THE CUSTOM PROPERTIES TABLE
              JOIN NodesCustomProperties NCP ON T1.NodeID=NCP.NodeID
              
              --THIS IS WHERE YOU WILL FILTER BASED ON THE CUSTOM PROPERTY VALUE
              WHERE
              NCP.n_site LIKE 'wm%'
              --NCP.area LIKE '%noc%'
              
              GROUP BY
              T1.NodeID
              ,T1.Caption
              ,n.Vendor
              ,n.Description
              
              --SINCE YOU ARE NOT DOING ANY CALCULATIONS ON THIS VALUE, IT NEEDS TO BE INCLUDED IN THIS GROUP BY STATEMENT
              ,NCP.n_site
              --,NCP.area
              
              ORDER BY "Available Ports" DESC
              

               

               

               

              While I am sure it can be done, I am not really sure how to go about doing the drop down menu.

              However, if we convert this query into SWQL, we can use a custom query resource to use dynamic filtering, which would adjust the results on the fly, so to speak.

               

              Let's start with this first, and see how close we are to getting the results you need. If I am way off, please let me know.

               

               

              Thank you,

               

              -Will

                • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                  phillsb

                  wluther,

                   

                  Thanks and yes n_site is a customer property i have a couple of them, i will pop them at the bottom.

                   

                  I popped the code and although the table creates but it is still not pulling any data in .

                  I have used UDT to port discover but would of assumed that NCM would do a more detailed interrogation.
                  as I used UDT would UDT be a more preferred way to pull the port data in?
                  Customer Properties
                    • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                      wluther

                      phillsb Yes, with UDT being an option here, I believe UDT would be the best place to get your numbers. While I am not certain, I think you would need to have NCM run an inventory job to have those details available. Perhaps cnorborg or vispetto would know better. Unfortunately, I do not have my lab environment running at this moment, so I am unable to verify anything with UDT.

                       

                       

                       

                      What happens if you run the query below, commenting out the "WHERE" statement?

                       

                       

                      SELECT
                      T1.NodeID
                      ,T1.Caption
                      
                      --THIS IS WHERE YOU ADD THE CUSTOM PROPERTY TO BE VIEWED IN THE RESULTS
                      ,NCP.n_site
                      --,NCP.area
                      
                      ,n.Vendor
                      ,n.Description
                      ,COUNT (*) AS "Total Ports"
                      ,SUM (IsUp) AS "Used Ports"
                      ,SUM (IsDown) AS "Available Ports"
                      ,CAST (ROUND ((1.0 * SUM (IsDown)/COUNT (*)) *100, 0) as float) AS "% Available"
                      
                      FROM (
                      SELECT ND.Caption, ND.NodeID,
                      CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,
                      CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown
                      FROM NCM_Interfaces NCMI
                      INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
                      INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
                      WHERE (InterfaceTypeName like '%ethernet%')
                      ) T1
                      
                      JOIN Nodes n ON T1.NodeID=n.NodeID
                      
                      --THE FOLLOWING LINE JOINS THE CUSTOM PROPERTIES TABLE
                      JOIN NodesCustomProperties NCP ON T1.NodeID=NCP.NodeID
                      
                      --THIS IS WHERE YOU WILL FILTER BASED ON THE CUSTOM PROPERTY VALUE
                      --WHERE
                      --NCP.n_site LIKE 'wm%'
                      --NCP.area LIKE '%noc%'
                      
                      GROUP BY
                      T1.NodeID
                      ,T1.Caption
                      ,n.Vendor
                      ,n.Description
                      
                      --SINCE YOU ARE NOT DOING ANY CALCULATIONS ON THIS VALUE, IT NEEDS TO BE INCLUDED IN THIS GROUP BY STATEMENT
                      ,NCP.n_site
                      --,NCP.area
                      
                      ORDER BY "Available Ports" DESC
                      

                       

                       

                       

                       

                      Thank you,

                       

                      -Will

                        • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                          phillsb

                          wluther

                           

                          Nope nothing changed - table created fine but again no data.

                           

                          cnorborg & vispetto if you are able to shine any light on what might be going wrong your assistance would be much appreciated.

                           

                          I used this bit of code form the post i found you in and it pulls the n_site data in.

                           

                          SELECT DISTINCT(Interfaces.NodeID), Nodes.n_site, COUNT(Interfaces.InterfaceID) AS InterfaceCount

                           

                          FROM Interfaces LEFT JOIN Nodes ON Interfaces.NodeID = Nodes.NodeID

                           

                          GROUP BY Nodes.n_site, Interfaces.NodeID

                           

                          ORDER BY InterfaceCount DESC, n_site

                           

                          and i tried this  UDT code and that fails to import the data too

                           

                          SELECT

                               UDT_UnUsedPorts.NodeID AS NodeID,

                               UDT_UnUsedPorts.PortID AS PortID,

                               UDT_UnUsedPorts.DNS AS DNS,

                               UDT_UnUsedPorts.IP_Address AS IP_Address,

                               UDT_UnUsedPorts.Name AS Name,

                               UDT_UnUsedPorts.PortDescription AS Port_Description,

                               UDT_UnUsedPorts.DaysUnused AS DaysUnused,

                               UDT_Port.PortID AS PortID2,

                               UDT_Port.AdministrativeStatus AS AdminStatus,

                               UDT_Port.OperationalStatus AS OperStatus

                              FROM UDT_UnUsedPorts

                              INNER JOIN UDT_Port

                               ON UDT_Port.PortID = UDT_UnUsedPorts.PortID   

                              WHERE

                               (

                                ISNUMERIC(UDT_UnUsedPorts.DaysUnused) = 1

                                AND CAST(UDT_UnUsedPorts.DaysUnused AS INT) >= 60

                                OR UDT_UnUsedPorts.DaysUnused = 'Never'

                               )

                               AND UDT_Port.AdministrativeStatus = 1

                               AND UDT_Port.OperationalStatus = 2 

                           

                           

                           

                          Regards,

                           

                          Phill

                            • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                              wluther

                              phillsb

                              Hmmm...

                               

                              Well, the "Nodes" table is a view that SolarWinds created when they split the nodes and custom properties into different tables, so we are still looking at the same data there...

                               

                              How about just a simple query to see the basics, separately, instead of joined together...

                              Since we know your query works as a whole, the following two queries should work separately as well.

                               

                              This should show you all of the node names and values for "n_site".

                               

                              SELECT Nodes.Caption, Nodes.n_site
                              FROM Nodes
                              ORDER BY n_site
                              

                               

                              And this should show you just the interface half of the results.

                               

                              SELECT DISTINCT(Interfaces.NodeID), COUNT(Interfaces.InterfaceID) AS InterfaceCount
                              FROM Interfaces
                              GROUP BY Interfaces.NodeID
                              ORDER BY InterfaceCount DESC
                              

                               

                              I suspect this is where we are retrieving an empty result set.

                              Give this a run and let me know if you still have an empty list of results. (I suspect you will...)

                               

                              SELECT ND.Caption, ND.NodeID,
                              CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,
                              CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown
                              FROM NCM_Interfaces NCMI
                              INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
                              INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
                              WHERE (InterfaceTypeName like '%ethernet%')
                              

                               

                               

                              If the above query worked, but did NOT return any results, then run the following and let's see if there are any results.

                               

                              SELECT ND.Caption, ND.NodeID,
                              CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,
                              CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown
                              FROM NCM_Interfaces NCMI
                              INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
                              INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
                              

                               

                               

                              Also, just to make sure, on your SolarWinds page, go to the "Configs" tab, and click on "Configuration Management". Select a few nodes, and then click the "Update Inventory" button. After the inventory has updated, run the queries again, and see if anything changed.

                               

                               

                              Thank you,

                               

                              -Will

                                • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                                  phillsb

                                  T

                                  his should show you all of
                                  the node names and values for "n_site".

                                   

                                  1. SELECT Nodes.Caption, Nodes.n_site 
                                  2. FROM Nodes 
                                  3. ORDER BY n_site 

                                  Worked – DATA PULLEDAnd this should show you just
                                  the interface half of the results.

                                  1. SELECT DISTINCT(Interfaces.NodeID), COUNT(Interfaces.InterfaceID) AS InterfaceCount 
                                  2. FROM Interfaces 
                                  3. GROUP BY Interfaces.NodeID 
                                  4. ORDER BY InterfaceCount DESC 

                                  ERROR – Msg 102, Level 15, State
                                  1, Line 1
                                  Incorrect syntax near
                                  '1.'.
                                  Msg 102, Level 15, State
                                  1, Line 2
                                  Incorrect syntax near
                                  '2.'.
                                  I suspect this is where we
                                  are retrieving an empty result set.
                                  Give this a run and let me
                                  know if you still have an empty list of results. (I suspect you will...)

                                  1. SELECT ND.Caption, ND.NodeID, 
                                  2. CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp, 
                                  3. CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown 
                                  4. FROM NCM_Interfaces NCMI 
                                  5. INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID) 
                                  6. INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID) 
                                  7. WHERE (InterfaceTypeName like '%ethernet%') 

                                  ERROR- Msg 102, Level 15, State
                                  1, Line 1
                                  Incorrect syntax near
                                  '1.'.
                                  Msg 156, Level 15, State
                                  1, Line 2
                                  Incorrect syntax near
                                  the keyword 'CASE'.
                                  If the above query worked,
                                  but did NOT return any results, then run the following and let's see if there
                                  are any results.

                                  1. SELECT ND.Caption, ND.NodeID, 
                                  2. CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp, 
                                  3. CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown 
                                  4. FROM NCM_Interfaces NCMI 
                                  5. INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID) 
                                  6. INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID) 

                                  ERROR –  i expected this on as the previous did not
                                  work

                                  Msg 102, Level 15, State
                                  1, Line 1

                                  Incorrect syntax near
                                  '1.'.

                                  Msg 156, Level 15, State
                                  1, Line 2

                                  Incorrect syntax near
                                  the keyword 'CASE'.

                                   

                                  After Inventory Update

                                   

                                  Code 1 worked as before – pre-inventory update

                                  1. Code 2 worked
                                  2. Code 3 worked
                                  3. Code 4 worked

                                  each table received data.

                                  So it looks like to start with may need to either rediscover all the nodes or just update the inventory?

                                  However i did run my original code and that did not pull any data as expected.

                                   

                                  -Phill

                      • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                        Craig Norborg

                        Well, I'll answer the question about what do I need to do to make this happen first.   UDT, NCM and NPM are the most likely candidates for pulling your data.  But you have to remember how each module gets its data.   UDT and NPM both have to be managing both the node AND the individual ports that you want to count.  They both poll fairly often, although NPM is usually set to poll a bit more often than UDT.   You'll have to check your polling intervals to know for sure how long you might need to wait for ports to have info in either module, I know I don't have them set to defaults on our system.   The problem with this is that unless you're running with an unlimited license on these products, you will consume a lot more ports most likely.   Not to mention the polling overhead of polling lots of unused ports.   Even though we are running unlimited licenses I tend to not monitor unused ports, but this is a whole different discussion.

                         

                        NCM might be better suited to your cause, because it will inventory the devices and their interfaces via the Inventory process and you can produce reports on all the interfaces on a device without having to add them to NCM, not that there is even a way to add an interface to NCM!!  :-)   The problem is that NCM inventory is a somewhat heavyweight process and is generally run on a schedule by you every week or so during off-hours.   Depending on your server/environment it might not be desirable to try and run an inventory midday during the hours when people will have their computers turned on so it can catch the state of the port "correctly".  Now if everyone in your company always leaves their computers on at night, running the report at night wouldn't be a bad thing, but...

                         

                        That being said, the first SQL query you posted looks like it works well for me.  Since this is using the NCM Interfaces table, make sure you run an inventory on the devices you want to be in the report.  You can do that via "Configs" / "Config Management", then select the nodes you want and "Update Inventory".   To schedule inventory updates, go to "Configs" / "Jobs" and there might be a default job that will update inventory that you might have to enable or run.  If not, create your own job and have that run on a schedule.   By creating your own, you can specify the sites to run on, or maybe the types of devices and such, very flexible...

                         

                        Once you start getting data that you consider to be "good", its just a matter of modifying the query to include the other info you want, such as the Site.    I believe the Site will need to be a custom property, or are you getting it another way?   Knowing what you're using would help us to show you how to do that.   I believe the Last Active could correspond to the "Last Change" field in NCM_Interfaces, which is nice.   However, it isn't quite last active.   ie: if the "OperStatus" field is "up", this will be when it went up.   If the "OperStatus" field is "down" it will be when the interface went down.  However, in either case, if the node was rebooted 6 hours before the Inventory was performed, I believe everything will end up being when it was last rebooted.  I could be wrong, this could be something NCM figures out rather than polls, but I'm guessing its getting its data for this by polling the switch and not doing any interpretation on it.

                         

                        HTH!    Please ask questions, lots of stuff here that might be confusing.   But knowing where you want to get your data from and how to interpret it is important when dealing with Orion!!

                          • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                            phillsb

                            cnorborg (wluther)

                             

                            Hello Craig. Many thanks for your response.

                             

                            The SQL for the report has generated with some of the information that I am looking for. It was the Inventory that required updating that allowed the data within NCM to be used.

                             

                            a bit of background - SW has been installed for over 1 year but due to the location it has been installed it has been a challenge to connect to all device across 3 different networks. 1 of the networks is being decommed, 2nd is new network, 3rd is for voice (no connection yet) the primary network is the 2nd.

                            SW is not been installed to make any changes to the devices but I am hoping in the future I will be able to make the case for SW to be the one tool that does all.

                            The key element is to be able to provide reporting on all devices as and when asked for and for a monthly report to be generated ( this would be several different reports) that have been set up and are ready to go and have been pulling in some data.

                             

                            The reports that have already been setup (this was actioned by someone else who has now left) should provide all the data the support team requires so hopefully when all the nodes are correctly added these reports will be good.

                             

                            With the new reports that I have to create (with the help of yourself and wluther (Thanks )) they are slightly different to what's been setup already.

                            Report to

                            report 1. identify Per Site (n_site) and Per Node (n_role) how many Total Ports / Used Ports /Available Ports / %available - Not to include any uplinks

                            report 2. on core/distribution switches (n_role) interface uplinks, interface and module type e.g. GigEther / Fibre SX or LX  / Te's etc

                             

                            I have got some custom properties setup and all fields for the nodes have been entered via the custom property editor. and I have also added details to some of SWs default properties as I thought this would also help.

                             

                            I have scanned the network for all nodes on the primary network and although the scan picks them up the inventory does not -

                            Node Discovery - 531 nodes

                            Inventory update - 410 nodes

                             

                             

                            on Node discovery or UDT what should I select for discovery - Admin up/down /operational status/ Ethernet Gi TE etc.... Not selecting VLAN’s Port Channels etc... as this would be too many wasted ports even though I have unlimited.

                             

                             

                            The tools that I have are all unlimited, but not all up and running : -

                            3 x servers - Application / DB / NTA - running OS Server 2012

                            Application has 1 x polling engine (default)

                            NTA has 2 Poling engines - stacked I believe and I am trying to equal out nodes across the 2 servers however would prefer NTA server to be more of a primary poller than the Application server as this server is running all the apps

                            Orion Platform 2016.1.5300, 

                            VNQM 4.2.4, installed not configured

                            IPAM 4.3, installed not configure

                            DPA 10.0.0, did not know we had it must be part of NPM12 update

                            NCM 7.5, installed working

                            IVIM 2.1.2, did not know we had it must be part of NPM12 update

                            QoE 2.1.0, Installed not configured

                            Toolset 11.0.3, installed

                            NetPath 1.0, Installed not used yet

                            NPM 12.0, Installed

                            NTA 4.2.0, Installed not used/configured

                            UDT 3.2.4 installed used but maybe not configured properly

                             

                            back in July I updated all the applications to their latest version although I have not checked recently for any updates.

                             

                            about me - Engineer/Design Architect, no knowledge of SQL or SWQL or reporting tool this is all new. I am trying to keep this tool as tidy as possible before it goes into production.

                             

                            Again thanks in advance for any help

                             

                            -Phillsb

                              • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                                Craig Norborg

                                Glad to hear your seeing data now!   Yea, with all the components it can be a bit tricky sometimes figuring out how the info gets in there (ie: polled, inventoried, does the port have to be monitored, etc).   Once you know a bit and sit down and think about it though, it all makes sense.

                                 

                                So, why are 531 nodes discovered and 431 inventoried?   Good question.  Are they all added to NCM?   You can check that by going to Manage Nodes and there is a column that is NCM_Licensed.  If you click on that, it will sort by it and make it easier to see if any aren't.   To add them, select them and go to the "More Actions" on the menu bar, drop it down and choose "Add node to NCM".

                                 

                                If they are all in NCM, then you want to figure out which ones weren't inventoried.   If you ran an inventory job, make sure you have "Save Job Log" configured under the "Enter Notification Details" when creating or editing the job.   Then you can go to the "History" column in the "Jobs List" page and see what devices didn't inventory and why.  Make sure you clear this history on occasion.

                                 

                                If you didn't run it as a job, and just did the nodes on demand, you can go to "Config Management" and the "Inventory Status" tab.   You know, I'm not sure, the scheduled ones might show up here too.  Usually the errors on why it didn't inventory are self-explanitory, but if you run into problems be sure to ask here.

                                 

                                Discovery Settings, it really depends on you and what you want!  If you "Define Monitoring Settings", under "Interfaces" this is what NPM monitors.   If you want to monitor ports that are down, you can, but I don't recommend it unless you have a need.   Trunk and Access are just what you would think.  A trunk port would usually be an uplink or some other type of port that is trunked (maybe to a VMWare host?), this is what I choose - I want uplinks but not end-user ports.   An access port is usually to an end user.  Unknown you usually get when its not Cisco or something odd.   Physical would be an actual hardware port.  Virtual would be something like a port-channel or a tunnel interface, something that doesn't exist as a physical port.    Personally I would do Physical usually, although if there are port-channels I usually want to monitor them too.   If it was important enough to create a port-channel, its probably important enough to monitor.   Advanced settings are kind of neat, if you're consistent in setting interface descriptions, you can choose whether to monitor based off those for instance!! 

                                 

                                The "Ports" setting is for UDT if I'm not wrong (haven't worked with 12 much).   Usually you get the same questions like you do under interfaces, but I'm not seeing it in the "Define Monitoring Settings".   For UDT I tend to NOT monitor trunk ports, but instead monitor end-user ports.   That's because of how UDT works.   UDT builds a table of MAC addresses and what ports it see's the MAC addresses on.   It also loads all the ARP tables you have, which cross references MAC Addresses to IP addresses.  Finally it builds a table of Active Directory log-ins and what IP address is associated with them.  That way you can by looking at all this info, search for what port a person is on for instance.  Now, if you monitor both end-user ports AND trunk ports, a users MAC address will be seen on both.   And lets say the user has to hop through 5 switches to get to the router, their MAC address will be seen on ALL the uplink ports of those 5 switches!   So, if you search for what port a user is on, not only should you get the port the user is on, but all of the uplink ports that it see's their MAC address on also.   This is why I don't have UDT monitor uplinks!!

                                 

                                Solarwinds servers.   If you have 3 servers for your entire solarwinds environment, the usual way to set them up would be to have one for your NPM database, which will also have other modules using it.   The second server would be both your main poller and host all the modules.  If you don't install all the modules on one server, you won't get full integration of them.   The third server should be your NTA FSDB (Flow Storage Database) server.   Because the type of data that NTA generates isn't conducive to working with an SQL database, you'll get way better performance setting up a separate server as the FSDB server, which is a NOSQL database.   Takes tons of disk space and fairly intensive, so you don't want anything else on it.   Your main poller will still get the Netflow data, but instead of storing it on your SQL server it will store it here.   HIGHLY recommended...

                                 

                                Network:   Why are you setting up two different networks?   One for voice and one for data?   You should be able to run them as one converged network with QoS configured quite well, and get better flexiblity.   The money you'd save in the long run would more than pay for QoS training I would think!!    If you're stuck on running a separate VoIP network, I would still connect it to your main network as maybe a VLAN so you can at least manage the devices fully from your main network.   If you really don't want to do either, it should be possible to hook a second NIC into your solarwinds server and set up manual routes on the server so it can do this.   HIGHLY recommended to do so!!   Solarwinds will help you monitor and troubleshoot that network quite a bit!!

                                 

                                Just so you know, most of us started off as Networking folks with no SQL (and definitely no SWQL) training, so don't stop trying to learn!!   But, having a good DBA care for your Solarwinds SQL server is a good thing!

                                 

                                Hope I covered everything...  Lots of stuff in your last reply!

                                  • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                                    phillsb

                                    Hi Craig (cnorborg)

                                     

                                    not sure how to reply back so added my response to you paragraphs....

                                     

                                    I still have a fair bit of work to do to bring the application up to where I want it and this weeks not been a good week for time.

                                     

                                    So, why are 531 nodes discovered and 431
                                    inventoried?   Good question.  Are they all added to
                                    NCM?   You can check that by going to Manage Nodes and there is a
                                    column that is NCM_Licensed.  If you click on that, it will sort by it and
                                    make it easier to see if any aren't.   To add them, select them and
                                    go to the "More Actions" on the menu bar, drop it down and choose
                                    "Add node to NCM".

                                    Great – Many nodes not active in NCM
                                    – this has been rectified.

                                     

                                    If they are all in NCM, then you want to figure out which ones
                                    weren't inventoried.   If you ran an inventory job, make sure you
                                    have "Save Job Log" configured under the "Enter Notification
                                    Details" when creating or editing the job.   Then you can go to
                                    the "History" column in the "Jobs List" page and see what
                                    devices didn't inventory and why.  Make sure you clear this history on
                                    occasion.

                                    All nodes now in NCM

                                     

                                    If you didn't run it as a job, and just did the nodes on demand,
                                    you can go to "Config Management" and the "Inventory
                                    Status" tab.   You know, I'm not sure, the scheduled ones might
                                    show up here too.  Usually the errors on why it didn't inventory are self-explanitory,
                                    but if you run into problems be sure to ask here.

                                     

                                    Discovery Settings, it really depends on you and what you
                                    want!  If you "Define Monitoring Settings", under
                                    "Interfaces" this is what NPM monitors.   If you want to
                                    monitor ports that are down, you can, but I don't recommend it unless you have
                                    a need.   Trunk and Access are just what you would think.  A
                                    trunk port would usually be an uplink or some other type of port that is
                                    trunked (maybe to a VMWare host?), this is what I choose - I want uplinks but
                                    not end-user ports.   An access port is usually to an end user.
                                    Unknown you usually get when its not Cisco or something odd. 
                                    Physical would be an actual hardware port.  Virtual would be something
                                    like a port-channel or a tunnel interface, something that doesn't exist as a
                                    physical port.    Personally I would do Physical usually,
                                    although if there are port-channels I usually want to monitor them
                                    too.   If it was important enough to create a port-channel, its
                                    probably important enough to monitor.   Advanced settings are kind of
                                    neat, if you're consistent in setting interface descriptions, you can choose
                                    whether to monitor based off those for instance!! 

                                     

                                    The "Ports" setting is for UDT if I'm not wrong
                                    (haven't worked with 12 much).   Usually you get the same questions
                                    like you do under interfaces, but I'm not seeing it in the "Define
                                    Monitoring Settings".   For UDT I tend to NOT monitor trunk
                                    ports, but instead monitor end-user ports.   That's because of how
                                    UDT works.   UDT builds a table of MAC addresses and what ports it
                                    see's the MAC addresses on.   It also loads all the ARP tables you
                                    have, which cross references MAC Addresses to IP addresses.  Finally it
                                    builds a table of Active Directory log-ins and what IP address is associated
                                    with them.  That way you can by looking at all this info, search for what
                                    port a person is on for instance.  Now, if you monitor both end-user ports
                                    AND trunk ports, a users MAC address will be seen on both.   And lets
                                    say the user has to hop through 5 switches to get to the router, their MAC
                                    address will be seen on ALL the uplink ports of those 5 switches! 
                                    So, if you search for what port a user is on, not only should you get the port
                                    the user is on, but all of the uplink ports that it see's their MAC address on also. 
                                    This is why I don't have UDT monitor uplinks!!

                                    This I will need to set to be re discovered
                                    then as I totally agree that UDT should only be monitoring end points

                                    I will set NCM to rediscover all ports and monitor each interface port channel trunk etc

                                     

                                    one thing I have now been asked to do is on slected code nodes to provide interface info on the uplinks

                                    interface / port type Eth/Gi/Te - if fibre what Gbic SX or LX etc...this may be a challenge.

                                    I assume that there may already be a predefined report to do this?

                                     

                                     

                                    Solarwinds servers.   If you have 3 servers for your
                                    entire solarwinds environment, the usual way to set them up would be to have
                                    one for your NPM database, which will also have other modules using
                                    it.   The second server would be both your main poller and host all
                                    the modules.  If you don't install all the modules on one server, you
                                    won't get full integration of them.   The third server should be your
                                    NTA FSDB (Flow Storage Database) server.   Because the type of data
                                    that NTA generates isn't conducive to working with an SQL database, you'll get
                                    way better performance setting up a separate server as the FSDB server, which
                                    is a NOSQL database.   Takes tons of disk space and fairly intensive,
                                    so you don't want anything else on it.   Your main poller will still
                                    get the Netflow data, but instead of storing it on your SQL server it will
                                    store it here.   HIGHLY recommended...

                                    The setup is as you described – S1 =
                                    Application server all Apps  / S2 = DB
                                    server only / S3 = NTA server and additional Poller/s

                                    I have 4 additional pollers only 2
                                    in use – 1 x poller is on the S1 giving total of 2 Pollers (1 is default) –
                                    should the remaining pollers be installed on S3 making them staking/stackable
                                    pollers?

                                     

                                    Network:   Why are you setting up two different
                                    networks?   One for voice and one for data?   You should be
                                    able to run them as one converged network with QoS configured quite well, and
                                    get better flexiblity.   The money you'd save in the long run would
                                    more than pay for QoS training I would think!!    If you're
                                    stuck on running a separate VoIP network, I would still connect it to your main
                                    network as maybe a VLAN so you can at least manage the devices fully from your
                                    main network.   If you really don't want to do either, it should be
                                    possible to hook a second NIC into your solarwinds server and set up manual
                                    routes on the server so it can do this.   HIGHLY recommended to do
                                    so!!   Solarwinds will help you monitor and troubleshoot that network
                                    quite a bit!!

                                    Sorry miss directed you ,VoIP is using the same network but the back where the CUCM servers are they are on a
                                    separate network out of my control. still be able to reach them but it has not been a priority as the user data network is more of priority than VoIP. Yes I would like to use the QoS. I have not looked into it, and again I'm not a VoIP expert (yet) with knowledge of how to integrate this with the CUCM servers. Assuming SolarWinds will need to interrogate the CUCM servers?

                                     

                                    Regards

                                     

                                    -Phill

                                      • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                                        Craig Norborg

                                        Good job on the NCM, should help out with this problem..

                                         

                                        So, the first question I would have is "how are you going to identify your uplinks"?   Are all trunked ports uplinks and will you use this assumption?   Or are you setting the interface description in a way to separate out the uplinks?    If there is CDP or LLDP info on it?  By what criteria do you determine it's an uplink?   Trunked links or CDP/LLDP info can be a bit deceiving, esp. if you have VMWare hosts.   While not traditionally uplinks they will quite often be trunked or respond to either CDP or LLDP.   Cisco AP's will respond to both CDP and LLDP I think, while Aerohive AP's only respond to LLDP, or did you not want to include uplinks to Access Points?   If not, once again another set of trunked links you might or might not want to report on.   A consistent interface description could work, but that's a lot more work to set them all.

                                         

                                        Once you have that down, you can explore how you want to do a report on it.  I don't think there is an existing report for that, but it would be nice!!  :-)   Information might have to come from a couple different sources within Orion.   Is this a homogeneous environment, like all Cisco or something?  Or do you have a mixture of technologies?   The reason I ask is I think for Cisco you will have to get into the NCM.EntityPhysical table, where you can search the EntityDescription for something ending in SFP for instance and come up with what interfaces have what SFP's in them.   There is also a NCM.EntityPhysicalJuniper table, which is basically the same thing but for Juniper.  Makes things more complex, but they operate in different ways.   If you have another brand equipment you might have to get into UnDP (Universal Device Pollers) to find that info.  Otherwise you might be able to get some of the info out of the NCM.Interfaces or maybe even the UDT Ports or NPM Interfaces tables.  But, once again, unlike NCM you would need to be monitoring said ports within the other modules in order for those ports to show up!!   There might be a report that gives you some of it, just search or browse through whats out there to see!!

                                         

                                        So, on to servers.  S3 is a VM host?   And you have 4 VM's set up to be pollers?   Unless you're monitoring TONS of ports on your nodes, I would think just a single poller should handle your load, which would be your main application server (ie: S1).   If you want to offload S1 to have it serve up web pages quicker or something, you could move nodes onto a different poller to conserve that box a bit...

                                         

                                        I think once you get into the voice quality monitor you'll need to get in to monitoring those servers (ie: VNQM).   Until then I wouldn't worry about it unless you want to.  Just to clarify though, they're on the same network, but you don't have access to the CUCM servers to monitor them, and possible not the network hardware they're connected to?   Personally I'd lobby to get them monitored ASAP, both the servers and the network equipment, and get them monitored by your standard tools (ie: Solarwinds).   Otherwise they might want to set up their own little IT world with them and monitor it in a completely different way   If getting that done is a problem I'd harp on it until they caved in!  :-)

                                          • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                                            phillsb

                                            Hi Craig cnorborg

                                             

                                            There are 2 Gi Interfaces and can be any of Gi49-Gi52 ) depending on when the device was installed and if anything was at fault since installation) these 2 uplinks are port-channel / trunks. although there may be a legacy config out there that is a single connection.

                                            Network Devices used -  CISCO
                                            2960 / 4506 / 76060

                                            Cisco AP’s Aironet 1262’s

                                            No vm hosts

                                             

                                             

                                            Creating these reports are for 2 reasons – all access ports are chargeable to the customer so any access port that is either open up or down is
                                            fully chargeable – any port that is Admin down is not. Trunk and AP ports are not at all chargeable but need monitoring and identifying its status and if a GBic what type – Single mode/ multimode (SX/LX) etc....

                                             

                                             

                                            All devices to be monitored in this manor will be CISCO only, we do have different flavour devices within FWs but these are currently out of my control and I won’t need to report on them.....yet.

                                             

                                             

                                            All Servers are Physical servers – S1 for Orion on Applications – S2for the Database and S3 for NTA  Network Traffic Analyser  and Pollers if/where possible. I think it would be a good idea to offload S1 polls to other pollers as you said to allow S1 to load web pages quicker.

                                             

                                            I will speak to our voice guy and see what he can do to arrange access, he believes there is already a system monitoring voice already.

                                             

                                            I am now seeing issues with the poller and unable to add the additional to pollers to S3, would there be any reason why an additional poller not be able to be installed olong with another Poller?

                                             

                                            Thanks

                                             

                                            -Phill

                                  • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                                    Craig Norborg

                                    So, are AP ports trunked?   If not, that could be a problem.   

                                     

                                    I still think you might have an easier time if you had consistent interface descriptions, wouldn't have to leverage so many tables from different sources to try and come up with your report.   If you were to do something like  "UPLINK" in the description of your uplink ports (whether or not they're trunked) you'd know they're not chargeable.  Plus, you'd be able to find it easy rather than having to cross reference different tables.    You could also put in stuff like SX and LX in that description too to make searching for that type of stuff easy for reporting purposes.    If you really wanted to get ambitious, you could do something like "CHARGEABLE" or "USER" or "ACCESS" or something to signify that this is a port you should charge for.    Takes some work to get set up initially, but it could make your reporting life easier in the long run.

                                     

                                    You could then make a quick report or resource that would search for trunk links that didn't have a valid description, or ports in general.   Then you'd just have to go fix the descriptions on those ports and you're reporting would be good.

                                     

                                    Hopefully that's enough to get you on your way to producing the reports you need!!   Doing the whole interface description thing could make this easier too, rather than having to do custom SWQL queries if you leverage the interface description you could probably use the query builder to do your work for you too...

                                     

                                    As for the poller not working?   Not enough details to even guess.  I'd recommend opening a ticket with support on that, they can probably fix you up really quick!

                                     

                                    Good luck!

                                    • Re: Create Report Summary for Port status Admin-Up Down Inactive Last used etc
                                      rschroeder

                                      I use this method to discover ports on specific switches that have not been used recently.  It enables me to recover ports going to removed PC's or printers, thus allowing me to re-use those switch ports and saves me the cost and hassle of buying additional switches. 

                                       

                                      How to create a report displaying the Last Time Data was Transmitted or Received on a Switch Port

                                       

                                      It sounds like you're hoping to find a more global report, instead of one targeting specific switches; is that true?