Category Archives: Scripts and Scripting

Quickly getting media info using MediaInfo

Years ago I started ripping all my DVD’s and BluRays to my NAS so I could stream them without having to load the disk into my XBox, which is my only player in the house. Although the process is a pain it is so much more convenient to be able to stream a movie to any TV in the house and on the plus side when taking a trip I can now download the movies to my phone or tablet and bring them with me. The problem is the file size….some movies are massive and halfway through ripping my collection I started to run out of space. So I started looking into Handbrake to compress them into a more manageable size. But first I wanted to get a idea of everything I had, mainly so I could start compressing the largest files first.

I started using MediaInfo which is a great program to get almost every piece of information from a media file. The problem was when scanning a lot of files, especially across a network, it would lock up and crash. I’m assuming because it’s retrieving hundreds of properties from each file it just gets overloaded. But they also make a command line version which lets you poll files for only the info you want which can run a lot quicker.

So after lots of trial and error using the command line version I made a PowerShell script that automates the command line MediaInfo program to get the basics: File name, duration, size, resolution height and width, and what was used to encode it so I could see whether it was already compressed with Handbrake or if it was the original rip from MakeMKV. Copy and paste this into your PowerShell editor, change the initial variables, and run:

# This script uses the MediaInfo CLI ( https://mediaarea.net/en/MediaInfo/Download/Windows ) to 
# quickly catalog media into a CSV file.  Change the initial variables to match your system then run

# Path to the root folder where you have your media
$RootFolder = "X:\Movies"

# Path to the MediaInfo CLI executable
$MediaInfoCLI ="C:\Temp\MediaInfo.exe"

# CSV File to create for your media's info
$CSVExport = "C:\Temp\MovieExport.csv"

# File types to include in the search.  Seperate by a comma like @("*.mkv","*.mp4","*.mpg") etc
$FileTypes = @("*.mkv","*.mp4")

# Add a resolution column to the file.  Once this finishes running a formula will be displayed that you
# can insert into excel to guesstimate the resolution.  Turn off to skip this.
$CreateResolutionColumn = 1

# Display status as the script is running.  0 for None, 1 for basic status, 2 for full status
$DisplayStatus = 1

#Display the exported file once complete.  0 for No, 1 for Yes
$DisplayExport = 1


### Do not change anything under here unless you know what you are doing

# Get our media info.
if (Test-Path -Path $MediaInfoCLI -PathType Leaf) { #Make sure MediaInfo exists
    $timer = [Diagnostics.Stopwatch]::StartNew()
    # The information to pull out of our media.  If you change any of this you will also have to change the 
    # header that is created.  Note the variable for new line since MediaInfo needs different sections on 
    # different lines even though it's exporting everything to a single line.  This info is put into a temp
    # file for MediaInfo to then read.
    $NL = "`r`n"
    $InfoToPull = 'General;"""%FileName%""",%FileSize/String%,%Encoded_Application%,' + $NL + 'Video;%Width%,%Height%,%Duration/String%' + $NL
    $TempFile = New-TemporaryFile
    $InfoToPull | Out-File -Encoding ascii -FilePath $TempFile

    # Create a header for our CSV file.
    If ($CreateResolutionColumn -eq 0) {
        'File Name, Size, Encoder, Width, Height, Duration, Notes' | Out-File -Encoding utf8 -FilePath $CSVExport
    } Else {
        'File Name, Size, Encoder, Width, Height, Duration, Resolution, Notes' | Out-File -Encoding utf8 -FilePath $CSVExport
    }

    If ($DisplayStatus -ge 1) {
        #Display all our variables if option is set
        CLS
        Write-Host "MediaInfo found at $MediaInfoCLI"
        Write-Host "Searching $RootFolder for all files matching these extensions:" $FileTypes.Replace("(", "").Replace(")", "").Replace("*.", "")
        Write-Host "Writting results to $CSVExport"
        Write-Host "Retrieving list of files...."
    }
    $ListOfFiles = Get-ChildItem -Path $RootFolder -Recurse -File -Include $FileTypes
    If ($DisplayStatus -ge 1) {
        $GetFilesTimer = [math]::Round($timer.elapsed.totalseconds,2)
        Write-Host "Found" $ListOfFiles.count "files in" $GetFilesTimer "seconds"
    }
    ForEach ($file in $ListofFiles) {
        If ($DisplayStatus -eq 2) {
            Write-Host "Getting info for $file..."
        }
        If ($DisplayStatus -eq 1) {
            Write-Host -NoNewline "."
        }
        $Args = @("--Output=file://$TempFile",$file)
        & $MediaInfoCLI $Args | Out-File -encoding utf8 -Append -FilePath $CSVExport
    }
    If ($DisplayStatus -ge 1) {
        $FinishedTimer = [math]::Round($timer.elapsed.totalseconds,2)
        Write-Host "`n"
        Write-Host "Finished exporting info.  Took" $FinishedTimer "seconds"
    }
    
    Remove-Item $TempFile  # remove the temp file used by MediaInfo

    if ($CreateResolutionColumn -eq 1) {
        Write-Host "`n"
        Write-Host "For Resolution column:  Copy and paste the following text into Row 2's resolution column then extend it down to the other rows:"
        Write-Host "=IF(D2>3830,""4K"",IF(E2=2160, ""4K"",IF(D2 <=719,IF(D2="""",""Need"",""SD""),IF(D2 <= 1900,IF(E2 > 1070, ""1080"", ""SD""),""1080""))))"
        Write-Host "`n"
        Write-Host "Then make sure to save the file as a standard xls/xlsx/ods to retain formatting."
    }
    If ($DisplayExport -eq 1) {
        Start-Process $CSVExport
    }
} Else {
    Write-Host "MediaInfo could not be found at $MediaInfoCLI.  Please double check it's location."
}

The script is pretty bare bones, not a lot of error checking other then making sure the CLI MediaInfo program is found, but does the job quickly.

Batch Converting Excel XLS files to XLSX

A little while back I posted a macro to batch covert Visio VSD files to VSDX files which got a decent number of people messaging me. Recently I found how many excel files we had using the old format which just like old Visio files take up a lot of extra space. So I went through and modified my Visio converter over for Excel. So here is a step by step to write your own Excel file converter:

  1. Open a new Excel document. Save it as a “Excel Macro-Enabled Workbook (*.xlsm)
  2. In the first cell put something like “To run the conversion hit ALT+F11 to open the program then F5 to run it”.
  3. Hit ALT+F11 to open up the Microsoft Visual Basic for Applications screen
  4. Right click “ThisWorkbook” at the top left then Insert -> Module
  5. In the module copy and paste the following in:
Public FilesAttempted As Integer
Public FilesConverted As Integer
Public FilesDeleted As Integer
Public FilesSkipped As String

Sub ConvertToXlsx()
FilesAttempted = 0
FilesConverted = 0
FilesDeleted = 0
FilesSkipped = ""
Dim FileSystem As Object
Set FileSystem = CreateObject("Scripting.FileSystemObject")

Dim HostFolder As String
Dim DeleteOriginal As Boolean
Dim RemovePersonal As Boolean

''' HostFolder is directory to start at.  Change to your base directory.
HostFolder = "C:\temp"

''' DeleteOriginal will delete the original file as long as the xlsx was created.  Either True or False
DeleteOriginal = False

DoFolder FileSystem.GetFolder(HostFolder), DeleteOriginal

MsgBox "Conversion complete! " & vbCrLf & vbCrLf & "Files attempted: " & FilesAttempted & vbCrLf & "Files converted: " & FilesConverted & vbCrLf & "Files deleted: " & _
    FilesDeleted & vbCrLf & "Files with issues: " & vbCrLf & FilesSkipped, vbOKOnly + vbInformation, "Conversion Complete"
  
End Sub

Sub DoFolder(Folder, DeleteOriginal)
  On Error GoTo ErrHandler:
  Dim SubFolder
  For Each SubFolder In Folder.SubFolders
    DoFolder SubFolder, DeleteOriginal
  Next
  Dim File
  Dim myWorkbook As Workbook
  For Each File In Folder.Files
    ' For each file name sure its a xls and not a temp file
    If ((Right(File, 3) = "xls") And (Right(File, 4) <> "~xls")) Then
      FilesAttempted = FilesAttempted + 1
      ' Open the file
      Set myWorkbook = Workbooks.Open(File)
       
      ' Save as a xlsx and increase our counter
      myWorkbook.SaveAs Filename:=File & "x", FileFormat:=xlOpenXMLWorkbook
      myWorkbook.Close (False)
      FilesConverted = FilesConverted + 1
      
      ' Delete the original if set and the new xlsx exists
      If ((DeleteOriginal = "True") And (FileExists(File & "x"))) Then
        SetAttr File, vbNormal
        Kill File
        FilesDeleted = FilesDeleted + 1
      End If
NextFile:
    End If
  Next
Done:
  Exit Sub
  
ErrHandler:
Debug.Print "Error encountered.  Error number: " & Err.Number & " - Error description: " & Err.Description
  If File <> "" Then
    FilesSkipped = FilesSkipped & File & vbCrLf
    GoTo NextFile:
  End If
    
End Sub

Function FileExists(ByVal FileToTest As String) As Boolean
   FileExists = (Dir(FileToTest) <> "")
End Function

Change the HostFolder to the directory you want to run this on and hit F5 to run. It will open each Excel workbook with a xls extension in that directory, and all sub directories, then save it as a xlsx. If you want it to automatically delete the old xls file change the DeleteOriginal variable to True or just manually delete them after conversion.

Resetting Windows Update on Domain Joined Computers

Windows Update & WSUS have been a thorn in my side for many many years. When it works its great but when it doesn’t it can be very frustrating to figure out what went wrong. Over the years I’ve had to rebuilt WSUS twice, once when it just stopped pushing updates and another time where it imploded itself and corrupted the database. Recently we had a number of computers, around 10%, stop reporting back to WSUS for status. They also reported no updates available when checking for updates using WSUS. We tried all the troubleshooters, DISM cleanups, etc, but nothing seemed to work. I thought maybe it was WSUS again but that wouldn’t make sense with so many successfully getting updated. Even tried the Microsoft recommendations on resetting Windows Update but in our case BITS didn’t want to stop. And related to this we were getting Task Host errors on shutdown for those machines with the reason being “AutomaticUpdateHost” which would make sense if BITS was stuck.

After some testing we found that the Microsoft recommendations did work when in safe mode. Problem was doing this as easily with the least amount of downtime and hands on touching. To that end I created a series of three batch files. The first one, run as a administrator, will set the boot options to safeboot with networking and reboot:

bcdedit /set {default} safeboot network
shutdown -r -t 5

The second is most of the Microsoft recommendations along with others I’ve found on the internet:

@echo off
echo Stopping Windows Update and BTIS services…
net stop bits /y
net stop wuauserv /y
net stop appidsvc /y
net stop cryptsvc /y
echo Killing any windows updates in process
taskkill /im wuauclt.exe /f
echo Deleting some files…
del /s /q /f "%ALLUSERSPROFILE%\Application Data\Microsoft\Network\Downloader\qmgr.dat" del /s /q /f "%ALLUSERSPROFILE%\Microsoft\Network\Downloader\qmgr.dat"
del /s /q /f "%SYSTEMROOT%\WindowsUpdate.log"
rd /s /q "C:\WINDOWS\SoftwareDistribution"
rd /s /q "%SYSTEMROOT%\system32\Catroot2"
del %USERPROFILE%\AppData\Local\Temp* /s /q
for /d %%x in (%USERPROFILE%\AppData\Local\Temp*) do @rd /s /q "%%x"
del %systemroot%\Temp* /s /q
for /d %%y in (%systemroot%\Temp*) do @rd /s /q "%%y"
echo Reset the BITS service and the Windows Update service to the default security descriptor.
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)
echo Deleting registry keys….
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
reg delete "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v SusClientIDValidation /f
REG DELETE "HKLM\Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update" /v LastWaitTimeout /f
REG DELETE "HKLM\Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update" /v DetectionstartTime /f
Reg Delete "HKLM\Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update" /v NextDetectionTime /f
echo Re-registering Windows Update components…
regsvr32.exe /s c:\windows\system32\atl.dll
regsvr32.exe /s c:\windows\system32\urlmon.dll
regsvr32.exe /s c:\windows\system32\mshtml.dll
regsvr32.exe /s c:\windows\system32\shdocvw.dll
regsvr32.exe /s c:\windows\system32\browseui.dll
regsvr32.exe /s c:\windows\system32\jscript.dll
regsvr32.exe /s c:\windows\system32\vbscript.dll
regsvr32.exe /s c:\windows\system32\scrrun.dll
regsvr32.exe /s c:\windows\system32\msxml.dll
regsvr32.exe /s c:\windows\system32\msxml3.dll
regsvr32.exe /s c:\windows\system32\msxml6.dll
regsvr32.exe /s c:\windows\system32\actxprxy.dll
regsvr32.exe /s c:\windows\system32\softpub.dll
regsvr32.exe /s c:\windows\system32\wintrust.dll
regsvr32.exe /s c:\windows\system32\dssenh.dll
regsvr32.exe /s c:\windows\system32\rsaenh.dll
regsvr32.exe /s c:\windows\system32\gpkcsp.dll
regsvr32.exe /s c:\windows\system32\sccbase.dll
regsvr32.exe /s c:\windows\system32\slbcsp.dll
regsvr32.exe /s c:\windows\system32\cryptdlg.dll
regsvr32.exe /s c:\windows\system32\oleaut32.dll
regsvr32.exe /s c:\windows\system32\ole32.dll
regsvr32.exe /s c:\windows\system32\shell32.dll
regsvr32.exe /s c:\windows\system32\initpki.dll
regsvr32.exe /s c:\windows\system32\wuapi.dll
regsvr32.exe /s c:\windows\system32\wuaueng.dll
regsvr32.exe /s c:\windows\system32\wuaueng1.dll
regsvr32.exe /s c:\windows\system32\wucltui.dll
regsvr32.exe /s c:\windows\system32\wups.dll
regsvr32.exe /s c:\windows\system32\wups2.dll
regsvr32.exe /s c:\windows\system32\wuweb.dll
regsvr32.exe /s c:\windows\system32\qmgr.dll
regsvr32.exe /s c:\windows\system32\qmgrprxy.dll
regsvr32.exe /s c:\windows\system32\wucltux.dll
regsvr32.exe /s c:\windows\system32\muweb.dll
regsvr32.exe /s c:\windows\system32\wuwebv.dll
echo Resetting Winsock…
netsh winsock reset
echo Resetting WinHTTP proxy…
netsh winhttp reset proxy
echo Resetting the services as automatic…
sc.exe config wuauserv start= auto
sc.exe config bits start= delayed-auto
sc.exe config cryptsvc start= auto
sc.exe config TrustedInstaller start= demand
sc.exe config DcomLaunch start= auto
echo Restarting services…
net start bits
net start wuauserv
net start appidsvc
net start cryptsvc
net start DcomLaunch
echo Telling Windows to detect updates…
wuauclt.exe /resetauthorization
wuauclt.exe /detectnow
wuauclt.exe /reportnow
PowerShell.exe (New-Object -ComObject Microsoft.Update.AutoUpdate).DetectNow()

Then finally once that runs through is the third batch file to set the computer back to a normal boot:

bcdedit /deletevalue {default} safeboot
shutdown -r -t 5

I put all three into a folder and placed it on the desktops of the troubled computers then ran each in sequence. First the computer rebooted in safe mode, then Windows Update gets reset along with deleting all temp files, then the computer reboots normally.

So far every computer this was run on has reported in. Not sure what causes this in the first place but at least we have a quick solution now.

Note: The script was written to be run in regular mode so its stopping services that are normally already stopped in safe mode but was just reused for this purpose on the machines where BITS kept getting stuck.

Check Uptime of Domain Computers

Recently we pushed some updates through GPO which ran at a users login to the domain. Weeks went by and I kept getting calls about people with old software that didn’t update. After some quick investigating these users were simply not rebooting or shutting down their computers and some were going on two months. On one hand that’s pretty good for Windows 10 machines but on the other they were missing important updates. After looking around I found that PsInfo.exe, part of the PSTools suite, would let me poll a computer for uptime but I wanted to poll all the computers and see how widespread this problem was.

First I started with a list of all computers taken from Active Directory using this PowerShell command to export them to a text file. Technically this command exports to a csv but I’m only taking one column so I skipped a step:

Get-ADComputer -Filter * -Properties Name | Select-Object Name | Export-CSV "C:\temp\ComputerNames.txt" -NoTypeInformation

Opening the file you should have a header of Name with all your workstations. I deleted the header and did a global find and replace to remove the quotes so I had a file with just the workstation names. Next I made a batch file with this single line:

For /f "tokens=*" %%i in (ComputerNames.txt) do psinfo uptime -nobanner \\%%i >> uptime.txt

I placed the batch file (CheckUptime.bat for me) in the same directory as PsInfo.exe and my ComputerNames.txt file. Run the batch file and it will step through each computer name in the file and check the uptime giving you something like this:

System information for \WSComputer2:
Uptime: 0 days 5 hours 24 minutes 57 seconds
System information for \WSComputer6:
Uptime: 2 days 17 hours 45 minutes 18 seconds
System information for \WSComputer23:
Uptime: 0 days 0 hours 42 minutes 41 seconds

I’m sure there is also a way to scrap the file and clean this up but it works for my needs.

Kixtarter – KiXtart Script Editor

KiXtart is a free-format scripting language written by Ruud van Velsen of Microsoft Netherlands.  Many companies have used KiXtart as there login script processor although as time goes by people are switching to purely GPO’s or VB script.  I personally still use it for my login scripts, it’s easy to understand and simply to deply.  Just copy a exe to each machine through GPO and edit your users profiles to run the exe and script at login.  In fact I was using it so much that wrote a KiXtart script editor with color highlighting of the keywords, a insert menu with all the commands, and some wizards for commonly used things.

The last update brought the software to v4.12 and will most likely be the last version released.  Download it here:

Kixtarter v4.12 – KiXtart Script Editor Installer

EDIT: I’m still using this program so I made some optimizations to it and brought it up to targeting the .Net Framework 4.7 as the old one was still running on 3.5.  This is now considered v4.13 and is the newest version:

Kixtarter v4.13 – KiXtart Script Editor Installer