Month: August 2019

How To: Resolving a System Hang During Patching, Remotely!

This is pretty awesome:

Routine patching of systems and software is a crucial piece of any business’ information security strategy. Even so, many systems go unnoticed and unpatched for months, even years until an external threat forces the organization into action (e.g. the recent WannaCry ransomware outbreak).

When that happens, server administrators need to be prepared for irregularities they’re likely to encounter, such as a hang prior to reboot.

In this scenario, we’re going to assume that you’ve just finished patching and clicked the “Restart Now” button. You begin a continuous ping (ping -t [hostname/IP address]) and wait for the server to restart.

Let’s assume a normal reboot takes 5-10 minutes for this machine, and that 25+ minutes have passed.

You check the console, and are greeted by the “‘Preparing to Configure Windows. Do not turn off your computer” message. Time continues to pass while your maintenance window dwindles like falling grains in an hourglass… pressure is mounting, the business won’t wait. Time for action!

Logged in as an Administrator from your workstation check the Windows Module Installer service on the remote system…

  1. Run services.msc
  2. Right-click “Services (Local)” and select “Connect to another computer …”
  3. Make sure the “Another computer” radio button is selected and enter the hostname of the stuck server and click “OK”
  4. Search for “Windows Module Installer” service and verify its status. If it’s “stopping,” then you will need to force it to stop. This can’t be done here, so we’ll need to query its PID and use our old friend TaskKill to manually kill the service

Query the Process ID (PID) of the Windows Module Installer (TrustedInstaller) service…

  1. Open Command Prompt as an Administrator
  2. Run the following command:
    sc \\[hostname of the server] queryex trustedinstaller
  3. This will return (among other information) the PID of the stuck service, write it down as you’ll need it for the next step

Kill the hung service remotely using TaskKill…

  1. From the Command Prompt already opened, run the following command:taskkill /s [hostname of the server] /pid [PID recorded above] /t

Source: https://it.toolbox.com/blogs/yousef-alahmad/how-to-resolving-a-system-hang-during-patching-remotely-061417

Convert CSV to XLSX via PowerShell

Revisiting an old friend as some date format junk was needing adjustment and leading 0s from txt file were being dropped. Script originally brought txt into xlsx and all data was “General” format. Tweak noted below brought all data into xlsx as “Text” format.

Changed:

$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
to
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count

Entire script with stuff removed to protect the martians:

# Define locations and delimiter
$SourceFolder = "\\server\share\path1\path2"
$csv = $SourceFolder+"\sourcefile.txt" #Location of the source file
$xlsx = $SourceFolder+"\Cool Filename "+(Get-Date -f dd)+"."+(Get-Date -f MM)+"."+(Get-Date -f yyyy)+".xlsx" #Desired location of output
$delimiter = "|" #Specify the delimiter used in the file

# REMOVE ALL .XLSX files in the folder
Remove-Item $SourceFolder\* -include *.xlsx

# Create a new Excel workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

# Build the QueryTables.Add command and reformat the data
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

# Execute & delete the import query
$query.Refresh()
$query.Delete()

# Save & close the Workbook as XLSX.
$Workbook.SaveAs($xlsx,51)
$excel.Quit()

# Legacy file process rename tasks
dir \\server\share\path1\file.txt | Rename-Item -NewName {"New_Filename_"+(Get-Date -f yyyyMMdd)+".csv"}
dir \\server\share\path1\file2.txt | Rename-Item -NewName {"Another_File_"+(Get-Date -f yyyyMMdd)+".csv"}

Source: https://code.adonline.id.au/csv-to-xlsx-powershell/