jpomfret

<#
.SYNOPSIS
    Function to get a list of Quarterfinal scores from CrossFit Games API for a specific affiliate

.DESCRIPTION
    Function to get a list of Quarterfinal scores from CrossFit Games API for a specific affiliate

.PARAMETER affiliateName
    The name of the affiliate to filter by, e.g. 'CrossFit Southampton'

jpomfret

## Using PSHTML to create great looking email reports
## Blog post with more information: https://jesspomfret.com/pshtml-email-reports

## Email details
$emailTo = 'me@jesspomfret.com'
$emailFrom = 'reports@jesspomfret.com'
$emailSubject = ('Authors: {0}' -f (get-date -f yyyy-MM-dd))
$smtpServer = 'smtp.server.address'

## Query details

jpomfret

$perms = import-excel -Path C:\Temp\spreadsheetName.xlsx -WorksheetName permissions

<#
The spreadsheet should have a worksheet named permissions with the following columns:

Username    Server  Database     Permissions
JessUser    mssql1  database1    db_datareader
UserName2   mssql2  database2    db_datareader, db_datawriter, execute
#>

jpomfret

# some variab;es
$resourceGroupName = 'rgTest'
$location = 'uksouth'
$vmName = 'rg-sm'
$snapshotName = ('snappyshot-SomethingUseful')

## take a snapshot of the disk
$vm = Get-AzVM -ResourceGroupName $resourceGroupName -Name $vmName
$currentOsDisk = $vm.StorageProfile.OsDisk.Name
$snapshot =  New-AzSnapshotConfig -SourceUri $vm.StorageProfile.OsDisk.ManagedDisk.Id -Location $location  -CreateOption copy

jpomfret

# Login to https://connect.garmin.com/ & Navigate to 'Health Stats > Weight > 1 Year' 
# at the top there is an export, that'll get you the csv.
# but the csv is in a poor format, with the date on the row above the data - this will reformat it for you

$weight = import-csv 'C:\Users\JessPomfret\Downloads\Weight.csv'

$counter = 0

$final = $weight.foreach{
    if ( ($counter % 2) -eq 0) {

jpomfret

## I'd like to copy logins/jobs but also script them out for source control

# get logins from source
$logins = Get-DbaLogin -SqlInstance $dbatools1

# first export, then copy
$logins | Export-DbaLogin -FilePath ('Export\Logins_{0}.sql' -f (get-date -f 'yyyyMMdd_hhmmss'))
$logins | Copy-DbaLogin -Destination $dbatools2

jpomfret

$PrimaryInstance = 'Sql1'
$SecondaryInstance = 'Sql2'
 
$SqlInstance = $PrimaryInstance, $SecondaryInstance
$Database = 'DbName'
 
$results = Get-DbaHelpIndex -SqlInstance $SqlInstance -Database $Database
 
$export = @()
 

jpomfret

$labname = 'FailOverLab1'
New-LabDefinition -Name $labname -DefaultVirtualizationEngine HyperV -VmPath C:\AutomatedLab-VMs\VMs

Add-LabDomainDefinition -Name pomfret.com -AdminUser Install -AdminPassword Somepass1

Set-LabInstallationCredential -Username Install -Password Somepass1

Add-LabVirtualNetworkDefinition -Name $labname -AddressSpace 192.168.50.0/24

$PSDefaultParameterValues = @{

jpomfret

# Define the Lab, specify the virtualization engine and the path for the VMs to live
New-LabDefinition -Name SQLLab -DefaultVirtualizationEngine HyperV -VmPath C:\AutomatedLab-VMs\VMs
 
# Configure the Lab Network
Add-LabVirtualNetworkDefinition -Name SQLLab
Add-LabVirtualNetworkDefinition -Name 'Default Switch' -HyperVProperties @{ SwitchType = 'External'; AdapterName = 'Wi-Fi' }
 
$netAdapter = @()
$netAdapter += New-LabNetworkAdapterDefinition -VirtualSwitch SQLLab
$netAdapter += New-LabNetworkAdapterDefinition -VirtualSwitch 'Default Switch' -UseDhcp

jpomfret

###########
## SETUP ##
###########

## get some data into xlsx files :)
$svr = Connect-DbaInstance -SqlInstance mssql1 -SqlCredential (Get-Credential)

foreach ($tbl in (Get-DbaDbTable -SqlInstance $svr -Database AdventureWorks2017 | where schema -eq 'sales' | select -first 10)){
    Invoke-DbaQuery -SqlInstance $svr -Database AdventureWorks2017 -Query "Select top 10 * from $($tbl.Schema).$($tbl.name)" |
    Export-Excel -Path "c:\temp\excel\$($tbl.name).xlsx"  -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors