Using postgres I am trying to write a query that will show me which devices do not have all access-list entries defined on them.

The table net_schema.acl_ssh defines the master list of ACL entries that all devices should have:

The table net_config.acl contains the ACL entries that each device has:

The table net_inv.master is my inventory table of all devices:

The query should return device "sw01" since it is missing entry "20.20.20.20" and "sw03" since it's missing all ACL entries.

CodePudding user response：

I usually shy away from select distinct, but this seems like a reasonable use case if ever there was one.

I'm effectively using the left join to find entities where an inner join would fail.

select distinct
  i.hostname
from
  net_inv.master i
  cross join net_schema.acl_ssh s
  left join net_config.acl a on
    i.hostname = a.hostname and
    s.addr = a.src_addr
where
  a.hostname is null

For some background/explanation, this query gives you the full output, so you can see why the where condition followed by distinct narrows it down to the ones missing entries:

select *
from
  net_inv.master i
  cross join net_schema.acl_ssh s
  left join net_config.acl a on
    i.hostname = a.hostname and
    s.addr = a.src_addr

CodePudding user response：

I would blow out the hostname and src_addr combinations with a cross join and then use an except to find the missing records:

with needed as (
  select m.hostname, 'ssh' as acl_id, s.addr as src_addr
    from net_inv.master m
         cross join net_schema.acl_ssh s
)
select hostname, acl_id, src_addr from net_schema.acl
except
select hostname, acl_id, src_addr from needed;