Hi,

i hope that the result matching your goal.

USE FNMSCompliance;

GO

SELECT

  cc.ComplianceComputerID             AS ComplianceComputerID,

  cc.ComputerName                AS ComputerName,

  ccs.DefaultValue                AS [Status],

  st.SoftwareTitleID               AS SoftwareTitleID,

  st.FullName                  AS RecognizedApplication,

  iife.ExternalFileID              AS ExternalFileID,

  ifem.FileEvidenceID              AS FileEvidenceID,

  ife.FileName                  AS FileName,

  ife.FileVersion                AS FileVersion,

  COALESCE(iifep.ExternalFilePath, ife.FilePath) AS FilePath,

  ife.Company                  AS Company,

  ife.FileSize                  AS FileSize,

  ife.Description                AS Description,

  CAST(NULL AS DATETIME)             AS InsertTime

FROM dbo.ImportedInstalledFileEvidence AS iife

INNER JOIN dbo.ImportedComputer AS ic

  ON ic.ComplianceConnectionID = iife.ComplianceConnectionID

  AND ic.ExternalID       = iife.ExternalID

INNER JOIN dbo.ComplianceComputer AS cc

  ON cc.ComplianceComputerID = ic.ComplianceComputerID

LEFT JOIN dbo.ComplianceComputerStatus AS ccs

  ON ccs.ComplianceComputerStatusID = cc.ComplianceComputerStatusID

INNER JOIN dbo.ImportedFileEvidence AS ife

  ON ife.ComplianceConnectionID = iife.ComplianceConnectionID

  AND ife.ExternalFileID     = iife.ExternalFileID

LEFT JOIN dbo.ImportedInstalledFileEvidencePath AS iifep

  ON iifep.ComplianceConnectionID = iife.ComplianceConnectionID

  AND iifep.ExternalFilePathID   = iife.ExternalFilePathID

INNER JOIN dbo.ImportedFileEvidenceMapping AS ifem

  ON ifem.ComplianceConnectionID = iife.ComplianceConnectionID

  AND ifem.ExternalFileID     = iife.ExternalFileID

INNER JOIN dbo.SoftwareTitleFileEvidence AS stfe

  ON stfe.FileEvidenceID = ifem.FileEvidenceID

INNER JOIN dbo.SoftwareTitle AS st

  ON st.SoftwareTitleID = stfe.SoftwareTitleID

INNER JOIN dbo.InstalledSoftwareData AS isd

  ON isd.ComplianceComputerID = cc.ComplianceComputerID

  AND isd.SoftwareTitleID   = st.SoftwareTitleID

WHERE

  ic.ComplianceComputerID IS NOT NULL

  AND stfe.EvidenceExistenceRuleID IN (1, 2, 4)

ORDER BY

  cc.ComputerName,

  st.FullName,

  ife.FileName,

  FilePath;