Join Office Versions to Resource, User Name, Hostname

SELECT  
       a.[TimeStamp]
      ,a.[ARPDisplayName0]
      ,a.[InstallDate0]
      ,a.[ProductName0]
      ,a.[ProductVersion0]
      ,a.[Publisher0]
      ,a.[RegisteredUser0]
      ,b.Name0
      ,b.User_Domain0
      ,b.User_Name0
  FROM [CMDBXXX].[dbo].[v_GS_INSTALLED_SOFTWARE] a
  JOIN v_R_System b on a.ResourceID=b.ResourceID
  WHERE convert(varchar(10),timestamp,121)>='2016-01-01' AND ARPDisplayName0 IN (
  'Microsoft Office Home and Business 2010',
  'Microsoft Office Standard 2010',
  'Microsoft Office Home and Student 2010',
  'Microsoft Office Standard 2013',
  'Microsoft Office XP Professional',
  'Microsoft Office 2000 SR-1 Professional') 

 

Comparing Hash values on files

HashDir - https://drive.google.com/drive/folders/0B9W5e09kwwSXVFROR3R2b0gxNXM?usp=sharing

Bulk Create AD Groups from File

########################################################### 
# AUTHOR  : Marius / Hican - http://www.hican.nl - @hicannl  
# DATE    : 08-08-2012
# EDIT    : 16-11-2012
# CHANGES : Added functionality for duplicate OU names and
#           changed the input file slightly because of this
# COMMENT : This script does a bulk creation of Groups in
#           Active Directory based on an input csv and the
#           Active Directory Module. 
########################################################### 
Import-Module ActiveDirectory
#Import CSV
$path     = Split-Path -parent $MyInvocation.MyCommand.Definition 
$newpath  = $path + "\bulk_input.csv"
$csv      = @()
$csv      = Import-Csv -Path $newpath

#Get Domain Base
$searchbase = Get-ADDomain | ForEach {  $_.DistinguishedName }

#Loop through all items in the CSV
ForEach ($item In $csv)
{
  #Check if the OU exists
  $check = [ADSI]::Exists("LDAP://$($item.GroupLocation),$($searchbase)")
  
  If ($check -eq $True)
  {
    Try
    {
      #Check if the Group already exists
      $exists = Get-ADGroup $item.GroupName
      Write-Host "Group $($item.GroupName) alread exists! Group creation skipped!"
    }
    Catch
    {
      #Create the group if it doesn't exist
      $create = New-ADGroup -Name $item.GroupName -GroupScope $item.GroupType -Path ($($item.GroupLocation)+","+$($searchbase))
      Write-Host "Group $($item.GroupName) created!"
    }
  }
  Else
  {
    Write-Host "Target OU can't be found! Group creation skipped!"
  }
}

Create Bulk User Collections from File

#Create the required ‘global’ variables

$ConfigMgrModulePath=“[PATH TO CM]\ConfigurationManager.psd1”
$ConfigMgrSiteCode=“[SITE CODE]:”

#Connecting to site

Import-Module $ConfigMgrModulePath
Set-Location $ConfigMgrSiteCode

#Creating the User Collections

 Import-CSV [PATH_TO\]BulkCreateUserCollections.csv | %{

#Create the required ‘local’ variables

$AllUsers="All Users"
$RefreshType="Periodic"
$RefreshSchedule=New-CMSchedule -RecurInterval Hours -RecurCount 1
$DomainName=“[DOMAIN]”
$UCInstallName="'"+$DomainName+'\\CM '+$_.CMName+"'"

# $UCUninstallName=“Uninstall “+$_.BulkCreateUserCollections
$QueryExpression='"select SMS_R_USER.ResourceID,SMS_R_USER.ResourceType,SMS_R_USER.Name,SMS_R_USER.UniqueUserName,SMS_R_USER.WindowsNTDomain from SMS_R_User where SMS_R_User.SecurityGroupName='

#Create the User Collection with a query rule

New-CMUserCollection -LimitingCollectionName $AllUsers -Name $_.CMName -RefreshType $RefreshType -RefreshSchedule $RefreshSchedule
$QueryExpression
$UCInstallName


Add-CMUserCollectionQueryMembershipRule -CollectionName $_.CMName -RuleName $_.CMName -QueryExpression $QueryExpression$UCInstallName

#Create the ‘uninstall’ User Collection with 2 rules: include All Users and exclude the User Collection

# New-CMUserCollection -LimitingCollectionName $AllUsers -Name $UCUninstallName -RefreshType $RefreshType

# Add-CMUserCollectionIncludeMembershipRule -CollectionName $UCUninstallName -IncludeCollectionName $AllUsers

# Add-CMUserCollectionExcludeMembershipRule -CollectionName $UCUninstallName -ExcludeCollectionName $_.BulkCreateUserCollections

}

Find Collections with "all devices are part of the same server group"

select collections_g.SiteID from CEP_CollectionExtendedProperties join collections_g onCEP_CollectionExtendedProperties.collectionID=collections_g.collectionid where UseCluster= 1

https://social.technet.microsoft.com/Forums/en-US/86783d86-0e38-4cb4-acf8-6110acc76c0e/configmgr-1602-error-0x87d006662016410010-while-installing-update?forum=configmanagersecurity