Dear All,
I am altiris beginner, May I know how to write a SQL statment to show "Application Usage Report by Computer" ?
Here is download from aonther forum. May I show the Application Name on the report only ? Not show software patch ??
Please help. Thanks
SELECT DISTINCT
TOP (100) PERCENT dbo.Inv_AddRemoveProgram.DisplayName AS Application, dbo.Evt_Application_Start.[Policy Name],dbo.Inv_AddRemoveProgram.DisplayVersion AS ProductVersion,
dbo.vUser.Department, dbo.vUser.[Office Location], UPPER(dbo.Inv_AeX_AC_Identification.Name) AS PCName,
dbo.Inv_AeX_AC_Identification.[Last Logon User] AS UserID, dbo.vUser.[Display Name] AS UserName, COUNT(DISTINCT CONVERT(NVARCHAR(40),
dbo.Evt_Application_Start.[Start Date], 21) + dbo.Evt_Application_Start.PID + CAST(dbo.Evt_Application_Start._ResourceGuid AS NVARCHAR(40))) AS '# Uses'
FROM dbo.Evt_Application_Start INNER JOIN
dbo.Inv_AeX_AC_Identification ON dbo.Evt_Application_Start._ResourceGuid = dbo.Inv_AeX_AC_Identification._ResourceGuid INNER JOIN
dbo.vUser ON dbo.Inv_AeX_AC_Identification.[Last Logon User] = dbo.vUser.Name INNER JOIN
dbo.Inv_AddRemoveProgram ON dbo.Evt_Application_Start._ResourceGuid = dbo.Inv_AddRemoveProgram._ResourceGuid
WHERE (dbo.Inv_AeX_AC_Identification.Name LIKE '%Computer%')
AND (dbo.Evt_Application_Start.[Start Date] BETWEEN '%Start Date%' AND '%End Date%')
GROUP BY dbo.Inv_AddRemoveProgram.DisplayName, dbo.Inv_AddRemoveProgram.DisplayVersion, dbo.vUser.Department, dbo.vUser.[Office Location],
dbo.Inv_AeX_AC_Identification.[Last Logon User], dbo.vUser.[Display Name], dbo.Inv_AeX_AC_Identification.Name,dbo.Evt_Application_Start.[Policy Name]
HAVING (dbo.Inv_AddRemoveProgram.DisplayName LIKE N'%Application%') AND (dbo.Inv_AeX_AC_Identification.[Last Logon User] <> N'')
ORDER BY Application