psLogAnalytics.psm1
|
function Write-PsLogAnalytics { <# .SYNOPSIS Query data sources and upload results to a Log Analytics workspace .DESCRIPTION Use to gather data local from a computer and send it to Microsoft Log Analytics .PARAMETER CustomerID The LogAnalytics Workspace ID number .PARAMETER SharedKey The LogAnalytics Workspace Primary Key .PARAMETER InformationType The type of information to gather (Exchange, WMI, SQL, AD) .PARAMETER Command The command used to gather the information (Get-ExchangeServer | ? serverrole -Like *Mailbox* | Get-Queue | select Identity,DeliveryType,Status,MessageCount,NextHopDomain | Sort -Descending MessageCount) .PARAMETER LogName Indicates the name of the log file to create in Log Analytics (ExchangeQueues, win32_logicaldisk, SQLProcessSpaceInfo) .PARAMETER Computer [only partially integrated] Indicates the computer where the command should be run and/or what computer data is listed as in Log Analytics .EXAMPLE Using WMI to gather disk space information Write-PsLogAnalytics -InformationType "WMI" -LogName "win32_logicaldisk" -Command "Get-WmiObject -Class Win32_LogicalDisk | Select-Object Name, VolumeName, FileSystem, Description, FreeSpace, Size" .EXAMPLE Using WMI to gather Physical Memory $Command = @' Get-WmiObject CIM_PhysicalMemory | Measure-Object -Property capacity -sum | % {[math]::round(($_.sum / 1GB),2)} '@ Write-PsLogAnalytics -InformationType "WMI" -LogName "PhysicalMemory" -Command $Command .EXAMPLE Using WMI to gather CPU information Write-PsLogAnalytics -InformationType "WMI" -LogName "win32processor" -Command "Get-WmiObject �class Win32_processor | Select-Object Name,DeviceID,NumberOfCores,NumberOfLogicalProcessors, Addresswidth" .EXAMPLE Using Microsoft Exchange Write-PsLogAnalytics -InformationType "Exchange" -LogName "ExchangeQueues" -Command "Get-ExchangeServer | ? serverrole -Like *Mailbox* | Get-Queue | select Identity,DeliveryType,Status,MessageCount,NextHopDomain | Sort -Descending MessageCount" .EXAMPLE Using Active Directory $Command = @' Get-ADUser -SearchBase 'OU=Launch,DC=CAT,DC=DEMO' -SearchScope 'Subtree' -properties "Created","Department","Displayname","EmailAddress","Manager","Modified","Name","Office","SamAccountName","Title","UserPrincipalName" -filter * '@ Write-PsLogAnalytics -InformationType "AD" -LogName "ActiveDirectory" -Command $Command .EXAMPLE Using SQL $Command = @' SELECT SERVERPROPERTY('MachineName') AS [MachineName] , physical_memory_in_use_kb / 1024 AS [SQLServerMemoryUsageMB] , locked_page_allocations_kb / 1024 AS [SQLServerLockedPagesAllocationMB] , large_page_allocations_kb / 1024 AS [SQLServerLargePagesAllocationMB] , page_fault_count , memory_utilization_percentage , available_commit_limit_kb , process_physical_memory_low , process_virtual_memory_low FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE); '@ Write-PsLogAnalytics -InformationType "SQL" -LogName "SQLProcessSpaceInfo" -Command $Command -Computer "SQL1" .NOTES * Originally created as Write-LogAnalytics.ps1 by Cameron Fuller (Twitter: @CFullerMVP) * Available at https://gallery.technet.microsoft.com/PowerShell-script-to-0823e09d #> [CmdletBinding()] param ( [parameter(Mandatory)][ValidateNotNullOrEmpty()][string] $CustomerId, [parameter(Mandatory)][ValidateNotNullOrEmpty()][string] $SharedKey, [parameter(Mandatory)][ValidateNotNullOrEmpty()][string] $InformationType, [parameter(Mandatory)][ValidateNotNullOrEmpty()][string] $Command, [parameter(Mandatory)][ValidateNotNullOrEmpty()][string] $LogName, [parameter()][string] $Computer ) Write-Verbose "Specify the name of the record type that you'll be creating" $LogType = $LogName Write-Verbose "Specify a field with the created time for the records" $TimeStampField = Get-Date if (!$Computer) { $computername = $env:COMPUTERNAME } else { $computername = $Computer } # Create the function to create the authorization signature Function Build-Signature ($customerId, $sharedKey, $date, $contentLength, $method, $contentType, $resource) { $xHeaders = "x-ms-date:" + $date $stringToHash = $method + "`n" + $contentLength + "`n" + $contentType + "`n" + $xHeaders + "`n" + $resource $bytesToHash = [Text.Encoding]::UTF8.GetBytes($stringToHash) $keyBytes = [Convert]::FromBase64String($sharedKey) $sha256 = New-Object System.Security.Cryptography.HMACSHA256 $sha256.Key = $keyBytes $calculatedHash = $sha256.ComputeHash($bytesToHash) $encodedHash = [Convert]::ToBase64String($calculatedHash) $authorization = 'SharedKey {0}:{1}' -f $customerId, $encodedHash return $authorization } # Create the function to create and post the request Function Write-PsLogAnalyticsData { param ( [parameter()] $customerId, [parameter()] $sharedKey, [parameter()] $body, [parameter()] $logType ) $method = "POST" $contentType = "application/json" $resource = "/api/logs" $rfc1123date = [DateTime]::UtcNow.ToString("r") $contentLength = $body.Length $signature = Build-Signature ` -customerId $customerId ` -sharedKey $sharedKey ` -date $rfc1123date ` -contentLength $contentLength ` -fileName $fileName ` -method $method ` -contentType $contentType ` -resource $resource $uri = "https://" + $customerId + ".ods.opinsights.azure.com" + $resource + "?api-version=2016-04-01" $headers = @{ "Authorization" = $signature; "Log-Type" = $logType; "x-ms-date" = $rfc1123date; "time-generated-field" = $TimeStampField; } Write-Verbose "submitting web request" $response = Invoke-WebRequest -Uri $uri -Method $method -ContentType $contentType -Headers $headers -Body $body -UseBasicParsing return $response.StatusCode } if ($InformationType -match "Exchange") { Write-Verbose "Exchange pre-tasks" Add-PSSnapin *exchange* -ErrorAction SilentlyContinue } elseif ($InformationType -match "SQL") { # FUTURE: consider replacing with dbatools or sqlserver modules Write-Verbose "SQL pre-tasks" $database = 'master' $server = "$computername" $connection = New-Object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database"); $cmd = New-Object System.Data.SqlClient.SqlCommand($sqlquery, $connection); $connection.Open(); $reader = $cmd.ExecuteReader() $results = @() while ($reader.Read()) { $row = @{ } for ($i = 0; $i -lt $reader.FieldCount; $i++) { $row[$reader.GetName($i)] = $reader.GetValue($i) } $results += New-Object psobject -property $row } $connection.Close(); } elseif ($InformationType -match "WMI") { Write-Verbose "WMI pre-tasks" # Add any WMI specific precursor tasks here } elseif ($InformationType -match "AD") { # FUTURE: consider adsips module for cases w/o ActiveDirectory module Write-Verbose "ActiveDirectory pre-tasks. Must be run with Admin permissions initially" Import-Module ActiveDirectory } # Run the command which was send in with the Command parameter if ($InformationType -ne "SQL") { $results = Invoke-Expression $Command } foreach ($result in $results) { if ($InformationType -match "Exchange") { $result | Add-Member -MemberType NoteProperty -Name Computer -Value $computername } elseif ($InformationType -match "SQL") { # Add any SQL specific post tasks here } elseif ($InformationType -match "WMI") { $result | Add-Member -MemberType NoteProperty -Name Computer -Value $computername } elseif ($InformationType -match "AD") { # Add any AD specific post tasks here } Write-Verbose $result Write-Verbose "convert results to JSON and compressing" $json = $result | ConvertTo-Json -Compress Write-Verbose "Submitting the data to the API endpoint" Write-PsLogAnalyticsData -customerId $customerId -sharedKey $sharedKey -body ([System.Text.Encoding]::UTF8.GetBytes($json)) -logType $logType } } function Write-PsLogAnalyticsDeviceInventory { <# .SYNOPSIS Write Windows device inventory data to a Log Analytics workspace .PARAMETER CustomerID The LogAnalytics Workspace ID number .PARAMETER SharedKey The LogAnalytics Workspace Primary Key .PARAMETER InventoryMode Controls whether all inventory queries are run, just a selected query, or just list queries without uploading data The default is All queries .EXAMPLE Write-PsLogAnalyticsDeviceInventory -CustomerID "..." -SharedKey "..." .NOTES TBD #> [CmdletBinding()] param ( [parameter(Mandatory)][ValidateNotNullOrEmpty()][string] $CustomerId, [parameter(Mandatory)][ValidateNotNullOrEmpty()][string] $SharedKey, [parameter()][ValidateSet('All','Select','List')] $InventoryMode = 'All' ) try { $xmlsource = "$(Split-Path -Path $(Get-Module "psLogAnalytics").Path -Parent)\queries.xml" if (!(Test-Path $xmlsource)) { Write-Warning "Xml Source is missing: queries.xml"; break; } [xml]$xdata = Get-Content -Path $xmlsource -Raw -ErrorAction Stop $queries = $xdata.queries.query if ($InventoryMode -eq 'Select') { $choice = $queries | Select-Object name,type,comment | Sort-Object name | Out-GridView -Title "Select Query to Run" -OutputMode Single if ($null -ne $choice) { $queries = $queries | Where-Object {$_.name -eq $choice.name} Write-Verbose "query was selected, continuing process" } else { Write-Warning "No query selected. Done." break } } $index = 0 foreach ($query in $queries) { $cmd = $query.command $log = $query.name $cat = $query.type Write-Verbose "query: $log" switch ($InventoryMode) { 'List' { $query } Default { Write-PsLogAnalytics -InformationType $cat -LogName $log -Command $cmd -CustomerId $CustomerID -SharedKey $SharedKey } } $index++ } } catch { Write-Error $_.Exception.Message } } function Show-PsLogAnalyticsDeviceQuery { <# .SYNOPSIS Show stored Windows device inventory queries .PARAMETER GridView Display queries in GridView for selection and execution. If not provided, all queries are output to the console. .EXAMPLE Show-PsLogAnalyticsDeviceQuery .NOTES TBD #> [CmdletBinding()] param ( [parameter()][switch] $GridView ) try { $xmlsource = "$(Split-Path -Path $(Get-Module "psLogAnalytics").Path -Parent)\queries.xml" if (!(Test-Path $xmlsource)) { Write-Warning "Xml Source is missing: queries.xml"; break; } [xml]$xdata = Get-Content -Path $xmlsource -Raw -ErrorAction Stop $queries = $xdata.queries.query Write-Verbose "imported $($queries.Count) queries" if ($GridView) { $choice = $queries | Select-Object name,type,comment | Sort-Object name | Out-GridView -Title "Inventory Queries" -OutputMode Single if ($choice) { $queries | Where-Object {$_.name -eq $choice.name} | Select-Object -ExpandProperty command | Invoke-Expression } } else { $queries } } catch { Write-Error $_.Exception.Message } } function Export-PsLogAnalyticsQuery { <# .DESCRIPTION Invokes a query against the Log Analtyics Query API. .EXAMPLE Invoke-PsLogAnalyticsQuery -WorkspaceName my-workspace -SubscriptionId 0f991b9d-ab0e-4827-9cc7-984d7319017d -ResourceGroup my-resourcegroup -Query "union * | limit 1" -New-ObjectView .PARAMETER WorkspaceName The name of the Workspace to query against. .PARAMETER SubscriptionId The ID of the Subscription this Workspace belongs to. .PARAMETER ResourceGroup The name of the Resource Group this Workspace belongs to. .PARAMETER Query The query to execute. .PARAMETER Timespan The timespan to execute the query against. This should be an ISO 8601 timespan. .PARAMETER IncludeTabularView If specified, the raw tabular view from the API will be included in the response. .PARAMETER IncludeStatistics If specified, query statistics will be included in the response. .PARAMETER IncludeRender If specified, rendering statistics will be included (useful when querying metrics). .PARAMETER ServerTimeout Specifies the amount of time (in seconds) for the server to wait while executing the query. .PARAMETER Environment Internal use only. .EXAMPLE Export-PsLogAnalyticsQuery -WorkspaceName "my-workspace-123" ` -SubscriptionId "029340293840293402934809802" ` -ResourceGroup "my-rg123" -Query "DeviceInventory_CL" .EXAMPLE Export-PsLogAnalyticsQuery -WorkspaceName "my-workspace-123" ` -SubscriptionId "029340293840293402934809802" ` -ResourceGroup "my-rg123" ` -Query "DeviceInventory_CL | where Domain_s == "CONTOSO" " ` -IncludeTabularView -IncludeStatistics -IncludeRender ` -ServerTimeOut 30 -Environment int .NOTES Adapted heavily from Eli Shlomo example at https://www.eshlomo.us/query-azure-log-analytics-data-with-powershell/ #> [CmdletBinding()] param ( [parameter(Mandatory)][string] $WorkspaceName, [parameter(Mandatory)][guid] $SubscriptionId, [parameter(Mandatory)][string] $ResourceGroup, [parameter(Mandatory)][string] $Query, [parameter()][string] $Timespan, [parameter()][switch] $IncludeTabularView, [parameter()][switch] $IncludeStatistics, [parameter()][switch] $IncludeRender, [parameter()][int] $ServerTimeout, [parameter()][string][ValidateSet("", "int", "aimon")] $Environment = "", [parameter()][string] $apiVersion = "2017-01-01-preview" ) $ErrorActionPreference = "Stop" function Get-AccessToken { $azureCmdlet = Get-Command -Name Get-AzureRMContext -ErrorAction SilentlyContinue if ($null -eq $azureCmdlet) { $null = Import-Module AzureRM -ErrorAction Stop; } $AzureContext = & "Get-AzureRmContext" -ErrorAction Stop; $authenticationFactory = New-Object -TypeName Microsoft.Azure.Commands.Common.Authentication.Factories.AuthenticationFactory if ((Get-Variable -Name PSEdition -ErrorAction Ignore) -and ('Core' -eq $PSEdition)) { [Action[string]]$stringAction = {param($s)} $serviceCredentials = $authenticationFactory.GetServiceClientCredentials($AzureContext, $stringAction) } else { $serviceCredentials = $authenticationFactory.GetServiceClientCredentials($AzureContext) } # We can't get a token directly from the service credentials. Instead, we need to make a dummy message which we will ask # the serviceCredentials to add an auth token to, then we can take the token from this message. $message = New-Object System.Net.Http.HttpRequestMessage -ArgumentList @([System.Net.Http.HttpMethod]::Get, "http://foobar/") $cancellationToken = New-Object System.Threading.CancellationToken $null = $serviceCredentials.ProcessHttpRequestAsync($message, $cancellationToken).GetAwaiter().GetResult() $accessToken = $message.Headers.GetValues("Authorization").Split(" ")[1] # This comes out in the form "Bearer <token>" $accessToken } function Get-ArmHost { param ( [string] $environment ) switch ($environment) { "" { $armHost = "management.azure.com" } "aimon" { $armHost = "management.azure.com" } "int" { $armHost = "api-dogfood.resources.windows-int.net" } } $armHost } function Build-Uri { param ( [string] $armHost, [string] $subscriptionId, [string] $resourceGroup, [string] $workspaceName, [string] $queryParams ) "https://$armHost/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/" + ` "microsoft.operationalinsights/workspaces/$workspaceName/api/query?$queryParamString" } function Get-Headers { param ( [string] $AccessToken, [switch] $IncludeStatistics, [switch] $IncludeRender, [int] $ServerTimeout ) $preferString = "response-v1=true" if ($IncludeStatistics) { $preferString += ",include-statistics=true" } if ($IncludeRender) { $preferString += ",include-render=true" } if ($null -ne $ServerTimeout) { $preferString += ",wait=$ServerTimeout" } $headers = @{ "Authorization" = "Bearer $accessToken"; "prefer" = $preferString; "x-ms-app" = "LogAnalyticsQuery.psm1"; "x-ms-client-request-id" = [Guid]::NewGuid().ToString(); } $headers } function New-ObjectView { param ($data) # Find the number of entries we'll need in this array $count = 0 foreach ($table in $data.Tables) { $count += $table.Rows.Count } $objectView = New-Object object[] $count $i = 0; foreach ($table in $data.Tables) { foreach ($row in $table.Rows) { # Create a dictionary of properties $properties = @{} for ($columnNum=0; $columnNum -lt $table.Columns.Count; $columnNum++) { $properties[$table.Columns[$columnNum].name] = $row[$columnNum] } # Then create a PSObject from it. This seems to be *much* faster than using Add-Member $objectView[$i] = (New-Object PSObject -Property $properties) $null = $i++ } } $objectView } $accessToken = Get-AccessToken $armhost = Get-ArmHost $environment $queryParams = @("api-version=$apiVersion") $queryParamString = [string]::Join("&", $queryParams) $uri = Build-Uri $armHost $subscriptionId $resourceGroup $workspaceName $queryParamString $body = @{ "query" = $query; "timespan" = $Timespan } | ConvertTo-Json $headers = Get-Headers $accessToken -IncludeStatistics:$IncludeStatistics -IncludeRender:$IncludeRender -ServerTimeout $ServerTimeout $response = Invoke-WebRequest -UseBasicParsing -Uri $uri -Body $body -ContentType "application/json" -Headers $headers -Method Post if ($response.StatusCode -ne 200 -and $response.StatusCode -ne 204) { $statusCode = $response.StatusCode $reasonPhrase = $response.StatusDescription $message = $response.Content throw "Failed to execute query.`nStatus Code: $statusCode`nReason: $reasonPhrase`nMessage: $message" } $data = $response.Content | ConvertFrom-Json $result = New-Object PSObject $result | Add-Member -MemberType NoteProperty -Name Response -Value $response # In this case, we only need the response member set and we can bail out if ($response.StatusCode -eq 204) { $result return } $objectView = New-ObjectView $data $result | Add-Member -MemberType NoteProperty -Name Results -Value $objectView if ($IncludeTabularView) { $result | Add-Member -MemberType NoteProperty -Name Tables -Value $data.tables } if ($IncludeStatistics) { $result | Add-Member -MemberType NoteProperty -Name Statistics -Value $data.statistics } if ($IncludeRender) { $result | Add-Member -MemberType NoteProperty -Name Render -Value $data.render } $result } |