MeasureTrace.Database.psm1
|
Set-StrictMode -Version Latest <# .Synopsis Adds a given Measured Trace to a database .DESCRIPTION Typically used indirectly via call to Measure-MtDbNewTracesFromFolder .EXAMPLE dir c:\MyTracesFolder | Measure-Trace | Add-MtdbTrace -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' #> function Add-MtDbTrace { param( [Parameter(Mandatory=$true)] [string] $ConnectionString , [Parameter(Mandatory=$true, ValueFromPipeline=$true)] [MeasureTrace.TraceModel.Trace[]]$MeasuredTrace ) begin{ $repository = Connect-MtDbRepository -ConnectionString $ConnectionString $repository.Database.EnsureCreated() } process{ foreach($trace in $MeasuredTrace){ $repository.SaveTraceAndMeasurements($trace) } } end{ $repository.Dispose() } } <# .Synopsis Creates a connection to the MeasureTrace database, attempting to provision as needed .DESCRIPTION Typically used indirectly via call to Measure-MtDbNewTracesFromFolder .EXAMPLE Connect-MtDbRepository -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' #> function Connect-MtDbRepository { param( [Parameter(Mandatory=$true)] [string]$ConnectionString ) $r = New-Object MeasureTrace.Database.Repository -ArgumentList $ConnectionString try{ $r.TryToProvisionDatabase() } catch{} $r } <# .Synopsis Determine whether a specific trace file is already represented in a DB .DESCRIPTION Typically used indirectly via call to Measure-MtDbNewTracesFromFolder .EXAMPLE Compare-MtDbTraceFileStatus -Path d:\MyEtlTraces -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' #> function Compare-MtDbTraceFileStatus { param( ## Path to a trace file [Parameter(ValueFromPipelineByPropertyName=$true,Mandatory=$true)] [Alias('Path')] [string[]]$FullName, ## For connection to database. See Example section [string]$ConnectionString ) begin{ $repo = Connect-MtDbRepository -ConnectionString $ConnectionString } process{ foreach($pathX in $FullName){ $packageFileName = Split-Path -Path $pathX -Leaf [pscustomobject]@{ PackageFileNameFull = $pathX PackageFIleName = $packageFileName IsInDatabase = $repo.IsTraceAlreadyInDb( $packageFileName ) } } } } <# .Synopsis Determine which traces in a folder are already represented in a DB .DESCRIPTION Compares a given folder of traces against a database of parsed traces. Returns an object per trace which describes the status. Typically used indirectly via call to Measure-MtDbNewTracesFromFolder .EXAMPLE Compare-MtDbFolderVsRepository -Path d:\MyEtlTraces -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' #> function Compare-MtDbFolderVsRepository{ param( ## Path to a folder containing ETL traces [Parameter(ValueFromPipelineByPropertyName=$true,Mandatory=$true)] [Alias('Path')] [string[]]$FullName, ## For connection to database. See Example section [string]$ConnectionString, [string[]]$Filter = @("*.etl","*.zip") ) foreach($folder in $FullName){ foreach($filterX in $Filter){ Get-ChildItem -Path $folder -Filter $filterX -Recurse | Compare-MtDbTraceFileStatus -ConnectionString $ConnectionString } } } <# .Synopsis Process traces from a folder into MeasuredTrace representation in a database .DESCRIPTION Compares a given folder of traces against a database of parsed traces. For traces which are not yet reflected in the db it attempts to Measure them and export Measurements to the database .EXAMPLE Measure-MtDbNewTracesFromFolder -Path d:\MyEtlTraces -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' .EXAMPLE Measure-MtDbNewTracesFromFolder -Path d:\MyEtlTraces -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' -MaxCount 100 -Parallel .EXAMPLE ## Continuous ingestion as traces arrive while( $true ){ Measure-MtDbNewTracesFromFolder -Path d:\MyEtlTraces -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' -MaxCount 100 -Parallel Start-Sleep -Seconds 60 } #> function Measure-MtDbNewTracesFromFolder{ param( ## Path to a folder containing ETL traces [Parameter(ValueFromPipelineByPropertyName=$true,Mandatory=$true)] [Alias('Path')] [string[]]$FullName, ## For connection to database. See Example section [string]$ConnectionString = 'server=(localdb)\MsSqlLocalDb;database=MeasureTrace;integrated security=true', ## Max number of new traces to process on this pass [int]$MaxCount = 50, ## Enables parallel processing via RSJobs [switch]$Parallel ) begin{ $batchLabel = [Guid]::NewGuid().ToString() } process{ Compare-MtDbFolderVsRepository -Path $FullName -ConnectionString $ConnectionString | Where-Object {-not $_.IsInDatabase} | Select-Object -First $MaxCount | Foreach-Object{ if(-not $Parallel){ Measure-Trace -Path $_.PackageFileNameFull | Add-MtDbTrace -ConnectionString $ConnectionString } else{ Start-RSJob -ModulesToImport $psscriptroot -Throttle $ParallelThrottle ` -Batch $batchLabel -InputObject $_ -ScriptBlock { Measure-Trace -Path $_.PackageFileNameFull | Add-MtDbTrace -ConnectionString $Using:ConnectionString } } } } end{ Get-RSJob -Batch $batchLabel | Wait-RSJob -ShowProgress Get-RSJob -Batch $batchLabel | Receive-RSJob } } <# .Synopsis Create task to process traces from a folder into MeasuredTrace representation in a database .DESCRIPTION Via scheduled task-- compares a given folder of traces against a database of parsed traces. For traces which are not yet reflected in the db it attempts to Measure them and export Measurements to the database .EXAMPLE Register-MtDbMeasurementTask -Path d:\MyEtlTraces -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' .EXAMPLE Register-MtDbMeasurementTask -Path d:\MyEtlTraces -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' -MaxCount 100 -Parallel .EXAMPLE ## Continuous ingestion as traces arrive while( $true ){ Measure-MtDbNewTracesFromFolder -Path d:\MyEtlTraces -ConnectionString 'server=localhost;database=MyTraces;integrated security=SSPI' -MaxCount 100 -Parallel Start-Sleep -Seconds 60 } #> function Register-MtDbMeasurementTask{ param( ## Path to a folder containing ETL traces [Parameter(ValueFromPipelineByPropertyName=$true,Mandatory=$true)] [Alias('Path')] [string[]]$FullName, ## For connection to database. See Example section [Parameter(Mandatory=$true)] [string]$ConnectionString = 'server=(localdb)\MsSqlLocalDb;database=MeasureTrace;integrated security=true', ## Max number of new traces to process on each pass [int]$MaxCount = 50, ## Enables parallel processing via RSJobs [switch]$Parallel ) $taskActionArgs = @( '-ExecutionPolicy' 'Bypass' '-Command' '"&{Measure-MtDbNewTracesFromFolder -Path PATHTOKEN -ConnectionString CONNECTIONSTRINGTOKEN }"' ) $taskPath = '\MeasureTrace.Database\' $taskName = 'Measure-MtDbNewTracesFromFolder' Get-ScheduledTask -TaskPath $taskPath -TaskName $taskName -ErrorAction SilentlyContinue | Unregister-ScheduledTask $pathsToWatchMergedToken = $FullName -replace '^',"'" -replace '$',"'" -join "," $taskActionArgs[3] = $taskActionArgs[3] -replace 'PATHTOKEN',$pathsToWatchMergedToken -replace 'CONNECTIONSTRINGTOKEN',("'" + $ConnectionString + "'") $ta = New-ScheduledTaskAction -Execute '%windir%\system32\WindowsPowerShell\v1.0\powershell.exe' -Argument ($taskActionArgs -join " ") $tt = New-ScheduledTaskTrigger -Daily -At (Get-Date -Hour 0 -Minute 1) -DaysInterval 1 $ts = New-ScheduledTaskSettingsSet -Compatibility Win8 -MultipleInstances IgnoreNew $st = Register-ScheduledTask -TaskName $taskName -TaskPath $taskPath -User 'NT AUTHORITY\NetworkService' -Action $ta -Trigger $tt -Settings $ts -RunLevel Highest $st.Triggers[0].Repetition.StopAtDurationEnd = $false $st.Triggers[0].Repetition.Interval = 'PT2M' $st.Triggers[0].Repetition.Duration = 'P1D' Set-ScheduledTask -InputObject $st } |