Raw Evidence on Computers custom SQL report
- Owner: Nicolas Rousseau
- Solution Type: Custom SQL Reports
- Flexera Product & Version: FNMS on Premise. One version up to 2019R1, One for 2019R2+
- Development Effort: 1 day
- Implementation Days: 0.1 Day
- Pre-Requisites: DB access with read write access
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
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)
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
- 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
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)
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
Installer Evidences on computers (NR)
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"
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.