Post by Eddie Jackson – Page 40 – Lab Core

PowerShell – Update SQL Record

Posted onFebruary 27, 2018February 27, 2018

This is how you update a record in a database using PowerShell. I’m using this to modify the OS field, which is sometimes delayed due to slow inventory scans.

Note, where it says “The_”, I recommend using variables in your scripting.

Set-Location SQLSERVER:\SQL\The_SQL_Server_Name\The_SQL_Instance
Invoke-Sqlcmd -Query "UPDATE dbo.vSMS_R_System SET Operating_System_Name_and0 = 'Microsoft Windows NT Workstation 10.0' WHERE Name0='The_Computer_Name'" -Database "The_SCCM_DB_Name"

Windows Resource Icons

Posted onFebruary 22, 2018February 22, 2018

Reference list of Windows Icons contained in DLL and EXE icon libraries

The numbers below each icon refer to the icon number of the file, and the number in brackets indicates how many variants of the icon there are.

%windir%\system32\compstui.dll (Common Property Sheet User Interface)

compstui.dll icons

%windir%\system32\ddores.dll (Device Category Information and Resources)

DDORes.dll icons

%windir%\system32\ieframe.dll (Internet Explorer)

ieframe.dll icons

%windir%\system32\imageres.dll (Windows Image Resource)

imageres.dll icons

%windir%\system32\mmcndmgr.dll (Microsoft Management Console Node Manager)

mmcndmgr.dll icons

%windir%\system32\moricons.dll (Windows NT Setup Icon Resources)

moricons.dll icons

%windir%\system32\netshell.dll (Network Connections Shell)

netshell.dll icons

%windir%\system32\pnidui.dll (Network System Icons)

pnidui.dll icons

%windir%\system32\shell32.dll (Windows Shell Common)

shell32.dll icons

%windir%\system32\wmploc.dll (Windows Media Player Resources)

wmploc.dll icons

%windir%\system32\pifmgr.dll (Windows NT PIF Manager Icon Resource Library)

pifmgr.dll icons

%windir%\system32\wpdshext.dll (Portable Devices Shell Extension)

wpdshext.dll icons

%windir%\system32\comres.dll (COM+ Resources)

comres.dll icons

%windir%\system32\dmdskres.dll (Disk Management Support Snap-In)

dmdskres.dll icons

%windir%\system32\dsuiext.dll (Directory Service Common UI)

dsuiext.dll icons

%windir%\system32\inetcplc.dll (Internet Control Panel)

inetcplc.dll icons

%windir%\system32\mstsc.exe (Remote Desktop Connection Client)

mstsc.exe icons

%windir%\system32\mstscax.dll (Remote Desktop Services ActiveX Client)

mstscax.dll icons

%windir%\system32\setupapi.dll (Windows Setup API)

setupapi.dll icons

%windir%\system32\shdocvw.dll (Shell Doc Object and Control Library)

shdocvw.dll icons

%windir%\system32\urlmon.dll (OLE32 Extension for Win32)

urlmon.dll icons

%windir%\system32\wiashext.dll (Imaging Devices Shell Folder UI)

wiashext.dll icons

%windir%\system32\mmres.dll (General Audio Resources)

mmres.dll icons

Windows 10 – Associate Image Types to Photo Viewer

Posted onFebruary 19, 2018November 21, 2022

If you’re like me, you like the simplicity of certain apps and functions. When previewing photos, my favorite app was the Windows Photo Viewer. Well, if you notice, in Windows 10 it has gone away; you can no longer associate image types to Photo Viewer. To bring back the availability of Photo Viewer, just copy and paste the following into notepad, name photoviewer.reg, and double-click it. When selecting one of your images, right-click, and this will add the option to Open With > Choose Another App > More Apps….scroll down to the bottom. This will also add the option to the Default apps area in Settings.

Save as photoviewer.reg, double-click

Windows Registry Editor Version 5.00

; Change Extension’s File Type
[HKEY_CURRENT_USER\Software\Classes\.jpg]
@=”PhotoViewer.FileAssoc.Tiff”

; Change Extension’s File Type
[HKEY_CURRENT_USER\Software\Classes\.jpeg]
@=”PhotoViewer.FileAssoc.Tiff”

; Change Extension’s File Type
[HKEY_CURRENT_USER\Software\Classes\.gif]
@=”PhotoViewer.FileAssoc.Tiff”

; Change Extension’s File Type
[HKEY_CURRENT_USER\Software\Classes\.png]
@=”PhotoViewer.FileAssoc.Tiff”

; Change Extension’s File Type
[HKEY_CURRENT_USER\Software\Classes\.bmp]
@=”PhotoViewer.FileAssoc.Tiff”

; Change Extension’s File Type
[HKEY_CURRENT_USER\Software\Classes\.tiff]
@=”PhotoViewer.FileAssoc.Tiff”

; Change Extension’s File Type
[HKEY_CURRENT_USER\Software\Classes\.ico]
@=”PhotoViewer.FileAssoc.Tiff”

[HKEY_CLASSES_ROOT\Applications\photoviewer.dll]

[HKEY_CLASSES_ROOT\Applications\photoviewer.dll\shell]

[HKEY_CLASSES_ROOT\Applications\photoviewer.dll\shell\open]
“MuiVerb”=”@photoviewer.dll,-3043”

[HKEY_CLASSES_ROOT\Applications\photoviewer.dll\shell\open\command]
@=hex(2):25,00,53,00,79,00,73,00,74,00,65,00,6d,00,52,00,6f,00,6f,00,74,00,25,\
00,5c,00,53,00,79,00,73,00,74,00,65,00,6d,00,33,00,32,00,5c,00,72,00,75,00,\
6e,00,64,00,6c,00,6c,00,33,00,32,00,2e,00,65,00,78,00,65,00,20,00,22,00,25,\
00,50,00,72,00,6f,00,67,00,72,00,61,00,6d,00,46,00,69,00,6c,00,65,00,73,00,\
25,00,5c,00,57,00,69,00,6e,00,64,00,6f,00,77,00,73,00,20,00,50,00,68,00,6f,\
00,74,00,6f,00,20,00,56,00,69,00,65,00,77,00,65,00,72,00,5c,00,50,00,68,00,\
6f,00,74,00,6f,00,56,00,69,00,65,00,77,00,65,00,72,00,2e,00,64,00,6c,00,6c,\
00,22,00,2c,00,20,00,49,00,6d,00,61,00,67,00,65,00,56,00,69,00,65,00,77,00,\
5f,00,46,00,75,00,6c,00,6c,00,73,00,63,00,72,00,65,00,65,00,6e,00,20,00,25,\
00,31,00,00,00

[HKEY_CLASSES_ROOT\Applications\photoviewer.dll\shell\open\DropTarget]
“Clsid”=”{FFE2A43C-56B9-4bf5-9A79-CC6D4285608A}”

[HKEY_CLASSES_ROOT\Applications\photoviewer.dll\shell\print]

[HKEY_CLASSES_ROOT\Applications\photoviewer.dll\shell\print\command]
@=hex(2):25,00,53,00,79,00,73,00,74,00,65,00,6d,00,52,00,6f,00,6f,00,74,00,25,\
00,5c,00,53,00,79,00,73,00,74,00,65,00,6d,00,33,00,32,00,5c,00,72,00,75,00,\
6e,00,64,00,6c,00,6c,00,33,00,32,00,2e,00,65,00,78,00,65,00,20,00,22,00,25,\
00,50,00,72,00,6f,00,67,00,72,00,61,00,6d,00,46,00,69,00,6c,00,65,00,73,00,\
25,00,5c,00,57,00,69,00,6e,00,64,00,6f,00,77,00,73,00,20,00,50,00,68,00,6f,\
00,74,00,6f,00,20,00,56,00,69,00,65,00,77,00,65,00,72,00,5c,00,50,00,68,00,\
6f,00,74,00,6f,00,56,00,69,00,65,00,77,00,65,00,72,00,2e,00,64,00,6c,00,6c,\
00,22,00,2c,00,20,00,49,00,6d,00,61,00,67,00,65,00,56,00,69,00,65,00,77,00,\
5f,00,46,00,75,00,6c,00,6c,00,73,00,63,00,72,00,65,00,65,00,6e,00,20,00,25,\
00,31,00,00,00

[HKEY_CLASSES_ROOT\Applications\photoviewer.dll\shell\print\DropTarget]
“Clsid”=”{60fd46de-f830-4894-a628-6fa81bc0190d}”

NOTES

rundll32.exe “%ProgramFiles%\Windows Photo Viewer\PhotoViewer.dll”, ImageView_Fullscreen C:\test.png

Windows 10 – DVI Adapter Monitor Issues, Half of Monitor

Posted onFebruary 19, 2018February 12, 2020

After upgrading to Windows 10, if you’re using some kind of DVI/Display port adapter for dual screen setup, you may notice that one of the monitors have a corrupted or black screen, or only showing on half of the monitor. This is due to how W10 communicates with the port. At first, you may think it’s the display driver itself; it is not. Then, you may think the OS needs to be repaired in some way; it doesn’t.

Screenshot of Dual Monitor Setup

The driver error

Specifically, the 1709 Fall Creators update has compatibility issues with older display ports. So, some adapter drivers (from the original vendor or built into MS) will not work.

The solution? To install the j5create DVI adapter driver. It does not matter which adapter you’re using, the j5create installs the necessary W10 component, which in turn allows the regular video driver to communicate with the display port (Microsoft left out this piece in 1709). Go figure.

Here is the driver package:

http://eddiejackson.net/apps/j5create-18.01.0118.3179_Windows_10_DVI_Connector_Fix.exe

PowerShell – AutoIt – Set Window Size and Position

Posted onFebruary 19, 2018March 1, 2018

PowerShell

Function Set-WindowPosition {
    <# .SYNOPSIS Sets the window size (height,width) and coordinates (x,y) of a process window. .DESCRIPTION Sets the window size (height,width) and coordinates (x,y) of a process window. .PARAMETER ProcessName Name of the process to determine the window characteristics .PARAMETER X Set the position of the window in pixels from the top. .PARAMETER Y Set the position of the window in pixels from the left. .PARAMETER Width Set the width of the window. .PARAMETER Height Set the height of the window. .PARAMETER Passthru Display the output object of the window. .OUTPUT System.Automation.WindowInfo .EXAMPLE Get-Process powershell | Set-Window -X 2040 -Y 142 -Passthru ProcessName Size TopLeft BottomRight ----------- ---- ------- ----------- powershell 1262,642 2040,142 3302,784 Description ----------- Set the coordinates on the window for the process PowerShell.exe #>
	
    [OutputType('System.Automation.WindowInfo')]
    [cmdletbinding()]
    Param (
        [parameter(ValueFromPipelineByPropertyName=$True)]
        $ProcessName,
        [int]$X,
        [int]$Y,
        [int]$Width,
        [int]$Height,
        [switch]$Passthru
    )
    Begin {
        Try{
            [void][Window]
        } Catch {
        Add-Type @"
              using System;
              using System.Runtime.InteropServices;
              public class Window {
                [DllImport("user32.dll")]
                [return: MarshalAs(UnmanagedType.Bool)]
                public static extern bool GetWindowRect(IntPtr hWnd, out RECT lpRect);

                [DllImport("User32.dll")]
                public extern static bool MoveWindow(IntPtr handle, int x, int y, int width, int height, bool redraw);
              }
              public struct RECT
              {
                public int Left;        // x position of upper-left corner
                public int Top;         // y position of upper-left corner
                public int Right;       // x position of lower-right corner
                public int Bottom;      // y position of lower-right corner
              }
"@
        }
    }
    Process {
        $Rectangle = New-Object RECT
        $Handle = (Get-Process -Name $ProcessName).MainWindowHandle
        $Return = [Window]::GetWindowRect($Handle,[ref]$Rectangle)
        If (-NOT $PSBoundParameters.ContainsKey('Width')) {            
            $Width = $Rectangle.Right - $Rectangle.Left            
        }
        If (-NOT $PSBoundParameters.ContainsKey('Height')) {
            $Height = $Rectangle.Bottom - $Rectangle.Top
        }
        If ($Return) {
            $Return = [Window]::MoveWindow($Handle, $x, $y, $Width, $Height,$True)
        }
        If ($PSBoundParameters.ContainsKey('Passthru')) {
            $Rectangle = New-Object RECT
            $Return = [Window]::GetWindowRect($Handle,[ref]$Rectangle)
            If ($Return) {
                $Height = $Rectangle.Bottom - $Rectangle.Top
                $Width = $Rectangle.Right - $Rectangle.Left
                $Size = New-Object System.Management.Automation.Host.Size -ArgumentList $Width, $Height
                $TopLeft = New-Object System.Management.Automation.Host.Coordinates -ArgumentList $Rectangle.Left, $Rectangle.Top
                $BottomRight = New-Object System.Management.Automation.Host.Coordinates -ArgumentList $Rectangle.Right, $Rectangle.Bottom
                If ($Rectangle.Top -lt 0 -AND $Rectangle.LEft -lt 0) {
                    Write-Warning "Window is minimized! Coordinates will not be accurate."
                }
                $Object = [pscustomobject]@{
                    ProcessName = $ProcessName
                    Size = $Size
                    TopLeft = $TopLeft
                    BottomRight = $BottomRight
                }
                $Object.PSTypeNames.insert(0,'System.Automation.WindowInfo')
                $Object            
            }
        }
    }
}

AutoIt

Position()

Func Position()    
    Local $hWnd = WinWait("Untitled - Notepad", "", 1)    
    WinMove($hWnd, "", 200, 200, 200, 150)
EndFunc

Turn off Screen – Place Display into Standby Mode

Posted onFebruary 19, 2018February 22, 2018

powershell (Add-Type ‘[DllImport(\”user32.dll\”)]^public static extern int SendMessage(int hWnd, int hMsg, int wParam, int lParam);’ -Name a -Pas)::SendMessage(-1,0×0112,0xF170,2)

Windows – PowerShell – Reset Windows Updates

Posted onFebruary 19, 2018February 22, 2018


<#
.SYNOPSIS
Reset-WindowsUpdate.ps1 - Resets the Windows Update components

.DESCRIPTION 
This script will reset all of the Windows Updates components to defaults.

.OUTPUTS
Results are printed to the console. Future releases will support outputting to a log file. 

#>


$arch = Get-WMIObject -Class Win32_Processor -ComputerName LocalHost | Select-Object AddressWidth

Write-Host "1. Stopping Windows Update Services..."
Stop-Service -Name BITS
Stop-Service -Name wuauserv
Stop-Service -Name appidsvc
Stop-Service -Name cryptsvc

Write-Host "2. Remove QMGR Data file..."
Remove-Item "$env:allusersprofile\Application Data\Microsoft\Network\Downloader\qmgr*.dat" -ErrorAction SilentlyContinue

Write-Host "3. Renaming the Software Distribution and CatRoot Folder..."
Rename-Item $env:systemroot\SoftwareDistribution SoftwareDistribution.bak -ErrorAction SilentlyContinue
Rename-Item $env:systemroot\System32\Catroot2 catroot2.bak -ErrorAction SilentlyContinue

Write-Host "4. Removing old Windows Update log..."
Remove-Item $env:systemroot\WindowsUpdate.log -ErrorAction SilentlyContinue

Write-Host "5. Resetting the Windows Update Services to defualt settings..."
"sc.exe sdset bits D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)"
"sc.exe sdset wuauserv D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)"

Set-Location $env:systemroot\system32

Write-Host "6. Registering some DLLs..."
regsvr32.exe /s atl.dll
regsvr32.exe /s urlmon.dll
regsvr32.exe /s mshtml.dll
regsvr32.exe /s shdocvw.dll
regsvr32.exe /s browseui.dll
regsvr32.exe /s jscript.dll
regsvr32.exe /s vbscript.dll
regsvr32.exe /s scrrun.dll
regsvr32.exe /s msxml.dll
regsvr32.exe /s msxml3.dll
regsvr32.exe /s msxml6.dll
regsvr32.exe /s actxprxy.dll
regsvr32.exe /s softpub.dll
regsvr32.exe /s wintrust.dll
regsvr32.exe /s dssenh.dll
regsvr32.exe /s rsaenh.dll
regsvr32.exe /s gpkcsp.dll
regsvr32.exe /s sccbase.dll
regsvr32.exe /s slbcsp.dll
regsvr32.exe /s cryptdlg.dll
regsvr32.exe /s oleaut32.dll
regsvr32.exe /s ole32.dll
regsvr32.exe /s shell32.dll
regsvr32.exe /s initpki.dll
regsvr32.exe /s wuapi.dll
regsvr32.exe /s wuaueng.dll
regsvr32.exe /s wuaueng1.dll
regsvr32.exe /s wucltui.dll
regsvr32.exe /s wups.dll
regsvr32.exe /s wups2.dll
regsvr32.exe /s wuweb.dll
regsvr32.exe /s qmgr.dll
regsvr32.exe /s qmgrprxy.dll
regsvr32.exe /s wucltux.dll
regsvr32.exe /s muweb.dll
regsvr32.exe /s wuwebv.dll

Write-Host "7) Removing WSUS client settings..."
REG DELETE "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v AccountDomainSid /f
REG DELETE "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v PingID /f
REG DELETE "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v SusClientId /f

Write-Host "8) Resetting the WinSock..."
netsh winsock reset
netsh winhttp reset proxy

Write-Host "9) Delete all BITS jobs..."
Get-BitsTransfer | Remove-BitsTransfer

Write-Host "10) Attempting to install the Windows Update Agent..."
if($arch -eq 64){
    wusa Windows8-RT-KB2937636-x64 /quiet
}
else{
    wusa Windows8-RT-KB2937636-x86 /quiet
}

Write-Host "11) Starting Windows Update Services..."
Start-Service -Name BITS
Start-Service -Name wuauserv
Start-Service -Name appidsvc
Start-Service -Name cryptsvc

Write-Host "12) Forcing discovery..."
wuauclt /resetauthorization /detectnow

Write-Host "Process complete. Please reboot your computer."

Windows – PowerShell – Remove Windows Store Apps

Posted onFebruary 19, 2018February 22, 2018

Yet another Win app removal script

#requires -Version 3

<
.SYNOPSIS
The RemoveAppxPackage command will remove Windows Store Appx packages.

.DESCRIPTION
This script can help you to remove several Windows Store Apps at one time.

.EXAMPLE
PS C:>; C:\Script\RemoveWindowsStoreApp.ps1

ID App name
1 Microsoft.Media.PlayReadyClient.2
2 Microsoft.Media.PlayReadyClient.2
3 CheckPoint.VPN
4 f5.vpn.client
5 FileManager
6 JuniperNetworks.JunosPulseVpn
7 Microsoft.MoCamera
8 SonicWALL.MobileConnect
9 windows.immersivecontrolpanel
10 winstore
11 Microsoft.BingSports
12 Microsoft.BingTravel
13 Microsoft.SkypeApp
14 Microsoft.BingFinance
15 Microsoft.HelpAndTips
16 Microsoft.BingFoodAndDrink
17 Microsoft.BingHealthAndFitness
18 Microsoft.BingNews
19 microsoft.windowscommunicationsapps
20 Microsoft.WindowsSoundRecorder
21 Microsoft.WindowsScan
22 Microsoft.ZuneMusic
23 Microsoft.VCLibs.120.00
24 Microsoft.WindowsAlarms
25 Microsoft.WinJS.2.0
26 Microsoft.WindowsCalculator
27 Microsoft.BingWeather
28 Microsoft.Reader
29 Microsoft.ZuneVideo
30 Microsoft.WindowsReadingList
31 Microsoft.BingMaps
32 Microsoft.XboxLIVEGames
33 Microsoft.VCLibs.120.00
Which Apps do you want to remove?
Input their IDs and seperate IDs by comma: 28

This example shows how to list all Windows Store apps, and remove the apps specified by user.

.LINK
Windows PowerShell Advanced Function
http://technet.microsoft.com/en-us/library/dd315326.aspx

.LINK
Get-AppxPackage
http://technet.microsoft.com/en-us/library/hh856044.aspx

.LINK
Remove-AppxPackage
http://technet.microsoft.com/en-us/library/hh856038.aspx
>

Import-LocalizedData -BindingVariable Messages

Function PSCustomErrorRecord
{
#This function is used to create a PowerShell ErrorRecord
Param
(
[Parameter(Mandatory=$true,Position=1)][String]$ExceptionString,
[Parameter(Mandatory=$true,Position=2)][String]$ErrorID,
[Parameter(Mandatory=$true,Position=3)][System.Management.Automation.ErrorCategory]$ErrorCategory,
[Parameter(Mandatory=$true,Position=4)][PSObject]$TargetObject
)
Process
{
$exception = New-Object System.Management.Automation.RuntimeException($ExceptionString)
$customError = New-Object System.Management.Automation.ErrorRecord($exception,$ErrorID,$ErrorCategory,$TargetObject)
return $customError
}
}

Function RemoveAppxPackage
{
$index=1
$apps=Get-AppxPackage
#return entire listing of applications
Write-Host "ID`t App name"
foreach ($app in $apps)
{
Write-Host " $index`t $($app.name)"
$index++
}

Do
{
$IDs=Read-Host -Prompt "Which Apps do you want to remove? `nInput their IDs and seperate IDs by comma"
}
While($IDs -eq "")

#check whether input values are correct
try
{
[int[]]$IDs=$IDs -split ","
}
catch
{
$errorMsg = $Messages.IncorrectInput
$errorMsg = $errorMsg -replace "Placeholder01",$IDs
$customError = PSCustomErrorRecord `
-ExceptionString $errorMsg `
-ErrorCategory NotSpecified -ErrorID 1 -TargetObject $pscmdlet
$pscmdlet.WriteError($customError)
return
}

foreach ($ID in $IDs)
{
#check id is in the range
if ($ID -ge 1 -and $ID -le $apps.count)
{
$ID--
#Remove each app
$AppName=$apps[$ID].name

Remove-AppxPackage -Package $apps[$ID] -ErrorAction SilentlyContinue
if (-not(Get-AppxPackage -Name $AppName))
{
Write-host "$AppName has been removed successfully"
}
else
{
Write-Warning "Remove '$AppName' failed! This app is part of Windows and cannot be uninstalled on a per-user basis."
}
}
else
{
$errorMsg = $Messages.WrongID
$errorMsg = $errorMsg -replace "Placeholder01",$ID
$customError = PSCustomErrorRecord `
-ExceptionString $errorMsg `
-ErrorCategory NotSpecified -ErrorID 1 -TargetObject $pscmdlet
$pscmdlet.WriteError($customError)
}
}
}

RemoveAppxPackage

Windows – Enable Remote Assistance

Posted onFebruary 19, 2018February 22, 2018

netsh advfirewall firewall set rule group=”remote assistance” new enable=Yes

reg add “HKLM\System\CurrentControlSet\Control\Remote Assistance” /v fAllowToGetHelp /t REG_DWORD /d 1 /f

reg add “HKLM\SYSTEM\CurrentControlSet\Control\Terminal Serve” /v fDenyTSConnection /t REG_DWORD /d

Windows 10 – Start Menu/Pinned Icons Corrupted – Rebuild Menu

Posted onFebruary 19, 2018January 16, 2019

If your Windows 10 start menu isn’t acting right, try rebuilding the tile data. Delete the following reg key, and restart the explorer.exe process:

reg delete “HKCU\Software\Microsoft\Windows\CurrentVersion\CloudStore\Store\Cache\DefaultAccount\$start.tilegrid$windows.data.curatedtilecollection.root” /f /reg:64

Windows – Disable Intel AT Registration Notification

Posted onFebruary 19, 2018February 22, 2018

REG ADD “HKLM\SOFTWARE\Wow6432Node\Intel\PIcon\Setting” /v “ShowUserNotification” /t REG_DWORD /d “0” /f

Notes

sc config LMS start=disabled

To disable application load on startup for all users, add a value named
AppAutoStartDefaultVal with value 0 to the following registry location
HKLM\SOFTWARE\Intel\PIcon\Setting

To enable application load in ”disabled state” add a value named
AutoStartInDisabled with value 1 to the following registry location
HKLM\SOFTWARE\Intel\PIcon\Setting

SCCM – PowerShell – SQL Query

Posted onFebruary 18, 2018February 22, 2018

This is a SQL query written in PowerShell. It connects to the SCCM DB and returns Custom_Data for C and D drives.

#import-module sqlps
#Install-Module -Name SqlServer -AllowClobber
#Update-Module -Name SqlServer

Clear-Host

[string] $Server= "SCCM_Server\SQL_Instance"
[string] $Database = "CM_SITENAME"

# C Drive Query
[string] $SQLQuery1 = $("SELECT [Bitlocker_C_Drive00] FROM [dbo].[Custom_Custom_DATA] WHERE MachineID='$ResourceID'")

# D Drive Query
[string] $SQLQuery2 = $("SELECT [Bitlocker_D_Drive00] FROM [dbo].[Custom_Custom_DATA] WHERE MachineID='$ResourceID'")

# Not used at this time - no need to use this if connecting in authorized security context
#[string] $user = "username"
#[string] $pwd = "LetMeIn99$"

$Connection = New-Object System.Data.SQLClient.SQLConnection
$dt = new-object "System.Data.DataTable"

$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
#$Connection.ConnectionString = "server='$Server';uid=$user;pwd=$pwd;database='$Database';trusted_connection=true;"
$Connection.Open()

# C Drive
$cmd1 = $Connection.CreateCommand()
$cmd1.CommandText = $SQLQuery1
$data1 = $cmd1.ExecuteReader()
$dt.Load($data1)
$CDrive = $dt | foreach { $_.Bitlocker_C_Drive00 }
$CDrive

# D Drive
$cmd2 = $Connection.CreateCommand()
$cmd2.CommandText = $SQLQuery2
$data2 = $cmd2.ExecuteReader()
$dt.Load($data2)
$DDrive = $dt | foreach { $_.Bitlocker_D_Drive00 }
$DDrive

# Close Connection
$Connection.Close()