Private/Operations.ps1
|
<#
.SYNOPSIS Invokes a Kusto query. .DESCRIPTION Invokes a Kusto query by calling the Kusto SDK that is loaded when the module is imported. Builds a Kusto connection string and executes a query against a database in a cluster. Connections utilize AAD credentials to avoid getting into credential management. Clusters must exist in the *.kusto.windows.net subdomain. Queries have a 45 second threshold before timing out. .EXAMPLE $query = "..." Invoke-Kusto -Cluster 'AzureCM' -Database 'AzureCM' -Query $query .NOTES Initial framework author: Cale Vernon (CAVERNON) #> function Invoke-Kusto { [CmdletBinding()] param ( [Parameter( Mandatory = $TRUE )][string]$kustoCluster, [Parameter( Mandatory = $TRUE )][string]$kustoDatabase, [Parameter( Mandatory = $TRUE )][string]$query ) Try { # Create a Kusto connection string. $connectionStringBuilder = New-Object Kusto.Data.KustoConnectionStringBuilder("https://$kustoCluster.kusto.windows.net;AAD Federated Security=True", $kustoDatabase) $queryProvider = [Kusto.Data.Net.Client.KustoClientFactory]::CreateCslQueryProvider($connectionStringBuilder) $clientRequestProperties = New-Object Kusto.Data.Common.ClientRequestProperties # Set $clientRequestProperties, specifically a GUID for identification and a timeout threshold of one minute. $clientRequestProperties.ClientRequestId = 'MyPowerShellScript.ExecuteQuery.' + [guid]::NewGuid().Tostring() $clientRequestProperties.SetOption([Kusto.Data.Common.ClientRequestProperties]::OptionServerTimeout, [TimeSpan]::FromSeconds(45)) # Execute the query and retrieve the resulting data table as a DataView. $reader = $queryProvider.ExecuteQuery($query, $clientRequestProperties) $dataTable = [Kusto.Cloud.Platform.Data.ExtendedDataReader]::ToDataSet($reader).Tables[0] # Create and return a $results object. $results = New-Object System.Data.DataView($dataTable) return $results } Catch { Write-Console -Message 'Kusto returned a critical error, such as a timeout or temporary throttle.' -Color 'Red' Write-Console -Message 'Try again momentarily as this type of error is typically transient.' -Color 'Red' Return } } function Invoke-NoInstancesFound { Write-Console -Message "No instances of $($Global:session.resource) were found in the given timespan." $timestamp = Get-Date -Format FileDateTimeUniversal $filename = "instances-$timestamp" $query | Out-String | Out-File -FilePath "$($Global:configuration.Output)\Temporary\$filename.csl" -Force Write-Console -Message 'Opening the query in Kusto Explorer for you to review.' Invoke-Item "$($Global:configuration.Output)\Temporary\$filename.csl" } function Invoke-Operation { [CmdletBinding()] param ( [Parameter( Mandatory = $TRUE )][PSCustomObject]$operation ) # Open any reference links. If ($operation.links) { Write-Console -Message "Opening the reference links for $($operation.name)..." ForEach ($link in $operation.links) { Start-Process $link } } # Ask for any parameters not already stored in $Global:session. ForEach ($parameter in $operation.parameters) { If (!($Global:session.$parameter)) { Write-Console -Message $parameter -Color 'Cyan' $parameterValue = Read-Host '?' If (!($parameterValue)) { Do { $parameterValue = Read-Host '?' } Until ($parameterValue) } $Global:session | Add-Member -NotePropertyName $parameter -NotePropertyValue $parameterValue } } # Log the operation use. Add-Log -Operation $operation.id # Validate the operation type. Switch ($operation.type) { 'Global' { ForEach ($dashboard in $operation.body) { ForEach ($parameter in $operation.parameters) { $matchingString = '`$' + $parameter If ($parameter -eq 'timespanStart') { $dashboard = ($dashboard).Replace($matchingString, $Global:session.timespanStartGlobal) } ElseIf ($parameter -eq 'timespanEnd') { $dashboard = ($dashboard).Replace($matchingString, $Global:session.timespanEndGlobal) } Else { $dashboard = ($dashboard).Replace($matchingString, $($Global:session.$parameter)) } } Write-Console -Message "Launching the following dashboard: $($operation.name)..." Start-Process $dashboard } # Break the Switch statement. Break } 'Kusto' { $connectionTokens = ($operation.body[0]).Split('.') $kustoCluster = ($connectionTokens[0].Split("'"))[1] $kustoDatabase = ($connectionTokens[1].Split("'"))[1] $query = $operation.body -Join "`r`n" ForEach ($parameter in $operation.parameters) { $matchingString = '`$' + $parameter If ($parameter -eq 'timespanStart') { # Calculate the Kusto timestamp format if it does not already exist, such when using Free Mode. If (!($Global:session.timespanStartStandard)) { $timespanStartStandard = "$(Get-Date $Global:session.timespanStart -Format s)Z" $Global:session | Add-Member -NotePropertyName 'timespanStartStandard' -NotePropertyValue $timespanStartStandard } $query = ($query).Replace($matchingString, $Global:session.timespanStartStandard) } ElseIf ($parameter -eq 'timespanEnd') { # Calculate the Kusto timestamp format if it does not already exist, such when using Free Mode. If (!($Global:session.timespanEndStandard)) { $timespanEndStandard = "$(Get-Date $Global:session.timespanEnd -Format s)Z" $Global:session | Add-Member -NotePropertyName 'timespanEndStandard' -NotePropertyValue $timespanEndStandard } $query = ($query).Replace($matchingString, $Global:session.timespanEndStandard) } Else { $query = ($query).Replace($matchingString, $($Global:session.$parameter)) } } # Begin Kusto invocation. $results = Invoke-Kusto -KustoCluster $kustoCluster -KustoDatabase $kustoDatabase -Query $query # Begin results handling. # No results were found. If ($results.Count -eq 0) { Write-Console -Message "$($operation.name): No results were returned." $timestamp = Get-Date -Format FileDateTimeUniversal $filename = "$($operation.name)-$timestamp" $query | Out-String | Out-File -FilePath "$($Global:configuration.Output)\Temporary\$filename.csl" -Force Write-Console -Message "Opening the $($operation.name) query in Kusto Explorer for you to review." Invoke-Item "$($Global:configuration.Output)\Temporary\$filename.csl" } # Results were found. Else { Write-Console -Message "$($operation.name): $($results.Count) result(s) returned." $records = $results | Out-GridView -PassThru -Title "[Nanite] $($operation.name): Please select from the $($results.Count) record(s) returned." # No records were selected.. If (!($records)) { Write-Console -Message 'No records were selected.' Return } # Records were selected. Else { # # Entering a continuous loop to ensure the results handling menu is shown until exited. While ($TRUE) { # Show the results actions menu. Do { Write-Console -Message "What would you like to do with the selected record(s)?" Write-Console -Message "[B] Copy both the $($operation.name) query and records list to clipboard" -Color 'Cyan' Write-Console -Message '[L] Copy records list to clipboard' -Color 'Cyan' Write-Console -Message "[K] Open the $($operation.name) query in Kusto Explorer" -Color 'Cyan' Write-Console -Message "[Q] Copy the $($operation.name) query to your clipboard" -Color 'Cyan' Write-Console -Message '[S] Save the records as a comma-separated values (*.csv) file' -Color 'Cyan' Write-Console -Message '[X] Run another operation.' -Color 'Cyan' $selection = Read-Host '?' } Until (@('B', 'L', 'K', 'Q', 'S', 'X') -contains $selection) Switch ($selection) { 'B' { $query | Out-String | Set-Clipboard $records | Format-List | Out-String | Set-Clipboard -Append Write-Console -Message "Copied both the $($operation.name) query and the record(s) list to clipboard." Break } 'L' { $records | Format-List | Out-String | Set-Clipboard Write-Console -Message "Copied the record(s) list to clipboard." Break } 'K' { $timestamp = Get-Date -Format FileDateTimeUniversal $filename = "$($operation.name)-$timestamp" $query | Out-String | Out-File -FilePath "$($Global:configuration.Output)\Temporary\$filename.csl" -Force Write-Console -Message 'Created temporary query file; opening.' Invoke-Item "$($Global:configuration.Output)\Temporary\$filename.csl" Break } 'Q' { $query | Out-String | Set-Clipboard Write-Console -Message "Copied the $($operation.name) query to your clipboard." Break } 'S' { $timestamp = Get-Date -Format FileDateTimeUniversal $filename = "$($operation.name)-$timestamp" $records | Export-Csv -Path "$($Global:configuration.Output)\Temporary\$filename.csv" -Delimiter ',' -NoTypeInformation -Force Write-Console -Message "Saved the record(s) to $($Global:configuration.Output)\Temporary\$filename.csv." Invoke-Item "$($Global:configuration.Output)\Temporary\$filename.csv" Break } 'X' { Write-Console 'Returning to the operations menu...' Return Break } } } } } } 'Standard' { ForEach ($dashboard in $operation.body) { ForEach ($parameter in $operation.parameters) { $matchingString = '`$' + $parameter If ($parameter -eq 'timespanStart') { $dashboard = ($dashboard).Replace($matchingString, $Global:session.timespanStartStandard) } ElseIf ($parameter -eq 'timespanEnd') { $dashboard = ($dashboard).Replace($matchingString, $Global:session.timespanEndStandard) } Else { $dashboard = ($dashboard).Replace($matchingString, $($Global:session.$parameter)) } } Write-Console -Message "Launching the following dashboard: $($operation.name)..." Start-Process $dashboard } # Break the Switch statement. Break } Default { Write-Console -Message "Operation $($operation.name) is an unsupported type: $($operation.type)" Break } } } |