Month – February 2012

ONE Row for each Server

For full details see the forum post. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/bbc5e667-085f-47a2-a5b6-8a5089aa9e91 SELECT Distinct R.Name0 as ‘PC’, LD.FreeSpace0 as ‘Free Space’, LD.Size0 as ‘Size’, isnull(Share.Path0,’n/a’) as ‘Path’ FROM dbo.v_R_System R inner join dbo.v_GS_SERVICE Serv on R.ResourceID = Serv.ResourceID inner join dbo.v_GS_LOGICAL_DISK LD on LD.ResourceID = R.ResourceID left outer join (select ResourceID, Name0, Path0 from v_GS_SHARE Where Name0 = ‘Print$’) […]

Display the User Group Name in 1 column

To see full forum post see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/986960ba-8a50-4e69-9e5a-64a40c9d8df5/ SELECT R.User_Name0 as ‘user name’, U.Full_User_Name0 as ‘full name’, U.givenName0 as ‘First Name’, U.sn0 as ‘Surname’, U.mail0 as ‘Email’, max(OU.User_OU_Name0) as ‘OU Path’, Groups.Groups as ‘User Group’ FROM dbo.v_R_System R JOIN dbo.v_FullCollectionMembership FCM ON R.ResourceID = FCM.ResourceID JOIN dbo.v_R_User U ON R.User_Domain0+ ‘\’+R.User_Name0 = U.Unique_User_Name0 left outer join […]

AD discovery and add the following attributes (givenname, sn, mail & ou) report

For full details see. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/7f48aa48-d260-4ad1-babc-8b2496d103fb I fix the query to make it work, change OS to a readable name, formatting etc.. SELECT R.Name0 as ‘Host Name’, CS.Model0 as ‘model’, BIOS.SerialNumber0 as ‘SN’, OS.Caption0 as ‘OS’, R.User_Name0 as ‘user name’, U.Full_User_Name0 as ‘full name’, U.givenName0 as ‘First Name’, U.sn0 as ‘Surname’, U.mail0 as ‘Email’, max(OU.User_OU_Name0) as […]

PC not logon for 14 days.

For full details see. http://social.technet.microsoft.com/Forums/en-US/configmgrsdk/thread/cf7d80a0-1a7c-430c-b33a-ee47a0efa806   select CS.Name0, LCU.date From dbo.v_GS_COMPUTER_SYSTEM CS join ( Select ResourceID, max(LastConsoleUse0) as ‘date’ from dbo.v_GS_SYSTEM_CONSOLE_USER SCU Group by ResourceID) as LCU on CS.ResourceID = LCU.ResourceID Where datediff(dd,LCU.date,getdate()) >14

Symantec av software to a query

Adding symantec av software to a query, see here for full details.http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/7c6b65be-cdfc-414f-a78d-f198f5e6a323   SELECT Sys.Netbios_Name0 as ‘Computer Name’, Sys.User_Domain0 as ‘User Domain’, Sys.User_Name0 as ‘UserName’, Comp.Manufacturer0 as ‘Make’, Comp.Model0 as ‘Model’, Process.Name0 as ‘Processor’, BIOS.SerialNumber0 as ‘Serial#’, Memory.TotalPhysicalMemory0/1000 as ‘RAM (MB)’, Sys.Operating_System_Name_and0 as ‘Operating System’, OS.CSDVersion0 as ‘Service Pack’, Status.LastHWScan as ‘Last H/W Scan’, OS.InstallDate0 […]

IT Virtualization Boot Camp

Did you know that the OWSUG is putting on the IT Virtualization Boot Camp? This boot camp is designed to help you pass the 70-659 exam…   What do you need to know. the boot camp cost ~$27, there are two date for this boot camp Feb 17 & 18.  Spots are going fast soo, […]