Script to Pull Applications Assigned to User for OSD

GetUserAppList.ps1
#Set $Username to SMSTSUdaUsers variable
$TaskSequenceEnvironment = New-Object -COMObject Microsoft.SMS.TSEnvironment
$Username = $TaskSequenceEnvironment.Value("SMSTSUdaUsers")

if ($UserName -like '*\*') { $UserName = ($UserName -split '\\')[1] }

# Define default parameters (Splatting)
$Splatting = @{
    ComputerName = "infcmps01.corplan.net"
    NameSpace = "root\SMS\Site_XXX"
}

# Find the User in SCCM CMDB
$User = Get-WMIObject @Splatting -Query "Select * From SMS_R_User WHERE UserName='$UserName'"

#Retrieve the collections the user is member of
# Find the collections where the user is member of
$Collections = Get-WmiObject -Class sms_fullcollectionmembership @splatting -Filter "ResourceID = '$($user.resourceid)'"

#Retrieve the deployments of each Collections and output the information
# For each collection we find the deployments
# Then output an object with information of the user, collection and application advertised
Foreach ($Collection in $collections)
{
    # Find the Deployment on one collection                    
    $Deployments = (Get-WmiObject @splatting -Query "Select * From SMS_DeploymentInfo WHERE CollectionID='$($Collection.CollectionID)'")
    
    Foreach ($Deploy in $Deployments)
    {
        $deploy.targetname | Out-File -append C:\SCCM_Logs\UDAList.txt
                    
    }
   

    }

SetTSUserAppVariables
# Create an TaskSequence Environment Object
$TaskSequenceEnvironment = New-Object -COMObject Microsoft.SMS.TSEnvironment
$basevariablename = 'XXX'
$applicationlist = get-content 'C:\SCCM_logs\UDAList.txt'

# Create a Counter
$Counter = 1
# Foreach Application we create an incremented variable
$ApplicationList | ForEach-Object {

# Define the Variable Name
$Variable = "$BaseVariableName{0:00}" -f $Counter

# Create the Task Sequence Variable
$TaskSequenceEnvironment.value("$Variable") = "$_"

# Increment the counter
[void]$Counter++


}

Workstations by OSD Method - Useful for reporting non-standard deployments.

Use RegKey2MOF to add a MOF for HKLM\Software\Microsoft\Deployment 4

SELECT b.Name0
 ,c.UniqueUserName
 ,b.Domain0
 ,d.Version0
 ,d.Caption0
 ,a.[TimeStamp]
      ,a.[DeploymentMethod0]
      ,a.[DeploymentSource0]
      ,a.[DeploymentTimestamp0]
      ,a.[DeploymentToolkitVersion0]
      ,a.[DeploymentType0]
      ,a.[OSDAdvertisementID0]
      ,a.[OSDPackageID0]
      ,a.[OSDProgramName0]
      ,a.[TaskSequenceID0]
      ,a.[TaskSequenceName0]
      ,a.[TaskSequenceVersion0]
  FROM [CM_I01].[dbo].[v_GS_OSDInformation_OSDInfo_2_00] a
  join v_GS_SYSTEM b on a.ResourceID=b.ResourceID
  join v_UserMachineRelation c on a.ResourceID=c.MachineResourceID
  join v_GS_OPERATING_SYSTEM d on a.ResourceID=d.ResourceID
  where SystemRole0 = 'Workstation'

Powershell: List AD Group Members in Table and Export to CSV

Import-Module ActiveDirectory

$Groups = (Get-AdGroup -filter * | Where {$_.name -like "NAME*"})
$Table = @()
$Record = [ordered]@{
"Group Name" = ""
"Name" = ""
}
Foreach ($Group in $Groups)
{
$Arrayofmembers = Get-ADGroupMember -identity $Group | select name
foreach ($Member in $Arrayofmembers)
{
$Record."Group Name" = $Group
$Record."Name" = $Member.name
$objRecord = New-Object PSObject -property $Record
$Table += $objrecord
}
}
$Table | export-csv "C:\temp\SecurityGroups.csv" -NoTypeInformation

Diskstation SSH

Turn off blue light:

su -s
[password]
echo 6 >/dev/ttyS1

OSD - Microsoft Surfaces

Found this. Lots of interesting Task Sequence customisations for Surface deployments, some of which are still relevant for Win 10 OSD.

http://download.microsoft.com/download/4/4/0/4407548E-ECE0-43C2-8EE1-13544CFC591B%2Fsurface-devices-in-the-enterprise-OS-deployment-with-SCCM-2012.pdf

SQL Query to Join Results via ResourceID

SELECT 
a.[ResourceID],
a.FileName,
a.FileVersion,
a.FilePath,
b.Distinguished_Name0
FROM [CM_DBxx].[dbo].[v_GS_SoftwareFile] a
JOIN [CM_DBxx].[dbo].[v_R_System] b on a.[ResourceID]=b.[ResourceID]
WHERE a.FileName = 'application.exe'

or..get even more information

Select 
sf.ResourceID,
sf. FilePath,
sf.FileVersion,
sf.CreationDate,
sf.FileCount,
sf.FileDescription,
sf.FileModifiedDate,
sf.FileSize,
sf.ProductId,
sf.ResourceID,
b.Name0,
b.Last_Logon_Timestamp0,
b.User_Name0,
b.Creation_Date0

from v_gs_softwarefile sf
join v_R_System b on sf.ResourceID=b.resourceID

where sf.FileName = 'acrord32.exe'

Using Powershell and WinSCP to SFTP upload

There may be an occasion where you need to send your files somewhere using SFTP. Perhaps as an automated, scheduled task:

It is not necessary to install the full WinSCP application stack. The automation pack should be used for automation. Download WinSCP automation from .NET assembly / COM library on the https://winscp.net/eng/download.php page and place on the server.

This pack contains an executable and the DLL that interact with each other. Just put the DLL and EXE together.

Script:

[System.Reflection.Assembly]::LoadFrom('C:\PATH\WinSCPnet.dll')
Add-Type -Path "C:\PATH\WinSCPnet.dll"
$sessionOptions = New-Object WinSCP.SessionOptions
$sessionOptions.ParseUrl("sftp://USER:PASS@;fingerprint=FINGERPRINT@SFTP.SITE.COM")
$session = New-Object WinSCP.Session
$session.Open($sessionOptions)
$session.PutFiles("C:\PATHTOUPLOAD\[file.xyz] or [* for all]", "/[upload folder if necessary]/").Check()
$session.Dispose()

If you downloaded the files, make sure you unblock:

Now, simply configure your Scheduled Task. Don't forget to set the Security Options.

Program/Script:                        powershell.exe

Add arguments (optional):        -ExecutionPolicy Bypass -File "C:\PathToYour\File.ps1"

SQL Query for Device Information

Lovely query for your Device information overview

SET NOCOUNT ON;

-- Declaration

  
--Devices
SELECT 
             COALESCE(Computer_System_DATA.Name0, '') AS DeviceName,
COALESCE(v_gS_operating_system.Caption0, '') + ' ' + COALESCE(v_gS_operating_system.CSDVersion0, '') + ' ' + COALESCE(v_gS_operating_system.Version0, '') AS OperatingSystem,
CASE
when v_gS_operating_system.Caption0 like '%server%' then 'Server'
  when System_Enclosure_data.ChassisTypes0 = 1 then 'Other'
when System_Enclosure_data.ChassisTypes0 = 2 then 'Unknown'
when System_Enclosure_data.ChassisTypes0 = 3 then 'Desktop'
when System_Enclosure_data.ChassisTypes0 = 4 then 'Desktop'
when System_Enclosure_data.ChassisTypes0 = 5 then 'Desktop'
when System_Enclosure_data.ChassisTypes0 = 6 then 'Desktop'
when System_Enclosure_data.ChassisTypes0 = 7 then 'Desktop'
when System_Enclosure_data.ChassisTypes0 = 8 then 'Desktop'
when System_Enclosure_data.ChassisTypes0 = 9 then 'Laptop'
when System_Enclosure_data.ChassisTypes0 = 10 then 'Laptop'
when System_Enclosure_data.ChassisTypes0 = 11 then 'Hand Held'
when System_Enclosure_data.ChassisTypes0 = 12 then 'Docking Station'
when System_Enclosure_data.ChassisTypes0 = 13 then 'All in One'
when System_Enclosure_data.ChassisTypes0 = 14 then 'Sub Notebook'
when System_Enclosure_data.ChassisTypes0 = 15 then 'Space-Saving'
when System_Enclosure_data.ChassisTypes0 = 16 then 'Lunch Box'
when System_Enclosure_data.ChassisTypes0 = 17 then 'Main System Chassis'
when System_Enclosure_data.ChassisTypes0 = 18 then 'Expansion Chassis'
when System_Enclosure_data.ChassisTypes0 = 19 then 'SubChassis'
when System_Enclosure_data.ChassisTypes0 = 20 then 'Bus Expansion Chassis'
when System_Enclosure_data.ChassisTypes0 = 21 then 'Peripheral Chassis'
when System_Enclosure_data.ChassisTypes0 = 22 then 'Storage Chassis'
when System_Enclosure_data.ChassisTypes0 = 23 then 'Rack Mount Chassis'
when System_Enclosure_data.ChassisTypes0 = 24 then 'Sealed-Case PC'
end as 
  DeviceType,
'' as ServicePack,
coalesce(Network_DATA.IPAddress0,'') as IPAddress,
  case
when System_Enclosure_DATA.SerialNumber0 like 'VMware%' then 'Virtual'
when Computer_System_DATA.Model0 like 'VMWare%' then 'Virtual'
when Computer_System_DATA.Manufacturer0 like 'VMWare%' then 'Virtual'
when System_Enclosure_DATA.SerialNumber0 is not null then 'Physical'
when Computer_System_DATA.Manufacturer0 is not null then 'Physical'
when System_Enclosure_DATA.Model0 is not null then 'Physical'
else null
 end as PhysicalOrVirtual,
             COALESCE(Computer_System_DATA.Manufacturer0, '') AS Make,
             COALESCE(Computer_System_DATA.Model0, '') AS Model,
             COALESCE(System_Enclosure_DATA.SerialNumber0, '') AS serial_number,
COALESCE(LTRIM(Processor_DATA.Name0), 'Unknown') AS CPUName,
             COALESCE(cpus.cpu_count, '') AS NumberPhysicalCPUs,
sum(cpus.corecounts) as NumberLogicalCPUs,
             COALESCE(PC_Memory_DATA.TotalPhysicalMemory0 / 1024, '') AS RAMinBytes,
RS.User_Name0 as LastLoggedOnUser,
WS.LastHWScan as LastSeenDate,
null as Environment,
null as PhysicalHost
FROM         v_gs_Computer_System Computer_System_DATA
LEFT JOIN    v_GS_NETWORK_ADAPTER_CONFIGUR Network_DATA
                 ON (Computer_System_DATA.Resourceid = Network_DATA.Resourceid AND Network_DATA.Index0 = 1)

LEFT JOIN    v_gS_operating_system 
                 ON (Computer_System_DATA.Resourceid = v_gS_operating_system.Resourceid
                     AND v_gS_operating_system.BootDevice0 like '\Device\HarddiskVolume%')
LEFT JOIN    v_gs_Processor Processor_DATA
                 ON (Computer_System_DATA.Resourceid = Processor_DATA.Resourceid AND Processor_DATA.DeviceID0 = 'CPU0')
LEFT JOIN    v_GS_X86_PC_MEMORY pc_memory_DATA
                 ON (Computer_System_DATA.Resourceid = PC_Memory_DATA.Resourceid)
LEFT JOIN    v_gs_Disk disk_DATA
                 ON (Computer_System_DATA.Resourceid = Disk_DATA.Resourceid AND Disk_DATA.Index0 = 0)
LEFT JOIN    v_gs_Video_Controller Video_Controller_DATA
                 ON (Computer_System_DATA.Resourceid = Video_Controller_DATA.Resourceid
                     AND Video_Controller_DATA.DeviceID0 = 'VideoController1')
LEFT JOIN    v_gs_PC_BIOS PC_BIOS_DATA
                 ON (Computer_System_DATA.Resourceid = PC_BIOS_DATA.Resourceid)
LEFT JOIN    (
                 SELECT ResourceID,
                        COUNT(*) AS cpu_count,
sum(numberofCores0) as corecounts
                 FROM v_gs_Processor
                 GROUP BY resourceID
             ) cpus ON (Computer_System_DATA.Resourceid = cpus.Resourceid)
LEFT JOIN    v_gs_System_Enclosure System_Enclosure_data
                 ON (Computer_System_DATA.Resourceid = System_Enclosure_DATA.Resourceid AND System_Enclosure_DATA.GroupID = 1)
LEFT JOIN    v_R_System_valid RS
                 ON (Computer_System_DATA.Resourceid = RS.ResourceID)
LEFT JOIN    v_GS_WORKSTATION_STATUS WS
                 ON (RS.ResourceID = WS.ResourceID)
GROUP BY     Computer_System_DATA.Resourceid,
             COALESCE(Computer_System_DATA.Name0, ''),
             Computer_System_DATA.UserName0,
             Network_DATA.IPAddress0,
             v_gS_operating_system.InstallDate0,
             Processor_DATA.Name0,
             Processor_DATA.NormSpeed0,
             PC_Memory_DATA.TotalPhysicalMemory0,
             Disk_DATA.Size0,
             Video_Controller_DATA.Description0,
             PC_BIOS_DATA.Description0,
             System_Enclosure_DATA.ChassisTypes0,
            v_gS_operating_system.Caption0,
v_gS_operating_system.organization0,
             v_gS_operating_system.CSDVersion0,
             v_gS_operating_system.Version0,
             cpus.cpu_count,
             System_Enclosure_DATA.SMBIOSAssetTag0,
             Processor_DATA.DeviceID0,
             Computer_System_DATA.Manufacturer0,
             Computer_System_DATA.Model0,
             System_Enclosure_DATA.SerialNumber0,
             PC_BIOS_DATA.SerialNumber0,
             WS.LastHWScan,
System_Enclosure_DATA.Model0,
RS.User_Name0

Useful SQL Reports

Last Logon (since xxtime):

SELECT 
       [Netbios_Name0] AS 'Computer Name'
 ,[Full_Domain_Name0] AS 'Domain'
 ,[Last_Logon_Timestamp0] AS 'Last Logon'
 ,[Distinguished_Name0] AS 'Distinguished Name'
      ,[User_Domain0] AS 'User Logon Domain'
      ,[User_Name0] AS 'User Name'
      ,[Operating_System_Name_and0] AS 'OS'
  
  FROM [CM_I01].[dbo].[v_R_System] 

  WHERE convert(varchar(10),Last_Logon_Timestamp0,121)>='2016-01-01' AND Client0 = '1'

SQL Reports: Microsoft Office Suites

The difficulty in creating a report from the out-of-box reports in ConfigMgr is the wonderful array of product names that Microsoft uses. That is, one size doesn't fit all. You can't just use LIKE 'Microsoft Office%':

• Microsoft Office 97 Professional Edition
• Microsoft Office 365 ProPlus - en-us
• Microsoft Office 365 ProPlus - ko-kr
• Microsoft Office OSM MUI (English) 2013
• Microsoft Office OSM UX MUI (English) 2013
• Microsoft Office Shared Setup Metadata MUI (English) 2013
• Microsoft Office Shared 64-bit Setup Metadata MUI (English) 2013
• Microsoft Office 64-bit Components 2013
• Microsoft Office Shared 64-bit MUI (English) 2013
• Microsoft Office Home and Business 2010
• Microsoft Office Professional Edition 2003
• Microsoft Office Professional Edição 2003

In a word: Urgh! So how do we report on count of all the version installed? Firstly, you should get a report of all the versions that your estate has installed. Run a query against your CM DB and export to a file:

SELECT [ResourceID]

      ,[GroupID]

      ,[TimeStamp]

      ,[ARPDisplayName0]

      ,[InstallDate0]

      ,[InstallDirectoryValidation0]

      ,[InstalledLocation0]

      ,[InstallSource0]

      ,[InstallType0]

      ,[Language0]

      ,[LocalPackage0]

      ,[MPC0]

      ,[OsComponent0]

      ,[PackageCode0]

      ,[ProductID0]

      ,[ProductName0]

      ,[ProductVersion0]

      ,[Publisher0]

      ,[RegisteredUser0]

      ,[ServicePack0]

      ,[SoftwareCode0]

      ,[UninstallString0]

      ,[UpgradeCode0]

      ,[VersionMajor0]

      ,[VersionMinor0]

  FROM [CM_I01].[dbo].[v_GS_INSTALLED_SOFTWARE] WHERE ARPDisplayName0 like 'Microsoft Office%'

Now you need to prune out all the individual product names (Excel, Powerpoint, Outlook), Proofing, Shared Components etc). This I did exporting the query to Excel and applying a filter. Took about 7 minutes. This will leave you with the Product list for the Office suites themselves:

Microsoft Office 97 Professional Edition

Microsoft Office 365 ProPlus - en-us

Microsoft Office 365 ProPlus - ko-kr

Microsoft Office Home and Business 2010

Microsoft Office 365 ProPlus - de-de

Microsoft Office Professional Edition 2003

Microsoft Office Professional Edição 2003

Microsoft Office Standard Edition 2003

Microsoft Office Enterprise 2007

Microsoft Office Professional Plus 2007

Microsoft Office Professional Plus 2010

Microsoft Office Standard 2010

Microsoft Office Home and Student 2010

Microsoft Office Click-to-Run 2010

Microsoft Office Starter 2010 - English

Microsoft Office Professional Plus 2013

Microsoft Office Standard 2013

Microsoft Office Professional Plus 2016

Microsoft Office XP Professional

Microsoft Office 2000 SR-1 Professional

Now you can get to the good stuff. The SQL report. In my report I want to know all the suites installed and reported on from the start of 2016, grouped by count and sorted by descending value. I won't go through how to create a report and how to pretty is up, but here's the query. You should paste in your own results for the suites you're looking for: 

Select ARPDisplayName0 AS "Office Product Suite", count (*) AS "Count" FROM (

SELECT  

       [ResourceID]

      ,[GroupID]

      ,[TimeStamp]

      ,[ARPDisplayName0]

       ,[InstallDate0]

      ,[InstallDirectoryValidation0]

      ,[InstalledLocation0]

      ,[InstallSource0]

      ,[InstallType0]

      ,[Language0]

      ,[LocalPackage0]

      ,[MPC0]

      ,[OsComponent0]

      ,[PackageCode0]

      ,[ProductID0]

      ,[ProductName0]

      ,[ProductVersion0]

      ,[Publisher0]

      ,[RegisteredUser0]

      ,[ServicePack0]

      ,[SoftwareCode0]

      ,[UninstallString0]

      ,[UpgradeCode0]

      ,[VersionMajor0]

      ,[VersionMinor0]

  FROM [CM_I01].[dbo].[v_GS_INSTALLED_SOFTWARE]

  WHERE convert(varchar(10),timestamp,121)>='2016-01-01' AND ARPDisplayName0 IN (

  'Microsoft Office 97, Professional Edition',

  'Microsoft Office 365 ProPlus - en-us',

  'Microsoft Office 365 ProPlus - ko-kr',

  'Microsoft Office Home and Business 2010',

  'Microsoft Office 365 ProPlus - de-de',

  'Microsoft Office Professional Edition 2003',

  'Microsoft Office Professional Edição 2003',

  'Microsoft Office Standard Edition 2003',

  'Microsoft Office Enterprise 2007',

  'Microsoft Office Professional Plus 2007',

  'Microsoft Office Professional Plus 2010',

  'Microsoft Office Standard 2010',

  'Microsoft Office Home and Student 2010',

  'Microsoft Office Click-to-Run 2010',

  'Microsoft Office Starter 2010 - English',

  'Microsoft Office Professional Plus 2013',

  'Microsoft Office Standard 2013',

  'Microsoft Office Professional Plus 2016',

  'Microsoft Office XP Professional',

  'Microsoft Office 2000 SR-1 Professional')) a

group by ARPDisplayName0

order by count (*) DESC

Management are very happy bunnies, and your SQL-Fu has improved by +2

Software File Versions.

So a request came in from a business team to find out who isn't on the correct version and/or patch level of SAPGui. 

I had quite a few attempts at using Asset Intelligence but Hardware Inventory wasn't giving me the right info. Instead, I turned to Software Inventory. Software Inventorying is notoriously slow/intensive and isn't really recommended save for specific reasons (e.g. see Sherry Kissinger's posts and blog) but it's enabled weekly for .exes only so it was already gathering the right stuff for me.

Props go Sherry Kissinger for her help with my TechNet post. She very kindly and gentl pointed out "You're doing it wrong"

First, I needed to know how many versions of SAPGui.exe were out there, so a SQL query could do this:

Select sf.filename, sf.fileversion, sf.filepath, count(*)
from v_gs_softwarefile sf
where sf.filename = 'sapgui.exe'
group by sf.filename, sf.fileversion, sf.filepath

Returned:

Copying those results out and into Excel, removing duplicates and separating into general versions gave me all versions out there:

I am only concerned with the Program Files path exes, so I can now create my Collections with Queries:

• All Computers with Sagui.exe installed
• All Computers with the supported version 7300.3.15.8950 of Sapgui.exe installed
• All Computers with version 7300 that aren't on the supported 7300.3.15.8950 installed
• All Computers with version 7400 installed (naughty!)
• All Computers with version 7200 installed
• All Computers with version 7100

(The reason for the 7200 and 7100 Collections is that they require a different Application in order to upgrade)

All Computers with Sagui.exe installed
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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "sapgui.exe" and (SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files (x86)\\SAP\\FrontEnd\\SAPgui\\" or SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files\\SAP\\FrontEnd\\SAPgui\\")

All Computers with the supported version 7300.3.15.8950 of Sapgui.exe installed
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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "sapgui.exe" and (SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files (x86)\\SAP\\FrontEnd\\SAPgui\\" or SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files\\SAP\\FrontEnd\\SAPgui\\") and SMS_G_System_SoftwareFile.FileVersion = "7300.3.15.8950"

All Computers with version 7300 that aren't on the supported 7300.3.15.8950 installed

Query 7300.1.0.8948

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "sapgui.exe" and (SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files (x86)\\SAP\\FrontEnd\\SAPgui\\" or SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files\\SAP\\FrontEnd\\SAPgui\\") and SMS_G_System_SoftwareFile.FileVersion = "7300.1.0.8948"

Query 7300.3.7.8950

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "sapgui.exe" and (SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files (x86)\\SAP\\FrontEnd\\SAPgui\\" or SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files\\SAP\\FrontEnd\\SAPgui\\") and SMS_G_System_SoftwareFile.FileVersion = "7300.3.7.8950"

And repeat changing the SMS_G_System_SoftwareFile.FileVersion for each Query and for each Collection.

Now I can include (or re-use) the relevant Collection(s) in my Software Deployment Collections, and my Business team will be happy bunnies.

Lessons learnt:

1. Don't use Like!
2. Use SQL to help get your query right
3. Know what you are going to use the Collections for in the end

Amended SQL Report for 'Count operating systems and service packs' including architecture

Requirement was for OS versions/sp count but with added x86 or x64. 

Taking Sherri Kissinger's findings and adding a join for addresswidth

oh, and if running as query to test then replace (@UserSIDs) with ('Disabled') 

SELECT OPSYS.Caption0 as C054, OPSYS.CSDVersion0,addresswidth0 as [x64/x86], COUNT(*) AS 'Count'  
FROM fn_rbac_GS_OPERATING_SYSTEM(@UserSIDs)  OPSYS  
inner join fn_rbac_R_System(@UserSIDs)  sys on OPSYS.ResourceID=sys.ResourceID join [v_GS_PROCESSOR] c on sys.ResourceID=c.ResourceID
GROUP BY OPSYS.Caption0, OPSYS.CSDVersion0,addresswidth0  
ORDER BY OPSYS.Caption0, OPSYS.CSDVersion0