Page 1 of 1
[RESOLVED] Request to display dmi audit data
Published: March 14, 2025 - 4:49 PM
by 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.
Re: Request to display DMI audit data
Published: March 17, 2025 - 09:47
by 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;
Re: Request to display DMI audit data
Published: March 17, 2025 - 10:49
by psgca85
Hello,
Thank you for this answer, which I was able to adapt to my needs.
Sincerely,
Benoit.
Re: [RESOLVED] Request to display dmi audit data
Published: March 17, 2025 - 10:54
by 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
Re: [RESOLVED] Request to display dmi audit data
Published: March 17, 2025 - 10:59
by 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.
Re: [RESOLVED] Request to display dmi audit data
Published: March 17, 2025 - 2:36 PM
by dcardon
THANKS