Unused ports more than 30 days

marcinb · 7 March 2023 10:58

Due to the fact that from time to time service technicians uncross ports that have not been operating for more than 30 days.

Can I get such a list from librnms??

slalomsk8er · 7 March 2023 13:08

I guess It’s quite doable in a DB-query.

RR1 · 8 March 2023 06:04

For WebUI, refer below screenshot

marcinb · 8 March 2023 06:36

OK,
If you export to excel there is no this data
ports

marcinb · 8 March 2023 06:37

Can you create such a query

RR1 · 8 March 2023 09:05

value is stored as “ifLastChange” in ports table in DB.but its value is long integer.
if we run a query on ports table, we just get long digits only.

some where librenms script is converting its value to years,months,days,hours,minutes and showing in webui

need to figure, how its calculating

rhinoau · 8 March 2023 13:23

There is an ajax call that is returning the last Status Change time in seconds from the below - by subtracting ifLastChange / 100 from the device uptime:

github.com

librenms/librenms/blob/f0c9e1f275bf427d7c11bac8ab6fcc14cf06312f/app/Http/Controllers/Table/PortsController.php#L163


      
          public function formatItem($port)
          {
              $status = $port->ifOperStatus == 'down'
                  ? ($port->ifAdminStatus == 'up' ? 'label-danger' : 'label-warning')
                  : 'label-success';
          
          
    return [
                  'status' => $status,
                  'device' => Url::deviceLink($port->device),
                  'port' => Url::portLink($port),
                  'secondsIfLastChange' => ceil(optional($port->device)->uptime - ($port->ifLastChange / 100)),
                  'ifConnectorPresent' => ($port->ifConnectorPresent == 'true') ? 'yes' : 'no',
                  'ifSpeed' => $port->ifSpeed,
                  'ifMtu' => $port->ifMtu,
                  'ifInOctets_rate' => $port->ifInOctets_rate * 8,
                  'ifOutOctets_rate' => $port->ifOutOctets_rate * 8,
                  'ifInUcastPkts_rate' => $port->ifInUcastPkts_rate,
                  'ifOutUcastPkts_rate' => $port->ifOutUcastPkts_rate,
                  'ifInErrors_delta' => $port->poll_period ? Number::formatSi($port->ifInErrors_delta / $port->poll_period, 2, 3, 'EPS') : '',
                  'ifOutErrors_delta' => $port->poll_period ? Number::formatSi($port->ifOutErrors_delta / $port->poll_period, 2, 3, 'EPS') : '',
                  'ifType' => Rewrite::normalizeIfType($port->ifType),

This is then being converted client-side with a javascript call from here:

github.com

librenms/librenms/blob/64dcfcb8cc0ab0938cebc47148bccc1413fa0bf0/includes/html/pages/ports/list.inc.php#L91


      
              var dm = decimals || 2;
              var i = Math.floor(Math.log(units) / Math.log(base));
              return parseFloat((units / Math.pow(base, i)).toFixed(dm)) + display[i];
          }
          
          
var grid = $("#ports").bootgrid({
              ajax: true,
              rowCount: [50, 100, 250, -1],
              converters: {
                  'duration': {
                      to: function (value) { return moment.duration(value, 'seconds').humanize(); }
                  },
                  'human-bps': {
                      to: function (value) { return formatUnits(value); }
                  },
                  'human-pps': {
                      to: function (value) {
                          return formatUnits(value, 2, ['pps', 'Kpps', 'Mpps', 'Gpps', 'Tpps', 'Ppps', 'Epps', 'Zpps', 'Ypps']);
                      }
                  }
              },

As an example from browser inspect console:

With that knowledge you should be able to compare that to > 30 days of seconds in a query.

yoweb84 · 26 May 2023 09:32

Hello, have you found the solution to ports more than 30 days.
thank you

RR1 · 13 June 2023 05:02

dirty way to get the required details

select devices.device_id, devices.sysName, devices.hostname, devices.uptime, ports.port_id, ports.ifDescr,ports.ifLastChange, ((devices.uptime-(ports.ifLastChange/100))/86400)/(365/12) as lastchange, ports.ifOperStatus from devices left join ports on devices.device_id=ports.device_id where ports.ifOperStatus=“Down” and ((devices.uptime-(ports.ifLastChange/100))/86400)/(365/12) between 1 and 2;

gives ports list which are down for 30-60 days