Using PowerShell and youtube-dl to automate grunt work

Earlier this evening I was tasked by my wife to create audio files from some (DRM-free) YouTube content. I knew how to do this via a manual process, however after the 5th manual file conversion I started to remember the youtube-dl Python project and figured there has to be an easier and quicker way to do this file conversion grunt work.

And sure enough... youtube-dl (with some help from PowerShell) does the trick.

Expected input and output

For starters (we are assuming you have already installed Python 3), you will need to have pip install two modules:

 pip install youtube-dl  
 pip install ffmpeg  

Next, create a youtube-uris.txt with the URI of each song you want to convert on separate lines of the file.

Finally, you can use the following PowerShell script to copy the song URIs into a string array which is then iterated through, converting each array item into a playable audio file (mp4), saved in whatever directory you run the script from.

 [string[]]$arrayFromFile = Get-Content -Path 'C:\sand\youtube-dl-sandbox\youtube-uris.txt'  
 foreach ($uri in $arrayFromFile) {  
   youtube-dl -f 140 $uri  
  }  

That's it. This script can be used for automating other manual/repetitive tasks requiring a procedure to be run against every item in a large list.


Reference: https://github.com/rg3/youtube-dl

Generate Secure Machine Key Section for Web.config via PowerShell

Machine Keys are used in ASP.NET for securing machines that are part of a web farm as well as for sharing encrypted application session and state information.

This PowerShell script (function) can be called (once run and saved to your PS session) via,

"PS C:\: Generate-MachineKey"

With the output from this PS function, you can copy and paste to your web.config ie:

 <configuration>  
  <system.web>  
   <machineKey ... />  
  </system.web>  
 </configuration>  

Generate-MachineKey function definition/PS script

 # Generates a <machineKey> element that can be copied + pasted into a Web.config file.  
 function Generate-MachineKey {  
  [CmdletBinding()]  
  param (  
   [ValidateSet("AES", "DES", "3DES")]  
   [string]$decryptionAlgorithm = 'AES',  
   [ValidateSet("MD5", "SHA1", "HMACSHA256", "HMACSHA384", "HMACSHA512")]  
   [string]$validationAlgorithm = 'HMACSHA256'  
  )  
  process {  
   function BinaryToHex {  
     [CmdLetBinding()]  
     param($bytes)  
     process {  
       $builder = new-object System.Text.StringBuilder  
       foreach ($b in $bytes) {  
        $builder = $builder.AppendFormat([System.Globalization.CultureInfo]::InvariantCulture, "{0:X2}", $b)  
       }  
       $builder  
     }  
   }  
   switch ($decryptionAlgorithm) {  
    "AES" { $decryptionObject = new-object System.Security.Cryptography.AesCryptoServiceProvider }  
    "DES" { $decryptionObject = new-object System.Security.Cryptography.DESCryptoServiceProvider }  
    "3DES" { $decryptionObject = new-object System.Security.Cryptography.TripleDESCryptoServiceProvider }  
   }  
   $decryptionObject.GenerateKey()  
   $decryptionKey = BinaryToHex($decryptionObject.Key)  
   $decryptionObject.Dispose()  
   switch ($validationAlgorithm) {  
    "MD5" { $validationObject = new-object System.Security.Cryptography.HMACMD5 }  
    "SHA1" { $validationObject = new-object System.Security.Cryptography.HMACSHA1 }  
    "HMACSHA256" { $validationObject = new-object System.Security.Cryptography.HMACSHA256 }  
    "HMACSHA385" { $validationObject = new-object System.Security.Cryptography.HMACSHA384 }  
    "HMACSHA512" { $validationObject = new-object System.Security.Cryptography.HMACSHA512 }  
   }  
   $validationKey = BinaryToHex($validationObject.Key)  
   $validationObject.Dispose()  
   [string]::Format([System.Globalization.CultureInfo]::InvariantCulture,  
    "<machineKey decryption=`"{0}`" decryptionKey=`"{1}`" validation=`"{2}`" validationKey=`"{3}`" />",  
    $decryptionAlgorithm.ToUpperInvariant(), $decryptionKey,  
    $validationAlgorithm.ToUpperInvariant(), $validationKey)  
  }  
 }  

Reference: https://support.microsoft.com/en-us/help/2915218/resolving-view-state-message-authentication-code-mac-errors#appendixa

Accessing SQL Server Data in R

Importing SQL Server data into R for analysis is pretty straightforward and simple. You will obviously need R installed on your machine. The following R code will connect to your SQL Server database (using R Studio):

 library(RODBC)  
 dbconnection <- odbcDriverConnect('driver={SQL Server};server=.;database=CLARO;trusted_connection=true')  
 initdata <- sqlQuery(dbconnection,paste('SELECT * FROM [CLARO].[dbo].[Fielding];')) 

SELECT data from a SQL Server database output in R Studio

Reference: https://stackoverflow.com/questions/39401230/connect-r-to-a-sql-server-database-engine