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

SQL Query to find Missing Security & Critical Updates

SQL Query to find Missing Security & Critical Updates

select
    CS.Name0 AS 'Computer',
CS.UserName0 AS 'Last User',
ws.lasthwscan as 'Last HW scan',
MAX(os.LastBootUpTime0) AS 'Last Reboot (not accurate)',
MAX(MP_ALL.Patches_Missing) AS 'Total',
MAX(MP_CRITICAL.Patches_Missing) AS 'Critical',
MAX(MP_SERVICEPACKS.Patches_Missing) AS 'Service Packs',
MAX(MP_SECURITY.Patches_Missing) AS 'Security Updates',
MAX(System_OU_Name0) AS 'Machine OU',
MAX(OS.Caption0) AS 'Win OS',
MAX(OS.CSDVersion0) AS 'Service Pack',
MAX(OS.InstallDate0) AS 'Image Date',
MAX(OS.TotalVisibleMemorySize0) AS 'RAM MB',
CS.Manufacturer0 AS 'Manufacturer',
CS.Model0 AS 'Model'


from
    v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM  CS
on CS.ResourceID = UCS.ResourceID
left join v_gs_workstation_status ws
on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM
on FCM.resourceid = CS.resourceid

left join v_RA_System_SystemOUName SOU
on SOU.ResourceID = CS.ResourceID
left join v_GS_Operating_System OS
on OS.ResourceID = CS.ResourceID

LEFT JOIN (
select
CS.resourceid,
sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
from
v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM  CS
on CS.ResourceID = UCS.ResourceID
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'
left join v_gs_workstation_status ws
on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM
on FCM.resourceid=CS.resourceid
WHERE
UCS.Status = '2'
and FCM.collectionid = 'PRI00306'
Group by
CS.resourceid,
ws.lasthwscan,
FCM.collectionID
) MP_ALL ON MP_ALL.resourceid = CS.resourceid

LEFT JOIN (
select
CS.resourceid,
sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
from
v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM  CS
on CS.ResourceID = UCS.ResourceID
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'
left join v_gs_workstation_status ws
on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM
on FCM.resourceid=CS.resourceid
WHERE
UCS.Status = '2'
and FCM.collectionid = 'PRI00306'
and catinfo2.CategoryInstanceName = 'Critical Updates'
Group by
CS.resourceid,
ws.lasthwscan,
FCM.collectionID
) AS MP_CRITICAL ON CS.resourceid = MP_CRITICAL.resourceid

LEFT JOIN (
select
CS.resourceid,
sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
from
v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM  CS
on CS.ResourceID = UCS.ResourceID
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'
left join v_gs_workstation_status ws
on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM
on FCM.resourceid=CS.resourceid
WHERE
UCS.Status = '2'
and FCM.collectionid = 'PRI00306'
and catinfo2.CategoryInstanceName = 'Security Updates'
Group by
CS.resourceid,
ws.lasthwscan,
FCM.collectionID
) AS MP_SECURITY ON CS.resourceid = MP_SECURITY.resourceid


WHERE
    UCS.Status = '2'
and FCM.collectionid = 'PRI00306'

Group by
CS.resourceid,
CS.Name0,
CS.UserName0,
CS.Manufacturer0,
CS.Model0,
ws.lasthwscan,
FCM.collectionID
Order by
MAX(MP_ALL.Patches_Missing) DESC,
CS.Name0,
ws.lasthwscan,
FCM.collectionID

Enable & Disable Driver Updates from Windows Update during OSD

Enable & Disable Driver Updates from Windows Update during OSD

To Prevent install driver from windows updates, you can add command line to disable in OSD task sequence

cmd.exe /c reg add "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\DriverSearching" /v SearchOrderConfig /t REG_DWORD /d 0 /f

To Enable use the below command line

cmd.exe /c reg add "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\DriverSearching" /v SearchOrderConfig /t REG_DWORD /d 1 /f