Using the latest version of Last Start (Reporting on applications)

This issue has been solved. See solution.
lmanpunk's picture

I am creating a report that will eventually use parameters to replace the hard data.  The query works; however, the problem I am having is there are multiple last starts time.  What do I need to do to use only the latest "Last Start"

SELECT DISTINCT
     vComputer.Name, vComputer.[User], vComputer.[OS Name], vComputer.[IP Address], vComputer.Domain, Inv_AeX_AM_Monthly_Summary.[Last Start], DATEDIFF(day,
                      Inv_AeX_AM_Monthly_Summary.[Last Start],GETDATE()) AS [# of Days Last Used], Inv_AeX_AM_Monthly_Summary.[Run Count],
                      Inv_AeX_AM_Monthly_Summary.[Total Run Time], Inv_AeX_AM_Monthly_Summary.[Product Name],
                      Inv_AeX_AM_Monthly_Summary.[Product Version]
FROM         vComputer INNER JOIN
                      Inv_AeX_AM_Monthly_Summary ON vComputer.Guid = Inv_AeX_AM_Monthly_Summary._ResourceGuid
WHERE     (Inv_AeX_AM_Monthly_Summary.[Product Name] LIKE N'Microsoft Visio%') and (DATEDIFF(day,Inv_AeX_AM_Monthly_Summary.[Last Start],GETDATE()) > '30')

Thanks 

KSchroeder's picture

MAX() and SUM() functions

Solution

I think the issue is that the Inv-AeX_AM_Monthly_Summary table has multiple rows per machine, based on the "Month Year" column (which I still don't quite get the usefulness of).  So you have to filter them out and get only the most recent month.  Try the following:
 

SELECT 
 vComputer.Name, vComputer.[User], vComputer.[OS Name], vComputer.[IP Address], vComputer.Domain,
 MAX(Inv_AeX_AM_Monthly_Summary.[Last Start]) AS [Last Start],
 DATEDIFF(day, MAX(Inv_AeX_AM_Monthly_Summary.[Last Start]),GETDATE()) AS [# of Days Last Used],
 SUM(Inv_AeX_AM_Monthly_Summary.[Run Count]) AS [Run Count],
 SUM(Inv_AeX_AM_Monthly_Summary.[Total Run Time]) AS [Total Run Time],
 Inv_AeX_AM_Monthly_Summary.[Product Name], Inv_AeX_AM_Monthly_Summary.[Product Version]
FROM   vComputer
INNER JOIN Inv_AeX_AM_Monthly_Summary
 ON vComputer.Guid = Inv_AeX_AM_Monthly_Summary._ResourceGuid
WHERE Inv_AeX_AM_Monthly_Summary.[Product Name] LIKE N'Microsoft Visio%'
 AND DATEDIFF(day,Inv_AeX_AM_Monthly_Summary.[Last Start],GETDATE()) > 30
GROUP BY vcomputer.name, vcomputer.[User],  vComputer.[OS Name], vComputer.[IP Address], vComputer.Domain, Inv_AeX_AM_Monthly_Summary.[Product Name], Inv_AeX_AM_Monthly_Summary.[Product Version]

 
Also, there is a concept in SQL called "aliasing" which allows you to use a short name (alias) for a table instead of typing out the whole name.  For example when i use vComputer view, I alias it as "vc", or Inv_AeX_AC_Identification as "acid".  YOu just put it after the table/view name:
FROM vComputer vc   --OR--  FROM vComputer AS vc. 

The "AS" is optional but helps readability.

Thanks,
Kyle
Symantec Trusted Advisor
If your question has been resolved, please be sure to click "Mark as Solution"! Thank you.

lmanpunk's picture

Thank you.. 

I have come long way with my SQL but I still have much to learn

KSchroeder's picture

Glad to help.  They don't

Glad to help.  They don't make it easy with the structure of those App Metering tables either.  I guess the extra columns give you more information if you want it, but for the most part it seems to just cause more trouble than it is worth.

Thanks,
Kyle
Symantec Trusted Advisor
If your question has been resolved, please be sure to click "Mark as Solution"! Thank you.