/*
For a table with INT column count rows for each Range of rows.
Set to 10M rows...
*/
SELECT
MIN([KeyTable].[Key]) AS [MinKey],
MAX([KeyTable].[Key]) AS [MaxKey],
[KeyTable].[Range] * 10000000 AS [Range10M],
COUNT(*) AS [RangeRows]
FROM (
SELECT
[Key],
(([Key] - 1) / 10000000) + 1 AS [Range]
FROM [dbo].[Detail]
) AS [KeyTable]
GROUP BY [KeyTable].[Range]
ORDER BY [KeyTable].[Range]
-- Test one.
SELECT COUNT(*)
FROM [dbo].[Detail]
WHERE [Key]
BETWEEN 150000001 AND 160000000
GO
PowerShell: Get the first files in each folder for each day
$SetFileNames = @()
$TESTFiles |
Sort-Object Directory, LastWriteTime, Name |
Group-Object -Property {Get-Date $_.LastWriteTime -Format d } |
ForEach-Object {
$_.Group |
ForEach-Object -Begin {
$counter = 0
$FirstFile = $_
# Grab the files for each folder which are the first file of each day.
$SetFileNames += $FirstFile.Group[0]
} -Process {
$counter++;
# Do work here
}
}
$SetFileNames
PowerShell: Get the most recent file in a path
This answers what was the most recent change in a folder and subfolder.
gci $Path -Recurse |?{-not $_.PsIsContainer} | sort LastWriteTime | select -last 1
PowerShell: Sum of selected file sizes
$FilesToDelete = Get-ChildItem -Path "$TopPath\*" -Include *.safe, *.bak, *.trn, *.log -Recurse ($FilesToDelete |Measure-Object -property length -sum).Sum / 1MB
PowerShell: Get list of registered SQL Servers
No reason to maintain more than one list of SQL Servers when you can query SSMS’s Registered Servers.
Import-Module SQLPS -DisableNameChecking
$SQLInstanceProd = @()
$ServerGroup = 'PRODUCTION'
$SQLPath = "SQLSERVER:\sqlregistration\Database Engine Server Group\PROD*\"
$SQLInstanceProd += @(get-childitem $SQLPath -Recurse).Name | ?{$_ -notmatch '\d\d\d\d'} | % {
$MyPSObject = New-Object PSObject -Property @{
SQLInstance = $_
ServerGroup = $ServerGroup;
}
$MyPSObject
}
$SQLInstanceProd
Putting that logic into Get-RegisteredServers I populate the following list in my Profile.
$AllServersList = (Get-RegisteredServers).SQLInstance
$AllComputersList = $AllServersList | %{ $_.split('\')[0] }
$SQLInstanceDev = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'DEV'}).SQLInstance
$SQLInstanceTest = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'TEST'}).SQLInstance
$SQLInstanceProd = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'PROD'}).SQLInstance
PowerShell: Get Windows Update scheduled date
Look for WU install dates for all servers.
$DayOfWeek = @{n='DayOfWeek';e={ ($_.LastRunTime).DayOfWeek }}
$Computer = @{n='Computer';e={$ComputerName}}
$TimeFromNow = @{n='HoursFromNow';e={"{0}" -f (New-TimeSpan -Start (Get-date) -End $_.NextRunTime) } }
$SQLInstanceDev + $SQLInstanceTEST + $SQLInstancePROD | %{
$ComputerName = $_.Split('\')[0]
Write-Host $ComputerName
# Actually get STATE for Windows Updates for each computer
.\adhoc\Get-ScheduledTask.ps1 -ComputerName $ComputerName |
Where-Object {$_.Name -in 'ProgramDataUpdater','AUScheduledInstall' } |
Select-Object $Computer, $TimeFromNow, Name, State, Enabled, LastRunTime, NextRunTime, ComputerName, $DayOfWeek
} | Out-GridView
PowerShell: List profiles in USE
# List profiles in USE
$PROFILE | get-member -MemberType noteproperty|
%{
$ProfileName = $_.Name;
$ProfilePath = ($_.Definition).Split('=')[1]
if (test-path $ProfilePath) {
write-host "$ProfileName `t$ProfilePath"
}
}
PowerShell: What files changed for DaysAgo?
Pass in multiple paths.
Create a variable for Owner, using Get-Acl.
Output to GridView so we can review all columns.
$PathList = @('\\Shared\1','\\Shared\2', '\\Shared\3')
$DaysAgo = 2
$Owner = @{
Name = 'File Owner'
Expression = { (Get-Acl $_.FullName).Owner }
}
Get-ChildItem -Path $PathList -Recurse -Filter '*.sql' |
? { $_.LastWriteTime -gt (Get-Date).AddDays(-$DaysAgo) } |
sort LastWriteTime |
Select LastWriteTime, $Owner, Name, Directory | Out-GridView