SCCM - SQL query (Find last logon user details of computers)

Select distinct
v_R_System.Netbios_Name0 AS "Computer Name",
v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS "Console User",
v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS "Console Logons",
v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS "Total Minutes on Console",
v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS "Last Console Use"
from v_R_System INNER JOIN
(SELECT
ResourceID,
SystemConsoleUser0,
NumberOfConsoleLogons0,
TotalUserConsoleMinutes0,
LastConsoleUse0,
ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY LastConsoleUse0 DESC) AS Point
FROM
v_GS_SYSTEM_CONSOLE_USER) v_GS_SYSTEM_CONSOLE_USER
ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
AND v_GS_SYSTEM_CONSOLE_USER.Point = 1
Order by v_R_System.Netbios_Name0

Description: This will get you the top console user info who logged in to the machines most recently.

SCCM - Approve Clients Script

 You can find the archive here.


Step 1.

Create the following collection and criteria on your Top SCCM Server and name it per your naming standards

My Collection and Query are both named "All Unapproved Systems".

You will need to Replace YOURDOMAIN with your domain name..

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId where SMS_CM_RES_COLL_SMS00001.IsApproved= '0' and SMS_R_SYSTEM.ResourceDomainORWorkgroup = 'YOURDOMAIN'
 

Step 2.

Download the script, modify a few lines of code.

Line 85 = Add your Top SCCM Site Server here.

Line 86 = Add your Top SCCM Site Code here.

SCCM 2012 - SQL query (Find Computer by Serial Number)

SELECT     dbo.v_R_System.Name0, dbo.v_GS_COMPUTER_SYSTEM.UserName0, dbo.v_GS_PC_BIOS.SerialNumber0,
                      dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0
FROM         dbo.v_R_System INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID
WHERE SerialNumber0 LIKE @SerialNumber

SCCM 2012 - Report Builder 2.0 is not installed ...

If you are using SQL 2008 R2 with ConfigMgr 2012, you`ll probably receive this error "Report Builder 2.0  is not installed as a click-once application on report server"

ConfigMgr 2012 is designed to work out of the box with Report Builder 2.0

You’ll need to set a registry key to name the RB 3.0 application manifest.  The key in question is “HKLM\Software\Wow6432Node\Microsoft\ConfigMgr10\AdminUI\Reporting\ReportBuilderApplicationManifestName”.  Change its value from “ReportBuilder_2_0_0_0.application” to “ReportBuilder_3_0_0_0.application” on any machine from which you want to launch RB 3.0.

Then start the Configuration Manager Administrator console as administrator and it works.