SCCM server Uptime report

This will list the time and date of which the servers was rebooted.

Select
os.Caption0 as 'Operating System',
cs.Name0 as Name,
DateDiff(hour,os.LastBootUpTime0,ws.LastHWScan) as 'Uptime (in Hours)',
CONVERT(varchar(20),os.LastBootUpTime0,100) as 'Last Reboot Date/Time',
CONVERT(varchar(20),ws.LastHWScan,100) as 'Last Hardware Inventory'
From
dbo.v_GS_WORKSTATION_STATUS ws Left Outer Join dbo.v_GS_Operating_System os
on ws.ResourceID = os.ResourceID
inner join dbo.v_GS_COMPUTER_SYSTEM cs
on cs.ResourceID = os.ResourceID
WHERE os.Caption0 LIKE '%server%'
and ws.LastHWScan <> 0 and cs.Name0 is not null
Order by
Cs.Name0

SCCM Security Rights Matrix

this will list all SCCM security rights assigned.

SELECT
   UCP.UserName,
   SO.ObjectName,  
   MAX(CASE UCP.PermissionName WHEN 'Administer' THEN 'X' END) as [Administer],
   MAX(CASE UCP.PermissionName WHEN 'Advertise' THEN 'X' END) as [Advertise],
   MAX(CASE UCP.PermissionName WHEN 'Create' THEN 'X' END) as [Create],
   MAX(CASE UCP.PermissionName WHEN 'Create Task Sequence Media' THEN 'X' END) as [Create Task Sequence Media],
   MAX(CASE UCP.PermissionName WHEN 'Delegate Explicit Instance Rights' THEN 'X' END) as [Delegate Explicit Instance Rights],
   MAX(CASE UCP.PermissionName WHEN 'Delete' THEN 'X' END) as [Delete],
   MAX(CASE UCP.PermissionName WHEN 'Delete Resource' THEN 'X' END) as [Delete Resource],
   MAX(CASE UCP.PermissionName WHEN 'Distribute' THEN 'X' END) as [Distribute],
   MAX(CASE UCP.PermissionName WHEN 'Import Machine' THEN 'X' END) as [Import Machine],
   MAX(CASE UCP.PermissionName WHEN 'Manage AI' THEN 'X' END) as [Manage AI],
   MAX(CASE UCP.PermissionName WHEN 'Manage BMC' THEN 'X' END) as [Manage BMC],
   MAX(CASE UCP.PermissionName WHEN 'Manage Folder' THEN 'X' END) as [Manage Folder],
   MAX(CASE UCP.PermissionName WHEN 'Manage OSD Certificate' THEN 'X' END) as [Manage OSD Certificate],
   MAX(CASE UCP.PermissionName WHEN 'Manage SQL Commands' THEN 'X' END) as [Manage SQL Commands],
   MAX(CASE UCP.PermissionName WHEN 'Manage Status Filters' THEN 'X' END) as [Manage Status Filters],
   MAX(CASE UCP.PermissionName WHEN 'Meter Site' THEN 'X' END) as [Meter Site],
   MAX(CASE UCP.PermissionName WHEN 'Modify' THEN 'X' END) as [Modify],
   MAX(CASE UCP.PermissionName WHEN 'Modify Collection Setting' THEN 'X' END) as [Modify Collection Setting],
   MAX(CASE UCP.PermissionName WHEN 'Modify Resource' THEN 'X' END) as [Modify Resource],
   MAX(CASE UCP.PermissionName WHEN 'Network Access' THEN 'X' END) as [Network Access],
   MAX(CASE UCP.PermissionName WHEN 'Read' THEN 'X' END) as [Read],
   MAX(CASE UCP.PermissionName WHEN 'Read Resource' THEN 'X' END) as [Read Resource],
   MAX(CASE UCP.PermissionName WHEN 'Recover User State' THEN 'X' END) as [Recover User State],
   MAX(CASE UCP.PermissionName WHEN 'Remote Control' THEN 'X' END) as [Remote Control],
   MAX(CASE UCP.PermissionName WHEN 'View AI' THEN 'X' END) as [View AI],
   MAX(CASE UCP.PermissionName WHEN 'View BMC' THEN 'X' END) as [View BMC],
   MAX(CASE UCP.PermissionName WHEN 'View Collected File' THEN 'X' END) as [View Collected File]
FROM
   dbo.v_SecuredObject as SO
   INNER JOIN dbo.v_UserClassPermNames as UCP
      ON SO.ObjectKey = UCP.ObjectKey
GROUP BY
   UCP.UserName,
   SO.ObjectName
ORDER BY
   UCP.UserName,
   SO.ObjectName

List of Workstations with microsoft .netframework less than 2.0

SELECT DISTINCT
                      dbo.v_R_System.Netbios_Name0, dbo.v_R_System.Operating_System_Name_and0, dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,
                      dbo.v_GS_ADD_REMOVE_PROGRAMS.Version0
FROM         dbo.v_R_System INNER JOIN
                      dbo.v_GS_ADD_REMOVE_PROGRAMS ON dbo.v_R_System.ResourceID = dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID
WHERE     (dbo.v_R_System.Operating_System_Name_and0 LIKE '%workstation%') AND
                      (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE 'Microsoft .NET Framework%') AND (dbo.v_GS_ADD_REMOVE_PROGRAMS.Version0 < '2.0%')

SCCM report for all Microsoft .Netframework versions

SELECT     arp.DisplayName0, arp.Version0, COUNT(DISTINCT dbo.v_R_System.ResourceID) AS 'Count'
FROM         dbo.v_GS_ADD_REMOVE_PROGRAMS AS arp INNER JOIN
                      dbo.v_R_System ON arp.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                      dbo.v_RA_System_SMSInstalledSites AS ASSG ON dbo.v_R_System.ResourceID = ASSG.ResourceID INNER JOIN
                      dbo.v_FullCollectionMembership AS col ON dbo.v_R_System.ResourceID = col.ResourceID
WHERE     (arp.DisplayName0 LIKE 'Microsoft .NET Framework%') AND (arp.DisplayName0 NOT LIKE '%hotfix%') AND (arp.DisplayName0 NOT LIKE '%update%') AND (arp.DisplayName0 NOT LIKE '%language%')AND (arp.Version0 IS NOT NULL)
GROUP BY arp.DisplayName0, arp.Version0
SELECT     dbo.v_R_System.Name0 AS 'Machines that do NOT have .NET Framework 2.0 Installed', dbo.v_R_System.User_Name0 AS 'User Name',
                      dbo.v_R_System.AD_Site_Name0 AS 'AD Site', ASSG.SMS_Installed_Sites0 AS 'SMS Site', dbo.v_R_System.Operating_System_Name_and0
FROM         dbo.v_GS_ADD_REMOVE_PROGRAMS AS arp INNER JOIN
                      dbo.v_R_System ON arp.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                      dbo.v_RA_System_SMSInstalledSites AS ASSG ON dbo.v_R_System.ResourceID = ASSG.ResourceID
WHERE     (arp.ResourceID NOT IN
                          (SELECT DISTINCT ResourceID
                            FROM          dbo.v_GS_ADD_REMOVE_PROGRAMS
                            WHERE      (DisplayName0 LIKE 'Microsoft .NET Framework 2.0%'))) AND (arp.ResourceID NOT IN
                          (SELECT DISTINCT ResourceID
                            FROM          dbo.v_GS_ADD_REMOVE_PROGRAMS_64
                            WHERE      (DisplayName0 LIKE 'Microsoft .NET Framework 2.0%')))
GROUP BY dbo.v_R_System.AD_Site_Name0, ASSG.SMS_Installed_Sites0, dbo.v_R_System.Name0, dbo.v_R_System.User_Name0,
                      dbo.v_R_System.Operating_System_Name_and0
HAVING      (NOT (dbo.v_R_System.Operating_System_Name_and0 LIKE '%6.%'))
ORDER BY 'AD Site', 'SMS Site', 'Machines that do NOT have .NET Framework 2.0 Installed', 'User Name'