h1. Database Queries

h3. 50 most recent package changes

<pre>
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
;
</pre>

h3. Package updates grouped by host,time

<pre>
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
;
</pre>

h3. Individual update on a host

<pre>
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
;
</pre>

h3. Entire Package history for a host

<pre>
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
;
</pre>