3/06/2009

SQL SCCM Query to return Advertisements, Programs and Main Users of all Computers

This SCCM SQL Query return each Computer, it's Advertisements & Programs and the Main User

SELECT v_Advertisement.AdvertisementName, v_Advertisement.ProgramName,Netbios_Name0 as Computername,v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 as MainUsername
FROM
(SELECT ResourceID, MAX(TotalUserConsoleMinutes0) as Anmeldezeit
FROM v_GS_SYSTEM_CONSOLE_USER
GROUP BY ResourceID) AS Table1,v_GS_SYSTEM_CONSOLE_USER, v_R_System_Valid, v_ClientAdvertisementStatus, v_Advertisement WHERE Table1.ResourceID = v_GS_SYSTEM_CONSOLE_USER.ResourceID
AND Table1.Anmeldezeit = v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0
AND Table1.ResourceID = v_R_System_Valid.ResourceID
AND Table1.ResourceID = v_ClientAdvertisementStatus.ResourceID
AND v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID