OpsMgr (SCOM) - CA Certificates Powershell Magic

If you're a SCOM Administrator, you've been through this ...

Create PFX certificates for a couple of hundreds machines that don't belong to any domain you trust, or just belong to workgroup ... So, since i hate to do the same thing more than 2 times i did this :

 $CAName = 'CA-SERVER.DOMAIN\DOMAIN-CA'  
 $CertPassword = 'YourCertificaPassword'  
 $CertTemplate = 'YourCAOpsMgrTemplate'  
   
 Set-Location C:\tmp\SCOM_CERTS\  
   
 foreach ($agent in (Get-Content Agent-List.txt))  
 {  
 Remove-Item ($agent + '.*') -Force  
 $inffile = $agent + '.inf'  
 '[NewRequest]' > $inffile  
 'Subject="CN=' + $agent + '"' >> $inffile  
 'Exportable=TRUE' >> $inffile  
 'KeyLength=1024' >> $inffile  
 'MachineKeySet=TRUE' >> $inffile  
 'FriendlyName="' + $agent + '"' >> $inffile  
 '[RequestAttributes]' >> $inffile  
 'CertificateTemplate="' + $CertTemplate + '"' >> $inffile  
 $reqfile = $agent + '.req'  
 $certfile = $agent + '.cer'  
 $pfxname = $agent + '.pfx'  
 certreq -New $inffile $reqfile  
 certreq -Submit -config $CAName $reqfile $certfile  
 certreq -accept $certfile  
 certutil -exportpfx -p $CertPassword $agent $pfxname "NoChain,NoRoot"  
 certutil -delstore my $agent  
 Remove-Item $inffile,$reqfile,$certfile  
 }  

Have fun! :)

SCCM Missing updates per Collection

Recently a customer had the need to have a simple query/report so they could know which updates (critical and security) were missing in a specific collection in SCCM
So, and i'm not a SCCM database schema expert, googled for an half an hour, and found several queries, but ... not what we needed - so i decided to retrieve the best of each, and put it all in one single query

And came out with this :

 SELECT dbo.v_R_System.Name0 AS 'Computername', dbo.v_UpdateInfo.Title AS 'Updatename', dbo.v_StateNames.StateName, dbo.v_Update_ComplianceStatusAll.LastStatusCheckTime, dbo.v_UpdateInfo.DateLastModified, dbo.v_UpdateInfo.IsDeployed, dbo.v_UpdateInfo.IsSuperseded,  
 dbo.v_UpdateInfo.IsExpired, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.DateRevised,  
 catinfo.CategoryInstanceName as 'Vendor',  
 catinfo2.CategoryInstanceName as 'UpdateClassification'  
 FROM dbo.v_StateNames  
 INNER JOIN dbo.v_Update_ComplianceStatusAll  
 INNER JOIN dbo.v_R_System ON dbo.v_R_System.ResourceID = dbo.v_Update_ComplianceStatusAll.ResourceID  
 INNER JOIN dbo.v_UpdateInfo ON dbo.v_UpdateInfo.CI_ID = dbo.v_Update_ComplianceStatusAll.CI_ID ON dbo.v_StateNames.StateID = dbo.v_Update_ComplianceStatusAll.Status  
 INNER JOIN v_CICategories_All catall on catall.CI_ID = dbo.v_UpdateInfo.CI_ID  
 INNER JOIN v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company'  
 INNER JOIN v_CICategories_All catall2 on catall2.CI_ID=dbo.v_UpdateInfo.CI_ID  
 INNER JOIN v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'  
 WHERE (dbo.v_StateNames.TopicType = 500)  
 AND (dbo.v_StateNames.StateName = 'Update is required')  
 AND (dbo.v_R_System.Name0 IN  
 (SELECT TOP (100) PERCENT SD.Name0 AS 'Machine Name'  
 FROM dbo.v_R_System AS SD INNER JOIN  
 dbo.v_FullCollectionMembership AS FCM ON SD.ResourceID = FCM.ResourceID INNER JOIN  
 dbo.v_Collection AS COL ON FCM.CollectionID = COL.CollectionID LEFT OUTER JOIN  
 dbo.v_R_User AS USR ON SD.User_Name0 = USR.User_Name0 INNER JOIN  
 dbo.v_GS_PC_BIOS AS PCB ON SD.ResourceID = PCB.ResourceID INNER JOIN  
 dbo.v_GS_COMPUTER_SYSTEM AS CS ON SD.ResourceID = CS.ResourceID INNER JOIN  
 dbo.v_RA_System_SMSAssignedSites AS SAS ON SD.ResourceID = SAS.ResourceID  
 WHERE (COL.Name like 'NOS - Patch Management OUT%')))  
 AND ((catinfo2.CategoryInstanceName like 'Critical%' ) OR (catinfo2.CategoryInstanceName like 'Security%' ))  

Result :

I'm not a SQL expert (at all!) so, if you've any other way to get this data, please, feel free to share!
Cheers!