[RESOLVED] Request to display dmi audit data

[psgca85]

Good morning,

Since the upgrade to version 2.6, DMI data is no longer stored in the hosts table. It is retrieved using DMI auditing.
How can I retrieve this data using a query?.
For example, in version 2.5, to retrieve TPM information from PCs, it was enough to make this request.

Code: Select all

SELECT
	    computer_name,
	    host_info -> 'tpm' AS tpm_infos
FROM
	    hosts
WHERE
   computer_name = 'nomdupc'

Thank you in advance.

Benoit.

[sfonteneau]

Example to retrieve Vendor from BIOS_Information in the audit-dmi package

Code: Select all

WITH cte AS (
    SELECT ctid,
           host_id,
           row_number() OVER (PARTITION BY host_id, value_key ORDER BY value_date) AS rank
    FROM HostAuditData
    WHERE value_section = 'audit-dmi' AND value_key = 'audit-dmi'
)
SELECT
    cte.host_id,
    h.computer_fqdn,
    had.value->'BIOS_Information'->'Vendor' as Vendor
FROM HostAuditData had
JOIN cte ON cte.ctid = had.ctid
JOIN hosts h ON h.uuid = cte.host_id
WHERE cte.rank = 1;

[psgca85]

Hello,
Thank you for this answer, which I was able to adapt to my needs.
Sincerely,
Benoit.

[dcardon]

Hello Benoit,

feel free to post your version of the query as a reply in the forum; there are probably others besides you who will have the same question.

- Denis

[psgca85]

Hello Denis,

Below is my query to find the TPM version.

Code: Select all

WITH cte AS (
    SELECT ctid,
           host_id,
           row_number() OVER (PARTITION BY host_id, value_key ORDER BY value_date) AS rank
    FROM HostAuditData
    WHERE value_section = 'audit-dmi' AND value_key = 'audit-dmi'
)
SELECT
    cte.host_id,
    h.computer_fqdn,
    had.value->'TPM_Device'->'Specification_Version' as TPMVersion
FROM HostAuditData had
JOIN cte ON cte.ctid = had.ctid
JOIN hosts h ON h.uuid = cte.host_id
WHERE cte.rank = 1;

Sincerely,

Benoit.

[dcardon]

THANKS