Querying Inventory Events Data

To extract all active inventory events stored in the Inventory Management System database, use the following query:

mysql> SELECT                            ev.type,

                                              ev.item,

                                              ev.event_time,

                                              ev.description AS details,

                                              dev.name,

                                              IF(ev.hardware_id != 0, hw.serial_number, "") 

                                              AS serial_number,

                                              IF(ev.hardware_id != 0, hw.description,

                                                    IF(ev.software_id != 0, "",

                                     IF(ev.package_id != 0, pkg.name,

                                                                IF(ev.licensing_id != 0, 

                                                                       lic.feature_description, "")))) 

                                             AS description,

                                              IF(ev.software_id != 0, sw.routing_engine,

                                                    IF(ev.package_id != 0, pkg.routing_engine, "")) 

                                              AS routing_engine

             FROM                             demo.events_inventory ev 

     INNER JOIN                             demo.devices dev ON ev.device_id = dev.id

     LEFT JOIN                         demo.hardware_inventory hw ON ev.hardware_id = hw.id

     LEFT JOIN                         demo.software_inventory sw ON ev.software_id = sw.id

     LEFT JOIN                         demo.packages_inventory pkg ON ev.package_id = pkg.id

     LEFT JOIN                         demo.licensing_inventory lic 

                ON             ev.licensing_id = lic.id

         WHERE                             ev.deleted_on = 0;