Powershell and Log Analytics – Tips and Tricks #1

Working with Gzip compressed base64 data

The following two PowerShell functions will compress or depress text using Gzip, and the result will be base64 encoded. When working with Sentinel incidents, some of the included incident data can be Gzip compressed and base64 encoded. As such, you may need to turn the base64 encoded Gzip data back into the actual log data that spawned the incident you are working with. You may need to upload data to Sentinel, which will exceed the limit of what’s allowed within a field. Gzip compressing it and then base64 encoding will allow significantly more data to be uploaded and stored in a sentinel log table.

Function ConvertTo-GzipBase64String
{
	param (
		$text
	)
	
	$ms = New-Object System.IO.MemoryStream
	$cs = New-Object System.IO.Compression.GZipStream($ms, [System.IO.Compression.CompressionMode]::Compress)
	$sw = New-Object System.IO.StreamWriter($cs)
	$sw.Write($text)
	$sw.Close();
	$s = [System.Convert]::ToBase64String($ms.ToArray())
	return $s
}

function ConvertFrom-GzipBase64String
{
	param (
		$text
	)
	$data = [System.Convert]::FromBase64String($text)
	$ms = New-Object System.IO.MemoryStream
	$ms.Write($data, 0, $data.Length)
	$ms.Seek(0, 0) | Out-Null
	$sr = New-Object System.IO.StreamReader(New-Object System.IO.Compression.GZipStream($ms, [System.IO.Compression.CompressionMode]::Decompress))
	$string = $sr.ReadToEnd()
	return $string
}

When using the Log Analytics search, KQL has a function to base64 decode and Gzip decompress to the stored data. You can view the KQL function here, and an example is below.

$base64 = Convert-ToGzipBase64String -text "1234567890qwertyuiop"
$text = Convert-FromGzipBase64String -text $base64
Write-Host $base64
Write-Host $text

Converting an Excel sheet to a KQL datatable operator

I occasionally need to use data from an Excel sheet while investigating a security incident or while auditing. The first few times I had to do it, I converted the data manually, and by the fourth time, I decided I needed to code a solution. I designed the function below, which will convert a PowerShell array object, loop through the rows and columns, and convert it to a KQL datatable, which can be included in a search query as a variable. I then created a function to import the Excel file, which requires the PowerShell module ImportExcel and then sends the array of data to the PSArrayToKqlDataTable function and copies the data to the clipboard and can also save the query to a text file.

ConvertTo-KqlDataTableFromArray function code:

Function ConvertTo-KqlDataTableFromArray
{
	param (
		$psArray,
		$hasHeaders = $true
	)
	
	$outputString = ''
	
	$props = Get-Member -InputObject $psArray[0]
	
	$workWithProps = @{ }
	$ignoreProps = @{ }
	
	if ($props[0].TypeName -eq 'Microsoft.ActiveDirectory.Management.ADUser')
	{
		$ignoreProps.Add("Item", "Item")
		$ignoreProps.Add("WriteWarningStream", "WriteWarningStream")
		$ignoreProps.Add("WriteVerboseStream", "WriteVerboseStream")
		$ignoreProps.Add("WriteInformationStream", "WriteInformationStream")
		$ignoreProps.Add("WriteErrorStream", "WriteErrorStream")
		$ignoreProps.Add("WriteDebugStream", "WriteDebugStream")
	}
	
	foreach ($prop in $props)
	{
		if ($prop.TypeName -eq 'Microsoft.ActiveDirectory.Management.ADUser' -or $prop.TypeName -eq 'Selected.Microsoft.ActiveDirectory.Management.ADUser')
		{
			if ($ignoreProps.ContainsKey("$($prop.Name)"))
			{
				continue
			}
			
			if ($prop.Definition.Contains("ADPropertyValueCollection"))
			{
				if (-not $workWithProps.ContainsKey($prop.Name))
				{
					$workWithProps.Add($prop.Name, $prop.Name)
				}
				else
				{
					Write-Debug "Skipping Adding $($prop.Name)($($prop.MemberType)))"
				}
			}
			else
			{
				if ($prop.MemberType -eq 'Property')
				{
					if (-not $workWithProps.ContainsKey($prop.Name))
					{
						$workWithProps.Add($prop.Name, $prop.Name)
						Write-Debug "Adding $($prop.Name)($($prop.MemberType)))"
					}
					else
					{
						Write-Debug "Skipping Adding $($prop.Name)($($prop.MemberType)))"
					}
				}
				if ($prop.MemberType -eq 'NoteProperty')
				{
					if (-not $workWithProps.ContainsKey($prop.Name))
					{
						$workWithProps.Add($prop.Name, $prop.Name)
						Write-Debug "Adding $($prop.Name)($($prop.MemberType)))"
					}
					else
					{
						Write-Debug "Skipping Adding $($prop.Name)($($prop.MemberType)))"
					}
				}
			}
		}
		else
		{
			if ($ignoreProps.ContainsKey("$($prop.Name)"))
			{
				continue
			}
			
			if ($prop.MemberType -eq 'Property')
			{
				if (-not $workWithProps.ContainsKey($prop.Name))
				{
					$workWithProps.Add($prop.Name, $prop.Name)
					Write-Debug "Adding $($prop.Name)($($prop.MemberType)))"
				}
				else
				{
					Write-Debug "Skipping Adding $($prop.Name)($($prop.MemberType)))"
				}
			}
			elseif ($prop.MemberType -eq 'NoteProperty')
			{
				if (-not $workWithProps.ContainsKey($prop.Name))
				{
					$workWithProps.Add($prop.Name, $prop.Name)
					Write-Debug "Adding $($prop.Name)($($prop.MemberType)))"
				}
				else
				{
					Write-Debug "Skipping Adding $($prop.Name)($($prop.MemberType)))"
				}
			}
		}
	}
	
	$outputstring = "let psData = datatable("
	
	foreach ($prop in $workWithProps.Keys)
	{
		$outputString += "$($prop.Replace(' ', "_")):string,"
	}
	
	$outputString = $outputString.Substring(0, $outputString.Length - 1)
	$outputString += ") ["
	$outputString += "`n"
	
	foreach ($obj in $psArray)
	{
		foreach ($prop in $workWithProps.Keys)
		{
			$outputString += "`"$($obj.$prop)`","
		}
		$outputString += "`n"
	}
	
	$outputString += "];`n"
	
	return $outputString
}

ConvertTo-KqlDataTableFromExcelFile function code:

Function ConvertTo-KqlDataTableFromExcelFile
{
param
    (
        [string]$InputFile,
        [switch]$SaveToClipboard = $true,
        [switch]$SaveToFile = $true,
        [string]$SaveToFileName = ''
    )

    $excelData = Import-Excel $InputFile

    $kqlTableString = KqlDataTableFromArray -psArray $excelData

    if($SaveToClipboard)
    {
        Set-Clipboard -Value $kqlTableString
    }
    if ($SaveToFile)
    {
        if ([string]::IsNullOrEmpty($SaveToFileName))
        {
            $SaveToFileName = "$([System.IO.Path]::GetTempFileName()).txt"
        }

        $kqlTableString | Out-File $SaveToFileName

        Write-Host "KQL table string saved to file: $($SaveToFileName)"

        Start-Process $SaveToFileName
    }
}

Usage

$kqlDataTableString = ConvertTo-KqlDataTableFromExcelFile -InputFile "pathToFile.xlsx"