Skip to content

Instantly share code, notes, and snippets.

View nanoDBA's full-sized avatar

nanoDBA nanoDBA

View GitHub Profile
@nanoDBA
nanoDBA / QueryStore Export to PivotTable using sp_QuickieStore.ps1
Created January 31, 2024 01:14
This PowerShell script allows users to export data from SQL Server Query Store to a PivotTable. The script utilizes the sp_QuickieStore stored procedure from Erik Darling ( https://erikdarling.com/sp_quickiestore/ ) to retrieve performance data from the Query Store and then formats it into a PivotTable for additional analysis and visualization.
# create an array of SQL Server instances
$allInstances = @('server1', 'server2', 'server3')
# Loop through each SQL server instance
foreach($sqlInstance in $allInstances) {
# Output the current SQL server instance
Write-Output "`$sqlInstance: $sqlInstance"
# Execute a query on the current SQL server instance and store the results
@nanoDBA
nanoDBA / Get-SqlAgentHistoryDetailsConsole.ps1
Created March 23, 2023 16:37
Console Output RED/GREEN failures for SQL Agent job history, multiple key property sorts. Attempting to imitate the output of the SQL Server Management Studio Job History window in a console.
# Console Output RED/GREEN failures for SQL Agent job history, multiple key property sorts
# attempting to imitate the output of the SQL Server Management Studio Job History window
$paramHash = @{
SqlInstance = 'YOURSERVER01','YOURSERVER02' # comma separated list of SQL Server instances
StartDate = "$(((Get-Date).AddDays(-.1) ))" # 0.1 days ago - ARE YOU SURE? This does not mean 1 day. It means 2.4 hours ago.
# StartDate = "$(((Get-Date).AddDays(-90) ))" # 90 days ago
EndDate = "$((Get-Date ))" # now
Job = 'Some SQL Agent Job Name goes here' # name of SQL Agent job goes here
ExcludeJobSteps = $false
@nanoDBA
nanoDBA / Proposed SQL Server Patching Approach.txt
Last active February 26, 2023 19:37
An approach I've been using for the last 4 years or so to deploy SQL Server Cumulative Updates
Proposed SQL Server Patching Approach
We do not patch to the absolute latest cumulative update(CU).
Rather, we strive to patch to the cumulative update(CU) that
was released without any intermediate releases(such as hotfixes)
between it and the most recent CU. Basically N-1 as long as
there aren't any hotfixes. If there were hotfixes then we'll wait
until two CU subsequent releases have occurred
without hotfixes in between the two to deploy the CU.
@nanoDBA
nanoDBA / Remove-SoundAndStillness.ps1
Last active February 18, 2023 06:05
FFmpeg Remove video frames without motion and remove audio track
$sourceFrameRate = 10.74
$filename = "C:\Users\kilroy\2023-02-17_17-37-36_boxstarter_2_of_2.mp4"
$newFilename = "$(dir $filename | foreach { Join-Path -Path $_.Directory -ChildPath $_.BaseName })_remove_nonmotion_mute_audio.mp4"
ffmpeg.exe -i "$($filename)" -vf "select=gt(scene\,0.0002),setpts=N/($($sourceFrameRate)*TB)" -an "$($newFilename)"
<# -an remove audio #>
<# -vf "select=gt(scene\,0.001),setpts=N/(<framerategoeshere>*TB)" #>
<# https://superuser.com/questions/984841/ffmpeg-remove-parts-without-motion/1029175#1029175
<#
.SYNOPSIS
BoxStarter script to configure Windows 10 development PC.
.DESCRIPTION
You might need to set:
Set-ExecutionPolicy RemoteSigned
Set-ExecutionPolicy Unrestricted
Set-ExecutionPolicy Bypass
@nanoDBA
nanoDBA / Get-Process-SSMSUsers.ps1
Created February 6, 2023 22:50
Shows users currently using process Ssms.exe in a loop executing every 55 minutes
do {
$ssms = Get-WmiObject -Class Win32_Process -Filter "name = 'Ssms.exe'"
<# Timer/time bomb stub #> $now = Get-Date
$timespan = New-TimeSpan -Start $now -End (Get-Date).AddMinutes(55) # '20:25') #'2077-10-09 19:30'
Write-Host -ForegroundColor magenta "SSMS active user count: $(($ssms.GetOwner()).User.count)"
Write-Host -ForegroundColor magenta "SSMS active users: $(($ssms.GetOwner()).User)"
Write-Host -ForegroundColor magenta "Sleeping until $((Get-date).AddSeconds($timespan.TotalSeconds)) ..."
Start-Sleep -Seconds $timespan.TotalSeconds
}
while ( ($ssms.GetOwner()).User.count -GT 0 )
@nanoDBA
nanoDBA / Get-Command_Line_History_for_all_users.ps1
Last active January 25, 2023 13:49
Loop through PSReadline histories for all user profiles and copy them to the clipboard
### Get Command Line History for all users
# source: https://gist.github.com/nanoDBA/48f2cd1eab1e4433a97bd106e0dac08d
$dirs = (Get-ChildItem -Directory (Split-Path $env:USERPROFILE)).FullName | Sort-Object
$allHistories = foreach($profileDir in $dirs){
Write-Output "### $profileDir"
$historySavePath = "$profileDir\AppData\Roaming\Microsoft\Windows\PowerShell\PSReadLine\ConsoleHost_history.txt"
if(!(Test-Path ($historySavePath) )) {
Write-Output "### $historySavePath does not exist"
}
@nanoDBA
nanoDBA / Get-Daylight.ps1
Created December 22, 2022 14:04
How many minutes of daylight are there in a given day?
# Yesterday was the winter solstice, so I am looking forward to more daylight!
# https://stackoverflow.com/questions/63236774/powershell-return-only-sunrise-sunset-time/63237047#63237047
# using API from https://sunrise-sunset.org/api
$lat = 35.787743 ; $long = -78.644257 # substitute desired latitude/longitude
# date range: 2 days ago until tomorrow and Loop de Loop using foreach
-2..1 | foreach {
$Date = (Get-Date).AddDays($_) | Get-Date -Format "yyyy-MM-dd"
$Daylight = (Invoke-RestMethod "https://api.sunrise-sunset.org/json?lat=$lat&lng=$long&formatted=0&date=$Date").results
# DANGER # Server Core? ---OPTIONAL--- Setting PowerShell as the Default Shell Manually
#If you've only got one server, a couple of servers or maybe your Server Core machines are workgroup members so you can't use Group Policy and if any of these are true, the manual method is for you. It's a simple PowerShell one-liner:
# source: https://richardjgreen.net/setting-powershell-default-shell-server-core/
Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon' -Name Shell -Value 'PowerShell.exe -NoExit'
<#what else has been run as this user? #>Get-Content (Get-PSReadlineOption).HistorySavePath | Set-Clipboard