Video Screencast Help
Search Video Help Close Back
to help
New in the Rewards Catalog: Vouchers for "Symantec Technical Specialist" and "Symantec Certified Specialist" exams.

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

Updated: 21 May 2010 | 3 comments
lmanpunk's picture
+1 1 Vote
Login to vote
This issue has been solved. See solution.

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 

Comments

KSchroeder's picture
29
Oct
2009
1 Vote +1
Login to vote

MAX() and SUM() functions

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

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

lmanpunk's picture
29
Oct
2009
0 Votes 0
Login to vote

Thank you.. 

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

KSchroeder's picture
29
Oct
2009
0 Votes 0
Login to vote

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

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.