Using the latest version of Last Start (Reporting on applications)
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
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:
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.
Thank you..
I have come long way with my SQL but I still have much to learn
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.
Would you like to reply?
Login or Register to post your comment.