Tag: vNext

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!

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 = […]

BIG NEWS!!!!

I have big news but there is a catch….. I can’t say much until Wednesday at noon PST! J Which works out perfectly, as that is almost the exact time that I will be finishing my SCCM Guru webcast! So make sure that your register for the session to hear it Live! http://bit.ly/yMimvp   oh, […]