Raw Evidence on Computers custom SQL report

[#1]

 

• Owner: Nicolas Rousseau
• Solution Type: Custom SQL Reports
• Flexera Product & Version: FNMS on Premise. One version up to 2019R1, One for 2019R2+
• Environment:
• Development Effort: 1 day
• Implementation Days: 0.1 Day
• Pre-Requisites: DB access with read write access
• Disclaimer: 

SOLUTIONS ARE PROVIDED ON AN "AS IS" BASIS. NEITHER FLEXERA NOR ITS SUPPLIERS MAKE ANY WARRANTIES, EXPRESS OR IMPLIED, STATUTORY OR OTHERWISE, INCLUDING BUT NOT LIMITED TO WARRANTIES OF MERCHANTABILITY, TITLE, FITNESS FOR A PARTICULAR PURPOSE OR NONINFRINGEMENT. LICENSEE MAY HAVE OTHER STATUTORY RIGHTS. HOWEVER, TO THE FULL EXTENT PERMITTED BY LAW, THE DURATION OF STATUTORILY REQUIRED WARRANTIES, IF ANY, WILL BE LIMITED TO THE SHORTER OF (I) THE STATUTORILY REQUIRED PERIOD OR (II) THIRTY (30) DAYS FROM LICENSEE’S ACCEPTANCE OF THE AGREEMENT.

Goal of the views and approach

Business need

For some years now, FNMS provides the transparency on evidences in the evidence tab of the inventory device detail... devices are now listed in the evidences details... but understanding evidences with recognition across devices, or getting this transparency if you are in an old version can be useful.  

Management of applications recognition is an advanced area that requires sometimes analysis and tracing back to objects that are not always visible in FNMP for performance reasons particularly

• File evidences 
• Installer (add remove programs typically)

And WMI evidences (used to identify applications that have no reliable file or installer evidences (SQL Server (for Editions), Windows etc.).

• The mapping between evidences and applications deserves a short explanation:

Found evidences (Name: acrobat.exe, version: 10.0.0.396, Publisher: Adobe) will be mapped to ARL evidences (Name acrobat.exe, Version: 10.%, Publisher: Acrobat%). These file evidences are mapped to applications (A title, linked to Product, Version and Edition).

The challenge is that these mappings can be n to n, the sources for evidences can be multiple and overlapping, the evidences can be used for recognition of just to catch usage up to the application (“Not for recognition” for example on acrobat.exe that has no information of edition for instance).

The present view can be imported in FNMP custom views using SQL Server Management Studio with a Power User on the FNMP Database (that can create stored procedures and views records)

Three versions of the view exist

Pre-requisites and reversibility

An initial check will be to make sure that no view or stored procedures already exist with the same name. I have prefixed the records with nr_Cust, which should decrease this risk.

You need to define what folders we want to see these view in. the “-17” folder being the root folder

Since the evidences tables can be huge (45 million records typically for the file evidences to a 30 000 devices implementation), this is key to filter the records loaded in the view to get acceptable performance. The view can be filtered with a “ComputerName|%EvdenceName%” using the search bar on the view.

Raw Evidence on computers (NR)

Description

This view looks up on all evidences on computers and uses a "Computer|Application" filter that you need to set in search box of this report. "|Visio.exe" will return all visio.exe evidences. "SRV01|" will return all evidences on SRV01'

The view contains the following:

• Evidence Name
• Evidence version
• Publisher
• Path
  • File Path
  • Or Installer evidence package installation location and package name (added April 21st, 2023).
• Inventory date (can be interesting when multiple inventory sources are merged)
• Installation date (relevant only for installer evidence)
• The mapped ARL Evidences
• The mapped ARL applications with type of mapping rule)
• The application actually recognized for this evidence
• The computer Name
• Computer Location
• The Computer OS
• The evidence type (File, Installer (add / remove programs), WMI)
• File Path: only path for files inventoried by FlexNet Inventory Manager will be accurate. For other inventory sources, this will be the most common path (this approach is taken for an import performance optimization)
• The inventory source for the evidence

Preview

You need to input in the view’s search bar the filter: here, computerName = LT-ITA-100117 and the searched evidence name is “acroba”.

The evidence search will perform a “ComputerNme = ’LT-ITA-100117’ AND EvidenceName like ‘%acroba%’

The created report is accessible from the root node of the report index. As this is a custom report not using the standard reporting objects, the report cannot be modified (it can be deleted)

The view will provide useful information: evidence name, evidence type, historical last used dates, mapped ARL evidences, mapped applications and type of mapping (could be not for recognition, required…)

Given the small number of WMI evidences and the link to application names that is not always obvious, the computer filer will show all WMI evidence. The evidence name filter will filter also WMI evidence (try with "SQL").

 

ARL Matched Evidences on Computers (NR)

Description

This view looks up on all matched ARL evidences on computers and uses a "Computer|EvidenceName" filter that you need to set in search box of this report. "|Visio.exe" will return all visio.exe evidences. "SRV01|" will return all evidences on SRV01. This report does not retrieve raw imported evidences and is adapter to checking remote application data

Preview 

Installer Evidences on computers (NR)

Description

This view looks up on all evidences on computers and uses a "Publisher|EvidenceName" filter that you need to set in search box of this report. "|Visio" will return all evidences containing "Visio" . "Micros|" will return all evidences containing "Micros"

Preview

  

Views Creation code

To create these views, run query below from the SQL Server Management Studio. (Please use the queries in embedded file).

Version 2016 to 2019R2

Important: the view join the Inventory Manager database to catch the file path that is not imported into the SAM database (FNMSCompliance). You need to check the name of the Inventory Manager Database that could be IM or FNMSInventory and replace with the appropriate name in the section of the SQL code with the “be careful” comment

AND iie.ComplianceConnectionID = (SELECT ComplianceConnectionID FROM ComplianceConnection WHERE ConnectionTypeID = 1 AND PrimaryConnection = 1) -- Be careful to the IM db name here!

Variation: focus on ARL evidences (to address the need for remote evidences) collection (on Citrix, on raw evidence is collected for instance).

Variation 2: The “Installer Evidences on computers (NR)” shows       only installer evidences and has an Publisher|EvidenceName filter

Version 2019R2 +

Starting with FNMS 2019R2, a new “Raw File Path” table has been created. There is no more normalization of the path and the report catches the real path for all inventory sources. No more need to check the Inventory Manager database name.

[nrousseau1]

Version 19 includes Installer evidence information collected in the Inventory Manager that gives precious additional information (Installation location and installation package)

[avi0408]

Hello @nrousseau1 , thank you for sharing this custom report. It helped us alot in performing remediations. However, we want your help to understand how we can fetch the location/path for installer evidence with type "Adobe". For these installer evidences, we are not able ot see any installatin location/path in this custom report. Is there any furhter enhanced version of this query which cna help us.

Thanks,

Awadh

[nrousseau1]

Hello @avi0408 ,

The Adobe evidences that catch the activated editions check in the Registry fir users authenticated in the last 90 days the following keys:

Computer\HKEY_CURRENT_USER\Software\Adobe\Adobe Acrobat\DC\AVEntitlement\iEntitlementLevel # ID the install:

• 300: Acrobat Pro
• 200: Acrobat Standard

Then, there is a correlation between this key and the "add/remove program" installer evidence (uninstall) that is named "Acrobat" from "Adobe") that unfortunately does not tell us if the is the Reader or Acrobat (eventually activated for any edition) to determine the version of Acrobat.

More information on

https://community.flexera.com/t5/FlexNet-Manager-Knowledge-Base/Challenges-identifying-installations-of-Adobe-Acrobat-and-how/ta-p/241207

and on: https://docs.flexera.com/fnms/EN/features/index.html#FeatureList/2022R1-6/RN-chg-AgentAdobeExtension.html

Best regards,

Nicolas

[sdbash]

Hi @nrousseau1,

I have a customer that has expressed some interest in getting some of these reports into their web portal.  I attempted to add these reports to my TEST environment before production deployment, and while the SQL query says it was executed successfully, I am not able to locate the reports in the portal.  When running the following queries, I can see that the reports have entries into the SQL tables (as it threw an error warning during the ComplianceSchema_schemacheck.sql action during the database migration step of platform upgrade), as well as when they were added:

Query 1:  

SELECT 'ComplianceSavedSearch ' + CAST(ComplianceSavedSearchID AS NVARCHAR) + ' (SearchNameResourceName ' + ISNULL(SearchNameResourceName, 'NULL') + ', SearchName ' + ISNULL(SearchName, 'NULL') + ') does not exist for all tenants' AS MigrationErrors FROM dbo.ComplianceSavedSearch_MT AS t1 CROSS APPLY (SELECT COUNT(*) AS ReportCount FROM dbo.ComplianceSavedSearch_MT AS t2 WHERE t2.ComplianceSavedSearchSystemID = t1.ComplianceSavedSearchSystemID) a WHERE t1.TenantID = 1 AND ReportCount <> (SELECT COUNT(*) FROM dbo.Tenant)

Query 2:

SELECT * from ComplianceSavedSearch_MT WHERE TenantID = 1 AND ComplianceSavedSearchSystemID IS NULL

Being that these are custom reports, I am unable to get much assistance from the support team and they have suggested reaching out this way.  Thanks in advance!

Cheers,

Shawn

[ChrisG]

@sdbash - some input from @nrousseau1 from his vacation 😎:

For these reports to work on a multi-tenant FlexNet Manager Suite instance, you will need to modify the @TenantID parameter value that is used when calling the ComplianceCustomViewRegister stored procedure. Change the value from "1" as specified in the scripts in the attached Word document to the ID of the tenant you want to register the report for:

EXEC ComplianceCustomViewRegister
       @TenantID = 1, -- <<<< change "1" to the tenant ID to register the report for
       [...]

[sdbash]

Hi @ChrisG and @nrousseau1,

Really appreciate the quick response!  That update looks to have done the trick!

Cheers,

Shawn

[pcsekar]

Hi Good Afternoon,

The embedded document getting failed to download.  Could you help me to download.

Regards

Chandrasekaran.

[nrousseau1]

Hello @pcsekar , are you not able to download the Word Document or not able to open the embedded word document that contains the code?

I just tried both successfully.

If this is the second issue, make sure you "enable content" when prompted (nice Microsoft security feature that sometimes causes issues), otherwise, you can't open an embedded document in the Word document.

Best regards,

Nicolas