osQuery Management Server (Prime).Network Interfaces (Windows) (SQLite SQL Statements)

      SELECT
          ia.address,
          id.mac
      FROM
          interface_addresses ia
          JOIN interface_details id ON id.interface = ia.interface
          JOIN routes r ON r.interface = ia.address
      WHERE
          (r.destination = '0.0.0.0' OR r.destination = '::') AND r.netmask = 0
          AND r.type = 'remote'
          AND (
          inet_aton(ia.address) IS NOT NULL AND (
            split(ia.address, '.', 0) = '10'
            OR (split(ia.address, '.', 0) = '172' AND (CAST(split(ia.address, '.', 1) AS INTEGER) & 0xf0) = 16)
            OR (split(ia.address, '.', 0) = '192' AND split(ia.address, '.', 1) = '168')
          )
          OR (inet_aton(ia.address) IS NULL AND regex_match(lower(ia.address), '^f[cd][0-9a-f][0-9a-f]:[0-9a-f:]+', 0) IS NOT NULL)
        )
      ORDER BY
          r.metric ASC,
        inet_aton(ia.address) IS NOT NULL DESC
      LIMIT 1;