Month – March 2012

Last Reboot Report

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/e0147d8a-8946-42bb-b6ed-a3c92bae5b23   select distinct R.ResourceID, R.Netbios_Name0 AS ‘Computer Name’, OS.LastBootUpTime0 AS ‘Last Boot Time’, R.Resource_Domain_OR_Workgr0 AS ‘Domain/Workgroup’, S.SiteCode as ‘SMS Site Code’, adv.time, Datediff(dd,OS.LastBootUpTime0, adv.time) from dbo.v_R_System_Valid R inner join dbo.v_GS_OPERATING_SYSTEM OS on (OS.ResourceID = R.ResourceID) inner join dbo.v_FullCollectionMembership FCM on (FCM.ResourceID = R.ResourceID) left join dbo.v_Site S on (FCM.SiteCode = […]

Lists people and how much they’ve been using specified software with OU fix

For full details see: http://www.myitforum.com/forums/tm.aspx?high=&m=234978&mpage=1#234980 select U.Name0 as ‘Bruger’, — U.displayName0 as ‘Bruger’, MU.FullName as ‘Brugernavn’, ou.ou as OU, round(sum(MUS.UsageTime)/60.0,2) as ‘Forbrug (min)’ from dbo.v_R_System R join dbo.v_MonthlyUsageSummary MUS on R.ResourceID = MUS.ResourceID join dbo.v_MeteredFiles MF on MUS.FileID = MF.MeteredFileID join dbo.v_MeteredUser MU on MUS.MeteredUserID = MU.MeteredUserID join dbo.v_R_User U on U.Unique_User_Name0 = MU.FullName left […]

Lists people and how much they’ve been using specified software

For full details see: http://www.myitforum.com/forums/Need-some-SQL-help-think-its-simple-m234978.aspx select U.Name0 as ‘Bruger’, — U.displayName0 as ‘Bruger’, MU.FullName as ‘Brugernavn’, replace(max(ou.User_OU_Name0),’Gartek.LOCAL/Ottawa/’,”) as OU, round(sum(MUS.UsageTime)/60.0,2) as ‘Forbrug (min)’ from dbo.v_R_System R join dbo.v_MonthlyUsageSummary MUS on R.ResourceID = MUS.ResourceID join dbo.v_MeteredFiles MF on MUS.FileID = MF.MeteredFileID join dbo.v_MeteredUser MU on MUS.MeteredUserID = MU.MeteredUserID join dbo.v_R_User U on U.Unique_User_Name0 = MU.FullName left […]

List collection name and number of resources by OS

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/370ceff1-0f54-46f3-8e8e-5459069125e4     SELECT C.Name, OS.Caption0 as ‘Operating System’, OS.CSDVersion0 as ‘Service Pack’, COUNT(*) AS ‘Count’ FROM dbo.v_R_System R join dbo.v_GS_OPERATING_SYSTEM OS on OS.Resourceid = R.Resourceid join dbo.v_FullCollectionMembership FCM on FCM.Resourceid = R.Resourceid join dbo.v_Collection C on C.CollectionID = FCM.CollectionID GROUP BY C.Name, OS.Caption0, OS.CSDVersion0 ORDER BY C.Name, OS.Caption0, OS.CSDVersion0

78!

I have been told that we are 78 people short of the 1200 people for tomorrows Webcast! Will you be the one to help set a new record for myITForm webcast? Sign up now! http://bit.ly/yMimvp   And don’t forget that I have BIG news to spill at the end of the Webcast!

Add Serial Number and Manufacturer to “Computers that do not meet the minimum system requirements for Windows 7” report

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/2ed1b090-2b2b-4a3f-9359-e1ac5fd0adba   SELECT DISTINCT SYS.Netbios_Name0, CS.Manufacturer0, BIOS.SerialNumber0, fcm.SiteCode, ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) AS FreeSpace0, ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 AS ‘Processor (GHz)’, ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) AS C083, ISNULL(CONVERT(VARCHAR(7), MAX (DA.AdapterRAM0) / 1024), ‘UNKNOWN’) AS AdapterRAM, OPSYS.Caption0 AS ‘Operating System’, CONVERT(FLOAT, LEFT(OPSYS.Version0, […]

Report for matching names

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/8bda0fe9-7d80-44d0-9a52-f1f573423abf/   select CS.ResourceID, cs.NAME0, PC2.name0, substring(CS.NAME0,5,LEN(cs.NAME0)) from dbo.v_GS_COMPUTER_SYSTEM CS, (select CS.ResourceID, cs.NAME0, substring(CS.NAME0,5,LEN(cs.NAME0)) as ‘TAG’ from dbo.v_GS_COMPUTER_SYSTEM cS) as PC2 Where substring(CS.NAME0,5,LEN(cs.NAME0)) = PC2.Tag and PC2.Tag != ” and PC2.ResourceID != CS.ResourceID

Total SQL Server Systems Query

See forum post for full details: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/51ead03a-a43d-46df-b0ab-059d9322e432   SELECT DISTINCT R.Name0, R.User_Name0, ARP.DisplayName0 AS [Software Name], ARP.Version0 AS [Installed Version], IP.IP_Addresses0 AS IPAddress, — ARP.TimeStamp AS [Last Time Stamp], ARP.InstallDate0 AS [Software Installed Date] FROM dbo.v_R_System R inner join dbo.v_FullCollectionMembership FCM ON FCM.ResourceID = R.ResourceID INNER JOIN dbo.v_Add_REMOVE_PROGRAMS ARP ON R.ResourceID = ARP.ResourceID INNER JOIN […]

Link v_MeteredUser.FullName to a full name from AD v2

To see full post see http://www.myitforum.com/forums/tm.aspx?high=&m=234899&mpage=1#234905 select R.Netbios_Name0 as ‘Computernavn’, — U.displayName0 as ‘Bruger’, Full_User_Name0, max(ou.User_OU_Name0), MU.FullName as ‘Brugernavn’, MUS.LastUsage as ‘Sidst brugt’, MUS.UsageCount + MUS.TSUsageCount as ‘Antal brug’, round(MUS.UsageTime/60.0,2) as ‘Forbrug (min)’ from dbo.v_R_System R join dbo.v_MonthlyUsageSummary MUS on R.ResourceID = MUS.ResourceID join dbo.v_MeteredFiles MF on MUS.FileID = MF.MeteredFileID join dbo.v_MeteredUser MU on MUS.MeteredUserID […]

Link v_MeteredUser.FullName to a full name from ad

To see the full post http://www.myitforum.com/forums/How-to-link-v_MeteredUserFullName-to-a-full-name-from-ad-m234899.aspx select R.Netbios_Name0 as ‘Computernavn’, — U.displayName0 as ‘Bruger’, Full_User_Name0, MU.FullName as ‘Brugernavn’, MUS.LastUsage as ‘Sidst brugt’, MUS.UsageCount + MUS.TSUsageCount as ‘Antal brug’, round(MUS.UsageTime/60.0,2) as ‘Forbrug (min)’ from dbo.v_R_System R join dbo.v_MonthlyUsageSummary MUS on R.ResourceID = MUS.ResourceID join dbo.v_MeteredFiles MF on MUS.FileID = MF.MeteredFileID join dbo.v_MeteredUser MU on MUS.MeteredUserID = […]