Raw file path for multiple computers

[msutharh]

We need Raw file path of multiple applications and for multiple computers and for that we are trying to build custom report from Report Builder but unable to find its relevant fields to get this report.

We are looking for below fields in Report builder. Can someone help me on this?

We have tried some tables but it is not giving desired output. (we are using FNMS on-prem 2022 R2.)

FileEvidencePath, ImportedFileEvidence_MT

Computer Name	IP Address	Application Name	Raw Name	Raw File path	Raw version

[JohnSorensenDK]

@msutharh 

I don't think that there are any evidence objects associated with the application object within a custom report, so a custom report within FNMS would not be able to produce the needed information.

If you need this information for just a few devices, you can inspect/export the file evidence from the Evidence tab of the Inventory device's properties page.

If you have a significant amount of devices/applications then you may want to query the database directly by referring to its schema.

Thanks,

[msutharh]

Hi @JohnSorensenDK I am not able to find correct table from Schema guide and I am looking evidence path details for multiple Inventory devices. Can you please guide me which table I need to refer for InstlallerEvidence and FileEvidence ? I am looking into below table.

InstalledInstallerEvidenceDetail, InstalledFileEvidenceDetail, ImportedFileEvidenceMapping, ImportedFileEvidenceMapping_MT, FileEvidenceFile, FileEvidencePath.

[bfaller]

Raw evidences are not available using report builder. 3 means to see them per:

 device, sql request & Flexera'special report like transparency report.

Last solution is very open and do not need any sql. You can link raw evidence (if needed new local ones) to an ad hoc special local application (and product) to do what you need (do not forget, transparency report asks for a product, not  application).

[erwin_lindemann]

Hi,

As @bfaller indicated, the information that you are looking for is not available from the FNMS report builder.

You have been looking into the correct tables in the FNMS data model though.

The following SQL will retrieve exactly what you are looking for:

SELECT 
  device.ComputerName       AS [Computer Name]
, device.IPAddress          AS [IP Address]
, title.FullName            AS [Application Name]
, f_evidence.FileName       AS [Raw Name]
, f_evidence.FilePath       AS [Raw File path]
, f_evidence.FileVersion    AS [Raw version]
FROM   
[ComplianceComputer] device
CROSS APPLY  
dbo.UtilsDeviceFileEvidence(device.ComplianceComputerID) f_evidence
JOIN
[SoftwareTitle]  title on title.SoftwareTitleID = f_evidence.SoftwareTitleID

For being able to run this SQL, you must insert a procedure named 'UtilsDeviceFileEvidence' into the FNMS Compliance database first. You find this procedure in the attachment.

Please be aware that if you run the query above on a production database, the query will be slow and it will return a lot of rows. You may want to restrict the query to a few computers only.

With some additional work, this query could be exposed as a report in the FNMS UI, or the additional properties could be exposed in the FNMS report builder.

We typically do similar work in our customer projects ...

[msutharh]

Thanks @erwin_lindemann @erwin_lindeman

I just executed given query on single computer but the record count is showing different. I got 827 File Evidence count in Inventory Properties -> File Evidence whereas in query I got only 20 records. and I found netcool file path details in Inventory Properties but not found in query result.

For example, I need path details from File Evidence of multiple computers for IBM Tivoli Netcool application.

I have attached snapshot from Inventory properties and query result for reference.

[erwin_lindemann · Accepted solution]

Hi Again 😀,

The stored procedure and the sample query have been designed to return file evidence for applications on the device that have been 'recognized' by FNMS (linked to an application contained in the [SoftwareTitle] table) only.

If you want to return ANY file evidence, you can use a 'relatively simple' query like the following:

-- Enter a computer name search pattern to restrict the amount of output
DECLARE @ComputerFilter nvarchar(256)
SET @ComputerFilter = '%'

select DISTINCT
	ic.ComputerName, 
	ic.LastLoggedOnUser, 
	ife.Description, 
	ife.FileVersion, 
	ife.FileName, 
	ife.FileSize, 
	ife.FilePath, 
	ife.Company
from 
	ImportedFileEvidence ife WITH (NOLOCK)
	join ComplianceConnection cc  WITH (NOLOCK) 
		on ife.ComplianceConnectionID = cc.ComplianceConnectionID
	join ImportedInstalledFileEvidence ii WITH (NOLOCK) 
		on ife.ExternalFileID  = ii.ExternalFileID and ii.ComplianceConnectionID = cc.ComplianceConnectionID
	join importedComputer ic WITH (NOLOCK) 
		on ife.ComplianceConnectionID = ic.ComplianceConnectionID and ii.ExternalID = ic.ExternalID
WHERE 
	ic.ComputerName LIKE @ComputerFilter
ORDER BY 
	ic.ComputerName, ife.FileName

[JJacildo]

Hello @msutharh , may I recommend our out-of-the-box Application Transparency report?

This should allow you to see File Paths for each application installed on a device. Please check the link below for reference in how to implement this reporting.

https://docs.flexera.com/fnms/EN/WebHelp/index.html#topics/Rep_AppTransprncy.html

I hope that helps.

[msutharh]

Hello @JJacildo 

It's not helpful as it shows results only for java product when I search Product as Tivoli Netcool then it shows blank result and If I run Report with All Product then it shows only JAVA results. Same I have checked in 2022 on-prem and Cloud version but result is same for both.