WSUS Server Report

COMPLIANT
SELECT a.ResourceID
      ,b.Name0 as "Name"
      ,a.TimeStamp as "Time Stamp"
      ,a.WUServer0 as "WSUS Server"
 ,b.Full_Domain_Name0 as Domain
 ,b.Distinguished_Name0 as FQDN
 ,b.Operating_System_Name_and0 as OS
 ,b.Build01 as "OS Build"
  FROM v_GS_WSUSSettings_WSUSServer0 a
  join v_R_System b on a.ResourceID=b.ResourceID
  WHERE a.WUServer0 = 'http://WSUSSERVER:8530'

NON-COMPLIANT
SELECT a.ResourceID
      ,b.Name0 as "Name"
      ,a.TimeStamp as "Time Stamp"
      ,a.WUServer0 as "WSUS Server"
 ,b.Full_Domain_Name0 as Domain
 ,b.Distinguished_Name0 as FQDN
 ,b.Operating_System_Name_and0 as OS
 ,b.Build01 as "OS Build"
  FROM v_GS_WSUSSettings_WSUSServer0 a
  join v_R_System b on a.ResourceID=b.ResourceID
  WHERE (a.WUServer0 <> 'http://WSUSSERVER:8530' or a.WUServer0 IS NULL)

****

IMPORT.MOF:

// RegKeyToMOF by Mark Cochrane (tribute to Skissinger & Steverac)

// this section tells the the inventory agent what to report to the server

#pragma namespace ("\\\\.\\root\\cimv2\\SMS")

#pragma deleteclass("WSUSServer", NOFAIL)

[SMS_Report(TRUE),SMS_Group_Name("WSUSServer"),SMS_Class_ID("WSUSSettings|WSUSServer|1.0")]

Class WSUSServer: SMS_Class_Template

{

[SMS_Report(TRUE),key] string KeyName;

[SMS_Report(TRUE)] String WUServer;

};

CONFIGURATION.MOF

// RegKeyToMOF by Mark Cochrane (tribute to Skissinger & Steverac)

// this section tells the inventory agent what to collect

#pragma namespace ("\\\\.\\root\\cimv2")

#pragma deleteclass("WSUSServer", NOFAIL)

[DYNPROPS]

Class WSUSServer

{

[key] string KeyName;

String WUServer;

String WUStatusServer;

Uint32 AcceptTrustedPublisherCerts;

};

[DYNPROPS]

Instance of WSUSServer

{

keyname="RegKeyToMOF[MarkCochrane]";

[PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\Windows\\WindowsUpdate|WUServer"),Dynamic,Provider("RegPropProv")] WUServer;

[PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\Windows\\WindowsUpdate|WUStatusServer"),Dynamic,Provider("RegPropProv")] WUStatusServer;

};

SQL - Add Remove Programs joined to Logged on User

select 
hs.ResourceID,hs.ARPDisplayName0,hs.ProductVersion0,
b.User_Domain0, b.User_Name0,b.Name0
from v_HS_INSTALLED_SOFTWARE hs 
join v_R_System b on hs.ResourceID=b.ResourceID
where ProductName0 like '%app name%'

Remove Citrix ICA Webclient (7.x)

when installed via browser (minimal)

@echo off
cls
echo Running Removal of Citrix ICA WEB Client (Minimal) 7.0.17534
echo Stopping Services
REM TASKKILL /F /IM pn.exe
REM TASKKILL /F /IM wfcrun32.exe
REM TASKKILL /F /IM wfica32.exe
TASKKILL /F /IM iexplore.exe

:: Process
If %PROCESSOR_ARCHITECTURE% == x86 goto OS32bit_i
If %PROCESSOR_ARCHITECTURE% == AMD64 goto OS64bit_i

:OS32bit_i
"C:\Program Files\Citrix\icacab\wfica32.exe" /unregserver
RD /s /q "C:\Program Files\Citrix"
REG DELETE "HKLM\SOFTWARE\Citrix" /f 
REG DELETE "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Small Citrix ICA Web Client" /f
goto end

:OS64bit_i
"C:\Program Files\Citrix\icacab\wfica32.exe" /unregserver
RD /s /q "C:\Program Files\Citrix"
REG DELETE "HKLM\SOFTWARE\Wow6432Node\Citrix" /f
REG DELETE "HKLM\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\Small Citrix ICA Web Client" /f
goto end

:end
PAUSE

+++++++

When installed via ica32t.exe (setup)

@echo off
cls
echo Running Removal of Citrix ICA WEB Client 7.0.17534
echo Stopping Services
REM TASKKILL /F /IM pn.exe
REM TASKKILL /F /IM wfcrun32.exe
REM TASKKILL /F /IM wfica32.exe
TASKKILL /F /IM iexplore.exe

:: Process
If %PROCESSOR_ARCHITECTURE% == x86 goto OS32bit_i
If %PROCESSOR_ARCHITECTURE% == AMD64 goto OS64bit_i

:OS32bit_i
"C:\Program Files\Citrix\icaweb32\wfica32.exe" /unregserver
RD /s /q "C:\Program Files\Citrix"
del c:\windows\System32\ctxsetup.exe
RD /s /q "C:\Program Files\Citrix"
RD /s /q "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Citrix ICA Client"
REG DELETE "HKLM\SOFTWARE\Citrix" /f 
REG DELETE "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Citrix ICA Web Client" /f
goto end

:OS64bit_i
"C:\Program Files (x86)\Citrix\icaweb32\wfica32.exe" /unregserver
RD /s /q "C:\Program Files (x86)\Citrix"
del c:\windows\SysWOW64\ctxsetup.exe
REG DELETE "HKLM\SOFTWARE\Wow6432Node\Citrix" /f
REG DELETE "HKLM\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\Citrix ICA Web Client" /f
goto end

:end
PAUSE

File Version Detection Login

$FilePath = "7-Zip"
$FileName = "7zFM.exe"
$DesiredVer = "16"
$FileVerLength = 2

If (Test-Path $env:ProgramFiles\$FilePath\$FileName) {
$AppVer = (Get-Item -Path $env:ProgramFiles\$FilePath\$FileName).VersionInfo
$AppVer = $AppVer.FileVersion.substring(0,$FileVerLength)
if ($AppVer -ge $DesiredVer)

{ write-host "Success" }
}


Reason:
    Performing detection of app deployment type Install_7Zip_16.04.0.0(ScopeId_BEBD3B8D-19A8-422B-BBE9-9ECCE2A830DC/DeploymentType_33465a95-df3e-4cd6-be8c-84afb5629c79, revision 5) for user. AppDiscovery 02/11/2016 10:57:46 15744 (0x3D80)
    In-line script returned error output: Get-Item : Cannot find path 'C:\Program Files\7-Zip\7zFM.exe' because it does not exist.
At C:\WINDOWS\CCM\SystemTemp\91159495-3fce-405a-8392-edadd4f20e36.ps1:6 char:12
+ $AppVer = (Get-Item -Path $env:ProgramFiles\$FilePath\$FileName).Vers ...
+            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (C:\Program Files\7-Zip\7zFM.exe:String) [Get-Item], ItemNotFoundExcepti 
   on
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemCommand

You cannot call a method on a null-valued expression.
At C:\WINDOWS\CCM\SystemTemp\91159495-3fce-405a-8392-edadd4f20e36.ps1:7 char:1
+ $AppVer = $AppVer.FileVersion.substring(0,$FileVerLength)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

AppDiscovery 02/11/2016 10:57:47 15744 (0x3D80)
A script execution error has occurred. The script has no output in stdout and an error message in stderr. AppDiscovery 02/11/2016 10:57:47 15744 (0x3D80)
Script Execution returned error message: Get-Item : Cannot find path 'C:\Program Files\7-Zip\7zFM.exe' because it does not exist.
At C:\WINDOWS\CCM\SystemTemp\91159495-3fce-405a-8392-edadd4f20e36.ps1:6 char:12
+ $AppVer = (Get-Item -Path $env:ProgramFiles\$FilePath\$FileName).Vers ...
+            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (C:\Program Files\7-Zip\7zFM.exe:String) [Get-Item], ItemNotFoundExcepti 
   on
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemCommand

You cannot call a method on a null-valued expression.
At C:\WINDOWS\CCM\SystemTemp\91159495-3fce-405a-8392-edadd4f20e36.ps1:7 char:1
+ $AppVer = $AppVer.FileVersion.substring(0,$FileVerLength)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

, ExitCode: 4294967295 AppDiscovery 02/11/2016 10:57:47 15744 (0x3D80)
  Script Execution Returned :4294967295, Error Message: Get-Item : Cannot find path 'C:\Program Files\7-Zip\7zFM.exe' because it does not exist.
At C:\WINDOWS\CCM\SystemTemp\91159495-3fce-405a-8392-edadd4f20e36.ps1:6 char:12
+ $AppVer = (Get-Item -Path $env:ProgramFiles\$FilePath\$FileName).Vers ...
+            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (C:\Program Files\7-Zip\7zFM.exe:String) [Get-Item], ItemNotFoundExcepti 
   on
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemCommand

You cannot call a method on a null-valued expression.
At C:\WINDOWS\CCM\SystemTemp\91159495-3fce-405a-8392-edadd4f20e36.ps1:7 char:1
+ $AppVer = $AppVer.FileVersion.substring(0,$FileVerLength)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

. [AppDT Id: ScopeId_BEBD3B8D-19A8-422B-BBE9-9ECCE2A830DC/DeploymentType_33465a95-df3e-4cd6-be8c-84afb5629c79, Revision: 5] AppDiscovery 02/11/2016 10:57:47 15744 (0x3D80)
CScriptHandler::DiscoverApp failed (0xffffffff). AppDiscovery 02/11/2016 10:57:47 15744 (0x3D80)
Deployment type detection failed with error 0xffffffff. AppDiscovery 02/11/2016 10:57:47 15744 (0x3D80)

Failed to perform detection of app deployment type Install_7Zip_16.04.0.0(Install_7Zip_16.04.0.0, revision 5) for user. Error 0xffffffff AppDiscovery 02/11/2016 10:57:47 15744 (0x3D80)

User Group to Machine

Query an AD group then match and return computer(s):

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, SMS_R_User.UniqueUserName
FROM SMS_R_System
JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName
JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
Where SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "DOMAIN\\GROUPNAME")

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

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