Closed potatoqualitee closed 4 years ago
Something like this
Function Move-AuditFile { [cmdletbinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeline)] [System.IO.FileInfo]$File, [Parameter(Mandatory)] [string]$ServerName ) process { foreach ($f in $file) { $timestamp = (Get-date -Format yyyyMMddHHmm) $shortname = $f.basename $ext = $f.Extension.TrimStart(".") $filename = "$servername-$shortname-$timestamp.$ext" $f | Move-Item -Verbose -Destination "\\fileserver\sql\audit\$filename" -ErrorAction SilentlyContinue } } } foreach ($server in $servers) { $results = $files = @() $filepath = (Get-DbaDefaultPath -SqlInstance $server).Data $files += Get-DbaFile -SqlInstance $server -Path $filepath | Where FileName -match '.sqlaudit' $files += Get-DbaFile -SqlInstance $server -Path "$filepath\STIG" | Where FileName -match '.sqlaudit' $servername = $server -Replace '\\', '$' foreach ($file in $files) { $filename = Split-Path -Path $file.FileName -Leaf if ($filename -match '_') { $results += $file } } if ($results) { if ($server -eq $env:COMPUTERNAME) { Get-ChildItem -Verbose $results.Filename | Sort-Object LastWriteTime -Desc | Select -Skip 1 | Move-AuditFile -ServerName $servername } else { Get-ChildItem -Verbose $results.RemoteFilename | Sort-Object LastWriteTime -Desc | Select -Skip 1 | Move-AuditFile -ServerName $servername } } } # do it again $audits = Get-ChildItem \\fileserver\sql\audit\*.sqlaudit foreach ($audit in $audits) { try { $basename = $audit.basename Compress-Archive $audit.FullName -DestinationPath "\\fileserver\sql\audit\$basename.zip" -CompressionLevel Optimal -Verbose -Update Remove-Item $audit -Verbose } catch { Write-Warning $_ continue } }
done 🎉
Something like this