1. SQL Query To Retrieve Advanced Clients Assigned Site Code And Client Version
Select
SD.Name0 ‘Machine Name’,
SC.SMS_Assigned_Sites0 ‘Assigned Site’,
SD.Client_Version0 Version
From v_R_System SD
Join v_RA_System_SmsAssignedSites SC on SD.ResourceID = SC.ResourceID
Join v_GS_Operating_System OS on SD.ResourceID = OS.ResourceID
Where SD.Client0 = 1
And SD.Client_Type0 = 1
Order By ‘Machine Name’
2. SQL Query To Gather Video Card Memory Information
Select
SD.Name0 ‘Machine Name’,
VC.Name0 ‘Video Card’,
Convert(VarChar, VC.AdapterRam0 / 1024) + ‘ MB’
From v_R_System SD
Join v_Gs_Video_Controller VC on SD.ResourceID = VC.ResourceID
Where VC.Name0 <> ‘ConfigMgr Remote Control Driver’
Order By SD.Name0
3. SQL Query To See When Collections Were Last Updated
Select
CC.CollectionID,CN.CollectionName, CC.TimeUpdated
From Collection_MemberChg_Notif CC
Join Collections CN on CC.CollectionID = CN.SiteID
Order By CollectionName
4. SQL Query To Get Members Of A Specified Collection
Select
Members.Name
‘Collection Members:’
From CollectionMembers Members
Join Collections Coll on Members.SiteID = Coll.SiteID
Where Coll.CollectionName = ‘All Systems’
5. SQL Query Get User Information From A Specified OU
Select Distinct
CS.Name0 ‘Machine Name’,
CS.UserName0 ‘User Name’,
RU.Full_User_Name0 ‘Full Name’,
UOU.User_OU_Name0 ‘Users OU’,
RA.IP_Subnets0 ‘Subnet’
From v_Gs_Computer_System CS
Join v_RA_System_IPSubnets RA on RA.ResourceID = CS.ResourceID
Join v_R_User RU on RU.Unique_User_Name0 = CS.UserName0
Join v_RA_User_UserOUName UOU on UOU.ResourceID = RU.ResourceID
Where UOU.User_OU_Name0 = ‘DomainName.COM/OuName’
Order by CS.Name0, CS.Username0, RU.Full_User_Name0, RA.IP_Subnets0
6. SQL Query For Windows Media Player Versions
Select Distinct
v_R_System.Name0,
v_GS_SoftwareFile.FileName,
v_GS_SoftwareFile.FileDescription,
v_GS_SoftwareFile.FileVersion
From v_R_System
Join v_GS_SoftwareFile on v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID
Where FileName = ‘wmplayer.exe’
7. SQL Query Get Machine and User Information From A Specified Collection
Select
SD.Name0 ‘Machine Name’,
SD.Resource_Domain_OR_Workgr0 ‘Resource Domain’,
SD.User_Name0 ‘Login ID’,
SD.User_Domain0 ‘Account Domain’,
USR.Full_User_Name0 ‘Full Name’,
PCB.SerialNumber0 ‘Serial Number’,
CS.Manufacturer0 Manufacturer,
CS.Model0 Model,
SAS.SMS_Assigned_Sites0 ‘Assigned Site Code’
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
Join v_R_User USR on SD.User_Name0 = USR.User_Name0
Join v_GS_PC_BIOS PCB on SD.ResourceID = PCB.ResourceID
Join v_GS_COMPUTER_SYSTEM CS on SD.ResourceID = CS.ResourceID
Join v_RA_System_SMSAssignedSites SAS on SD.ResourceID = SAS.ResourceID
Where COL.Name = ‘All Systems’
8. SQL Query To Retrieve Clients Last Boot up Date
Select
SD.Name0 ‘Machine Name’,
SD.User_Name0 ‘Last Logged on User Name’,
Convert(VarChar(10), OS.LastBootUpTime0, 101) ‘Last Boot Date’
From v_R_System SD
Join v_Gs_Operating_System OS on SD.ResourceID = OS.ResourceID
Order By ‘Machine Name’
9. SQL Query To List Machines With IIS , FTP Or Telnet Installed
Select
SD.Name0 ‘Machine Name’,
SD.Operating_System_Name_and0 NOS,
SS.Name0 ‘Service Name’,
SS.DisplayName0 ‘Display Name’,
SS.StartMode0 ‘Start Type’,
SS.Started0 Started,
SS.State0 State,
SS.Status0 Status
From System_DISC SD
Join Services_DATA SS
on SS.MachineID = SD.ItemKey
Where SS.Name0 In (‘W3SVC’, ‘MsFtpSvc’, ‘TlntSvr’)
Order By ‘Machine Name’
10. SQL Query To Count The Number Of Client Machines With McAfee Virus Scan Installed
Select
Count(SD.Name0) Counts,
PF.DisplayName0,
PF.Version0
From v_Add_Remove_Programs PF
Join v_R_System SD on PF.ResourceID = SD.ResourceID
Where PF.DisplayName0 = ‘McAfee VirusScan Enterprise’
Group By PF.DisplayName0, PF.Version0
Order By Counts, PF.Version0
11. SQL Query To The Number Of Client Machines With McAfee Virus Scan Installed
Select
SD.Name0,
PF.DisplayName0,
PF.Version0
From v_Add_Remove_Programs PF
Join v_R_System SD on PF.ResourceID = SD.ResourceID
Where PF.DisplayName0 = ‘McAfee VirusScan Enterprise’
Group By SD.Name0, PF.DisplayName0, PF.Version0
Order By SD.Name0
12. Advertisement Status For Multiple Advertisement IDs
Select
OfferName Name,
Recieved,
Failures,
ProgramsStarted Started,
ProgramsFailure Errors,
ProgramsSuccess Success,
OfferID ‘Advertisement ID’
From vOfferStatusSummarizerRoot
Where ScheduleToken = ‘0001128000080008’
And OfferID In (‘XXX12345’, ‘XXX67890’)
Order by OfferName
13. SQL Query To List Roaming Boundary IP Subnets
Select IPSubnet
From RoamingBoundaryIPSubnet
Where SiteCode = ‘XXX’
Order by SiteCode
14. SQL Query To Locate Full User Names From A Specified Collection
Select
SD.Name0 ‘Machine Name’,
SD.User_Name0 ‘Login ID’,
USR.Full_User_Name0 ‘Full Name’
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
Join v_R_User USR on SD.User_Name0 = USR.User_Name0
Where COL.Name = ‘All Systems’
15. SQL Query To Find Machines In A Specified Collection Having A Specified Application Installed
Select
SD.Name0 ‘Machine Name’,
SD.Resource_Domain_OR_Workgr0 ‘Resource Domain’,
SD.User_Name0 ‘Login ID’,
SD.User_Domain0 ‘Account Domain’,
USR.Full_User_Name0 ‘Full Name’,
ARP.DisplayName0 ‘Display Name’
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection CN on FCM.CollectionID = CN.CollectionID
Join v_R_User USR on SD.User_Name0 = USR.User_Name0
Join v_Add_Remove_Programs ARP on SD.ResourceID = ARP.ResourceID
Where CN.Name = ‘All Systems’
And ARP.DisplayName0 Like ‘%VPN%’
16. SQL Query To View Web Reports By Category
Select
Name,
Comment,
ReportId,
Category
From v_Report
Where Category = ‘Asset Intelligence’
Order By Name
--Use this SQL Query to locate all of the available Report Categories for use with the query above:
Select Distinct Category
From V_Report
Order By Category
17. SQL Query To Retrieve A Users Full Name
Select
SD.Name0 ‘Machine Name’,
SD.User_Name0 ‘Logon Name’,
UD.Full_User_Name0 ‘Full Name’
From v_R_System SD
Join v_R_User UD on SD.User_Name0 = UD.User_Name0
Where SD.User_Name0 = ‘LogonId’
18. SQL Queries To Locate Client Resources For A Specified Subnet
Select Distinct
SD.Name0,
IP.Ip_Subnets0
From v_Ra_System_IpSubnets IP
Join v_R_System SD on IP.ResourceID = SD.ResourceID
Where IP.Ip_Subnets0 = ‘192.168.1.0‘
--Wildcard Search:
Select Distinct
SD.Name0,
IP.Ip_Subnets0
From v_Ra_System_IpSubnets IP
Join v_R_System SD on IP.ResourceID = SD.ResourceID
Where IP.Ip_Subnets0 Like ‘192.168.1.%’
Multiple Searches:
Select Distinct
SD.Name0,
IP.Ip_Subnets0
From v_Ra_System_IpSubnets IP
Join v_R_System SD on IP.ResourceID = SD.ResourceID
Where IP.Ip_Subnets0 In
(‘192.168.1.0‘,
‘192.168.2.0‘)
Order by IP.Ip_Subnets0,SD.Name0
19. SQL Query To Count Microsoft Office Versions From Add And Remove Programs
Select
Count(ResourceID) Counts,
DisplayName0,
Publisher0,
Version0
From v_Add_Remove_Programs
Where Publisher0 = ‘Microsoft Corporation’
And DisplayName0 Like ‘Microsoft Office%’
Group By DisplayName0, Publisher0, version0
Order By Counts Desc
20. SQL Query To Count Assigned And Installed Resources By Site Code
Select
SC.SiteCode ‘Site Code’,
(Select Count(ResourceId) From v_RA_System_SMSAssignedSites
Where SMS_Assigned_Sites0 = ‘XXX’) ‘Assigned’,
(Select Count(ResourceID) From v_RA_System_SMSInstalledSites
Where SMS_Installed_Sites0 = ‘XXX’)’Installed’
From v_Site SC
Order by SC.SiteCode
21. SQL Query To Count Computer Types For A Specified Collection Name
Select
CN.Name ‘Collection Name’,
Case SE.ChassisTypes0
When 1 Then ‘Other’
When 2 Then ‘Unknown’
When 3 Then ‘Desktop’
When 4 Then ‘Low Profile Desktop’
When 5 Then ‘PizzaBox’
When 6 Then ‘Mini-Tower’
When 7 Then ‘Tower’
When 8 Then ‘Portable’
When 9 Then ‘Laptop’
When 10 Then ‘Notebook’
When 11 Then ‘Handheld Device’
When 12 Then ‘Docking Station’
When 13 Then ‘All-In-One’
When 14 Then ‘Sub-Notebook’
When 15 Then ‘Space Saving’
When 16 Then ‘Lunch Box’
When 17 Then ‘Main System Chassis’
When 18 Then ‘Expansion Chassis’
When 19 Then ‘Sub-Chassis’
When 20 Then ‘Bus Expansion Chassis’
When 21 Then ‘Peripheral Chassis’
When 22 Then ‘Storage Chassis’
When 23 Then ‘Rack-Mount Chassis’
When 24 Then ‘Sealed PC’
Else ‘Unknown’
End ‘Chassis Type’,
Count(*) ‘Chassis Count’
From v_Collection CN
Join v_FullCollectionMembership CM on CN.CollectionID = CM.CollectionID
Join v_R_System SD on CM.ResourceID = SD.ResourceID
Join v_Gs_System_Enclosure SE on SD.ResourceID = SE.ResourceID
Where CN.Name = ‘All Systems’
Group By CN.Name, SE.ChassisTypes0
Order By CN.Name
22. SQL Query Add And Removed Programs For A Collection
Select
SD.Name0 ‘Machine Name’,
PF.DisplayName0 ‘Display Name’,
PF.ProdID0 ‘Product ID’,
PF.Publisher0 Publisher,
PF.Version0 Version
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
Join v_Add_Remove_Programs PF on SD.ResourceID = PF.ResourceID
Where COL.Name = ‘All Systems’
Group By SD.Name0, COL.CollectionID, COL.Name, PF.DisplayName0,
PF.ProdID0, PF.Publisher0, PF.Version0
Order By SD.Name0
23. SQL Query To Find Machines With Torrent Downloading Clients Installed
Select
SD.Name0,
SF.FileName
From v_Gs_SoftwareFile SF
Join v_R_System SD on SD.ResourceId = SF.ResourceId
Where SF.FileName Like ‘%Azureus%’
Or SF.FileName Like ‘%BitComet%’
Or SF.FileName Like ‘%BitLord%’
Or SF.FileName Like ‘%BitPump%’
Or SF.FileName Like ‘%BitTornado%’
Or SF.FileName Like ‘%BitTorrent%’
Or SF.FileName Like ‘%Shareaza%’
Or SF.FileName Like ‘%Utorrent%’
Order By SD.Name0, SF.FileName
--Below is a SQL query to find applications that are not only used to download Torrent files but other files as well using P2P technology.
--Other File Sharing Applications SQL Query:
Select
SD.Name0,
SF.FileName
From v_Gs_SoftwareFile SF
Join v_R_System SD on SD.ResourceId = SF.ResourceId
Where SF.FileName Like ‘%BearShare%’
Or SF.FileName Like ‘%eDonkey%’
Or SF.FileName Like ‘%Emule%’
Or SF.FileName Like ‘%Kazaa%’
Or SF.FileName Like ‘%LimeWire%’
Or SF.FileName Like ‘%Morpheus%’
Order By SD.Name0, SF.FileName
24. SQL Query To Retrieve Machines In A Specified Collections Last Logged On User Information
Select
C.CollectionName,
M.Name,
S.User_Name0,
U.Full_User_Name0
From Collections C
Join CollectionMembers M on C.SiteID = M.SiteID
Join System_Disc S on M.Name = S.Name0
Join User_DISC U on S.User_Name0 = U.User_Name0
Where CollectionName = ‘All Systems’
25. SQL Query To Retrieve The Machine Name And Asset Tag For A Specified Serial Number
Select
SD.Name0 ‘Machine Name’,
SE.SerialNumber00 ‘Serial Number’,
SE.SMBIOSAssetTag00 ‘Asset Tag’
From System_Disc SD
Join System_Enclosure_Data SE on SE.MachineID = SD.ItemKey
Where SE.SerialNumber00 = ‘MUR0FTIYM’
26. SQL Query Get Users OU Name
Select Distinct
S.Name0 ‘Machine Name’,
S.User_Name0 ‘Logon ID’,
U.Full_User_Name0 ‘Full Name’,
O.User_Ou_Name0 ‘Organizational Unit’
From v_R_System S
Join v_R_User U on S.User_Name0 = U.User_Name0
Full Join v_Ra_User_UserOuName O on U.Unique_User_Name0 = O.User_Ou_Name0
Where O.User_Ou_Name0 Is Not Null
27. Retrieve All Queries Information
Select
QueryKey ‘Query ID’,
Name ‘Query Name’,
Comments ‘Query Comments’,
Architecture ‘Query Object Type’,
CollectionID ‘Limit To Collection ID’
From Queries
28. SQL Query To Find And Count Software File And Product Information For A Specified Executable
Select
SF.FileId ‘File ID’,
SF.ProductId ‘Product ID’,
SF.FileName ‘File Name’,
SF.FileDescription ‘File Description’,
SF.FileSize ‘File Size’,
SF.FileVersion ‘File Version’,
SP.ProductName ‘Product Name’,
SP.ProductVersion ‘Product Version’,
SP.CompanyName ‘Company Name’,
Count(SF.FileName) ‘Installed Count’
From System_DISC SD
Join vSMS_G_System_SoftwareFile SF on SF.ClientId = SD.ItemKey
Join vSMS_G_System_SoftwareProduct SP on SP.ClientId = SD.ItemKey
Where SF.ProductId = SP.ProductId
And SF.FileName = ‘FileName.Exe’
Group by SF.FileId,SF.ProductId,SF.FileName,SF.FileDescription,
SF.FileSize,SF.FileVersion,SP.ProductName,SP.ProductVersion,SP.CompanyName
Order By SF.FileId ASC
29. List Total Number Of Records In The Software File Table
Select Count(*) as ‘Total Number Of Records’
From SoftwareFile
30. SQL Queries To Retrieve Serial Number Information
Select
SD.Name0 ‘Machine Name’,
SB.SerialNumber0 ‘Serial Number’
From v_R_System SD
Join v_Gs_Pc_BIOS SB on SD.ResourceID = SB.ResourceID
Order By SD.Name0
--The Serial Number For A Specified Machine
Select
SD.Name0 ‘Machine Name’,
SB.SerialNumber0 ‘Serial Number’
From v_R_System SD
Join v_Gs_Pc_BIOS SB on SD.ResourceID = SB.ResourceID
Where SD.Name0 = ‘Machine_Name’
Order By SD.Name0
--The Machine Name For A Specified Serial Number
Select
SD.Name0 ‘Machine Name’,
SB.SerialNumber0 ‘Serial Number’
From v_R_System SD
Join v_Gs_Pc_BIOS SB on SD.ResourceID = SB.ResourceID
Where SB.SerialNumber0 = ‘Serial_Number’
Order By SD.Name0
31. SQL Query To Return Add OR Remove Programs Installed On A Specified Machine
Select
P.DisplayName0,
P.ProdId0,
P.Publisher0,
P.Version0
From v_Add_Remove_Programs P
Join v_R_System S on P.ResourceId = S.ResourceId
Where Name0 = ‘MachineName’
<#
.SYNOPSIS
Shows the information about the deployment packages and Software update groups of which asoftware update is a member.
.DESCRIPTION
This script creates a form that requires a article id, a software update group, or a deployment package as input. Based on the input it will show information about
the specific software update, or all the software updates in a software update group, or a deployment package. When used for a software update it will show in which
software update group and deployment package it exists. When used for a software update group it will show in which deployment packages those updates also exists.
When used for a deployment package it will show in which software update groups those updates exist.
.PARAMETER SiteCode
The site code of the primary site.
.PARAMETER SiteServer
The site server of the primary site.
.NOTES
Author: Peter van der Woude - pvanderwoude@hotmail.com
Date published: 19-06-2014
.LINK
http://www.petervanderwoude.nl
.EXAMPLE
Get-SoftwareUpdateInformation.ps1 -SiteCode PCP -SiteServer CLDSRV02
#>
[CmdletBinding()]
param (
[string]$SiteCode,
[string]$SiteServer
)
#Function to load the form
function Load-Form {
$Form1.Controls.Add($Button1)
$Form1.Controls.Add($Button2)
$Form1.Controls.Add($ComboBox1)
$Form1.Controls.Add($DataGridView1)
$Form1.Controls.Add($DataGridView2)
$Form1.Controls.Add($Label1)
$Form1.Controls.Add($Label2)
$Form1.Controls.Add($LinkLabel1)
$Form1.Controls.Add($LinkLabel2)
$Form1.Controls.Add($TextBox1)
$Form1.Controls.Add($GroupBox1)
$Form1.Controls.Add($GroupBox2)
$Form1.Controls.Add($GroupBox3)
$ComboBox1.Items.add("Software Update")
$ComboBox1.Items.add("Deployment Package")
$ComboBox1.Items.add("Software Update Group")
$Form1.ShowDialog()
}
#Function to get the software update group membership of updates
function Get-SoftwareUpdateGroupMembership {
param (
[array]$Updates
)
foreach ($Update in $Updates) {
$UpdateCIID = $Update.CI_ID
$UpdateGroupNames = (Get-WmiObject -Namespace root/SMS/site_$($SiteCode) -ComputerName $SiteServer -Query "SELECT DISTINCT ALI.* from SMS_AuthorizationList ALI `
JOIN SMS_CIRelation CIR on ALI.CI_ID = CIR.fromCIID WHERE CIR.ToCIID='$UpdateCIID'").LocalizedDisplayName
if ($UpdateGroupNames -ne $null) {
foreach ($Name in $UpdateGroupNames) {
$UpdateGroupName = $Name
$DataGridView1.Rows.Add($UpdateGroupName,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
else {
$UpdateGroupName = "<NoSoftwareUpdateGroup>"
$DataGridView1.Rows.Add($UpdateGroupName,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
}
#Function to get the deployment package membership of updates
function Get-DeploymentPackageMembership {
param (
[array]$Updates
)
foreach ($Update in $Updates) {
$UpdateCIID = $Update.CI_ID
$UpdatePackageNames = (Get-WmiObject -Namespace root/SMS/site_$($SiteCode) -ComputerName $SiteServer -Query "SELECT DISTINCT sup.* FROM SMS_SoftwareUpdatesPackage AS sup `
JOIN SMS_PackageToContent AS pc ON sup.PackageID=pc.PackageID JOIN SMS_CIToContent AS cc ON pc.ContentID = cc.ContentID WHERE CC.CI_ID='$UpdateCIID'").Name
if ($UpdatePackageNames -ne $null) {
foreach ($UpdatePackageName in $UpdatePackageNames) {
$DataGridView2.Rows.Add($UpdatePackageName,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
else {
$UpdatePackageName = "<NoDeploymentPackage>"
$DataGridView2.Rows.Add($UpdatePackageName,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
}
#Function to get the members of a software update group
function Get-SoftwareUpdateGroupMembers {
param (
[string]$Group
)
$UpdateGroupCIID = (Get-WmiObject -ComputerName $SiteServer -Namespace root/SMS/site_$($SiteCode) -Class SMS_AuthorizationList -Filter "LocalizedDisplayName='$Group'").CI_ID
$Updates = Get-WmiObject -Namespace root/SMS/site_$($SiteCode) -ComputerName $SiteServer -Query "SELECT upd.* FROM SMS_SoftwareUpdate upd, SMS_CIRelation cr WHERE cr.FromCIID='$UpdateGroupCIID' AND cr.RelationType=1 AND upd.CI_ID=cr.ToCIID"
if ($Updates -ne $null) {
foreach ($Update in $Updates) {
$DataGridView1.Rows.Add($Group,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
else {
}
return $Updates
}
#Function to get the members of a deployment package
function Get-DeploymentPackageMembers {
param (
[string]$Package
)
$UpdatePackageID = (Get-WmiObject -ComputerName $SiteServer -Namespace root/SMS/site_$($SiteCode) -Class SMS_SoftwareUpdatesPackage -Filter "Name='$Package'").PackageID
$Updates = Get-WmiObject -Namespace root/SMS/site_$($SiteCode) -ComputerName $SiteServer -Query "SELECT DISTINCT su.* FROM SMS_SoftwareUpdate AS su JOIN SMS_CIToContent AS cc `
ON SU.CI_ID = CC.CI_ID JOIN SMS_PackageToContent AS pc ON pc.ContentID=cc.ContentID WHERE pc.PackageID='$UpdatePackageID' AND su.IsContentProvisioned=1"
if ($Updates -ne $null) {
foreach ($Update in $Updates) {
$DataGridView2.Rows.Add($Package,$Update.ArticleId,$Update.LocalizedDisplayName) | Out-Null
}
}
else {
}
return $Updates
}
#Button1 OnClick event
$Button1_OnClick= {
$Form1.Close()
}
#Button2 OnClick event
$Button2_OnClick= {
Try {
if ($DataGridView1 -ne $null) {
$DataGridView1.Rows.Clear()
$DataGridView2.Rows.Clear()
$Form1.Refresh()
}
$ErrorProvider1.SetError($TextBox1,"")
$ErrorProvider1.SetError($ComboBox1,"")
if($TextBox1.Text.Length -eq 0 -or $ComboBox1.SelectedItem -eq $null) {
if($TextBox1.Text.Length -eq 0) {
$ErrorProvider1.SetError($TextBox1, "Please provide a valid name")
}
if ($ComboBox1.SelectedItem -eq $null) {
$ErrorProvider1.SetError($ComboBox1, "Please select a valid type")
}
}
else {
$Name = $TextBox1.Text
$Type = $ComboBox1.SelectedItem
if ($Type -eq "Software Update") {
$GroupBox1.Text = "Software Update Group"
$GroupBox2.Text = "Deployment Package"
$TextBox1.Enabled = $false
$ComboBox1.Enabled = $false
$Updates = Get-WmiObject -ComputerName $SiteServer -Namespace root/SMS/site_$($SiteCode) -Class SMS_SoftwareUpdate -Filter "ArticleID='$Name'"
if ($Updates -ne $null) {
Get-SoftwareUpdateGroupMembership $Updates
Get-DeploymentPackageMembership $Updates
}
else {
$ErrorProvider1.SetError($TextBox1, "Please provide a valid article id of a software update")
}
$ComboBox1.Enabled = $true
$TextBox1.Enabled = $true
}
elseif ($Type -eq "Software Update Group") {
$GroupBox1.Text = "Software Update Group: $Name"
$GroupBox2.Text = "Deployment Package"
$TextBox1.Enabled = $false
$ComboBox1.Enabled = $false
$Updates = Get-SoftwareUpdateGroupMembers $Name
if ($Updates -ne $null) {
Get-DeploymentPackageMembership $Updates
}
else {
$ErrorProvider1.SetError($TextBox1, "Please provide a valid name of a software update group")
}
$ComboBox1.Enabled = $true
$TextBox1.Enabled = $true
}
elseif ($Type -eq "Deployment Package") {
$GroupBox1.Text = "Software Update Group"
$GroupBox2.Text = "Deployment Package: $Name"
$TextBox1.Enabled = $false
$ComboBox1.Enabled = $false
$Updates = Get-DeploymentPackageMembers $Name
if ($Updates -ne $null) {
Get-SoftwareUpdateGroupMembership $Updates
}
else {
$ErrorProvider1.SetError($TextBox1, "Please provide a valid name of a deployment package")
}
$ComboBox1.Enabled = $true
$TextBox1.Enabled = $true
}
}
}
Catch {
[Windows.Forms.MessageBox]::Show(“Please provide a valid combination of a name and type”, “Software Update Information”, [Windows.Forms.MessageBoxButtons]::OK, [Windows.Forms.MessageBoxIcon]::Error)
}
}
#LinkLabel1 event
$LinkLabel1_OpenLink= {
[System.Diagnostics.Process]::start($LinkLabel1.text)
}
#LinkLabel2 event
$LinkLabel2_OpenLink= {
[System.Diagnostics.Process]::start("http://twitter.com/pvanderwoude")
}
#Load Assemblies
[Reflection.Assembly]::LoadWithPartialName("System.Drawing") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
#Create ErrorProvider
$ErrorProvider1 = New-Object System.Windows.Forms.ErrorProvider
$ErrorProvider1.BlinkStyle = "NeverBlink"
#Create Form1
$Form1 = New-Object System.Windows.Forms.Form
$Form1.Size = New-Object System.Drawing.Size(700,590)
$Form1.MinimumSize = New-Object System.Drawing.Size(700,590)
$Form1.MaximumSize = New-Object System.Drawing.Size(700,590)
$Form1.SizeGripStyle = "Hide"
$Form1.Text = "Software Update Information"
$Form1.ControlBox = $true
$Form1.TopMost = $true
#Create Button1
$Button1 = New-Object System.Windows.Forms.Button
$Button1.Location = New-Object System.Drawing.Size(510,490)
$Button1.Size = New-Object System.Drawing.Size(150,25)
$Button1.Text = "Close"
$Button1.add_Click($Button1_OnClick)
#Create Button2
$Button2 = New-Object System.Windows.Forms.Button
$Button2.Location = New-Object System.Drawing.Size(510,30)
$Button2.Size = New-Object System.Drawing.Size(150,25)
$Button2.Text = "Execute"
$Button2.add_Click($Button2_OnClick)
#Create ComboBox1
$ComboBox1 = New-Object System.Windows.Forms.ComboBox
$ComboBox1.Location = New-Object System.Drawing.Size(255,30)
$ComboBox1.Size = New-Object System.Drawing.Size(150,25)
$comboBox1.Text = "<Select Type>"
#Create DataGriView1
$DataGridView1 = New-Object System.Windows.Forms.DataGridView
$DataGridView1.Location = New-Object System.Drawing.Size(20,95)
$DataGridView1.Size = New-Object System.Drawing.Size(640,170)
$DataGridView1.ColumnCount = 3
$DataGridView1.ColumnHeadersVisible = $true
$DataGridView1.Columns[0].Name = "Software Update Group"
$DataGridView1.Columns[0].AutoSizeMode = "Fill"
$DataGridView1.Columns[1].Name = "Article ID"
$DataGridView1.Columns[1].AutoSizeMode = "Fill"
$DataGridView1.Columns[2].Name = "Software Update"
$DataGridView1.Columns[2].AutoSizeMode = "Fill"
$DataGridView1.AllowUserToAddRows = $false
$DataGridView1.AllowUserToDeleteRows = $false
$DataGridView1.ReadOnly = $True
$DataGridView1.ColumnHeadersHeightSizeMode = "DisableResizing"
$DataGridView1.RowHeadersWidthSizeMode = "DisableResizing"
$DataGridView1.SelectionMode = "FullRowSelect"
#Create DataGridView2
$DataGridView2 = New-Object System.Windows.Forms.DataGridView
$DataGridView2.Location = New-Object System.Drawing.Size(20,305)
$DataGridView2.Size = New-Object System.Drawing.Size(640,170)
$DataGridView2.ColumnCount = 3
$DataGridView2.ColumnHeadersVisible = $true
$DataGridView2.Columns[0].Name = "Deployment Package"
$DataGridView2.Columns[0].AutoSizeMode = "Fill"
$DataGridView2.Columns[1].Name = "Article ID"
$DataGridView2.Columns[1].AutoSizeMode = "Fill"
$DataGridView2.Columns[2].Name = "Software Update"
$DataGridView2.Columns[2].AutoSizeMode = "Fill"
$DataGridView2.AllowUserToAddRows = $false
$DataGridView2.AllowUserToDeleteRows = $false
$DataGridView2.ReadOnly = $True
$DataGridView2.ColumnHeadersHeightSizeMode = "DisableResizing"
$DataGridView2.RowHeadersWidthSizeMode = "DisableResizing"
$DataGridView2.SelectionMode = "FullRowSelect"
#Create Groupbox1
$GroupBox1 = New-Object System.Windows.Forms.GroupBox
$GroupBox1.Location = New-Object System.Drawing.Size(10,75)
$GroupBox1.Size = New-Object System.Drawing.Size(660,200)
$GroupBox1.Text = "Software Update Group"
#Create GroupBox2
$GroupBox2 = New-Object System.Windows.Forms.GroupBox
$GroupBox2.Location = New-Object System.Drawing.Size(10,285)
$GroupBox2.Size = New-Object System.Drawing.Size(660,200)
$GroupBox2.Text = "Deployment Package"
#Create GroupBox3
$GroupBox3 = New-Object System.Windows.Forms.GroupBox
$GroupBox3.Location = New-Object System.Drawing.Size(10,10)
$GroupBox3.Size = New-Object System.Drawing.Size(660,55)
$GroupBox3.Text = "Required Information"
#Create Label1
$Label1 = New-Object System.Windows.Forms.Label
$Label1.Font = New-Object System.Drawing.Font("Tahoma",8.25,0,3,0)
$Label1.Location = New-Object System.Drawing.Size(20,530)
$Label1.Size = New-Object System.Drawing.Size(48,23)
$Label1.Text = "My blog:"
#Create Label2
$Label2 = New-Object System.Windows.Forms.Label
$Label2.Font = New-Object System.Drawing.Font("Tahoma",8.25,0,3,0)
$Label2.Location = New-Object System.Drawing.Size(460,530)
$Label2.Size = New-Object System.Drawing.Size(111,23)
$Label2.Text = "Follow me on twitter:"
#Create LinkLabel1
$LinkLabel1 = New-Object System.Windows.Forms.LinkLabel
$LinkLabel1.Font = New-Object System.Drawing.Font("Tahoma",8.25,0,3,0)
$LinkLabel1.Location = New-Object System.Drawing.Size(68,530)
$LinkLabel1.Size = New-Object System.Drawing.Size(142,23)
$LinkLabel1.Text = "www.petervanderwoude.nl"
$LinkLabel1.add_click($LinkLabel1_OpenLink)
#Create LinkLabel2
$LinkLabel2 = New-Object System.Windows.Forms.LinkLabel
$LinkLabel2.Font = New-Object System.Drawing.Font("Tahoma",8.25,0,3,0)
$LinkLabel2.Location = New-Object System.Drawing.Size(569,530)
$LinkLabel2.Size = New-Object System.Drawing.Size(90,23)
$linkLabel2.Text = "@pvanderwoude"
$LinkLabel2.add_click($LinkLabel2_OpenLink)
#Create TextBox1
$TextBox1 = New-Object System.Windows.Forms.TextBox
$TextBox1.Location = New-Object System.Drawing.Size(20,30)
$TextBox1.Size = New-Object System.Drawing.Size(150,25)
$TextBox1.Text = "<Provide Name>"
#Load form
Load-Form#############################################################################
# Author : Benoit Lecours
# Website : www.SystemCenterDudes.com
# Twitter : @scdudes
#
# Version : 1.0
# Created : 2018/06/05
# Modified :
#
# Purpose : This script delete deploymentsolder than the specified date
#
#############################################################################
#Load Configuration Manager PowerShell Module
Import-module ($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager.psd1')
#Get SiteCode
$SiteCode = Get-PSDrive -PSProvider CMSITE
Set-location $SiteCode":"
Clear-Host
Write-host "--------------- SYSTEM CENTER DUDES - DELETE OLD DEPLOYMENTS ----------------`n"
Write-host "This script deletes SCCM deployments older than the number of days specified.`n"
Write-Host "The script is based on Creation Date. The user will be prompted before each deletion.`n" -ForegroundColor Yellow
$SCCMServer = Read-Host "Enter your SCCM server Name"
$UserDate = Read-Host "Delete deployments older than how many days ? (ex: 365 = 1 year)"
$DeleteDate = (Get-Date).AddDays(-$UserDate)
Write-Host ""
Write-host "-------------------------------------------------------------------------------------"
Write-Host "Building Deployment List older than " -NoNewline; Write-Host $DeleteDate.ToShortDateString() -ForegroundColor Red -NoNewline; Write-Host " This may take a couple of minutes...";
Write-host "-------------------------------------------------------------------------------------`n"
$DeploymentList = Get-CMDeployment | Where-Object {$_.CreationTime -le $Deletedate}
$DeploymentNumber=0
Write-Host ("Found " + $DeploymentList.Count + " deployments older than specified date`n") -ForegroundColor Yellow
foreach ($Deployment in $DeploymentList)
{
$DeploymentID = $Deployment.DeploymentID
$DeploymentName = $Deployment.ApplicationName
$DeploymentNumber++
# User Prompt
Write-Host ""
Write-host "--------------- " -NoNewline; Write-host "DEPLOYMENT #"$DeploymentNumber -ForegroundColor Red -NoNewline; Write-host " ---------------------------------------------------------"
Write-Host ("Deployment Name: " + $DeploymentName)
Write-Host ("Targetted Collection: " + $Deployment.CollectionName)
Write-Host ("Deployment ID: " + $Deployment.DeploymentID)
Write-Host ("Creation Date: " + $Deployment.CreationTime)
Write-Host ("Deployment Time: " + $Deployment.DeploymentTime)
Write-Host ("Enforcement Deadline: " + $Deployment.EnforcementDeadline)
Write-Host ("Feature Type: " + $Deployment.FeatureType)
Write-host "----------------------------------------------------------------------------------------`n"
# User Confirmation
If ((Read-Host -Prompt "Type `"Y`" to delete the deployment, any other key to skip") -ieq "Y")
{
Try
{
#Delete the deployment based on the Deployment Feature Type
Switch ($Deployment.FeatureType)
{
#Application
1{Remove-CMDeployment -DeploymentId $DeploymentID -ApplicationName $DeploymentName -ErrorAction Stop -Force
Write-Host "Sucessfully Deleted Application Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
# Package/Program
2{
$AdvertFilter = "AdvertisementID='$DeploymentID'"
#gwmi sms_advertisement -Namespace $SiteNamespace -ComputerName $SiteServer -filter $advertFilter | % {$_.Delete()}
Get-WmiObject -Namespace "root\sms\site_$sitecode" -ComputerName $SCCMServer -class SMS_Advertisement -filter $advertFilter -ErrorAction stop | % {$_.Delete()}
Write-Host "Sucessfully Deleted Program Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
#Software Update
5{
$AdvertFilter = "AssignmentUniqueID='$DeploymentID'"
#gwmi SMS_UpdatesAssignment -Namespace $SiteNamespace -ComputerName $SiteServer -filter $advertFilter | % {$_.Delete()}
Get-WmiObject -Namespace "root\sms\site_$sitecode" -ComputerName $SCCMServer -class SMS_UpdatesAssignment -filter $advertFilter -ErrorAction stop | % {$_.Delete()}
Write-Host "Sucessfully Deleted Software Update Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
#Baseline
6{
$AdvertFilter = "AssignmentUniqueID='$DeploymentID'"
#gwmi sms_baselineassignment -Namespace $SiteNamespace -ComputerName $SiteServer -filter $advertFilter | % {$_.Delete()}
Get-WmiObject -Namespace "root\sms\site_$sitecode" -ComputerName $SCCMServer -class SMS_baselineassignment -filter $advertFilter -ErrorAction stop | % {$_.Delete()}
Write-Host "Sucessfully Deleted Baseline Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
#Task Sequence
7{
$AdvertFilter = "AdvertisementID='$DeploymentID'"
#gwmi sms_advertisement -Namespace $SiteNamespace -ComputerName $SiteServer -filter $advertFilter | % {$_.Delete()}
Get-WmiObject -Namespace "root\sms\site_$sitecode" -ComputerName $SCCMServer -class SMS_Advertisement -filter $advertFilter -ErrorAction stop | % {$_.Delete()}
Write-Host "Sucessfully Deleted Task Sequence Deployment #"$DeploymentNumber -ForegroundColor Green -BackgroundColor Black}
Default {
Write-Host ("Feature Type not supported" + $Deployment.FeatureType)}
}
}
Catch{Write-Host "Can't delete deployment #"$DeploymentNumber -ForegroundColor Red -BackgroundColor Black }
}
}
#############################################################################
# Author : Benoit Lecours
# Website : www.SystemCenterDudes.com
# Twitter : @scdudes
#
# Version : 1.1
# Created : 2017/04/05
# Modified : 2017/08/31
#
# Purpose : This script delete collections without members and deployments
#
#############################################################################
#Load Configuration Manager PowerShell Module
Import-module ($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager.psd1')
#Get SiteCode
$SiteCode = Get-PSDrive -PSProvider CMSITE
Set-location $SiteCode":"
Clear-Host
Write-host "This script delete device collections without members and deployments. You will be prompted before each deletion.`n"
Write-host "Built-in collections and users collections are excluded`n"
Write-host "------------------------------------------------------------------------"
Write-Host ("Building Devices Collections List. This may take a couple of minutes...")
Write-host "------------------------------------------------------------------------`n"
$CollectionList = Get-CmCollection | Where-Object {$_.CollectionID -notlike 'SMS*' -and $_.CollectionType -eq '2' -and $_.MemberCount -eq 0} | Select-Object -Property Name,MemberCount,CollectionID,IsReferenceCollection
#$DeploymentList = Get-CMDeployment | Select-Object -Property CollectionID
Write-Host ("Found " + $CollectionList.Count + " collections without members (MemberCount = 0) `n")
Write-Host ("Analyzing list to find collection without deployments... `n")
foreach ($Collection in $CollectionList)
{
$NumCollectionMembers = $Collection.MemberCount
$CollectionID = $Collection.CollectionID
$GetDeployment = Get-CMDeployment | Where-Object {$_.CollectionID -eq $Collection.CollectionID}
# Delete collection if there's no members and no deployment on the collection
If ($GetDeployment -eq $null) #$NumCollectionMembers -eq 0 -and
{
# User Prompt
Write-Host ("Collection " + $Collection.Name +" (" + $Collection.CollectionID + ")" + " has no members and deployments")
# User Confirmation
If ((Read-Host -Prompt "Type `"Y`" to delete the collection, any other key to skip") -ieq "Y")
{
#Check if Reference collection
Try
{
#Delete the collection object
Remove-CMCollection -Id $CollectionID -Force
Write-Host -ForegroundColor Green ("Collection: " + $Collection.Name + " Deleted")
}
Catch{Write-Host -ForegroundColor Red ("Can't delete " + $Collection.Name + " collection. Possible cause : There's referenced collection or a custom security scope assigned to the collection.")}
}
}
}<#############################################################################
Author : Benoit Lecours
Website : www.SystemCenterDudes.com
Twitter : @scdudes
Version : 1.0
Created : 2019/12/10
The purposes of this script:
1. Create SCCM device collections based on Active Directory Organisational Unit
2. Define the Refresh Schedule of collection to 7 days
3. Create Query Rule for collection membership
4. Move created collection to custom folder
5. Updates collection membership at once.
##############################################################################>
# Import PS modules
Import-Module ActiveDirectory
Import-Module 'D:\Program Files\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1'
# SCCM Site
$Site = (Get-PSDRive -PSProvider CMSite).Name
CD ${Site}:
# Defining refresh interval for collection - 7 days
$Schedule = New-CMSchedule –RecurInterval Days –RecurCount 7
##Get User Inputs for Base OU
$Users = Get-ADOrganizationalUnit -Filter * -Properties DistinguishedName,CanonicalName |Select-Object DistinguishedName,CanonicalName
Clear-Host
Write-Host "Here's the list of all OU in your Organisation".ToUpper()
Write-Host ""
For ($i=0; $i -lt $Users.Count; $i++) {
Write-Host "$($i+1): $($Users[$i].CanonicalName)"
}
Write-Host ""
[int]$number = Read-Host "Select the number corresponding to the desired top-most OU".ToUpper()
Write-Host ""
Write-Host -ForegroundColor Green "You've selected $($users[$number-1].CanonicalName). The script will create 1 collection for each OU under the selected OU.:".ToUpper()
$SearchBase = $($Users[$number-1]).DistinguishedName
#Get User Input for folder
Write-Host ""
$folderName = Read-Host "Enter the desired folder name. The folder will be created under the Device Collection Node and all collections will be moved to the folder"
New-Item -Name $folderName -Path "${Site}:\DeviceCollection"
$TargetFolder = "${Site}:\DeviceCollection\$folderName"
#Getting Canonical name and GUID from AD OUs based on user input
$ADOUs = Get-ADOrganizationalUnit -SearchBase "$SearchBase" -Filter * -Properties Canonicalname |Select-Object DistinguishedName,CanonicalName
#Create Collections
foreach ($OU in $ADOUs)
{
$O_Name = $OU.CanonicalName
$O_GUID = $OU.ObjectGUID
Try
{
New-CMDeviceCollection -LimitingCollectionName 'All Systems' -Name $O_Name -RefreshSchedule $Schedule -Comment $O_GUID | Out-Null
Write-host *** Collection $O_Name created ***
}
Catch
{
Write-host -ForegroundColor Red ("There was an error creating the: " + $O_Name + " collection. Possible cause is that there's already a collection with that name.")
}
# Creating Query Membership rule
Add-CMDeviceCollectionQueryMembershipRule -CollectionName $O_Name -QueryExpression "select * from SMS_R_System where SMS_R_System.SystemOUName = '$O_Name'" -RuleName "OU Membership" | Out-Null
# Getting collection ID
$ColID = (Get-CMDeviceCollection -Name $O_Name).collectionid
# Moving collection to folder
Try
{
Move-CMObject -FolderPath $TargetFolder -ObjectId "$ColID"
Write-host *** Collection $O_Name moved to the $folderName folder ***
}
Catch
{
Write-host -ForegroundColor Red ("There was an error moving the: " + $O_Name + " collection.")
}
# Updating collection membership
Invoke-CMDeviceCollectionUpdate -Name $O_Name
}
Write-Host ""
Write-host *** SCRIPT COMPLETED ***DECLARE @Today AS DATE SET @Today = GETDATE() DECLARE @BackInTime AS DATE SET @BackInTime = DATEADD(DAY, -30, @Today ) SELECT DISTINCT SYS.ResourceID, SYS.Name0 'Name', SYS.AD_Site_Name0 'ADSite', CS.UserName0 'User Name', CASE WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A' ELSE U.TopConsoleUser0 END AS TopUser, REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win') OS, REPLACE (OS.CSDVersion0,'Service Pack','SP') 'Service Pack', CS.Manufacturer0 'Manufacturer', CS.Model0 Model, BIOS.SerialNumber0 'Serial Number', CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate, BIOS.SMBIOSBIOSVersion0 AS BIOSVersion, (SELECT CONVERT(DATE,SYS.Creation_Date0)) 'Managed Date', SUM(ISNULL(RAM.Capacity0,0)) 'Memory (MB)', COUNT(RAM.ResourceID) '# Memory Slots', REPLACE (cs.SystemType0,'-based PC','') 'Type', SUM(D.Size0) / 1024 AS 'Disk Size GB', CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS 'Last Reboot Date/Time', CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS 'Install Date', CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS 'Last Hardware Inventory', CONVERT(VARCHAR(26), CH.LastOnline, 101) AS 'Last Seen Online', SYS.Client_Version0 as 'SCCM Agent Version', CPU.Manufacturer AS 'CPU Man.', CPU.[Number of CPUs] AS '# of CPUs', CPU.[Number of Cores per CPU] AS '# of Cores per CPU', CPU.[Logical CPU Count] AS 'Logical CPU Count', US.ScanTime AS ' Windows Updates Scan Time' , US.LastErrorCode AS ' Windows Updates Last Error Code' , US.LastScanPackageLocation AS ' Windows Updates Last Package Location' , CASE SE.ChassisTypes0 WHEN '1' THEN 'Other' WHEN '2' THEN 'Unknown' WHEN '3' THEN 'Desktop' WHEN '4' THEN 'Low Profile Desktop' WHEN '5' THEN 'Pizza Box' WHEN '6' THEN 'Mini Tower' WHEN '7' THEN 'Tower' WHEN '8' THEN 'Portable' WHEN '9' THEN 'Laptop' WHEN '10' THEN 'Notebook' WHEN '11' THEN 'Hand Held' WHEN '12' THEN 'Docking Station' WHEN '13' THEN 'All in One' WHEN '14' THEN 'Sub Notebook' WHEN '15' THEN 'Space-Saving' WHEN '16' THEN 'Lunch Box' WHEN '17' THEN 'Main System Chassis' WHEN '18' THEN 'Expansion Chassis' WHEN '19' THEN 'SubChassis' WHEN '20' THEN 'Bus Expansion Chassis' WHEN '21' THEN 'Peripheral Chassis' WHEN '22' THEN 'Storage Chassis' WHEN '23' THEN 'Rack Mount Chassis' WHEN '24' THEN 'Sealed-Case PC' ELSE 'Undefinded' END AS 'PC Type' FROM v_R_System SYS INNER JOIN ( SELECT Name0, MAX(Creation_Date0) AS Creation_Date FROM dbo.v_R_System GROUP BY Name0 ) AS CleanSystem ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date LEFT JOIN v_GS_COMPUTER_SYSTEM CS ON SYS.ResourceID=cs.ResourceID LEFT JOIN v_GS_PC_BIOS BIOS ON SYS.ResourceID=bios.ResourceID LEFT JOIN ( SELECT A.ResourceID, MAX(A.[InstallDate0]) AS [InstallDate0] FROM v_GS_OPERATING_SYSTEM A GROUP BY A.ResourceID ) AS X ON SYS.ResourceID = X.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM OS ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0 LEFT JOIN v_GS_PHYSICAL_MEMORY RAM ON SYS.ResourceID=ram.ResourceID LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3 LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U ON SYS.ResourceID = U.ResourceID LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID LEFT JOIN v_CH_ClientSummary CH ON SYS.ResourceID = CH.ResourceID LEFT JOIN ( SELECT DISTINCT(CPU.SystemName0) AS [System Name], CPU.Manufacturer0 AS Manufacturer, CPU.ResourceID, CPU.Name0 AS Name, COUNT(CPU.ResourceID) AS [Number of CPUs], CPU.NumberOfCores0 AS [Number of Cores per CPU], CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count] FROM [dbo].[v_GS_PROCESSOR] CPU GROUP BY CPU.SystemName0, CPU.Manufacturer0, CPU.Name0, CPU.NumberOfCores0, CPU.NumberOfLogicalProcessors0, CPU.ResourceID ) CPU ON CPU.ResourceID = SYS.ResourceID LEFT JOIN v_UpdateScanStatus US ON US.ResourceID = SYS.ResourceID WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND CH.LastOnline BETWEEN @BackInTime AND GETDATE() GROUP BY SYS.Creation_Date0 , SYS.Name0 , SYS.ResourceID , SYS.AD_Site_Name0 , CS.UserName0 , REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win'), REPLACE (OS.CSDVersion0,'Service Pack','SP'), CS.Manufacturer0 , CS.Model0 , BIOS.SerialNumber0 , REPLACE (cs.SystemType0,'-based PC','') , CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) , CONVERT(VARCHAR(26), OS.InstallDate0, 101) , CONVERT(VARCHAR(26), WS.LastHWScan, 101), CASE WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A' ELSE U.TopConsoleUser0 END, CPU.Manufacturer, CPU.[Number of CPUs] , CPU.[Number of Cores per CPU], CPU.[Logical CPU Count], US.ScanTime , US.LastErrorCode , US.LastScanPackageLocation , CASE SE.ChassisTypes0 WHEN '1' THEN 'Other' WHEN '2' THEN 'Unknown' WHEN '3' THEN 'Desktop' WHEN '4' THEN 'Low Profile Desktop' WHEN '5' THEN 'Pizza Box' WHEN '6' THEN 'Mini Tower' WHEN '7' THEN 'Tower' WHEN '8' THEN 'Portable' WHEN '9' THEN 'Laptop' WHEN '10' THEN 'Notebook' WHEN '11' THEN 'Hand Held' WHEN '12' THEN 'Docking Station' WHEN '13' THEN 'All in One' WHEN '14' THEN 'Sub Notebook' WHEN '15' THEN 'Space-Saving' WHEN '16' THEN 'Lunch Box' WHEN '17' THEN 'Main System Chassis' WHEN '18' THEN 'Expansion Chassis' WHEN '19' THEN 'SubChassis' WHEN '20' THEN 'Bus Expansion Chassis' WHEN '21' THEN 'Peripheral Chassis' WHEN '22' THEN 'Storage Chassis' WHEN '23' THEN 'Rack Mount Chassis' WHEN '24' THEN 'Sealed-Case PC' ELSE 'Undefinded' END , CONVERT (DATE,BIOS.ReleaseDate0) , BIOS.SMBIOSBIOSVersion0 , SYS.Client_Version0 , CONVERT(VARCHAR(26) ,CH.LastOnline, 101) ORDER BY SYS.Name0
Hardware Inventory SQL Report Filter by Collection ID
DECLARE @Today AS DATE DECLARE @CollectionID nvarchar(8) SET @Today = GETDATE() DECLARE @BackInTime AS DATE SET @BackInTime = DATEADD(DAY, -30, @Today ) SET @CollectionID = 'CM100017' SELECT DISTINCT dfc.CollectionID, SYS.ResourceID, SYS.Name0 'Name', SYS.AD_Site_Name0 'ADSite', CS.UserName0 'User Name', CASE WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A' ELSE U.TopConsoleUser0 END AS TopUser, REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win') OS, REPLACE (OS.CSDVersion0,'Service Pack','SP') 'Service Pack', CS.Manufacturer0 'Manufacturer', CS.Model0 Model, BIOS.SerialNumber0 'Serial Number', CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate, BIOS.SMBIOSBIOSVersion0 AS BIOSVersion, (SELECT CONVERT(DATE,SYS.Creation_Date0)) 'Managed Date', SUM(ISNULL(RAM.Capacity0,0)) 'Memory (MB)', COUNT(RAM.ResourceID) '# Memory Slots', REPLACE (cs.SystemType0,'-based PC','') 'Type', SUM(D.Size0) / 1024 AS 'Disk Size GB', CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS 'Last Reboot Date/Time', CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS 'Install Date', CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS 'Last Hardware Inventory', CONVERT(VARCHAR(26), CH.LastOnline, 101) AS 'Last Seen Online', SYS.Client_Version0 as 'SCCM Agent Version', CPU.Manufacturer AS 'CPU Man.', CPU.[Number of CPUs] AS '# of CPUs', CPU.[Number of Cores per CPU] AS '# of Cores per CPU', CPU.[Logical CPU Count] AS 'Logical CPU Count', US.ScanTime AS ' Windows Updates Scan Time' , US.LastErrorCode AS ' Windows Updates Last Error Code' , US.LastScanPackageLocation AS ' Windows Updates Last Package Location' , CASE SE.ChassisTypes0 WHEN '1' THEN 'Other' WHEN '2' THEN 'Unknown' WHEN '3' THEN 'Desktop' WHEN '4' THEN 'Low Profile Desktop' WHEN '5' THEN 'Pizza Box' WHEN '6' THEN 'Mini Tower' WHEN '7' THEN 'Tower' WHEN '8' THEN 'Portable' WHEN '9' THEN 'Laptop' WHEN '10' THEN 'Notebook' WHEN '11' THEN 'Hand Held' WHEN '12' THEN 'Docking Station' WHEN '13' THEN 'All in One' WHEN '14' THEN 'Sub Notebook' WHEN '15' THEN 'Space-Saving' WHEN '16' THEN 'Lunch Box' WHEN '17' THEN 'Main System Chassis' WHEN '18' THEN 'Expansion Chassis' WHEN '19' THEN 'SubChassis' WHEN '20' THEN 'Bus Expansion Chassis' WHEN '21' THEN 'Peripheral Chassis' WHEN '22' THEN 'Storage Chassis' WHEN '23' THEN 'Rack Mount Chassis' WHEN '24' THEN 'Sealed-Case PC' ELSE 'Undefinded' END AS 'PC Type' FROM v_R_System SYS INNER JOIN ( SELECT Name0, MAX(Creation_Date0) AS Creation_Date FROM dbo.v_R_System GROUP BY Name0 ) AS CleanSystem ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date LEFT JOIN v_GS_COMPUTER_SYSTEM CS ON SYS.ResourceID=cs.ResourceID LEFT JOIN v_GS_PC_BIOS BIOS ON SYS.ResourceID=bios.ResourceID LEFT JOIN ( SELECT A.ResourceID, MAX(A.[InstallDate0]) AS [InstallDate0] FROM v_GS_OPERATING_SYSTEM A GROUP BY A.ResourceID ) AS X ON SYS.ResourceID = X.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM OS ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0 LEFT JOIN v_GS_PHYSICAL_MEMORY RAM ON SYS.ResourceID=ram.ResourceID LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3 LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U ON SYS.ResourceID = U.ResourceID LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID LEFT JOIN v_CH_ClientSummary CH ON SYS.ResourceID = CH.ResourceID LEFT JOIN ( SELECT DISTINCT(CPU.SystemName0) AS [System Name], CPU.Manufacturer0 AS Manufacturer, CPU.ResourceID, CPU.Name0 AS Name, COUNT(CPU.ResourceID) AS [Number of CPUs], CPU.NumberOfCores0 AS [Number of Cores per CPU], CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count] FROM [dbo].[v_GS_PROCESSOR] CPU GROUP BY CPU.SystemName0, CPU.Manufacturer0, CPU.Name0, CPU.NumberOfCores0, CPU.NumberOfLogicalProcessors0, CPU.ResourceID ) CPU ON CPU.ResourceID = SYS.ResourceID LEFT JOIN v_UpdateScanStatus US ON US.ResourceID = SYS.ResourceID inner join dbo.v_FullCollectionMembership dfc on dfc.ResourceID = sys.ResourceID WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND dfc.CollectionID = @CollectionID and CH.LastOnline BETWEEN @BackInTime AND GETDATE() GROUP BY dfc.CollectionID, SYS.Creation_Date0 , SYS.Name0 , SYS.ResourceID , SYS.AD_Site_Name0 , CS.UserName0 , REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win'), REPLACE (OS.CSDVersion0,'Service Pack','SP'), CS.Manufacturer0 , CS.Model0 , BIOS.SerialNumber0 , REPLACE (cs.SystemType0,'-based PC','') , CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) , CONVERT(VARCHAR(26), OS.InstallDate0, 101) , CONVERT(VARCHAR(26), WS.LastHWScan, 101), CASE WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A' ELSE U.TopConsoleUser0 END, CPU.Manufacturer, CPU.[Number of CPUs] , CPU.[Number of Cores per CPU], CPU.[Logical CPU Count], US.ScanTime , US.LastErrorCode , US.LastScanPackageLocation , CASE SE.ChassisTypes0 WHEN '1' THEN 'Other' WHEN '2' THEN 'Unknown' WHEN '3' THEN 'Desktop' WHEN '4' THEN 'Low Profile Desktop' WHEN '5' THEN 'Pizza Box' WHEN '6' THEN 'Mini Tower' WHEN '7' THEN 'Tower' WHEN '8' THEN 'Portable' WHEN '9' THEN 'Laptop' WHEN '10' THEN 'Notebook' WHEN '11' THEN 'Hand Held' WHEN '12' THEN 'Docking Station' WHEN '13' THEN 'All in One' WHEN '14' THEN 'Sub Notebook' WHEN '15' THEN 'Space-Saving' WHEN '16' THEN 'Lunch Box' WHEN '17' THEN 'Main System Chassis' WHEN '18' THEN 'Expansion Chassis' WHEN '19' THEN 'SubChassis' WHEN '20' THEN 'Bus Expansion Chassis' WHEN '21' THEN 'Peripheral Chassis' WHEN '22' THEN 'Storage Chassis' WHEN '23' THEN 'Rack Mount Chassis' WHEN '24' THEN 'Sealed-Case PC' ELSE 'Undefinded' END , CONVERT (DATE,BIOS.ReleaseDate0) , BIOS.SMBIOSBIOSVersion0 , SYS.Client_Version0 , CONVERT(VARCHAR(26) ,CH.LastOnline, 101) ORDER BY SYS.Name0
Configure Collection Updates in MEMCM (SCCM) using a Powershell Script
If you want to configure device collections in collections.txt to only update on a schedule, you run this command:
.\Set-CollectionUpdates.ps1 -CollectionType Device -RefreshType 2
Source code:
<#
.PARAMETER COLLECTIONTYPE
Define the type of collections in the collections.txt file. Valid inputs are Device and User
.PARAMETER REFRESHTYPE
Define the Refresh type for the collection. Valid inputs are:
# The following refresh types exist for ConfigMgr collections
# 6 = Incremental and Scheduled Updates
# 4 = Incremental Updates Only
# 2 = Scheduled Updates only
# 1 = Manual Update only
.DESCRIPTION
Sets the collection refresh type for all collections defined in a text file.
.NOTES
Author: Daniel Classon
Version: 1.0
Date: 2015/05/18
.EXAMPLE
.\Set-Collection_Updates.ps1 -CollectionType Device -RefreshType 2
Will set the collections in collections.txt to "Scheduled Updates only".
.DISCLAIMER
All scripts and other powershell references are offered AS IS with no warranty.
These script and functions are tested in my environment and it is recommended that you test these scripts in a test environment before using in your production environment.
#>
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True, Helpmessage="Enter the Collection Type")]
[string]$CollectionType,
[Parameter(Mandatory=$True, Helpmessage="Enter the Refresh Type")]
[string]$RefreshType
)
Begin {
#Checks if the user is in the administrator group. Warns and stops if the user is not.
if (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) {
Write-Warning "You are not running this as local administrator. Run it again in an elevated prompt." ; break
}
try {
Import-Module (Join-Path $(Split-Path $env:SMS_ADMIN_UI_PATH) ConfigurationManager.psd1)
}
catch [System.UnauthorizedAccessException] {
Write-Warning -Message "Access denied" ; break
}
catch [System.Exception] {
Write-Warning "Unable to load the Configuration Manager Powershell module from $env:SMS_ADMIN_UI_PATH" ; break
}
}
Process {
try {
$CollectionIDs = Get-Content "collections.txt" -ErrorAction Stop
}
catch [System.Exception] {
Write-Warning "Unable to find collections.txt. Make sure to place it in the script directory."
}
$SiteCode = Get-PSDrive -PSProvider CMSITE
Set-Location -Path "$($SiteCode.Name):\"
$Count = 0
Foreach ($CollectionID in $CollectionIDs) {
$Count++
if ($CollectionType -eq "Device") {
$Collection = Get-CMDeviceCollection -CollectionId $CollectionID
$Collection.RefreshType = $RefreshType
$Collection.Put()
if ($RefreshType -eq 1) {
Write-Progress -Activity "Enabling Manual Update only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
Write-Host "Enabling Manual Update only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
}
elseif ($RefreshType -eq 2) {
Write-Progress -Activity "Enabling Scheduled Updates only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
Write-Host "Enabling Scheduled Updates only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
}
elseif ($RefreshType -eq 4) {
Write-Progress -Activity "Enabling Incremental Updates only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
Write-Host "Enabling Incremental Updates Only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
}
elseif ($RefreshType -eq 6) {
Write-Progress -Activity "Enabling Incremental and Scheduled Updates on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
Write-Host "Enabling Incremental and Scheduled Updates on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
}
}
if ($CollectionType -eq "User") {
$Collection = Get-CMUserCollection -CollectionId $CollectionID
$Collection.RefreshType = $RefreshType
$Collection.Put()
if ($RefreshType -eq 1) {
Write-Progress -Activity "Enabling Manual Update only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
Write-Host "Enabling Manual Update only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
}
elseif ($RefreshType -eq 2) {
Write-Progress -Activity "Enabling Scheduled Updates only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
Write-Host "Enabling Scheduled Updates only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
}
elseif ($RefreshType -eq 4) {
Write-Progress -Activity "Enabling Incremental Updates only on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
Write-Host "Enabling Incremental Updates Only on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
}
elseif ($RefreshType -eq 6) {
Write-Progress -Activity "Enabling Incremental and Scheduled Updates on $CollectionType Collection ID: $CollectionID" -Status "Modified $Count of $($CollectionIDs.Count) collections" -PercentComplete ($Count / $CollectionIDs.count * 100)
Write-Host "Enabling Incremental and Scheduled Updates on: " $Collection.CollectionID "`t" $Collection.Name -ForegroundColor Yellow
}
}
}
}
End {
Write-Host "$Count $CollectionType collections were updated"
Set-Location -Path $env:SystemDrive
}Use the following example queries and replace the example collection ID XYZ0003F with the ID of the collection you want to include or exclude.
Include:
Select * from SMS_R_System where SMS_R_System.ResourceId in (select ResourceID from SMS_CM_RES_COLL_XYZ0003F)
Exclude:
Select * from SMS_R_System where SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XYZ0003F)
Powershell detection method example
Modify the Powershell script
Modify the below script to suit your needs. The example below will look at strings in 3 different files. You will need to make the following modifications:
- String variables
- String<number>Location variables
#Define strings and their location. Also include the filename.
$String1 = ""
$String1Location = ""
$String2 = ""
$String2Location = ""
$String3 = ""
$String3Location = ""
# Detect presence of String1 in String1 Location
try {
$String1Exists = Get-Content $String1Location -ErrorAction Stop
}
catch {
}
# Detect presence of String2 in String2 Location
try {
$String2Exists = Get-Content $String2Location -ErrorAction Stop
}
catch {
}
# Detect presence of String3 in String3 Location
try {
$String3Exists = Get-Content $String3Location -ErrorAction Stop
}
catch {
}
if (($String1Exists -match $String1) -and ($String2Exists -match $String2) -and ($String3Exists -match $String3)) {
Write-Host "Installed"
}
else {
}gpresult /v >> c:\temp\gpresult.txt
<#
.SYNOPSIS
This script generate the status messages for sms provider, site server and client.
Author: Eswar Koneti
Date:15-Nov-2019
#>
<#param(
[Parameter(Mandatory=$True)]
[string]$stringPathToDLL,
[Parameter(Mandatory=$True)]
[string]$stringOutputCSV
) #>
$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath
$date = (get-date -f dd-MM-yyyy-HHmmss)
$Dllfiles=Get-ChildItem -Path "$dir\*.dll" -Recurse
foreach ($file in $Dllfiles)
{
$dir=$File.Directory.FullName
$dll=$file.name
$strOutputCSV = $dir+"\"+$dll+".csv"
$stringPathToDLL = $dir+"\"+$dll
#Start Invoke Code
$sigFormatMessage = @'
[DllImport("kernel32.dll")]
public static extern uint FormatMessage(uint flags, IntPtr source, uint messageId, uint langId, StringBuilder buffer, uint size, string[] arguments);
'@
$sigGetModuleHandle = @'
[DllImport("kernel32.dll")]
public static extern IntPtr GetModuleHandle(string lpModuleName);
'@
$sigLoadLibrary = @'
[DllImport("kernel32.dll")]
public static extern IntPtr LoadLibrary(string lpFileName);
'@
$Win32FormatMessage = Add-Type -MemberDefinition $sigFormatMessage -name "Win32FormatMessage" -namespace Win32Functions -PassThru -Using System.Text
$Win32GetModuleHandle = Add-Type -MemberDefinition $sigGetModuleHandle -name "Win32GetModuleHandle" -namespace Win32Functions -PassThru -Using System.Text
$Win32LoadLibrary = Add-Type -MemberDefinition $sigLoadLibrary -name "Win32LoadLibrary" -namespace Win32Functions -PassThru -Using System.Text
#End Invoke Code
$sizeOfBuffer = [int]16384
$stringArrayInput = {"%1","%2","%3","%4","%5", "%6", "%7", "%8", "%9"}
$flags = 0x00000800 -bor 0x00000200
$stringOutput = New-Object System.Text.StringBuilder $sizeOfBuffer
$colMessages = @()
#Load Status Message Lookup DLL into memory and get pointer to memory
$ptrFoo = $Win32LoadLibrary::LoadLibrary($stringPathToDLL.ToString())
$ptrModule = $Win32GetModuleHandle::GetModuleHandle($stringPathToDLL.ToString())
#Find Informational Status Messages
for ($iMessageID = 1; $iMessageID -ile 99999; $iMessageID++)
{
$result = $Win32FormatMessage::FormatMessage($flags, $ptrModule, 1073741824 -bor $iMessageID, 0, $stringOutput, $sizeOfBuffer, $stringArrayInput)
if( $result -gt 0)
{
$objMessage = New-Object System.Object
$objMessage | Add-Member -type NoteProperty -name MessageID -value $iMessageID
$objMessage | Add-Member -type NoteProperty -name MessageString -value $stringOutput.ToString().Replace("%11","").Replace("%12","").Replace("%3%4%5%6%7%8%9%10","")
$objMessage | Add-Member -type NoteProperty -name Severity -value "Informational"
$colMessages += $objMessage
#$iMessageID
#$stringOutput.ToString()
}
#$previousString = $stringOutput.ToString()
}
#Find Warning Status Messages
for ($iMessageID = 1; $iMessageID -ile 99999; $iMessageID++)
{
$result = $Win32FormatMessage::FormatMessage($flags, $ptrModule, 2147483648 -bor $iMessageID, 0, $stringOutput, $sizeOfBuffer, $stringArrayInput)
if( $result -gt 0)
{
$objMessage = New-Object System.Object
$objMessage | Add-Member -type NoteProperty -name MessageID -value $iMessageID
$objMessage | Add-Member -type NoteProperty -name MessageString -value $stringOutput.ToString().Replace("%11","").Replace("%12","").Replace("%3%4%5%6%7%8%9%10","")
$objMessage | Add-Member -type NoteProperty -name Severity -value "Warning"
$colMessages += $objMessage
#$iMessageID
#$stringOutput.ToString()
}
#$previousString = $stringOutput.ToString()
}
#Find Error Status Messages
for ($iMessageID = 1; $iMessageID -ile 99999; $iMessageID++)
{
$result = $Win32FormatMessage::FormatMessage($flags, $ptrModule, 3221225472 -bor $iMessageID, 0, $stringOutput, $sizeOfBuffer, $stringArrayInput)
if( $result -gt 0)
{
$objMessage = New-Object System.Object
$objMessage | Add-Member -type NoteProperty -name MessageID -value $iMessageID
$objMessage | Add-Member -type NoteProperty -name MessageString -value $stringOutput.ToString().Replace("%11","").Replace("%12","").Replace("%3%4%5%6%7%8%9%10","")
$objMessage | Add-Member -type NoteProperty -name Severity -value "Error"
$colMessages += $objMessage
#$iMessageID
#$stringOutput.ToString()
}
#$previousString = $stringOutput.ToString()
}
$colMessages | Export-CSV -path $strOutputCSV -NoTypeInformation
}
$csvs = Get-ChildItem "$dir\*.csv"
$y=$csvs.Count
Write-Host "Detected the following CSV files: ($y)"
foreach ($csv in $csvs)
{
Write-Host " "$csv.Name
}
$outputfilename = "StatusMessages-$date.xlsx" #creates file name with date/username
Write-Host Creating: $outputfilename
$excelapp = new-object -comobject Excel.Application
$excelapp.sheetsInNewWorkbook = $csvs.Count
$xlsx = $excelapp.Workbooks.Add()
$sheet=1
foreach ($csv in $csvs)
{
$row=1
$column=1
$worksheet = $xlsx.Worksheets.Item($sheet)
$worksheet.Name = $csv.Name
$file = (Get-Content $csv)
foreach($line in $file)
{
$linecontents=$line -split ',(?!\s*\w+")'
foreach($cell in $linecontents)
{
$worksheet.Cells.Item($row,$column) = $cell
$column++
}
$column=1
$row++
}
$sheet++
}
$output = $dir + "\" + $outputfilename
$xlsx.SaveAs($output)
$excelapp.quit()
cd \ #returns to drive root
Remove-Item "$dir\*.csv" -Recurse -Force -ErrorAction SilentlyContinueOn Error Resume Next
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = WScript.CreateObject("WScript.Shell")
'Delete all software update caches
If objFSO.FolderExists("C:\Windows\System32\ccm\cache") Then
objShell.Run ("cmd /c dir /b c:\windows\system32\ccm\cache\*-*-*-*-*.1.System>c:\temp\caches.txt")
else
objShell.Run ("cmd /c dir /b c:\windows\syswow64\ccm\cache\*-*-*-*-*.1.System>c:\temp\caches.txt")
End If
If objFSO.FileExists ("C:\temp\caches.txt") Then
Set objFile = objFSO.OpenTextFile("C:\temp\caches.txt", 1)
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
'Wscript.Echo strLine
dirPath = "c:\windows\system32\ccm\cache\" & strLine
'Wscript.Echo dirPath
objFSO.DeleteFolder dirPath, True
Wscript.Sleep 10
Loop
objFile.Close
End If
Wscript.Quit(0)