Software Management Group

Hi I am working, to find out last execution date for a software porduct. I am not a sql expert but I have started the query but not retruning any results. If someone can help I will appreciate. thanks below is the query I have and I think its my joins possibly. 

select distinct  vc.Guid as _ItemGuid, vc.Name as [Computer Name], vc.[OS Name]

, sc.Name as [software], sp.Name as [Software Product]
,  
      CASE WHEN p.IsManaged = 1 THEN 'Managed'
            WHEN p.IsManaged = 2 THEN 'Unmanaged'
            ELSE 'Unknown' END AS [Managed State]
, sc.Name as [Software Component], isw.InstallDate
,  
      CASE WHEN isw.InstallFlag = 1 THEN 'Yes'
            WHEN isw.InstallFlag = 0 THEN 'No'
            ELSE 'Unknown' END AS [Is Currently installed]
,isnull(spi.Usage,0) as Usage, isnull(spi.[Usage Previous Month],0) as [Usage Previous Month] 
, Max(spi.LastEvaluationDate) as [Last Evaluation date] 
,isnull(CONVERT(VARCHAR,Max(usage.[Last Start]),120), 'No Info') as [Last Used]

--select  distinct isw._ResourceGuid, sp.Name as Product , sc.Name as Component,  rf.Name 

from RM_ResourceSoftware_Product (nolock) sp
Inner Join ResourceAssociation ra1 (nolock) on ra1.ParentResourceGuid = sp.Guid  
                and ra1.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483' --Software product - sofware component
                and sp.Name NOT LIKE '%update%'
                and sp.Name NOT LIKE '%hotfix%'
Inner Join RM_ResourceSoftware_Component sc (nolock) on sc.Guid = ra1.ChildResourceGuid
                and sc.Name NOT LIKE '%update%'
                and sc.Name NOT LIKE '%hotfix%'

Join  Inv_SoftwareProduct_InstallationInfo arp (nolock) on sp.Guid = arp._ResourceGuid

                
Inner JOIN ResourceAssociation ccf (nolock) ON ccf.ParentResourceGuid = sc.Guid
   AND ccf.ResourceAssociationTypeGuid = 'EABE86D3-AAFD-487A-AF63-5C95D7511AF6' --component contains file 
Join (select Distinct Guid, ResourceTypeGuid, Name  , CreatedDate, Max(ModifiedDate) as ModifiedDate
        from RM_ResourceFile
        where Name like '%.exe%'
        group by Guid, ResourceTypeGuid, Name  , CreatedDate) RF 
    on RF.Guid = ccf.ChildResourceGuid
    --and rf.Name like '%outlook.exe%'     

join Inv_InstalledSoftware isw on isw._SoftwareComponentGuid = ccf.ParentResourceGuid
join [Inv_Software_Product_State] p on p._ResourceGuid = sp.Guid

JOIN (select vam._ResourceGuid, FileResourceGuid, UserGuid, Max([Last Start]) as [Last Start] from vAMMonthlySummary vam (nolock)
            inner join Inv_Software_Execution swe on swe._ResourceGuid = vam.FileResourceGuid and swe.IsMetered = 1
            
            group by vam._ResourceGuid, FileResourceGuid, UserGuid) usage    
    ON usage.FileResourceGuid = ccf.ChildResourceGuid
   AND usage._ResourceGuid = isw._ResourceGuid

join Inv_SoftwareProduct_InstallationInfo spi on sp.Guid = spi._ResourceGuid

Right Outer Join vcomputer vc on vc.Guid = isw._ResourceGuid

Where  sp.Name NOT LIKE '%update%'
                AND sp.Name NOT LIKE '%hotfix%'
                
Group by vc.Guid , spi.[Usage Previous Month],spi.Usage,  vc.Name, vc.[OS Name], sc.Name, sp.Name ,p.IsManaged, sc.Name , isw.InstallDate,  isw.InstallFlag               

Hi,

I don't get exactly where you wanted to go, but maybe this coud help.

It lists SoftwareProducts where the associated Softwarecomponent is installed at a computer and will show the last started timestamp if Apllication Metering Data is available.

SET LANGUAGE us_english
SELECT DISTINCT --f.Guid,
  f.[Name] [Application],

    i.Name,
    i.Guid AS Computerguid,
--    spu.IsUsageTracked,
--    spu.TrackingStartDate,
--    spu.UsageCount,
    a.[Last Start],
--    iis.InstallDate,
    arp.InstallDate,
    aai.[Client Date] AS 'Last Contact'
--    arp._SoftwareComponentGuid
    FROM dbo.vSoftwareProduct f
  JOIN dbo.Inv_Software_Product_State sps
    ON f.Guid = sps._ResourceGuid
    AND sps.IsManaged = 1
    JOIN ResourceAssociation ra
        ON ra.ParentResourceGuid = f.Guid
        AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
    JOIN vSoftwareComponent sc
        ON sc.Guid = ra.ChildResourceGuid
    --JOIN dbo.Inv_InstalledSoftware iis
    --    ON iis._SoftwareComponentGuid = ra.ChildResourceGuid
    --         AND iis.InstallFlag = 1
    JOIN Inv_AddRemoveProgram arp
        ON arp._SoftwareComponentGuid = ra.ChildResourceGuid
            AND arp.InstallFlag = 1
    JOIN dbo.vComputer i
        --ON i.Guid = iis._ResourceGuid
        ON i.Guid = arp._ResourceGuid
    JOIN Inv_AeX_AC_Identification aai
        ON aai._ResourceGuid = i.Guid
    LEFT JOIN Inv_Software_Product_Usage spu        
  ON spu._ResourceGuid = f.Guid
    LEFT JOIN (    SELECT DISTINCT    ms.FileResourceGuid,
                                ms._ResourceGuid,
                                MAX(ms.[Last Start]) [Last Start],
                                ra.ParentResourceGuid [ParentResourceGuid],
                                SUM (CAST (se.IsMetered AS INT)) [Metered]
                FROM dbo.Inv_Software_Execution se
                    LEFT JOIN dbo.vAMMonthlySummary ms
                        ON ms.FileResourceGuid = se._ResourceGuid
                        
                    JOIN ResourceAssociation ra
                        ON ra.ChildResourceGuid = se._ResourceGuid
                    /**
                        JOIN (--    SET LANGUAGE us_english
                            SELECT ams._ResourceGuid, ams.FileResourceGuid
                            FROM dbo.vAMMonthlySummary ams
                            WHERE  CAST(RIGHT(ams.[Month Year], 4) + '-' + LEFT(ams.[Month Year],CHARINDEX(' ',ams.[Month Year])-1) + '-' + '01' AS DATE) > DATEADD(MONTH,-3,GETDATE())
                                    AND [Run Count] > 2
                            GROUP BY _ResourceGuid, FileResourceGuid
                            --ORDER BY [Run Count]
                            HAVING COUNT(*) >2
                        ) blubb ON ms.FileResourceGuid = blubb.FileResourceGuid AND ms._ResourceGuid = blubb._ResourceGuid
                    **/
                WHERE se.IsMetered = 1            
                GROUP BY ms.FileResourceGuid, ms._ResourceGuid, ra.ParentResourceGuid
            ) a
        ON a.ParentResourceGuid = ra.ChildResourceGuid
        AND (a._ResourceGuid = i.Guid OR a._ResourceGuid IS NULL)
        AND a.[Last Start] IS NOT NULL
--WHERE spu.IsUsageTracked = 1
--AND f.Guid = '%MeteredProduct%'
--AND DATEDIFF(day,arp.InstallDate,GETDATE()) > %InstallDays%
--AND (DATEDIFF(day,a.[Last Start],GETDATE()) > %UseDays% OR a.[Last Start] IS NULL )

Hi I am looking for the date when last time a softwre product was used. You seem to be using Client date from Inv_AeX_AC_Identification as Last contact but is this the used date for an application?

sorry my bad, Last start date seems to good. Appreicate your help. I will test a bit more and then mark it as a soluiton