cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Evidence in report

I got a question from the "upgrade" team. 

They would like a list of all devices that have the evidence version of VIsual Studio 2019 Professional that is lower than 16.11 (so that they can see what devices needs an update).

Is it possible to create a report in this level in FNMS? The ones I have created stops at application level (since that is the most common type of reports).

(Today we are solving it by creating a report for the application and then manually look through all the devices what version they are on)

BR

(1) Solution

You can create a new application VS2019old and associate relevant evidences.

If Flexera'evidences are not enough precise, you can create your own evidences, based on the "raw" evidences you see at each device.

Then just wait for next reconciliation.

 

 

View solution in original post

(3) Replies

You can create a new application VS2019old and associate relevant evidences.

If Flexera'evidences are not enough precise, you can create your own evidences, based on the "raw" evidences you see at each device.

Then just wait for next reconciliation.

 

 

Hi,

Yeah - the SQL for retrieving the 'Raw Evidence' for applications from the [FNMSCompliance] becomes a little involved though.

As you can see in the SQL code below, you have join multiple tables for finally linking inventories ([ComplianceComputer]) with the raw evidence data imported from your Inventory source ([ImportedInstallerEvidence]).

The original version of each application will be listed in the [VersionRaw] column. You can use the 'Application' and 'VersionRaw' columns for filtering.

Without any filtering and with a large number of inventories, performance of this SQL query most likely will become a concern.

    ;WITH cteImportedComputer 
    (
        ComplianceComputerID, ComplianceConnectionID, ExternalID, RowNum
    ) AS
    (
       /* Retrieve the latest [ImportedComputer] */
        SELECT 
             ic.ComplianceComputerID       
           , ic.ComplianceConnectionID      
           , ic.ExternalID                                                
           , ROW_NUMBER() OVER 
                    (PARTITION BY ic.ComplianceConnectionID, ic.ComplianceComputerID 
                        ORDER  BY ic.InventoryDate DESC)              AS RowNum
         FROM  ImportedComputer  ic
    ),
    cteEvidenceLatest  
    (
          ComplianceComputerID
        , SoftwareTitleID
        , InstallerEvidenceID
        , ComplianceConnectionID
        , ExternalInstallerID
        , RowNum
    ) AS
    (
        /* Retrieve only the latest Installer Evidence   */
        SELECT 
             ComplianceComputerID
           , stie.SoftwareTitleID
           , stie.InstallerEvidenceID
           , cis.ComplianceConnectionID
           , iiem.ExternalInstallerID
           , ROW_NUMBER() OVER 
                    (PARTITION BY ComplianceComputerID, stie.SoftwareTitleID 
                        ORDER  BY iiie.InstallDate DESC)              AS RowNum
          FROM  dbo.SoftwareTitleInstallerEvidence     stie  
          JOIN  cteImportedComputer                    cis   
                ON cis.ComplianceComputerID         = ComplianceComputerID
               AND cis.RowNum                       = 1
          JOIN  dbo.ImportedInstallerEvidenceMapping   iiem  
                ON iiem.InstallerEvidenceID         = stie.InstallerEvidenceID     
               AND iiem.ComplianceConnectionID      = cis.ComplianceConnectionID
          JOIN  dbo.ImportedInstalledInstallerEvidence iiie  
                ON iiie.ComplianceConnectionID      = cis.ComplianceConnectionID
               AND iiie.ExternalInstallerEvidenceID = iiem.ExternalInstallerID
               AND iiie.ExternalComputerID          = cis.ExternalID
          WHERE 
                   stie.SoftwareTitleID = SoftwareTitleID
    ),
    cteSoftwareEvidence 
    (
          ComplianceComputerID
        , SoftwareTitleID
        , InstallerEvidenceID
        , DisplayName
        , [Version]
    ) AS
    (
          SELECT 
               cel.ComplianceComputerID
             , cel.SoftwareTitleID
             , cel.InstallerEvidenceID
             , iie.DisplayName
             , iie.[Version]
          FROM  cteEvidenceLatest                      cel  
          JOIN  dbo.ImportedInstallerEvidence          iie   
                ON iie.ComplianceConnectionID       = cel.ComplianceConnectionID
               AND iie.ExternalInstallerID          = cel.ExternalInstallerID
          WHERE 
                   cel.RowNum = 1
    )
    SELECT          
         isd.ComplianceComputerID       
       , isd.InstalledSoftwareID       
       , isd.InstallDate                 AS InstallDate
       , st.Fullname                     AS [Application] 
       , stpro.ProductName               AS ProductName
       , stv.VersionName                 AS [Version]
       , stpub.PublisherName             AS PublisherName
       , isd.DiscoveryDate               AS DiscoveryDate
       , cc.InventoryDate                AS InventoryDate
       , stc.DefaultValue                AS [Classification]
       , ste.EditionName                 AS Edition
       , cse.DisplayName                 AS DisplayName
       , cse.Version                     AS VersionRaw
    FROM       dbo.InstalledSoftwareData              isd
    LEFT JOIN  dbo.SoftwareTitle                      st    
         ON isd.SoftwareTitleID        = st.SoftwareTitleID
    LEFT JOIN  dbo.SoftwareTitleProduct               stpro 
         ON st.SoftwareTitleProductID  = stpro.SoftwareTitleProductID
    LEFT JOIN  dbo.SoftwareTitlePublisher             stpub 
         ON stpro.SoftwareTitlePublisherID = stpub.SoftwareTitlePublisherID
    LEFT JOIN  dbo.SoftwareTitleVersion               stv   
         ON st.SoftwareTitleVersionID = stv.SoftwareTitleVersionID
    LEFT JOIN  dbo.SoftwareTitleEdition               ste   
         ON st.SoftwareTitleEditionID = ste.SoftwareTitleEditionID
    LEFT JOIN  dbo.SoftwareTitleClassificationI18N    stc   
         ON st.SoftwareTitleClassificationID = stc.SoftwareTitleClassificationID              
    LEFT JOIN  dbo.ComplianceComputer                 cc    
         ON cc.ComplianceComputerID  = isd.ComplianceComputerID 
    LEFT JOIN  cteSoftwareEvidence                    cse   
         ON cse.ComplianceComputerID = isd.ComplianceComputerID
        AND cse.SoftwareTitleID      = isd.SoftwareTitleID 
mfranz
By Level 17 Champion
Level 17 Champion

There's an idea to expose evidence data to the report builder by default, please consider voting for it: https://flexerasfdc.ideas.aha.io/ideas/ITAM-I-177