Query to show packages with no deployments or task sequences linked

SELECT pkg.Name as [Package Name], pkg.Version, pkg.PackageID,
CASE pkg.PackageType
WHEN 0 THEN 'Software Distribution Package'
WHEN 3 THEN 'Driver Package'
WHEN 4 THEN 'Task Sequence Package'
WHEN 5 THEN 'Software Update Package'
WHEN 6 THEN 'Device Settings Package'
WHEN 7 THEN 'Virtual Package'
WHEN 257 THEN 'Image Package'
WHEN 258 THEN 'Boot Image Package'
WHEN 259 THEN 'OS Install Package'
END AS [Package Type], adv.AdvertisementID, tsp.Name as [TS Name]
FROM v_Package pkg
LEFT JOIN v_Advertisement adv on pkg.PackageID=adv.PackageID
LEFT JOIN v_TaskSequencePackageReferences tsr on pkg.PackageID=tsr.RefPackageID
LEFT JOIN v_TaskSequencePackage tsp on tsr.PackageID=tsp.PackageID
WHERE adv.AdvertisementID is null
AND pkg.PackageType <> 8
AND tsp.Name is null
ORDER BY pkg.PackageType

I added exclusion of PackageType 8 because it is Application model type (which is useless in this query)

source : http://www.jacquesinit.com/blog/sql-query-show-packages-with-no-deployments-or-task-sequences-linked/

Forcing/Re-Triggering an SCCM Application Install

$ComputerName = "PR00001"
$AppName = "Microsoft Office 2013 Pro"
$s = New-PSSession -ComputerName $ComputerName
Invoke-Command -Session $s -Argu $ComputerName,$AppName -ScriptBlock `
param ($ComputerName,$AppName)
write-host "Getting Parameters for '$AppName' on $ComputerName"
$App = Get-WmiObject -computername $ComputerName -Namespace "root\ccm\ClientSDK" -Class CCM_Application | where {$_.Name -like "$AppName"} | Select-Object Id, Revision, IsMachineTarget
$AppID = $App.Id
$AppRev = $App.Revision
$AppTarget = $App.IsMachineTarget
write-host $AppID, $AppRev, $AppTarget -ForegroundColor Yellow
write-host "Triggering Installation!" -ForegroundColor Green
([wmiclass]'ROOT\ccm\ClientSdk:CCM_Application').Install($AppID, $AppRev, $AppTarget, 0, 'Normal', $False) | Out-Null
Remove-PSSession $s

source :

SCCM Create Collections with all Laptops

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 inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where
SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( “8”, “9”, “10”, “14” )

If you want to change the system enclosure type, here is Microsoft’s list of chassis type codes:

1 Other
2 Unknown
3 Desktop
4 Low Profile Desktop
5 Pizza Box
6 Mini Tower
7 Tower
8 Portable
9 Laptop
10 Notebook
11 Hand Held
12 Docking Station
13 All in One
14 Sub Notebook
15 Space-Saving
16 Lunch Box
17 Main System Chassis
18 Expansion Chassis
19 Sub Chassis
20 Bus Expansion Chassis
21 Peripheral Chassis
22 Storage Chassis
23 Rack Mount Chassis
24 Sealed-Case PC


source :