Michael Schultz

AzureAD/Intune – Add list of systems from CSV to Group with PowerShell

My work has been moving more and more into the cloud and making use of Intune. Recently, I was tasked with adding a large list of systems to a group in AzureAD for use in Intune. No one wants to do something like that manually, so I put together a script to do it for me.

I make use of two different PowerShell modules and you will need you AzureAD tenant ID, the object ID of the group along with rights to add to groups in AzureAD, and a CVS of the systems. I labeled the first row DeviceName.

Install-Module AzureAD
Install-Module msonline

$TenentID = <AzureAD tenent ID>
$groupID = <object ID of the AzureAd group> 
Connect-AzureAD -TenantId $TenentID
Connect-MsolService

$CHGList = Import-Csv -path <path to CSV>
Foreach ($Device in $CHGList)
{
$deviceadd = Get-MsolDevice -Name $Device.DeviceName | Select-Object -ExpandProperty ObjectID 
Add-AzureADGroupMember -ObjectId $groupID -RefObjectId $deviceadd
}

ConfigMgr Client Boundary and Boundary Group memberships SQL Query

ConfigMgr current branch SQL query to return the boundaries and boundary groups systems are part of.

https://github.com/NecroMonkey/vault/blob/master/SQL/client_boundary_membership_update.sql


SELECT DISTINCT 

sysr.Netbios_Name0,

ipr.Name as &#91;Boundary],

ipr.BoundaryType as &#91;Type],

bg.Name AS &#91;Boundary Group]

FROM     v_R_System AS sysr INNER JOIN

                  System_IP_Address_ARR AS ip ON ip.ItemKey = sysr.ResourceID AND ip.NumericIPAddressValue &lt;> 0 INNER JOIN

                  v_RA_System_IPSubnets AS sub ON sub.ResourceID = sysr.ResourceID LEFT OUTER JOIN

                  v_RA_System_IPv6Prefixes AS v6 ON v6.ResourceID = sysr.ResourceID INNER JOIN

                  BoundaryEx AS ipr ON ipr.BoundaryType = 3 AND ip.NumericIPAddressValue BETWEEN ipr.NumericValueLow AND ipr.NumericValueHigh OR

                  ipr.BoundaryType = 1 AND ipr.Value = sysr.AD_Site_Name0 OR

                  ipr.BoundaryType = 0 AND ipr.Value = sub.IP_Subnets0 OR

                  ipr.BoundaryType = 2 AND ipr.Value = v6.IPv6_Prefixes0 INNER JOIN

                  vSMS_BoundaryGroupMembers as bgm ON ipr.BoundaryID = bgm.BoundaryID INNER JOIN

                  vSMS_BoundaryGroup as bg ON bgm.GroupID = bg.GroupID

GROUP BY sysr.Netbios_Name0, ipr.Name, ipr.BoundaryType, bg.Name

ORDER BY sysr.Netbios_Name0, ipr.Name, bg.Name

ConfigMgr Client Content Breakdown SQL Query

This SQL query for ConfigMgr current branch gives a breakdown of client content. It is also on my GitHub at https://github.com/NecroMonkey/vault/blob/master/SQL/ConfigMgr_content_source_breakdown.sql


drop table if exists ##temp1

drop table if exists ##temp2



SELECT distinct sysr.Name0,

sysr.ResourceID, ipr.Name as &#91;Boundary],

ipr.Value

into ##temp1

FROM v_R_System sysr

JOIN System_IP_Address_ARR ip ON ip.ItemKey = sysr.ResourceID AND ip.NumericIPAddressValue &lt;> 0     

JOIN v_RA_System_IPSubnets sub ON sub.ResourceID = sysr.ResourceID      

LEFT JOIN v_RA_System_IPv6Prefixes v6 ON v6.ResourceID = sysr.ResourceID

JOIN BoundaryEx AS ipr ON 

-- Check BoundaryType 3 (IPRANGE)

(ipr.BoundaryType = 3 AND ip.NumericIPAddressValue BETWEEN ipr.NumericValueLow AND ipr.NumericValueHigh)

-- Check BoundaryType 1 (ADSITE)

OR (ipr.BoundaryType = 1 AND ipr.Value = sysr.AD_Site_Name0) 

-- Check BoundaryType 0 (IPSUBNET)

OR (ipr.BoundaryType = 0 AND ipr.Value = sub.IP_Subnets0)

-- Check BoundaryType 2 (IPv6)

OR (ipr.BoundaryType = 2 AND ipr.Value = v6.IPv6_Prefixes0)

order by sysr.ResourceID



select rsys.Name0, rsys.ResourceID,

"Content Source Type" = CASE cdhs.DistributionPointType

WHEN 1 THEN 'Cloud DP (Distribution Point)'

WHEN 2 THEN 'Management Point'

WHEN 3 THEN 'Peer Cache'

WHEN 4 THEN 'DP (Distribution Point)'

WHEN 5 THEN 'BranchCache'

WHEN 6 THEN 'Delivery Optimization Peer'

WHEN 7 THEN 'Delivery Optimization Cache Server'

WHEN 8 THEN 'Microsoft Update'

END,

packages.Name as &#91;Content],

cdhs.ContentID,

CAST(SUM(cast(cdhs.BytesDownloaded as float)) / 1073741824 AS DECIMAL(10,2)) as &#91;GB]

into ##temp2

from

v_R_System as rsys

join ClientDownloadHistory as cdh on rsys.ResourceID = cdh.ClientId

join ClientDownloadHistorySources as cdhs on cdh.id = cdhs.DownloadHistoryID

join ClientDownloadHistoryBoundaryGroups as cdhbg on cdh.id = cdhbg.DownloadHistoryID JOIN

                         v_FullCollectionMembership AS a ON rsys.ResourceID = a.ResourceID JOIN

                         v_Collection AS b ON b.CollectionID = a.CollectionID LEFT JOIN CI_Contentpackages CI on CI.Content_UniqueID = cdhs.ContentID 

JOIN smspackages packages on packages.PkgID = ISNULL(CI.PkgID,cdhs.ContentId)

where (b.CollectionID = 'SMSDM003')

group by rsys.Name0, rsys.ResourceID, cdhs.DistributionPointType, packages.Name, cdhs.ContentID

order by rsys.Name0, rsys.ResourceID, cdhs.DistributionPointType, packages.Name, cdhs.ContentID



select

t1.Name0, t1.Boundary, t1.Value, t2.&#91;Content Source Type], t2.Content, t2.GB

from ##temp1 as t1 join

##temp2 as t2 on t1.ResourceID = t2.ResourceID



drop table if exists ##temp1

drop table if exists ##temp2

ConfigMgr Run Script – Kickoff Compliance Baseline Check

I came across a code snippet by Mick Pletcher () that you can add to the end of a compliance item script to kick off the evaluation of a baseline. I took that and added a parameter to it so you can use it as a ConfigMgr Run Script. It look like a lot of code but 90% is just documentation.

ConfigMgr PowerShell Script – Create Collections off Compliance Settings Configuration Items

The script uses Get-CMConfigurationItem cmdlet to get a list of LocalizedDisplayNames of Compliance Setting CIs and then runs a Foreach to create three collections based using the LocalizedDisplayName. Parameters specify the CM Site Code, Limiting Collection collection ID, and the CI Name. It does a check to see if a collection exists beofre creating which is also a self repair feature as a deleted collection will be recreated.

ConfigMgr Run Script – Set Service State

As you can tell, many of my script posts occur after something was needed at work and I ended scripting it. This time we needed to alter the startup of a service on a group of systems. I wrote this PowerShell script to set the startup type and status of a specified service. It does require three parameters: service name, startup type desired, and desired status.

ConfigMgr Run Script – Clear CM cache and BranchCache cache

We recently ran into some hash issues with content coming from BranchCache and wanted to clear out the cache on systems to wipe things clean so I did this quick script. I set the BranchCache part inside a comment block and used a # to comment out the comment block. This makes it so I could easily enable or disable the removal of the BranchCache part.