Powershell Script to create Multiple AD group Collections

#Give the Ad group names in the CSV file


$Collections = Import-Csv  -Path "C:\SCCM_Scripts\adgroupcollectionslists.csv"

foreach ($col in $collections )
{
$Schedule = New-CMSchedule -Start "01/01/2019 12:00 AM" -DurationInterval Minutes -DurationCount 0 -IsUtc:$False -Nonrecurring

$col.Name

$query = "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 where SMS_R_System.SystemGroupName = 'Domain\\$($col.Name)'"

#Change the domain name in the above line

New-CMDeviceCollection -Name $col.Name -LimitingCollectionId 'SMS00001' -RefreshType None -RefreshSchedule $Schedule | Out-Null

Add-CMDeviceCollectionQueryMembershipRule -CollectionName $col.Name -QueryExpression $query -RuleName $col.Name

}

SCCM client failed : Setup failed due to unexpected circumstances

Error:

File C:\Windows\ccmsetup\{8971B736-FB0A-4D07-AE81-82D40BBCD630}\client.msi installation failed. Error text: ExitCode: 1603

Action: CcmFixupServiceConfigInit.

ErrorMessages:

Setup failed due to unexpected circumstances

The error code is 80070002

Solution :

If so, you may need to use the following PowerShell lines to remove the left over services which can no longer be self-healed by the CcmFixupServiceConfigInit action in the client.msi

open task manager and kill any running msiexec.exe 

then reinstall again via ccmsetup.exe or via SCCM console 

$Computer = 'Machinename'

(Get-WmiObject Win32_Service -ComputerName $Computer -filter "name='ccmexec'").Delete()

(Get-WmiObject Win32_Service -ComputerName $Computer -filter "name='ccmsetup'").Delete()

(Get-WmiObject Win32_Service -ComputerName $Computer -filter "name='smstsmgr'").Delete()

Here is a neat little trick to get the information from SCCM, if the below highlighted options are selected.

For Packages

$SiteCode = "DEV"

$siteserver= "Site.server.com"

$Progs = Get-WMIObject -NameSpace "Root\SMS\Site_$($SiteCode)" -ComputerName $siteserver -Class SMS_program

foreach ($Prog in $Progs)  {

if (($Prog.ProgramFlags -band 33) -eq 1)

 < # change it to -eq 0 in the above condition, if you want to get the package list which are not checked #>

{

"  {0}:{1}:{2}" -f $Prog.PackageID, $Prog.PackageName, $Prog.ProgramName

 }

}

For Applications

$AppList = Get-CMApplication

foreach ($App in $AppList)

 {

    if ($App.SDMPackageXML  -like '*true*')

 {

  Write-Output $App.LocalizedDisplayName

 }

 }

Patch Compliance report for current month

This report runs on based on the KB downloaded date for the month in SCCM and deployed a particular (collection ID can be added or changed depending upon the environment) gets the status as installed or missing on the machine. Here only required KB for the machine will come as missing



Select
        v_R_System.name0 as 'Device Name',
        v_RA_System_ResourceNames.Resource_Names0 as 'FQDN',
        v_R_System.Resource_Domain_OR_Workgr0 as 'DOMAIN',
V_GS_OPERATING_SYSTEM.Caption0 as 'Operating System',
        V_UpdateInfo.DateCreated,
V_UpdateInfo.ArticleID,
        V_UpdateInfo.Title,
    CASE
                     WHEN v_Update_ComplianceStatus.Status = '2' THEN 'MISSING'
                     WHEN v_Update_ComplianceStatus.Status = '3' THEN 'INSTALLED'
              else 'UNKNOWN'
              END AS 'PatchStatus'
                             
from v_R_System
left join v_Update_ComplianceStatus on v_R_System.ResourceID = v_Update_ComplianceStatus.ResourceID
JOIN v_FullCollectionMembership fcm on v_R_System.ResourceID=fcm.ResourceID
left join V_UpdateInfo on v_Update_ComplianceStatus.CI_ID = V_UpdateInfo.CI_ID
left join v_RA_System_ResourceNames on v_R_System.ResourceID = v_RA_System_ResourceNames.ResourceID
left join v_UpdateScanStatus on v_R_System.ResourceID = v_UpdateScanStatus.ResourceID
left join v_GS_WORKSTATION_STATUS on v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
left Join v_GS_OPERATING_SYSTEM on v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
where month(V_UpdateInfo.DateCreated) like month(GetDATE())  and  year(V_UpdateInfo.DateCreated) like year(GetDATE()) and  v_UpdateInfo.IsDeployed = '1' and v_UpdateInfo.CIType_ID = '8' and v_UpdateInfo.IsSuperseded = '0' and V_r_System.Operating_System_Name_and0 not like '%server%' and fcm.CollectionID = 'CollectionID'
order by v_R_System.name0

SCCM Hardware Inventory failure CInvState::SendReport: Unable to send the message to mp:MP_HinvEndpoint: 80004005"

Hardware inventory failure

CInvState::SendReport: Unable to send the message to mp:MP_HinvEndpoint: 80004005"

Client would have stuck in provisioning mode, so the below lines will help you out.

$computer = 'Machinename'
Invoke-WmiMethod -Namespace "root\ccm" -Class "SMS_Client" -Name "SetClientProvisioningMode" $false -ComputerName $Computer 

Power-Shell script to delete the Hardware Inventory queue in client machine if it's stuck

If you see Inventoryagent.log says Hardware inventory already in queue and nothing running in inventory agent


$Computername = 'machinename'
$query = "SELECT * FROM InventoryActionStatus WHERE InventoryActionID='{00000000-0000-0000-0000-000000000001}'"
$hwint = Get-WmiObject -Query $query -ComputerName $Computername -Namespace "root\ccm\invagt"
$hwint.Delete()

SCCM SQL query to get collection Schedule and Refresh Types

select cg.SiteID AS [Collection Id],
cg.CollectionName [Collection Name],
CASE
WHEN cg.CollectionType = 0 THEN 'Other'
WHEN cg.CollectionType = 1 THEN 'User'
WHEN cg.CollectionType = 2 THEN 'Device'
ELSE 'Unknown' END AS [Collection Type],
case
WHEN CG.Schedule like '%0102000' THEN 'Every 1 minute'
WHEN CG.Schedule like '%010A000' THEN 'Every 5 mins'
WHEN CG.Schedule like '%0114000' THEN 'Every 10 mins'
WHEN CG.Schedule like '%011E000' THEN 'Every 15 mins'
WHEN CG.Schedule like '%0128000' THEN 'Every 20 mins'
WHEN CG.Schedule like '%0132000' THEN 'Every 25 mins'
WHEN CG.Schedule like '%013C000' THEN 'Every 30 mins'
WHEN CG.Schedule like '%0150000' THEN 'Every 40 mins'
WHEN CG.Schedule like '%015A000' THEN 'Every 45 mins'
WHEN CG.Schedule like '%0100100' THEN 'Every 1 hour'
WHEN CG.Schedule like '%0100200' THEN 'Every 2 hours'
WHEN CG.Schedule like '%0100300' THEN 'Every 3 hours'
WHEN CG.Schedule like '%0100400' THEN 'Every 4 hours'
WHEN CG.Schedule like '%0100500' THEN 'Every 5 hours'
WHEN CG.Schedule like '%0100600' THEN 'Every 6 hours'
WHEN CG.Schedule like '%0100700' THEN 'Every 7 hours'
WHEN CG.Schedule like '%0100B00' THEN 'Every 11 Hours'
WHEN CG.Schedule like '%0100C00' THEN 'Every 12 Hours'
WHEN CG.Schedule like '%0101000' THEN 'Every 16 Hours'
WHEN CG.Schedule like '%0100008' THEN 'Every 1 days'
WHEN CG.Schedule like '%0100010' THEN 'Every 2 days'
WHEN CG.Schedule like '%0100028' THEN 'Every 5 days'
WHEN CG.Schedule like '%0100038' THEN 'Every 7 Days'
WHEN CG.Schedule like '%0192000' THEN '1 week'
WHEN CG.Schedule like '80000' THEN 'Update Once'
WHEN CG.SChedule = '' THEN 'Manual'
END AS [Update Schedule],
VC.MemberCount AS [Member count],
Case
when VC.RefreshType = 1 then 'Manual'
when VC.RefreshType = 2 then 'Scheduled'
when VC.RefreshType = 4 then 'Incremental'
when VC.RefreshType = 6 then 'Scheduled and Incremental'
else 'Unknown'
end as [Refresh Type]
from collections_g cg
left join v_collections VC on VC.SiteID = CG.SiteID

SCCM SQL query for Collection report

Select v_Collection.CollectionID,
v_Collection.Name,
v_Collections_G.LimitToCollectionID as 'LimitToCollectionID',
Collections.LimitToCollectionName as 'LimitingCollectionName',
vSMS_CollectionDependencies.SourceCollectionID as 'Dependant Collection ID',
Case When
vSMS_CollectionDependencies.relationshiptype = 1 then 'Limited '
when vSMS_CollectionDependencies.relationshiptype = 2 then 'Included '
when vSMS_CollectionDependencies.relationshiptype = 3 then 'Excluded '
end as 'Included or excluded',
Case when dbo.v_Collections_G.CollectionType = 1 Then 'User' when dbo.v_Collections_G.CollectionType = 2 Then 'Device' Else 'Others' End as 'CollectionType',
v_Collection.MemberCount as 'MembersCount',
Collections.BeginDate as 'CreatedDate',
pck.Name AS Package_name,
app.ApplicationName,
FullPath AS 'Collection_Path'
from v_Collection
inner Join vFolderMembers ON v_Collection.CollectionID = vFolderMembers.InstanceKey
and vFolderMembers.ObjectTypeName='SMS_Collection_Device'
left join dbo.v_Collections_G on dbo.v_Collection.CollectionID = dbo.v_Collections_G.SiteID
left join dbo.Collections on dbo.v_Collection.CollectionID = dbo.Collections.SiteID
left join dbo.v_CollectionRuleQuery ON dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionID
left join v_Advertisement adv on v_Collection.CollectionID=adv.CollectionID
left join v_Package pck on adv.PackageID=pck.PackageID
left join v_ApplicationAssignment app on app.CollectionID=v_Collection.CollectionID
left join vSMS_CollectionDependencies on vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID
Left Join
(Select L1.Name,L1.ContainerNodeID,IsNull(L6.Name+'\','')+IsNull(L5.Name+'\','')+IsNull(L4.Name+'\','')+IsNull(L3.Name+'\','')+IsNull(L2.Name+'\','')+IsNull(L1.Name,'') as FullPath
from vSMS_Folders as L1
left join vSMS_Folders as L2 on L1.ParentContainerNodeID=L2.ContainerNodeID
left join vSMS_Folders as L3 on L2.ParentContainerNodeID=L3.ContainerNodeID
left join vSMS_Folders as L4 on L3.ParentContainerNodeID=L4.ContainerNodeID
left join vSMS_Folders as L5 on L4.ParentContainerNodeID=L5.ContainerNodeID
left join vSMS_Folders as L6 on L5.ParentContainerNodeID=L6.ContainerNodeID) as Mapping
on mapping.ContainerNodeID = vFolderMembers.ContainerNodeID