Hi,
Yeah - the SQL for retrieving the 'Raw Evidence' for applications from the [FNMSCompliance] becomes a little involved though.
As you can see in the SQL code below, you have join multiple tables for finally linking inventories ([ComplianceComputer]) with the raw evidence data imported from your Inventory source ([ImportedInstallerEvidence]).
The original version of each application will be listed in the [VersionRaw] column. You can use the 'Application' and 'VersionRaw' columns for filtering.
Without any filtering and with a large number of inventories, performance of this SQL query most likely will become a concern.
;WITH cteImportedComputer
(
ComplianceComputerID, ComplianceConnectionID, ExternalID, RowNum
) AS
(
/* Retrieve the latest [ImportedComputer] */
SELECT
ic.ComplianceComputerID
, ic.ComplianceConnectionID
, ic.ExternalID
, ROW_NUMBER() OVER
(PARTITION BY ic.ComplianceConnectionID, ic.ComplianceComputerID
ORDER BY ic.InventoryDate DESC) AS RowNum
FROM ImportedComputer ic
),
cteEvidenceLatest
(
ComplianceComputerID
, SoftwareTitleID
, InstallerEvidenceID
, ComplianceConnectionID
, ExternalInstallerID
, RowNum
) AS
(
/* Retrieve only the latest Installer Evidence */
SELECT
ComplianceComputerID
, stie.SoftwareTitleID
, stie.InstallerEvidenceID
, cis.ComplianceConnectionID
, iiem.ExternalInstallerID
, ROW_NUMBER() OVER
(PARTITION BY ComplianceComputerID, stie.SoftwareTitleID
ORDER BY iiie.InstallDate DESC) AS RowNum
FROM dbo.SoftwareTitleInstallerEvidence stie
JOIN cteImportedComputer cis
ON cis.ComplianceComputerID = ComplianceComputerID
AND cis.RowNum = 1
JOIN dbo.ImportedInstallerEvidenceMapping iiem
ON iiem.InstallerEvidenceID = stie.InstallerEvidenceID
AND iiem.ComplianceConnectionID = cis.ComplianceConnectionID
JOIN dbo.ImportedInstalledInstallerEvidence iiie
ON iiie.ComplianceConnectionID = cis.ComplianceConnectionID
AND iiie.ExternalInstallerEvidenceID = iiem.ExternalInstallerID
AND iiie.ExternalComputerID = cis.ExternalID
WHERE
stie.SoftwareTitleID = SoftwareTitleID
),
cteSoftwareEvidence
(
ComplianceComputerID
, SoftwareTitleID
, InstallerEvidenceID
, DisplayName
, [Version]
) AS
(
SELECT
cel.ComplianceComputerID
, cel.SoftwareTitleID
, cel.InstallerEvidenceID
, iie.DisplayName
, iie.[Version]
FROM cteEvidenceLatest cel
JOIN dbo.ImportedInstallerEvidence iie
ON iie.ComplianceConnectionID = cel.ComplianceConnectionID
AND iie.ExternalInstallerID = cel.ExternalInstallerID
WHERE
cel.RowNum = 1
)
SELECT
isd.ComplianceComputerID
, isd.InstalledSoftwareID
, isd.InstallDate AS InstallDate
, st.Fullname AS [Application]
, stpro.ProductName AS ProductName
, stv.VersionName AS [Version]
, stpub.PublisherName AS PublisherName
, isd.DiscoveryDate AS DiscoveryDate
, cc.InventoryDate AS InventoryDate
, stc.DefaultValue AS [Classification]
, ste.EditionName AS Edition
, cse.DisplayName AS DisplayName
, cse.Version AS VersionRaw
FROM dbo.InstalledSoftwareData isd
LEFT JOIN dbo.SoftwareTitle st
ON isd.SoftwareTitleID = st.SoftwareTitleID
LEFT JOIN dbo.SoftwareTitleProduct stpro
ON st.SoftwareTitleProductID = stpro.SoftwareTitleProductID
LEFT JOIN dbo.SoftwareTitlePublisher stpub
ON stpro.SoftwareTitlePublisherID = stpub.SoftwareTitlePublisherID
LEFT JOIN dbo.SoftwareTitleVersion stv
ON st.SoftwareTitleVersionID = stv.SoftwareTitleVersionID
LEFT JOIN dbo.SoftwareTitleEdition ste
ON st.SoftwareTitleEditionID = ste.SoftwareTitleEditionID
LEFT JOIN dbo.SoftwareTitleClassificationI18N stc
ON st.SoftwareTitleClassificationID = stc.SoftwareTitleClassificationID
LEFT JOIN dbo.ComplianceComputer cc
ON cc.ComplianceComputerID = isd.ComplianceComputerID
LEFT JOIN cteSoftwareEvidence cse
ON cse.ComplianceComputerID = isd.ComplianceComputerID
AND cse.SoftwareTitleID = isd.SoftwareTitleID
