view release on metacpan or  search on metacpan

share/config.yml  view on Meta::CPAN

      ORDER BY
        device_name, serial
  - tag: portserrordisabled
    label: 'Blocked - Error-Disabled'
    category: Port
    columns:
      - { ip: Device, _searchable: true }
      - { dns: DNS }
      - { port: Port }
      - { name: Description }
      - { reason: Reason }
    query: |
      SELECT dp.ip, d.dns, dp.port, dp.name, properties.error_disable_cause AS reason
        FROM device_port dp
        INNER JOIN device_port_properties properties USING (ip, port)
        LEFT JOIN device d USING (ip)
      WHERE properties.error_disable_cause IS NOT NULL
      ORDER BY dp.ip, dp.port
  - tag: devicemultipleaddresses
    label: 'Devices with Multiple Addresses'
    category: Device
    columns:
      - { 'ip': 'Device IP', _searchable: true }
      - { 'devname': 'Name' }
      - { 'count': 'Addresses Count' }
      - { 'contact': 'Contact' }
      - { 'location': 'Location' }
    query: |
      SELECT ip, COALESCE(NULLIF(device.dns,''), NULLIF(name,''), '(none)') AS devname, count(alias), contact, location
        FROM device_ip LEFT JOIN device USING (ip)
        GROUP BY ip, devname, contact, location
        HAVING count(alias) > 1
        ORDER BY count(alias) DESC, devname ASC
  - tag: devicesharedaddresses
    label: 'IPs on Multiple Devices'
    category: Device
    columns:
      - { 'alias': 'Interface IP', _searchable: true }
      - { 'count': 'Instance Count' }
    query: |
      SELECT alias, count(ip)
        FROM device_ip
        WHERE alias != '127.0.0.1'
        GROUP BY alias
        HAVING count(ip) > 1
        ORDER BY count(ip) DESC, alias ASC
  - tag: recentlyaddeddevices
    category: Device
    label: 'Recently Added Devices'
    columns:
      - { ip: 'Device IP', _searchable: true }
      - { devname: 'Name' }
      - { model: 'Model' }
      - { vendor: 'Vendor' }
      - { creation: 'Date Added' }
      - { os: 'Operating System' }
      - { os_ver: 'OS Version' }
      - { location: 'Location' }
      - { contact: 'Contact' }
      - { serial: 'Serial' }
    bind_params:
      - { param: 'since', default: '2 months' }
    query: |
      SELECT ip, COALESCE(NULLIF(dns,''), NULLIF(name,''), '') AS devname,
          model, vendor, creation, os, os_ver, location, contact, serial
        FROM device
        WHERE creation > (LOCALTIMESTAMP - COALESCE(NULLIF(?,''), '2 months')::interval)
        ORDER BY creation DESC
  - tag: portswithmostvlans
    category: Port
    label: 'Ports with the most VLANs'
    columns:
      - { ip: 'Device IP', _searchable: true }
      - { port: 'Port' }
      - { vlans: 'VLAN Count' }
    bind_params:
      - { param: 'threshold', default: 1, type: 'number' }
    query: |
      SELECT ip, port, count(vlan) AS vlans
        FROM device_port_vlan
        GROUP BY ip, port
        HAVING count(vlan) > COALESCE(NULLIF(?,''), '1') ::integer
        ORDER BY vlans DESC, ip ASC, port ASC
  - tag: duplicateprivatenetworks
    category: IP
    label: 'Duplicate Private Networks'
    columns:
      - { subnet: 'Subnet', _searchable: true }
      - { count: 'Instances' }
      - { seen: 'Where Seen', _searchable: true }
    query: |
      SELECT subnet, count(subnet), array_agg(host(alias)::text || ' on ' || host(ip)::text) AS seen
        FROM device_ip
        WHERE ip <> alias
          AND (masklen(subnet) <> 32 AND masklen(subnet) <> 128)
          AND (subnet << '10.0.0.0/8'
          OR subnet << '172.16.0.0/12'
          OR subnet << '192.168.0.0/16'
          OR subnet << '100.64.0.0/10'
          OR subnet << 'fd00::/8')
        GROUP BY subnet
        HAVING count(subnet) > 1
        ORDER BY subnet
  - tag: vlansonlyuplinks
    category: VLAN
    label: 'VLANs Only On Uplinks'
    columns:
      - { ip: 'Device IP', _searchable: true }
      - { vlans: 'VLAN List' }
    bind_params:
      - { param: 'chunk_size', default: 20, type: 'number' }
    query: |
      SELECT ip, array_agg(vlans) AS vlans FROM (
        SELECT ip, array_to_string(array_agg(vlan), ', ') AS vlans, (x / COALESCE(NULLIF(?,''), '20') ::integer) AS chunk FROM (
          SELECT *, (row_number() over (partition by ip)) AS x FROM (

            SELECT DISTINCT ip, vlan
              FROM device_port_vlan dpv
              WHERE native IS false
                AND vlan <> 1
                AND (
                  SELECT count(*) FROM device_port_vlan dpv2
                    WHERE dpv2.ip = dpv.ip
                     AND dpv2.vlan = dpv.vlan
                     AND native IS true
                ) = 0
            ORDER BY ip, vlan) s2

        ) s1 GROUP BY ip, chunk
      ) s0 GROUP BY ip ORDER BY ip
  - tag: vlansneverconfigured
    category: VLAN
    label: 'VLANs Known but Not Configured'
    columns:
      - { ip: 'Device IP', _searchable: true }
      - { vlans: 'VLAN List' }
    bind_params:
      - { param: 'chunk_size', default: 20, type: 'number' }
    query: |
      SELECT ip, array_agg(vlans) AS vlans FROM (
        SELECT ip, array_to_string(array_agg(vlan), ', ') AS vlans, (x / COALESCE(NULLIF(?,''), '20') ::integer) AS chunk FROM (
          SELECT *, (row_number() over (partition by ip)) AS x FROM (

            SELECT DISTINCT ip, vlan
              FROM device_vlan dv
              WHERE vlan <> 1
                AND NOT EXISTS (
                SELECT FROM device_port_vlan dpv
                  WHERE dpv.ip = dv.ip
                    AND dpv.vlan = dv.vlan
                )
                AND vlan NOT IN (1002, 1003, 1004, 1005)
              ORDER BY ip, vlan) s2

        ) s0 GROUP BY ip, chunk
      ) s1 GROUP BY ip ORDER BY ip
  - tag: vlansunused
    category: VLAN
    label: 'VLANs No Longer Used'
    columns:
      - { ip: 'Device IP', _searchable: true }
      - { vlans: 'VLAN List' }
    bind_params:
      - { param: 'chunk_size', default: 20, type: 'number' }
      - { param: 'since', default: '3 months' }
    query: |
      SELECT ip, array_agg(vlans) AS vlans FROM (
        SELECT ip, array_to_string(array_agg(vlan), ', ') AS vlans, (x / COALESCE(NULLIF(?,''), '20') ::integer) AS chunk FROM (
          SELECT *, (row_number() over (partition by ip)) AS x FROM (

            SELECT DISTINCT ip, vlan
              FROM device_port_vlan dpv
              WHERE dpv.native IS false
                AND dpv.vlan <> 1
                AND (
                  SELECT count(*) FROM device_port_vlan dpv2
                    LEFT JOIN device_port dp USING (ip, port)
                    LEFT JOIN device d USING (ip)
                    WHERE dpv2.ip = dpv.ip
                      AND dpv2.vlan = dpv.vlan
                      AND native IS true
                      AND (
                        dp.up_admin = 'up'
                        OR age( LOCALTIMESTAMP,
                                to_timestamp( extract( epoch FROM d.last_discover ) - ( d.uptime - dp.lastchange ) /100 ) ::timestamp )
                          < COALESCE(NULLIF(?,''), '3 months') ::interval
                      )
                ) = 0
              ORDER BY dpv.ip, dpv.vlan) s2

        ) s1 GROUP BY ip, chunk
      ) s0 GROUP BY ip ORDER BY ip
  - tag: devicevlancount
    category: VLAN
    label: 'VLAN Count per Device'
    columns:
      - { ip: 'Device IP', _searchable: true }
      - { vlans: 'VLAN Count' }
    query: |
      SELECT ip, count(vlan) AS vlans
        FROM device_vlan
        GROUP BY ip
        ORDER BY vlans DESC
table_pagesize: 10
table_showrecordsmenu:
  - [10, 25, 50, 100, '-1']
  - [10, 25, 50, 100, 'All']
inventory_collapse_threshold: 0
inventory_platforms_collapse_threshold: 5
inventory_releases_collapse_threshold: 5
portctl_nameonly: false
portctl_native_vlan: true
portctl_no: []
portctl_only: []
portctl_nowaps: false
portctl_nophones: false
portctl_uplinks: false
portctl_topology: false
portctl_by_role: {}
system_port_control_reasons:
  address:     'Address Allocation Abuse'
  copyright:   'Copyright Violation'
  dos:         'Denial of Service'