Hello all.. apologies if zombie thread is inappropriate but I'd like to revisit this conversation regarding firmware information being included.

We have started a patch to display firmware and date and are hoping to get some input on progressing.  What we were last discussing was to update the device table.  I'm also curious about a migration script.   Here is what we have so far.

Thanks for looking. Shawn.

diff --git a/Server/bkr/server/controllers.py b/Server/bkr/server/controllers.py
index cece8db..32dc0d4 100644
--- a/Server/bkr/server/controllers.py
+++ b/Server/bkr/server/controllers.py
@@ -123,9 +123,11 @@ def default(self, *args, **kw):
                         widgets.PaginateDataGrid.Column(name='driver', getter=lambda x: x.driver, title='Driver', options=dict(sortable=True)),
                         widgets.PaginateDataGrid.Column(name='vendor_id', getter=lambda x: x.vendor_id, title='Vendor ID', options=dict(sortable=True)),
                         widgets.PaginateDataGrid.Column(name='device_id', getter=lambda x: x.device_id, title='Device ID', options=dict(sortable=True)),
-                        widgets.PaginateDataGrid.Column(name='subsys_vendor_id', getter=lambda x: x.subsys_vendor_id, title='Subsys Vendor ID', options=dict(sortable=True)),
+            widgets.PaginateDataGrid.Column(name='subsys_vendor_id', getter=lambda x: x.subsys_vendor_id, title='Subsys Vendor ID', options=dict(sortable=True)),
                         widgets.PaginateDataGrid.Column(name='subsys_device_id', getter=lambda x: x.subsys_device_id, title='Subsys Device ID', options=dict(sortable=True)),
-                       ])
+                       widgets.PaginateDataGrid.Column(name='subsys_device_id', getter=lambda x: x.fw_version, title='Firmware Version', options=dict(sortable=True)),
++                        widgets.PaginateDataGrid.Column(name='subsys_device_id', getter=lambda x: x.fw_date, title='Firmware Date', options=dict(sortable=True)),
+            ])
         return dict(title="Devices",
                     grid = devices_grid,
diff --git a/Server/bkr/server/model/inventory.py b/Server/bkr/server/model/inventory.py
index ecbc073..88301ae 100644
--- a/Server/bkr/server/model/inventory.py
+++ b/Server/bkr/server/model/inventory.py
@@ -297,6 +297,8 @@ class System(DeclarativeMappedObject, ActivityMixin):
     kernel_type_id = Column(Integer, ForeignKey('kernel_type.id'),
            default=select([KernelType.id], limit=1).where(KernelType.kernel_type==u'default').correlate(None),
+    fw_version = Column(String(32))
+    fw_date = Column(DateTime, default=None)
     kernel_type = relationship('KernelType')
     devices = relationship('Device', secondary=system_device_map,
@@ -1246,6 +1248,13 @@ def update(self, inventory):
         self.date_modified = datetime.utcnow()
         return 0
+    def updateSystemFirmware(self, firmware):
+    # TODO finish this method
+    if firmware
+    self.fw_version = firmware['version']
+    self.fw_date = firmware['date']
     def updateHypervisor(self, hypervisor):
         if hypervisor:
             hvisor = Hypervisor.by_name(hypervisor)
@@ -2362,6 +2371,8 @@ class Device(DeclarativeMappedObject):
     device_class = relationship(DeviceClass)
     date_added = Column(DateTime, default=datetime.utcnow, nullable=False)
     systems = relationship(System, secondary=system_device_map, back_populates='devices')
+    fw_version = Column(String(32))
+    fw_date = Column(DateTime, default=None)
 Index('ix_device_pciid', Device.vendor_id, Device.device_id)
diff --git a/Server/bkr/server/templates/system_details.kid b/Server/bkr/server/templates/system_details.kid
index 19ba196..fd6f9d1 100644
--- a/Server/bkr/server/templates/system_details.kid
+++ b/Server/bkr/server/templates/system_details.kid
@@ -156,6 +156,12 @@
+    <td>
+        ${device.fw_revision}
+    </td>
+    <td>
+        ${device.fw_date}
+    </td>

diff --git a/systemscan/main.py b/systemscan/main.py
index 64b9312..41895f2 100755
--- a/systemscan/main.py
+++ b/systemscan/main.py
@@ -286,6 +286,14 @@ def read_inventory(inventory, arch = None, proc_cpuinfo='/proc/cpuinfo'):
     #Break the xml into the relevant sets of data
     cpuinfo = inventory.xpath(".//node[@class='processor']")[0]
+    #system firmware info
+    sysfwinfo = inventory.xpath('.//node[@id="firmware"]')[0]
+    #create dictionary with key-values for version and date
+    SystemFirmware = dict(version = sysfwinfo.findtext('version'), date = sysfwinfo.findtext('date'))
+    #add to data
+    data['SystemFirmware'] = SystemFirmware
     memory_elems = inventory.xpath(".//node[@class='memory']")
     memoryinfo = None
     for m in memory_elems:

On Thu, Apr 27, 2017 at 3:13 AM, Dan Callaghan <dcallagh@redhat.com> wrote:
Excerpts from Don Zickus's message of 2017-04-20 15:21 -04:00:
> Hi Dan,
> Shawn is working with Jon and myself to add system firmware/bios info to the
> beaker database to be displayed on the system details page.
> The idea was to make it easy to find how old a bios is on our lab machines
> and see which ones need an update.
> Shawn made all the front end changes in the inventory job, but the backend
> work is becoming a challenge.
> It seems like we are going to have to add a table entry to the database in
> server/model/inventory.py?  Which would probably cause a database migration
> event.
> For now, we are using numa_nodes as our template to copy the output of lshw
> (from the inventory script) into the database and onto the webpage.
> Are we going in the right direction?

Yes, it's important to settle on a good db structure for this now
because it is quite costly to put it in place and to change it in

The NUMA stuff is not a great example necessarily because it was always
intended that we would flesh it out to store more info about NUMA
topology, but that never happened. Right now it is just a single table
1-to-1 with "system" with a single column. It could have just been a new
column on the system table.

It also depends exactly what info you want to store about the firmware.
Just an opaque version string? Or also a release date? Assuming the
latter, the options I can see would be:

* Add a new table "firmware" which is 1-to-1 with system, rows are
  created for a system when the value is first populated. This is how
  Cpu, Numa, Power, and others work right now.

* Add new columns to the "system" table for firmware version and
  firmware release date. NULLable with default NULL, and populated by
  the inventory script if possible.

* Represent the system firmware as a device in the existing devices
  table (in fact it might already be in there from lshw?). You could add
  new columns to the "device" table for firmware version and firmware
  release date. Indeed this might make more sense, if for example we
  want to record details about *other* firmware inside the system
  besides the actual BIOS (system firmware). Think hard disk firmware,
  or attached USB devices, or similar.

It might help to discuss examples. You said lshw is already extracting
the info you want (which is good). Can you paste a piece of the XML
showing it? Which XML element does lshw put it into?

I have been wanting to gradually move Beaker's device model to more
closely match lshw's tree of devices so this might be a good opportunity
to bring them into closer alignment.

> Is this more work than we thought?

Probably. :-)

The good news is that you don't need to be *too* scared of adding new db
structures. You would have found the existing setup we have with Alembic
migrations, and there is an extensive test suite that goes with them.
The only downside we have is that they require an outage for deployment.

> Then as a follow-on, we are assuming the database has to be updated to
> add
> this entry, would it make sense to convert the whole database to a generic
> key/value table to dynamically support new fields without having to migrate
> the whole database all the time?  And then to prevent random junk from being
> added by the inventory script (or other script), have a whitelist filter
> that only allows certain keys to be added/updated.  Maintaining the
> whitelist would be easier on the database then adding table entries.
> This is just us trying to understand the architecture a little more and
> trying to see where we can add some value to make it easier to maintain our
> tests.

As Nick mentioned, Beaker *does* already have a key-value store for
inventory, based on the original design from RHTS, but we are trying to
get away from it.

Its biggest deficiency is types. Keys can be either strings or numbers,
but that is not really structured enough. For example how do you
represent multiple disks, each of which has various properties like size
and model etc. The existing key-value system has no good way to store
and query that kind of info. And the key names tended to be just
invented by Beaker admins randomly and then not used consistently.

So we prefer instead to build proper db structures to represent the
hardware info. (As we did for disks, for example.)

The other problem with the existing key-value system is performance in
MySQL's query planner, this proved to be quite a nightmarish bug:


For pie-in-the-sky kind of future goals, there are indeed some modern
alternatives like JSON columns in Postgres like Nick suggested. But that
is pretty far off. We are stuck on MySQL 5.1 for the foreseeable future
and migrating MySQL->Postgres is an even bigger task.

Dan Callaghan <dcallagh@redhat.com>
Senior Software Engineer, Products & Technologies Operations
Red Hat


Shawn Doherty

Software Engineer, Kernel-HW

Red Hat

314 Littleton Rd

Westford, MA 01886

sdoherty@redhat.com    T: 19785891080 INTERNAL:-8131080     IM: sdoherty