Azure–iterate resources in all tenants for GDPR

GDPR means that we now have a policy for all data to be in UK or Europe.

So – to find where all our azure resources are

1. Install the azure PowerShell toolkik (https://docs.microsoft.com/en-gb/azure/azure-resource-manager/powershell-azure-resource-manager)

2. Run the script below to check the location of your resources are in the list of allowed locations.

# login to azure this should request secure credentials
Login-AzureRmAccount
# get a list of tenants / subscriptions
$allowedlocations = 'northeurope', 'westeurope' , 'francecentral' , 'francesouth', 'ukwest' , 'uksouth', 'germanycentral' , 'germanynortheast'
$subscriptions = Get-AzureRmSubscription
foreach($sub in $subscriptions) {
     Select-AzureRmSubscription -Subscription $sub.Id
     $resources = Get-AzureRmResource
     $resources.where({ $_.Location  -notin $allowedlocations }) 
}

Grep for PowerShell

Get-Childitem -Path ".\" web.config -Recurse | Select-String -pattern 'dardaudit' –SimpleMatch

Works for me!

Powershell endpoints

For old school .svc and .asmx

foreach($dir in ("dir1",”dir2”)) {
    $files = Get-ChildItem -Path  D:\AppWebSites\$dir -Recurse -Include ('*.asmx','*.svc')
    $files
}

ODBC Settings multuple servers

You need to have some wdac sdk on the server and each machine that you connect to.

$credential = Get-Credential
foreach ($server in @("ESS027521","ESS026412","ESS026488","ESS026191")) {
    $session = New-CimSession -ComputerName $server -Credential $credential
    $odbc = Get-OdbcDsn  -CimSession $session
    $server + ":"
    $odbc        
}

Powershell file compare.

Simple script for comparing files in two directories.

param (
       [Parameter(Mandatory=$true)][string]$originalDir,
        [Parameter(Mandatory=$true)][string]$newDir
)

$fso = Get-ChildItem -Recurse -path $originalDir
foreach($file in $fso.Name) {
echo comparing $originalDir\$file to $newDir\$file
Compare-Object -ReferenceObject $(Get-Content $originalDir\$file)  -DifferenceObject $(Get-Content $newDir\$file) -IncludeEqual
}

Will compare line by line the files in the originalDir with those in newDir with the same name. 

• Probably breaks if the file isn’t in newDir
• Doesn’t check for extra files in newDir.

Sprint 1 Retrospective 1 – one hell of an overrun

_Overview

I’ll cover all that happened in subsequent blog entries – but it took three hours to accomplish the one hour goal that I set in Sprint 1.
Event though I’d never used VS DB Projects before, I created the project and schema and got it deployed in about 25 minutes which I thought was fair enough.  DB project was pretty easy – as I expected it to be.  To be honest I just created the tables with the tool and then typed in the columns, constraints etc. in the SQL window!
It seems to create a local database too – I’ll have to work out where that is for future testing as I now have live data.
Then came the PowerShell task.
I decided fairly late on to parse the excel rather than csv as I thought it would get rid of any comma separation issues.  A couple of google searches showed it was relatively easy.
It was – but using .Value instead of .Value2 (why?) cost me some time then building sql by concatenating strings was a stupid idea and I still had to work out how to cope with special characters like the ‘ which is prevalent in O’Tool, O’Connor etc.

Lessons

1. Really need to break down the user stories to tiny bits!  Not sure how to deliver useable features at the same time though.
2. Do the right thing – I new I should be creating stored procs
1. they’d certainly add valiue down the line
2. Strong typing is always the way to go
3. Don’t do new stuff in a limited time – I’d never parsed powershell with excel and was caught out by lack of experience.
4. Check for extra features/gold plating.  I added in the Organiser table because it’s in my schema but had no date for it or defined columns.  I then had to work out how to insert a row into a database with just an identity column!

The Script

Not much to publish – I’ll get the schema out – but here is the powershell that I used.  It’s probably full of holes!

#
# Script.ps1
#
function parseExcel([String] $fileName){
 $excel=new-object -com excel.application
 $wb=$excel.workbooks.open($fileName)
 $sh = $wb.Sheets.Item(1)
 $startrow = 2
 $endrow = 16#Connection and Query Info

$serverName="uj6arxo0jh.database.windows.net" 
$databaseName='AudaxDB' 
$connString = "Server=$serverName;Database=$databaseName;uid=stiona;pwd=2difficult4u2c#" 
$conn = new-object System.Data.SqlClient.SqlConnection $connString 
$conn.Open()
try{



 for($i = $startrow; $i -le $endrow; $i++) {
$Name = $sh.Cells.Item($i,1).Value2
$Surname = $sh.Cells.Item($i,2).Value2
[String]$Address = $sh.Cells.Item($i,3).Value2
$Tel = $sh.Cells.Item($i,4).Value2
$email = $sh.Cells.Item($i,5).Value2
$Club = $sh.Cells.Item($i,6).Value2
$CI = $sh.Cells.Item($i,7).Value2
$ClubNo = $sh.Cells.Item($i,8).Value2();
$EC = $sh.Cells.Item($i,9).Value2
$ECTel = $sh.Cells.Item($i,10).Value2
$PaidE = $sh.Cells.Item($i,11).Value2
$PaidS = $sh.Cells.Item($i,12).Value2 

$Command =  $Command = New-Object System.Data.SQLClient.SQLCommand

    $Command.Connection = $conn

    $Command.CommandText = "INSERT INTO tblRider VALUES( '" + $Name  + "'" +
    ",'" + $Surname.Replace("'","''") + "'" +
    ",'" + $Address.Split(',')[0] + "'" +
    ",'" + $Address.Split(',')[1] + "'" +
    ",'" + $Address.Split(',')[2] + "'" +
    ",NULL" + 
    ",NULL" +
    ",'" + $Tel + "'" +
    ",'" + $Club + "'" +
    ",'" + $ClubNo + "'" +
    ",'" + $EC + "'" +
    ",'" + $ECTEL + "'" +
        ")`n`r" + 
        "SELECT @@IDENTITY `n`r"
        
        
        $Command
        [int]$ID = $Command.ExecuteScalar()
        $ID
$Command =  $Command = New-Object System.Data.SQLClient.SQLCommand

    $Command.Connection = $conn
    $Command.CommandText = 
       "INSERT INTO tblEventParticipant VALUES( " +  $ID  +",1,'" + $PaidS + "','" + $PaidE +"')" 
    $Command
    $Command.ExecuteScalar()


if($CI -ne $null -and $CI.Trim() -ne '') { 
$Command =  $Command = New-Object System.Data.SQLClient.SQLCommand

    $Command.Connection = $conn
    $Command.CommandText = 
       "INSERT INTO tblLicense  VALUES( 2016, " +  $ID  + ",'" + $CI +"')" 
    $Command
        $Command.ExecuteScalar()

    }

 }
}
Finally
{
$conn.Close()
}
$wb.Close()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

 }

 parseExcel("C:\Users\stuartm\OneDrive\Documents\Audax\A300_2016\A30002016Entrants.xlsx")

Powershell delete all recycle bins

Get-ChildItem "D:\`$Recycle.bin\" -Recurse -Force | Remove-Item -Force -Recurse

Blatantly stolen from http://serverfault.com/questions/330776/clear-the-recycle-bin-for-all-users-in-windows-server-2008-r2