Database Queries¶

50 most recent package changes¶

select
        host.hostname,
        pkg.name,
        pkg.arch,
        pkg.version,
        pkg.release,
        host_pkg.timestamp
from
        host,
        host_pkg,
        pkg
where
        host.id=host_pkg.host_id
and
        pkg.id=host_pkg.pkg_id
order by
        timestamp desc
limit
        50
;

Package updates grouped by host,time¶

select
        host.hostname,
        count(pkg.name) as updates,
        date_format(host_pkg.timestamp,'%Y-%m-%d %H:%m') as timestamp
from
        host,
        host_pkg,
        pkg
where
        host.id=host_pkg.host_id
and
        pkg.id=host_pkg.pkg_id
group by
        timestamp,hostname
order by
        timestamp desc
limit
        50
;

Individual update on a host¶

select
        host.hostname,
        pkg.name,
        pkg.arch,
        pkg.version,
        pkg.release,
        host_pkg.timestamp
from
        host,
        host_pkg,
        pkg
where
        host.id=host_pkg.host_id
and
        pkg.id=host_pkg.pkg_id
and
        host.hostname="sdcdb01" 
and
        host_pkg.timestamp like "2009-04-24 15:31%" 
order by
        timestamp desc
;

Entire Package history for a host¶

select
        host.hostname,
        pkg.name,
        pkg.arch,
        pkg.version,
        pkg.release,
        host_pkg.timestamp
from
        host,
        host_pkg,
        pkg
where
        host.id=host_pkg.host_id
and
        pkg.id=host_pkg.pkg_id
and
        host.hostname="sdcdb01" 
order by
        timestamp desc
;