Flexera query for an executable

[hebertal74]

Hello,

I am looking for an SQL query that can generate the following information:

Computer Name
File name (example Excel.exe)
FileVersion
FilePath

We have Flexera on premise. The File Evidence view on the console gives me a unified view of excel.exe 16.0% but I am looking for a breakdown of all machines with the precise version they currently have. Thanks for your help.

[erwin_lindemann]

Here you are 😁:

/* File Evidence per Computer from the [FNMSCompliance] database. */

SELECT DISTINCT
      cc.ComputerName                                      AS  ComputerName
    , ISNULL(fe.FileName,'No File Evidence recorded')      AS  FileName
    , fe.ProductName
    , fe.ProductVersion
    , fe.FilePath
    , fe.FileSize

    , CASE
        WHEN fe.EvidenceStatusID = 1 AND fe.Ignored = 0   -- Active evidence
        THEN 'Yes'
        WHEN fe.FileName IS NULL
        THEN 'No File Evidence recorded'
        ELSE 'No' END                                      AS [UnrecognizedEvidence]
FROM               [ComplianceComputer]           cc  
        JOIN       [ComplianceComputerStatusI18N] stat 
            ON stat.ComplianceComputerStatusID = cc.ComplianceComputerStatusID
        LEFT JOIN  [ComplianceUser]               cu   
            ON ISNULL(cc.AssignedUserID,ISNULL(cc.CalculatedUserID,cc.ComplianceUserID)) 
				= cu.ComplianceUserID
        LEFT JOIN  [InstalledFileEvidence]        ife  
            ON ife.ComplianceComputerID=cc.ComplianceComputerID
        JOIN       [FileEvidence]                 fe   
            ON fe.FileEvidenceID = ife.FileEvidenceID
WHERE 
    /* only devices that are 'active' or 'inactive'  */
    cc.ComplianceComputerStatusID IN (1,2)
ORDER BY 
    cc.ComputerName

[jjoaquin]

Thanks for this erwin, if its not too much to ask, would there be a way to tweak this and show when was the last time the evidence was used or the application was used in associated with the unrecognized evidence?

[hebertal74]

Thank you for your quick reply ! When running the query I was getting several blanks but I found out that some file evidence were not entirely defined with all their fields. Do you know if it is feasible to directly query the raw information since on the FNMS console under File Evidence for a given machine I do see the information listed in the raw version, raw file path, etc. Thanks

[erwin_lindemann]

A few weeks back, I provided a stored procure with example code for a very similar request 'Raw file path for multiple computers' in this forum. The approach used in that request is using more detailed file evidence information from the [Imported ...] tables in FNMS.

Could you give this a try, please?

[hebertal74]

Thanks for your reply Erwin, I tried the query in the discussion you provided. I do get mixed results and doesn't always match what I directly see under machine's file evidence (raw data).
For example if I query for excel.exe the majority of machines are returning me the filepath used for Office updates (such as C:\Program Files (x86)\Microsoft Office\Updates\Download\PackageFile----) instead of the usual Office location (such as C:\Program Files%\Microsoft Office\root\Office16\) . From the console's view the file path is the right one and when creating a new file evidence based on the usual path it works fine. But when using the query it seems it picks a stored value that doesn't always represent where the typical path is. 

[ChrisG · Accepted solution]

Here is a sample query that returns the type of data being sought here:

SELECT cc.ComputerName, ife.FileName, ife.FileVersion, iifep.ExternalFilePath, ic.InventoryAgent, ic.InventoryDate
FROM dbo.ComplianceComputer cc
    JOIN dbo.ImportedComputer ic
        ON ic.ComplianceComputerID = cc.ComplianceComputerID
    JOIN dbo.ImportedInstalledFileEvidence iife
        ON iife.ComplianceConnectionID = ic.ComplianceConnectionID
        AND iife.ExternalID = ic.ExternalID
    JOIN dbo.ImportedFileEvidence ife
        ON ife.ComplianceConnectionID = iife.ComplianceConnectionID
        AND ife.ExternalFileID = iife.ExternalFileID
    JOIN dbo.ImportedInstalledFileEvidencePath iifep
        ON iifep.ComplianceConnectionID = iife.ComplianceConnectionID
        AND iifep.ExternalFilePathID = iife.ExternalFilePathID

This returns data like the following:

In addition to the other discussion thread referenced by @erwin_lindemann that contains some sample SQL queries to report on file evidence, see the following thread for some further examples of queries of this nature that you can use as the basis for your own querying: Raw file path for multiple computers.

Also be careful of the FileEvidence.FilePath column that is used in the first response posted on this thread. That column contains an illustrative (example) path at which the file has be found on at least one computer. It does not help identify the actual path at which the file will be found on any specific computer.

[hebertal74]

Thank you Chris, your sample query does return the right info. Thanks again

[bfaller]

For customer not having access to sql: you can create a local application "ad hoc", with a local product, let say adhoc, create local file evidences corresponding to what are looking for, assign to adhoc application with at least one rule. Then (after reconcialiation), run the transparency report with product adhoc.

Regards.