SCCM - SQL Query Server Hardware Inventory With MAC and IP Address

SCCM - SQL Query Server Hardware Inventory With MAC and IP Address

SELECT
A.Name0,
B.SerialNumber0 ,
A.Manufacturer0,
A.Model0,
 C.Name0 ,
D.TotalPhysicalMemory0 ,

MAX (J.MACAddress0) as MAC ,
MAX (F.IPAddress0) As IPAddress ,
G.AD_Site_Name0 ,
MAX (A.UserName0) as Username ,
H.Caption0 ,
H.CSDVersion0,
G.Creation_Date0 ,
I.LastHWScan


FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,
v_GS_NETWORK_ADAPTER J ,
v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I
 INNER JOIN
v_R_System G on G.ResourceID=I.ResourceID


WHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID AND
A.ResourceID = J.ResourceID AND
H.Caption0 like '%server%'


GROUP BY A.Name0,b.SerialNumber0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan

SCCM - Secondary Site Unable to Contact MP or DP

SCCM - Secondary Site Unable to Contact MP or DP

Recently we deployed few secondary site , Management point and Distribution point are working fine, but clients unable to receive policy from MP.

No error on mpcontrol.log 

And found some kerberos error on event logs

Log Name:      System
Source:        Microsoft-Windows-Security-Kerberos
Event ID:      4
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      SCSMDW.wsdemo.com
Description:
The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server smsvc. The target name used was MSOMSdkSvc/SCSMDW. This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target server principal name (SPN) is registered on an account other than the account the target service is using. Ensure that the target SPN is only registered on the account used by the server

After investigating, it turned out to be the Service Principal Name (SPN) that caused the issue. And I quickly saw why. The issue was that the SPN’s was created on the wrong Domain Service Account. If you are running the SQL Service (MSSQLSvc) with local computers system account, the SPN’s are registered automatically and you should not see these types of errors. But if you are running the SQL Service (MSSQLSvc) account with a domain account, then you have to do this manually

So you need to run the below command to register SPN manually

To delete existing SPNs from wrong service account:

setspn -D MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>
setspn -D MSSQLSvc/<SQL Server FQDN>:1433 <Domain\Account>

 To add new SPNs the correct service account:

setspn -A MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>
setspn -A MSSQLSvc/<SQL Server FQDN>:1433 <Domain\Account>  

SCCM - SQL Query to get Software Update Deployment Status - Pivot View

SCCM - SQL Query to get Software Update Deployment Status - Pivot View

Select  Deploymentname, Available, Deadline,

cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Pending system restart], 0) + ISNULL([Waiting for restart], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Downloaded update(s)], 0) + ISNULL([Downloading update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Waiting for maintenance window before installing], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Failed to download update(s)], 0) + ISNULL([Failed to install update(s)], 0) ))*100) as Numeric(10,2)) as varchar(256)) + '%' AS '% Compliant',

[Compliant],
[Successfully installed update(s)],
[Pending system restart],
[Waiting for restart],
[Installing update(s)],
[Downloaded update(s)],
[Downloading update(s)],
[Waiting for another installation to complete],
[Waiting for maintenance window before installing],
[Enforcement state unknown],
[Failed to download update(s)],
[Failed to install update(s)]

From

(select
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,
count(*) as NumberOfComputers
from v_CIAssignment a
join v_AssignmentState_Combined assc
on a.AssignmentID=assc.AssignmentID
join v_StateNames sn
on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

group by a.Assignment_UniqueID, a.AssignmentName, a.StartTime, a.EnforcementDeadline,
sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)
FOR LastEnforcementState IN

( [Compliant],
[Successfully installed update(s)],
[Pending system restart],
[Waiting for restart],
[Installing update(s)],
[Downloaded update(s)],
[Downloading update(s)],
[Waiting for another installation to complete],
[Waiting for maintenance window before installing],
[Enforcement state unknown],
[Failed to download update(s)],
[Failed to install update(s)])
) AS pvt

where DeploymentName in ('Deployment Name')

ORDER BY Deploymentname

SCCM - WQL Query (Collection Query) for Last Logon Machine Details

SCCM - WQL Query (Collection Query) for Last Logon Machine Details

Change the date Accordingly 

select *  from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ADLastLogonTime < "08/10/2019"

SCCM - SQL Query to find Missing and Required Updates for Specific Colletion

SCCM - SQL Query to find Missing and Required Updates for Specific Colletion

Change collection ID

select CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
ui.bulletinid [BulletinID],ui.articleid [ArticleID], ui.Title,
    Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
    IsRequired=(case when css.Status=2 then '*' else '' end),
    ui.InfoURL as InformationURL,
    ui.dateposted [Date Posted] ,
    Deadline=cdl.Deadline
    from V_UpdateComplianceStatus  css
    join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
    left join v_CITargetedMachines  ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
    INNER join v_CICategories_All catall2 on catall2.CI_ID=css.CI_ID
    INNER  join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID
    and catinfo2.CategoryTypeName='UpdateClassification'
    JOIN dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID
       outer apply (
       select Deadline=min(a.EnforcementDeadline)
       from v_CIAssignment  a
       join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
       ) cdl
   WHERE vrs.Name0='SESCADADC02' and
   ui.Severity IN (8) --this is for security and critical updates
  AND css.Status=2  --for required

ORDER BY 1

SCCM - SQL Query to find missing specific update machine details

SCCM - SQL Query to find missing specific update machine details

Changed the KB Article ID and specify OS Caption

select sys.name0 [Server Name],
  ui.ArticleID,
  osd.Caption00 [Operating System],
  ui.Title,
  ucs.Status

from v_updateinfo ui
  inner join v_UpdateComplianceStatus ucs on ucs.ci_id=ui.ci_id
  join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID
  join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'
  join v_R_System sys on sys.resourceid=ucs.resourceid
  join Operating_System_DATA osd on sys.ResourceID = osd.MachineID
   and ucs.status='2' -- required
   AND ui.articleid in ('4103715') and osd.Caption00 like '%server%'
  order by sys.name0

SCCM Console cannot connect to remote site database

SCCM Console cannot connect to remote site database

When you unable to open SCCM console and getting the below error, first thing you need to check SMSAdminUI.log. (C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\AdminUILog)

Error - The performance counter '# exceptions' was not found 

Performance counter on the machine may corrupted, you have to uninstall SCCM console completely and run the below command as administrator

LODCTR /R

Now install SCCM console it will start to work

Note : If the problem didn’t fix even after reinstalled, check if any SCCM files blocked by Antivirus

Error - Insufficient privilege to connect, error: 'Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Check DCOM permission and WMI are configured correctly, if all permission are configured correctly reinstall MSDTC component

Error - Login failed for user, Failed to get SQL connection    SMS_SITE_COMPONENT_MANAGER 

CSiteControlEx::GetMasterSCF:Failed to read site information from database

Open SQL Management Studio and connect to site database then navigate to <Server>/Security/Logins, check if the computer account is there. If not, add it. If yes, remove it and add it back with the same setting.

Additionally check whether the user has the necessary privileges to the SMS provider on the site server and Check whether the user has the necessary security rights to the database

Verify namespace and server connectivity using wbemtest

Use the below method to check remote SQL database connectivity

• Use telnet.exe to connect to the SQL server’s port 1433
• Create an ODBC System DSN to see if connection is successful 
• Create Universal Data Link files to check remote SQL connectivity