PowerShell: What files changed for DaysAgo?

\ by

Pass in multiple paths.
Create a variable for Owner, using Get-Acl.
Output to GridView so we can review all columns.

$PathList = @('\\Shared\1','\\Shared\2', '\\Shared\3')
$DaysAgo = 2
$Owner = @{
  Name = 'File Owner'
  Expression = { (Get-Acl $_.FullName).Owner }
}
Get-ChildItem -Path $PathList  -Recurse -Filter '*.sql'  |
     ? { $_.LastWriteTime -gt (Get-Date).AddDays(-$DaysAgo) } |
        sort LastWriteTime |
            Select LastWriteTime,  $Owner, Name, Directory    | Out-GridView
Posted in: Coding, PowerShell, Reports

PowerShell: Who RDP’d into our Servers and didn’t log out

\ by

Who used remote desktop to log into our servers but didn’t log out?

##########
# Remote desktop users - who is RDPed into SQL Servers?

$AllComputersList |%{ 
    $ComputerName = $_ ; 
    # $ComputerName = 'SQLPROD'
    write-host -ForegroundColor Cyan $ComputerName
    #(qwinsta /SERVER:$ComputerName)
     
    $RDPUser = (quser /SERVER:$ComputerName 2>$null )  # Don't show us any DOS errors.
    if (!$RDPUser) {
        return # continue
    }
    $RDPUser
}

Useful tip in this script is how to redirect output. Here we redirect to NUL like this

2>$null
Posted in: PowerShell, Reports

PowerShell: Get last reboot times for all servers.

\ by 
# Last Boot Times for all servers, rebooted. Bounced.
#

$Report = @() 
foreach ($machine in $SQLInstanceDev) {
    # Change SQL Instances to ComputerNames.
    $Computer = $machine.Split('\')[0]
    $object = Get-WmiObject win32_operatingsystem -ComputerName $Computer 
    $Report += [pscustomobject] @{
        Computer       = $object.PSComputerName
        LastBootUpTime = $object.ConverttoDateTime($object.lastbootuptime)
        DayOfWeek      = ($object.ConverttoDateTime($object.lastbootuptime)).DayOfWeek
        DaysAgo        = (NEW-TIMESPAN –Start (Get-Date) –End ($object.ConverttoDateTime($object.lastbootuptime))).Days
    }
}

$Report |Sort-Object LastBootUpTime
Posted in: PowerShell, Reports

Copy Files and SubDirectories and keep folder structure

\ by

Copies a set of files recursively and recreates subfolders and files in another location.

# Copy XML files from subfolders to another drive and create subfolders.

$SourcePath = 'G:\Prod\'
$TargetPath = 'E:\SQLScripts\'
$FileFilter = '*.xml'

Get-ChildItem $SourcePath -filter $FileFilter -recurse |
    ForEach-Object { 
        $targetFile = $TargetPath + $_.FullName.SubString($SourcePath.Length); 
        $DestFolder = Split-Path $targetFile
        if (-not (Test-Path $DestFolder)) {
            New-Item -ItemType Directory -Path $DestFolder #-WhatIf
        }

        Copy-Item $_.FullName -destination $targetFile #-WhatIf
}
Posted in: Files, PowerShell

PowerShell: Order of command precedence

\ by

Here is the order of command precedence that is used by the command discovery process in Windows PowerShell:

· Alias: All Windows PowerShell aliases in the current session

· Filter/Function: All Windows PowerShell functions

· Cmdlet: The cmdlets in the current session (“Cmdlet” is the default)

· ExternalScript: All .ps1 files in the paths that are listed in the Path environment variable ($env:PATH)

· Application: All non-Windows-PowerShell files in paths that are listed in the Path environment variable

· Script: Script blocks in the current session

Posted in: Documentation, PowerShell

PowerShell: Function Get-LastUpdates shows most recent Windows updates

\ by 
Function Get-LastUpdates {

    Param(
        [parameter(Position=0, Mandatory = $false)] [Int32] $DaysAgo = 0,
        [parameter(Position=1, Mandatory = $false)] [Int32] $LastNum
    )
    Process {
    
        $Session = New-Object -ComObject "Microsoft.Update.Session"
        $Searcher = $Session.CreateUpdateSearcher()
 
        if ($LastNum) {
            $historyCount = $LastNum
        } else {
            $historyCount = $Searcher.GetTotalHistoryCount()
        }
    
        # Limit the returned rows by $historyCount
        $HistItems = $Searcher.QueryHistory(0, $historyCount) 
 
     #$HistItems | ?{$_.Title -ne $null} |sort Date | Select-Object Date, Title, @{n="desc"; e={$_.Description.substring(0,10)}},  @{name="Operation"; expression={switch($_.operation){ 1 {"Installation"}; 2 {"Uninstallation"}; 3 {"Other"}}}}
     #$HistItems |select -First 1
        if ($DaysAgo -eq 0) {
            $HistItems | ?{$_.Title -ne $null} |sort Date | Select-Object Date, Title
        } else {
            $HistItems | ?{$_.Title -ne $null} | ?{$_.Date -gt (Get-Date).AddDays(-$DaysAgo) } |sort Date | Select-Object Date, Title
    
        }
        # https://itbeco.wordpress.com/microsoft/powershell/
        #Select-Object Date, @{expression={$COMPUTERNAME};Label="Host"}, @{name="Operation"; expression={switch($_.operation){1 {"Installation"}; 2 {"Uninstallation"}; 3 {"Other"}}}}, @{name="Status"; expression={switch($_.resultcode){1 {"In Progress"}; 2 {"Succeeded"}; 3 {"Succeeded With Errors"};4 {"Failed"}; 5 {"Aborted"}}}},@{name="Title";expression={[regex]::Match($_.Title,'(KB[0-9]{6,7})').Value}}
    }
}

And to run from DOS

@ECHO OFF
 
if {%1}=={} (
@ECHO USAGE
@ECHO   GetLastUpdates DaysAgo
@ECHO.
GOTO :FINISHED
)


Powershell -noprofile -command "&{ . E:\MSSQL\PS\adhoc\Get-LastUpdates.ps1; Get-LastUpdates -DaysAgo %1 }

:FINISHED
Posted in: PowerShell