IP address database tables in Plesk 10+

This article will include:

Relevant Database Tables
Tracking a domain to IP in the database
Useful SQL queries (tested on Plesk 10.4.4 and 11.0.9/11.5)

Relevant Database Tables

It’s kind of a mess to sort out so here’s the table relations in the Plesk psa database for IP addressing if you’re drilling down from a given domain to its IP records:

‘domains’ – This table stores all of the domains defined on the server and defines a number of aspects related to what services they have enabled. The useful fields from the ‘domains’ table are:
- id – an integer that has a one to many relationship with other tables in the database
- name – the domain name itself
- htype – hosting type, I tend to only care about records where it’s set to ‘vrt_hst’ which is a physical hosting site, not frame forwarding, not redirect
- parentDomainId – the ‘id’ field from the same table of the parent domain if this is a subdomain, or it will be set to 0 if it’s the primary domain
‘DomainServices’ – I believe this table’s purpose is to map a given service type to its associated IP address(es); i.e. web service for domain <id> is tied to IP addresses X and Y. Here’s the important fields:
- ipCollectionId – an integer that has a one to (possibly) many relationship with the ipCollectionId field of the IpAddressesCollections table. Basically this collection id maps a domain to what could ultimately be multiple IP addresses.
- type – the value of this field I typically care about is when it’s set to ‘web’ meaning we’re looking for an IP address related to web hosting.
- dom_id – the one to one mapped ‘id’ field from the ‘domains’ table, tying a given domain to a particular IP-based service.
‘IpAddressesCollections’ – This table maps a ‘collection’ to a specific IP address id number. Each service from DomainServices will refer to a collection id. There are only two fields:
- ipCollectionId – in this table, this number is not unique because it is possible for some services to have more than one IP address.
- ipAddressId – this number is also not unique in the table; it maps to the id field of the IP_Addresses table.
- Both of the above fields taken as a pair are unique; i.e. you’d never have a collection ID to ip address id pair that exists more than once in the table.
‘IP_Addresses’ – This table stores the actual list of IPv4 and IPv6 addresses defined on the server that Plesk has been made aware of. Important fields include:
- id – unique number
- ip_address – the text form of a given IP; IPv6 addresses are stored in lowercase hex and not compressed, but they will be stripped of leading zeroes per field.
- default_domain_id – the id number from the ‘domains’ table of a domain that has been configured to be served as the default site for the IP if a request were to come in to that IP without specifying the domain.
‘ip_pool’ – This table simply stores if a given IP address is shared or exclusive.
- ip_address_id – The id field from the IP_Addresses table.
- type – a enumeration field of either ‘shared’ or ‘exclusive’

Tracking a domain to IP in the database

Okay, so lets traverse all these tables to find a particular website’s IP address(es):

We know the domain name, ‘domain.com’ and find it in the domains table. It has id number 1.
Armed with our knowledge that the id number is 1, we query the DomainServices table for the ipCollectionId where the type is ‘web’ and dom_id is 1. In SQL form:
SELECT ipCollectionId FROM DomainServices WHERE type = ‘web’ AND dom_id = ‘1‘
Now we have what should be one, and only one, id number; we’ll say it is 10 for the sake of example queries. If you got more than one result, you either forgot to specify ‘type=web’ or your database has problems.
We need to query the IpAddressesCollections table for the ipAddressId where ipCollectionId is the value we retrieved in the previous step; 10 for our example. In SQL form:
SELECT ipAddressId FROM IpAddressesCollections WHERE ipCollectionId = ‘10‘
There are a number of possible responses to that query. If the site is configured and set with NO ip addresses, you’ll get nothing. If it has just one, you’ll get one record. If it has two, you’ll get two records back. I don’t believe under normal circumstances would you ever get more than two since, at most, you should be able to configure just one IPv4 and one IPv6 address.
Finally, now that we have the id numbers, we can query the IP_Addresses table for each of them:
SELECT ip_address,default_domain_id FROM IP_Addresses WHERE id = ?
You’ll need to run the above query once for each response you got from the IpAddressesCollections table, so if the site has both IPv4 and IPv6, you’ll end up altering the id number you query for and finding both addresses.
If you care whether the address in question is set as shared or exclusive in Plesk, you can query the ip_pool table for ‘type’ where the ip_address_field is the number from the table you just queried, or the address itself can be used in a subquery to find the id. Keep in mind that the ip_pool table has a type assignment for the same address multiple times if that address has been granted from admin to one or more resellers and/or clients. You can account for that in your query if you only want one response; in SQL:
SELECT DISTINCT type FROM ip_pool WHERE ip_address_id = (SELECT id FROM IP_Addresses WHERE ip_address = “IP address”)
or
SELECT DISTINCT type FROM ip_pool WHERE ip_address_id = ID

Useful SQL queries (run against the Plesk psa database)

Retrieve a list of domain names on the server and their corresponding IP address, or both IPv4 and IPv6 address if the domain has both, although that will be output as two lines with the domain name on each, so watch out for that if you’re using this data with a script that expects only one line per domain:
```
SELECT
  D.name AS DomainName,
  IPA.ip_address AS IPaddress
FROM
  domains D,
  DomainServices DS,
  IpAddressesCollections IAC,
  IP_Addresses IPA
WHERE D.id = DS.dom_id
  AND DS.ipCollectionId = IAC.ipCollectionId
  AND IAC.ipAddressId = IPA.id
  AND DS.type = 'web'
ORDER BY D.name
```
Retrieve a list of not-in-use IP addresses from a Plesk server (perl script to do this for you):

SELECT
    IP_Addresses.id,
    IP_Addresses.ip_address
FROM
    IP_Addresses
WHERE
    IP_Addresses.default_domain_id = '0' AND
    IP_Addresses.id NOT IN
    (
        SELECT DISTINCT
            ipAddressId
        FROM
            IpAddressesCollections
    )
ORDER BY
    INET_ATON(IP_Addresses.ip_address) ASC
;

Retrieve a list of IP addresses (both IPv4 and IPv6) that are defined as exclusive yet have more than one domain assigned to them:

SELECT
    iac.ipAddressId,
    ia.ip_address
FROM
    domains od,
    DomainServices ods,
    IpAddressesCollections iac,
    IP_Addresses ia
WHERE
    od.parentDomainId           = 0                     AND
    ods.dom_id                  = od.id                 AND
    ods.type                    = 'web'                 AND
    iac.ipCollectionId          = ods.ipCollectionId    AND
    ia.id                       = iac.ipAddressId       AND
    iac.ipAddressId IN
    (
        SELECT
            ipac.ipAddressId
        FROM
            domains d,
            DomainServices ds,
            IpAddressesCollections ipac,
            ip_pool ipp
        WHERE
            d.parentDomainId    = 0                    AND
            ds.dom_id           = d.id                 AND
            ds.type             = 'web'                AND
            ipac.ipCollectionId = ds.ipCollectionId    AND
            ipp.ip_address_id   = ipac.ipAddressId     AND
            ipp.type            = 'exclusive'
    )
GROUP BY
    iac.ipAddressId
HAVING
    COUNT( * ) &gt; 1
;

List all primary domain names that are configured for hosting (i.e. not aliases, not subdomains, not frame forwards, not redirects) AND their ‘ipCollectionId’ which maps to the IP address(es) the domains are hosted on:

SELECT
    d.name,
    ds.ipCollectionId
FROM
    domains d,
    DomainServices ds
WHERE
    d.id = ds.dom_id       AND
    ds.type = 'web'        AND
    d.parentDomainId = '0' AND
    d.htype = 'vrt_hst'
ORDER BY
    d.name ASC
;

VERY simple one; this query simply outputs the list of IP addresses on the server (that Plesk knows about):
```
SELECT * from IP_Addresses ORDER BY INET_ATON(ip_address) ASC;
```
Useful when combined with the above query; this one will give you all of the domains associated with a specific IP address ID number. So you’d run the above query to get your list of addresses and their associated id numbers, then you can use this query to find out which domains are on it. You must replace the ‘##’ with the id number from the above:
```
SELECT
    d.name
FROM
    domains d,
    DomainServices ds,
    IpAddressesCollections iac
WHERE
    d.id = ds.dom_id       AND
    ds.type = 'web'        AND
    d.parentDomainId = '0' AND
    d.htype = 'vrt_hst'    AND
    ds.ipCollectionId = iac.ipCollectionId  AND
    iac.ipAddressId = '##'
ORDER BY
    d.name ASC
;
```

Retrieve a list of IP addresses (ipv4 and ipv6) that are defined as ‘exclusive’ in Plesk, meaning they should be dedicated to one domain’s hosting, but actually have more than one domain defined on them. This condition should be corrected by either moving domains off the IP in question, or setting it as a shared IP instead.

SELECT
    iac.ipAddressId,
    ia.ip_address
FROM
    domains od,
    DomainServices ods,
    IpAddressesCollections iac,
    IP_Addresses ia
WHERE
    od.parentDomainId           = 0                     AND
    ods.dom_id                  = od.id                 AND
    ods.type                    = 'web'                 AND
    iac.ipCollectionId          = ods.ipCollectionId    AND
    ia.id                       = iac.ipAddressId       AND
    iac.ipAddressId IN
    (
        SELECT
            ipac.ipAddressId
        FROM
            domains d,
            DomainServices ds,
            IpAddressesCollections ipac,
            ip_pool ipp
        WHERE
            d.parentDomainId    = 0                    AND
            ds.dom_id           = d.id                 AND
            ds.type             = 'web'                AND
            ipac.ipCollectionId = ds.ipCollectionId    AND
            ipp.ip_address_id   = ipac.ipAddressId     AND
            ipp.type            = 'exclusive'
    )
GROUP BY
    iac.ipAddressId
HAVING
    COUNT( * ) &gt; 1
;

More to come!

Relevant Database Tables

Tracking a domain to IP in the database

Useful SQL queries (run against the Plesk psa database)

Leave a Reply Cancel reply