Blog‎ > ‎

IT


SQL map time to .Net ticks

posted Nov 12, 2015, 5:38 PM by Jake Vosloo

.Net System.DateTime.Ticks has 10,000,000 ticks per second and starts from 0:00:00 UTC on 1 January 0001, in the Gregorian calendar. To get this number in SQL is quite complex but here is a shortcut:

This select statement calculates the .Net ticks in SQL and is accurate to the nearest second.
select (cast(630822816000000000  as bigint) + cast(datediff(second,'2000-01-01',GETUTCDATE()) as bigint) * 10000000) as [Ticks.Net]

This is how I constructed the formula, first I got the ticks up to 2000 from powershell as follows:
Write-Host "2000:  " ([System.DateTime]"2000-01-01").Ticks
Write-Host "Now:   " ([DateTime]::UtcNow).Ticks
Write-Host "Seconds:          ^       " 

Which returns:
2000:   630822816000000000

Then I calculate the difference in seconds since 2000 and the current date and multiply the number of seconds with 10 million to get it into ticks.

References:


Windows update force reboot

posted Oct 17, 2015, 8:43 PM by Jake Vosloo   [ updated Oct 17, 2015, 8:43 PM ]

Windows sometimes restart even when there is a user logged in.  This can be remedied with the following registry edits. The attached file contains these edits.

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU]
"UseWUServer"=dword:00000001
"NoAutoUpdate"=dword:00000000
"AUOptions"=dword:00000005
"AutoInstallMinorUpdates"=dword:00000001
"IncludeRecommendedUpdates"=dword:00000001
"NoAutoRebootWithLoggedOnUsers"=dword:00000001
"RebootWarningTimeoutEnabled"=dword:00000000
"RebootWarningTimeout"=dword:00000090
"RebootRelaunchTimeoutEnabled"=dword:00000001
"RebootRelaunchTimeout"=dword:000005A0
"ScheduledInstallDay"=dword:00000000
"ScheduledInstallTime"=dword:00000001

References

PowerShell Find the Current Path

posted Oct 4, 2015, 5:39 PM by Jake Vosloo


Powershell has a few complications when finding the path where the current script is executing. Especially if you are running it from ISE or directly on the shell.  This function helps to get the most reliable path.

function GetScriptDirectory()
{
    #Try 1, should work for powershell 3.0+.
    try{
        $localPath = $PSScriptRoot
        if (![string]::IsNullOrWhiteSpace($localPath)) {return $localPath}
    } Catch [system.exception] {}
    
    #Try 2, for older version of powershell.
    try{
        if ($myInvocation.MyCommand.CommandType -ne [System.Management.Automation.CommandTypes]::Script) {
            $localPath = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Path)
            if (![string]::IsNullOrWhiteSpace($localPath)) {return $localPath}
        }
    } Catch [system.exception] {}
    
    #Try 3, if this is being run in ISE.
    try{
        $localPath = [System.IO.Path]::GetDirectoryName($psISE.CurrentFile.FullPath)
        if (![string]::IsNullOrWhiteSpace($localPath)) {return $localPath}
    } Catch [system.exception] {}

    #Try 4, as last resort use the current shell location.
    try{
        $localPath = (Get-Location).Path
        if (![string]::IsNullOrWhiteSpace($localPath)) {return $localPath}
    } Catch [system.exception] {}

    #All failed, throw an exception.
    throw "ERROR=""Execution path unknown."" messag=""Unable to identify the path of the script which is needed to load the dependency files."""
}

GetScriptDirectory

References:


Powershell create new self signed certificate

posted May 6, 2015, 6:50 PM by Jake Vosloo

The command to create a new self signed certificate is as follows:

New-SelfSignedCertificate -certstorelocation cert:\localmachine\my -dnsname customer1-test.cloudapp.net


From:
http://windowsitpro.com/blog/creating-self-signed-certificates-powershell

PowerShell Export SSL certificates

posted Apr 27, 2015, 7:27 PM by Jake Vosloo   [ updated May 11, 2015, 8:42 PM ]

PowerShell doesn't automatically offer to trust a remote server as many SSH clients does instead you have to go through a number of manual steps to be able to connect to an Azure remote server.  After configuring your VM in Azure, its powershell will be configured with a self signed cerificate for the external Azure URL. To be able to connect to this with PowerShell remoting, you must install the certificate into the local computer's trusted root certificates store. 

You can see which certificates is conneted on your computer using:
netsh http show sslcert

Here's a script which extracts a certificate from a port, save it as a file called DownloadCertAndImportRoot.ps1 on and then attempts to import it into the certificate store.

<#
This script retrieves the certificate from an SSL connection, saves the certificate as a file and attempts to import it into the trusted root store.
USAGE:  .\DownloadCertAndImportRoot.ps1 "https://www.google.com"
Adapted from: 
https://bernhardelbl.wordpress.com/2013/03/21/download-and-install-a-certificate-to-your-trusted-root-using-powershell/
#>
param($url)

[Net.ServicePointManager]::ServerCertificateValidationCallback = {$true} #Bypass Powershell certificate validation, so that we can download any untrusted certificate.
[System.Uri] $u = New-Object System.Uri($url)
[Net.ServicePoint] $sp = [Net.ServicePointManager]::FindServicePoint($u);
[System.Guid] $groupName = [System.Guid]::NewGuid() #allow to quickly close all connections
[Net.HttpWebRequest] $req = [Net.WebRequest]::create($url)
$req.Method = "GET"
$req.Timeout = 600000 # = 10 minutes
$req.ConnectionGroupName = $groupName
# // Set if you need a username/password to access the resource
#$req.Credentials = New-Object Net.NetworkCredential("username", "password");
[Net.HttpWebResponse] $result = $req.GetResponse() #If the server return 404 then you will get an exception here.
$sp.CloseConnectionGroup($groupName) | Out-Null

#Write the certificate to a temp file
$tempfilename = [System.IO.Path]::GetTempFileName() #get a temporary file reference
[System.Byte[]] $data = $sp.Certificate.Export([System.Security.Cryptography.X509Certificates.X509ContentType]::Cert)
[System.IO.File]::WriteAllBytes($tempfilename, $data)
Write-Debug "Downloaded to temp file: $tempfilename"

#move the temp file to the local folder for future use.
$outfilename = (Convert-Path .) + "\CertExport.cer"
if(Test-Path $outfilename) { del $outfilename }
mv $tempfilename $outfilename
Write-Host "Certificate saved as: $outfilename"

#Import the certificate into the root certificate store
if (([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator))
{
    #CertUtil -addStore Root $outfilename
    $pfx = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2
    $store = new-object System.Security.Cryptography.X509Certificates.X509Store(“Root”,”LocalMachine”)
    $pfx.Import($outfilename)
    $store.Open(“MaxAllowed”)
    $store.Add($pfx)
    $store.Close()
}
else
{
    Write-Host "The script is not running as administrator and cannot automatically import the certificate into the root store. You should Right-click the exported certificate file and install it into the trusted root store."
}


The manual process is:

You can use Chrome or Firefox to download the certificate and then user Windows Explorer or MMC to import it into the certificate store. This only works if you are not using any insecure ports which will be blocked by these browsers...

Open PowerShell and run the following command to confirm that it is failing:
Test-WsMan -Port 5986 -UseSSL YourServerUrl.cloudapp.net

You should get an error stating:
The SSL certificate is signed by an unknown certificate authority.

Now open the server url in chorme:
https://YourServerUrl.cloudapp.net:5986

Access and download the certificate to your local disk.
Right-click the certificate and install it into the trusted root store.
Try the PowerShell command again, it should work now.

References:

Get incremental build number in TFS VisualStudio online

posted Apr 25, 2015, 6:16 PM by Jake Vosloo

I wanted incremental build numbers from VisualStudio online when automatically building and deploying to Google play store.

The build process file creates the buildnumber in the first line:
this:Process.BuildNumberFormat="[&quot;$(Rev:rr)&quot;]"

The $(Rev:xxx) defines the build number. When a build is completed, if nothing else in the build number has changed, the Rev integer value is incremented by one.

Although I could not find any documentation, it seems the Rev number has the following format:
rr formats it like ## so:
$(Rev:.r) is formatted as .1
$(Rev:.rr) is formatted as .01
$(Rev:rr) is formatted as 01


References:


Root Samsung Note Pro 12.2 LTE SM-P905 - P905ZSUANH1

posted Apr 10, 2015, 2:42 AM by Jake Vosloo   [ updated Apr 10, 2015, 2:48 AM ]

Warning: This trips the Knox warranty void flag.

Download and extract the following files:

Power off your Android device.

Put the Galaxy Note Pro 12.2 LTE SM-P905 in Download Mode by pressing and holding down the Home, Volume Down buttons while pressing the Power button. A warning screen will come up; press Volume Up button to enter Download Mode. If you get a recovery menu instead, check that you are pressing the volume down button and not the volume up button.


Run Odin3 v3.09.exe as an Administrator because Admin privileges is required to complete the process smoothly

Connect your device to your PC using USB cable. You will see an  “Added!!”  massage in Odin’s message box.

Click the AP Button, and select that CF-Auto-Root-viennalte-viennaltexx-smp905.tar.md5 file

In Odin window, ensure only Auto Reboot and F. Reset Time checkboxes are selected.

Double  check everything in Step 8 & 9 above. When satisfied click the START button to begin the flashing process.

Your device will restart and it is complete when you see a PASS message with green background in the left-most box at  the very top of the Odin. You can now unplug the USB cable to disconnect  your device from computer.

If it worked, go to http://www.chainfire.eu/ and make a big donation for their work.


Extracted from:
http://androidpulp.blogspot.com/2014/09/root-p905zsuanh1-android-442-kitkat.html

Save text to file in Excel VBA

posted Dec 29, 2013, 3:00 AM by Jake Vosloo   [ updated Dec 29, 2013, 3:05 AM ]

This VBA script will save the text from one column (O) into a file for which the name is taken from column (K).
Surprisingly this method, which opens and closes a file for each row of the sheet is much faster than the method I describe here, which tells Excel to save sheets to file as csv. I presume the overhead is due to Excel having to serialise the sheets to CSV format.

Public Sub ExportPastelCSV()
On Error GoTo HandleErr

Dim ThisRow As Range
Dim OutputPath As String
Dim OutputFile As String
Dim OutputRow As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.CalculateFullRebuild

'Make a folder with the name of the worksheet
OutputPath = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) 'Simple but bad way to remove the extension.
OutputPath = ThisWorkbook.path & Application.PathSeparator & OutputPath
If Dir(OutputPath, vbDirectory) = "" Then
    MkDir OutputPath
Else
    If Dir(OutputPath & "\*.*") <> "" Then
        Kill OutputPath & "\*.*"
    End If
End If

Dim TheSheet As Worksheet
Set TheSheet = ThisWorkbook.ActiveSheet
Dim TheRange As Range
Set TheRange = ThisWorkbook.ActiveSheet.UsedRange

If TheRange.Rows.Count > 1 Then
   Set TheRange = TheRange.Resize(TheRange.Rows.Count - 1, TheRange.Columns.Count).Offset(1, 0)
 
    For Each ThisRow In TheRange.Rows
    OutputFile = ThisRow.Cells(1, 11).Value
        If OutputFile <> "" Then
            OutputRow = ThisRow.Cells(1, 15).Value
            OutputFile = OutputPath & Application.PathSeparator & OutputFile & ".csv"
            Open OutputFile For Append Lock Write As #1
            Write #1, OutputRow
            Close #1
        End If
    Next ThisRow
End If

MsgBox "Successfully exported the Pastel CSV files in a folder with the same name as this worksheet:" & vbCrLf & OutputPath & vbCrLf

Finally:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub

HandleErr:
MsgBox "Couldn't save all sheets to CSV." & vbCrLf & _
        "Source: " & Err.Source & " " & vbCrLf & _
        "Number: " & Err.Number & " " & vbCrLf & _
        "Description: " & Err.Description & " " & vbCrLf

GoTo Finally
End Sub

Save every sheet in an Excel workbook as a CSV file

posted Dec 28, 2013, 1:42 AM by Jake Vosloo   [ updated Dec 28, 2013, 3:05 AM ]

Today I wanted to save every sheet in an Excel workbook as a CSV file, the headings of each sheet is removed first.
Here's the code:

Public Sub SaveAllSheetsAsCSV()
On Error GoTo HandleErr

Dim Sheet As Worksheet
Dim NewWorkbook As Workbook
Dim OutputPath As String
Dim OutputFile As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.CalculateFullRebuild

'Make a folder with the name of the worksheet
OutputPath = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) 'Simple but bad way to remove the extension.
OutputPath = ThisWorkbook.path & Application.PathSeparator & OutputPath
If Dir(OutputPath, vbDirectory) = "" Then
    MkDir OutputPath
End If

For Each Sheet In Sheets
    Set NewWorkbook = Workbooks.Add
    ' Copy contents without headers
    With Sheet.UsedRange
        If .Rows.Count > 1 Then
            .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).Copy
        Else
            .Copy
        End If
    End With
    ' Paste values only
    NewWorkbook.Sheets.Item(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValues
    ' Save to CSV file
    OutputFile = OutputPath & Application.PathSeparator & Sheet.Name & ".csv"
    NewWorkbook.SaveAs Filename:=OutputFile, FileFormat:=xlCSV, CreateBackup:=False
    NewWorkbook.Close
Next


Finally:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub

HandleErr:
MsgBox "Couldn't save all sheets to CSV." & vbCrLf & _
        "Source: " & Err.Source & " " & vbCrLf & _
        "Number: " & Err.Number & " " & vbCrLf & _
        "Description: " & Err.Description & " " & vbCrLf

GoTo Finally
End Sub


I got help from the following sources:
http://www.mrexcel.com/forum/excel-questions/587522-copy-usedrange-except-header.html
http://stackoverflow.com/questions/59075/save-each-sheet-in-a-workbook-to-separate-csv-files
http://www.excelfox.com/forum/f2/export-all-worksheets-to-separate-csv-files-388/
http://windowssecrets.com/forums/showthread.php/140173-Open-excel-workbook-save-each-worksheet-as-csv-with-tab-name

Excel VBA copy sheet values only

posted Dec 28, 2013, 1:36 AM by Jake Vosloo   [ updated Dec 28, 2013, 1:53 AM ]

Today I wanted to copy the values from every sheet in one workbook into a new workbook with only the values.  Here is what worked for me eventually:

For Each Sheet In Sheets
    Set NewWorkbook = Workbooks.Add
    Sheet.UsedRange.Copy
    NewWorkbook.Sheets.Item(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValues
...


The URLs where I found advice were in no order of preference:
http://www.mrexcel.com/forum/excel-questions/571065-used-range-copy-paste-values.html
http://www.ozgrid.com/forum/showthread.php?t=37957
http://www.ozgrid.com/forum/showthread.php?t=79718
http://stackoverflow.com/questions/14881032/copy-excel-sheet-to-another-excel-book-but-just-formats-and-values
http://stackoverflow.com/questions/17565456/macro-to-copy-values-only-from-one-sheet-to-another
http://www.mrexcel.com/forum/excel-questions/650606-copy-worksheet-values-only-new-workbook.html



1-10 of 33