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

Answer File Components and it usages

Configuration Pass	Description
windowsPE	Configures Windows PE options and basic Windows Setup options. These options can include setting the product key and configuring a disk. If you require drivers for Windows PE to access the local hard disk drive or a network, use this configuration pass to add drivers to the Windows PE driver store and to reflect boot-critical drivers required by Windows PE.
offlineServicing	Applies updates to a Windows image. Also applies packages, including software fixes, language packs, and other security updates. During this pass, you can add drivers to a Windows image before that image is installed and processes out-of-box device drivers during Windows Setup.
specialize	Creates and applies system-specific information. For example, you can configure network settings, international settings, and domain information.
generalize	Enables you to minimally configure the sysprep /generalize command, as well as configure other Windows settings that must persist on your reference image. The sysprep /generalize command removes system-specific information. For example, the unique security ID (SID) and other hardware-specific settings are removed from the image. The generalize pass runs only if you run the sysprep /generalize command.
auditSystem	Processes unattended Setup settings while Windows is running in system context, before a user logs onto the computer in Audit mode. The auditSystem pass runs only if you boot to Audit mode.
auditUser	Processes unattended Setup settings after a user logs onto the computer in Audit mode. The auditUser pass runs only if you boot to Audit mode.
oobeSystem	Applies settings to Windows before Windows Welcome starts.

Custom Status Message Queries for Monitor OS Deployment

Like in SCCM 2007, there is no option to monitor OS deployment in SCCM 2012, but we can create a custom status message query to view the status of OD deployment.


Below query will help to monitor OS deployment,



select stat.*, ins.*, att1.*, att1.AttributeTime from  SMS_StatusMessage as stat left join SMS_StatMsgInsStrings as ins on ins.RecordID = stat.RecordID left join SMS_StatMsgAttributes as att1 on att1.RecordID = stat.RecordID inner join SMS_StatMsgAttributes as att2 on att2.RecordID = stat.RecordID where att2.AttributeID = 401 and att2.AttributeValue = "Deployment ID" and stat.SiteCode = "Site Code" and att2.AttributeTime >= ##PRM:SMS_StatMsgAttributes.AttributeTime## order by att1.AttributeTime DESC

Default Status Message Queries

In SCCM 2012 there are few default status message queries are available , which will help us view audit status of boundaries, collections, deployments, packages, programs, remote control activity, security roles, server components and site systems, totally they have 41 default queries.

Find Collection Membership Information of a Specific Client Machine (SSRS Report)

How to create SSRS report in SCCM 2012 using report builder

• ·Go to SCCM console – Reports – Create report

•       After click next report will open up in Report Builder.

•       Right click on Data sets and click Add Data set
•       Select Use dataset embedded in my report and select data source. Type the above query as follows

select a.CollectionId, b.Name from dbo.v_R_System r
join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID
join dbo.v_Collection b on b.CollectionID = a.CollectionID
Where R.Name0 =@machine

•  Click fields and click ok
•  Right click on Data Sets and click Add Data Set again. Fill the details as follows and press OK

select Name0 from v_R_System

•         Right Click on report, where the empty area of report page and select properties. Go to reference tab, Click ADD on assemblies
•          Add following assemblie  –  SrsResources, culture=neutral And Click OK

• Go to parameters and now you can see there are two parameters. machine and UserSIDs. Right click on UserSIDs and select properties

• Select parameter properties – Internal Default values, select specific value and ADD

Value – =SrsResources.UserIdentity.GetUserSIDs(User!UserID)

•       Go to insert and select table
•        Select dataset 1 and next

•        Now you can run the report on SSRS

Failed to create Stand Alone Media - SCCM OSD (Storage Issue)

When you create standalone media, the process storage all data to the temporary location until it complete the task.

If you don’t have enough space in temp location , normally C drive the stand alone media creation will fail with the below error

Solution

Now you need to either increase the C drive space or change the temp storage location to another drive

Control Panel -> System and Security -> Advanced System Settings and click Environment Variables

And Edit the Temp variable in the user profile to a folder on another drive with more space.