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 = […]
Month: March 2012
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 […]
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 […]
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
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!
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, […]
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
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 […]
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 […]
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 = […]