Tag Archives: Powershell

PowerShell: backup your SQL Agent Jobs

Posted on by
Reply

I needed to backup some SQL Agent Jobs. I decided to save them to a file, but I wanted to schedule this (in the event they might change).

Below the PowerShell Script I used


[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$serverInstance = "."

$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverInstance

$jobs = $server.JobServer.Jobs | where-object {$_.category -eq "[your category]"}

if ($jobs -ne $null)
{

ForEach ( $job in $jobs )
{
$FileName = "d:\backup\SQLjobs\" + $job.Name + ".sql"
$job.Script() | Out-File -filepath $FileName
}
}

PowerShell: IP Address Details

Posted on by
3

It irritates me that when I execute the command “IPConfig” to get my current DHCP IP Address, I get a large list of all kind of adapters in which I am not interested. I then have to scroll up in the Command Prompt windows to find my IP Address.

Command Prompt

As a project of my own to get to learn PowerShell more, I decided to make a PowerShell Function which would give me the current IP Address. As I went along, I added an extra feature which displayed my current external IP Address.

Below is the PowerShell Function:

Function GetMyIP
{
$NetworkAdapter = gwmi win32_networkadapterconfiguration | where {$_.DHCPEnabled -and $_.DNSDomain -ne $null}
$SourcePage = "href="http://automation.whatismyip.com/n09230945.asp">http://automation.whatismyip.com/n09230945.asp"
$WebClient = new-object System.Net.WebClient
$ExternalIP = $WebClient.downloadString($SourcePage)

Write-host "IP Address IPV4: "$networkadapter.IPAddress[0]
Write-host "IP Address IPV6: "$networkadapter.IPAddress[1]
Write-host "Gateway Address:" $Networkadapter.DefaultIpGateway
write-host "External IP Address:" $ExternalIP
}

PowerShell: Count Files in Folder and write to file

Posted on by
Reply

For my own backlog:

$subfolders = Get-childitem <path to folder> -recurse | where {$_.psIsContainer}
foreach ($subfolder in $subfolders)
{
$filecount = (dir $subfolder.fullname  | where {$_.GetType() -match "fileInfo"}).count
if  ($filecount -ne $null)
    {
        write-output "$($subfolder.fullname) `t$($filecount)" | Out-File $resultfile -append
    }
}

BPOS: Excluding Users from Directory Synchronization

Posted on by
1

In BPOS you can use the Directory Synchronization Tool to synchronize your on-premise AD users to Microsoft Online (BPOS).
You might not know that you can exclude users from synchronizing to your online environment. For this to work you have to create a xml file with the users GUID specified inside.

The xml file has to be in the following format:

<?xml version="1.0" encoding="utf-8"?>
<DirectorySyncFilters>
<ExcludedDN>CN={9030057d-d403-4590-8c3b-a643fc36df3d}</ExcludedDN>
<ExcludedDN>CN={792f1e02-4528-48ba-a2bf-695b3916fc05}</ExcludedDN>
……..(for every user, 1 excludeDN node)
</DirectorySyncFilters>

When you have a lot of users that you don’t want to sync, creating this XML file can be a pain in the ***.
With the following powershell, this xml file will be created for you.

Add-Content "C:\test.xml" ‘<?xml version="1.0" encoding="utf-8"?>’
Add-Content "C:\test.xml" ‘<DirectorySyncFilters>’
$users =  get-aduser –SearchBase "OU=SomeOU,DC=contoso,DC=com" -filter *
foreach ($user in $users) {
$String =  "<!–"+$user.Name+"–> <ExcludedDN>CN={"+ $user.objectGUID +"}</ExcludedDN>"
Add-Content "C:\test.xml" $String
}
Add-Content "C:\test.xml" ‘</DirectorySyncFilters>’

Powershell: Executing a SQL Query and displaying results

Posted on by
Reply
 
$SqlQuery = "<Select Query>"

#Open a SQL Connection
$SQLconnection = New-Object system.Data.SqlClient.SqlConnection
$SQLconnection.Connectionstring = "<ConnectionString>"
$SQLconnection.Open()

$SQLCommand = New-Object system.Data.SqlClient.SqlCommand
$SQLadapter = New-bject System.Data.SqlClient.SqlDataAdapter $sqlcommand
$SQLdataset = New-Object System.Data.DataSet

$SQLCommand.CommandText = $SqlQuery
$SQLCommand.Connection = $sqlconnection

$SQLadapter.Fill($SQLdataSet) | Out-Null
$SQLdataset.Tables[0] | ft

$SQLconnection.Close()

Backup solution using PowerShell and Amazon S3

Posted on by
1

The company I work for has some Amazon EC2 Instances. These instances are running all kinds of custom developed software and some databases.
We wanted to create some kind of backup solution for these and sql databases and the software.

Here are my requirements:

  • Backup solution must be done to some kind of storage and not local on the server
  • Backup solution must run automatically (Scheduled)

Optional requirements:

  • files must be zipped
  • databases must be backupped from within script
    reason: so I am not depended of a sql backup that has to be run first

After some research I came to the conclusion that I am going to be using PowerShell and that I will do my backup to a S3 storage bucket. I found a nice utililty named Cloudberry S3 Explorer which had some PowerShell Snap-in I could use. They even had an example script I could use as the base of my script.

To get the Cloudberry S3 Explorer powershell snap-in working (I did not wanted to install the tool on the server), follow these steps:

  • Install Cloudberry S3 Explorer somewhere and copy all dll, xml and ps1xml files to a directory on your server
  • Set the powershell execution policy to unrestricted by executing the command set-executionpolicy unrestricted
  • From within the directory where you copied the dll, xml and ps1xml files run the following command:
    C:\Windows\Microsoft.NET\Framework\v2.0.50727\installutil.exe –I  CloudBerryLab.Explorer.PSSnapIn.dll
    In x64, I suggest you also use: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\InstallUtil.exe CloudBerryLab.Explorer.PSSnapIn.dll
    (as you see, you need the     .net framework installed)

After some coding I had the following script (I did not implement the optional requirements just yet):

#declare variables
$SourceFolder = "D:\backup"
$key = "<your access key id>" 
$secret = "<your secret access key>"
$destBucket = "<name of your s3 bucket>"

#load CloudBerryLab PSSnapin

Add-PSSnapin CloudBerryLab.Explorer.PSSnapIn

$s3 = Get-CloudS3Connection -Key $key -Secret $secret
$destination = $s3 | Select-CloudFolder -Path $destBucket
$source = Get-CloudFilesystemConnection | Select-CloudFolder $SourceFolder
$source | Copy-CloudSyncFolders $destination -DeleteOnTarget -IncludeSubFolders

Execute it, and you will see that all files in the $SourceFolder will be copied to you $destbucket. As we are using the Copy-CloudSyncFolders the next time it runs, it will synchronize the 2 directories.

I can now create a windows task which will execute the powershell with the following parameters:
-command "& ‘<location to your ps1 script>\<filename>.ps1′"

Add PowerShell Task

As I still do not have my secondary goals, I will try to work on that. But there is one thing I want to add to the Primary Goals list, that is the fact that the script has the access Key Id and the Secret Access Key in it, in plain text. Off course “not secure”. As more people can access the EC2 server, I don’t want them to see the passwords.
I know the Cloudberry command can’t handle a SecureString object, so I cant use the commands convertfrom-securestring and convertto-securestring to supply the credentials via a plain text file.

So I am thinking of putting this powershell script on one of my on premise servers and using PowerShell Remoting to execute the commands for a backup (I already tested this and it works, but it’s 2 long for now to write down :-) ). So I will come back on that later.

In the mean time if some of the PowerShell Guru’s have a better idea to handle this in a more secure way, I would be most  grateful with any advice.

PowerShell: mailbox size and enabled mailboxes

Posted on by
Reply

(note to self)

retrieving all enabled mail addresses in Exchange:

get-mailbox | where-object {$_.IsMailboxEnabled -eq $True} | select-Object DisplayName,PrimarySMTPAddress,WhenChanged | Sort-Object WhenChanged -descending | Export-Csv d:\mailadresses.csv

Retrieving the size of all mailboxes in Exchange

get-mailbox | Get-MailboxStatistics | select-object DisplayName, TotalItemSize | sort-object TotalItemSize -descending | Out-File d:\mailboxsize.csv

PowerShell–Changing AD user attributes

Posted on by
Reply

Recently I was asked to change some User attributes in AD. As these changes we’re on a lot of users in one OU, I concluded it would be a good idea to try this in PowerShell. In this article you will find the steps I took.

First of all I used the Active Directory Commandlets from Quest Software, you can find them here. Second, make sure you start PowerShell as a domain administrator or someone who has delegated rights to change the attributes.

The users whose attributes had to change where all in one Organizational Unit. So I first tried to get all the users that we’re in this OU.

get-qaduser -ou "domain/ou/ou"

Now I had to pipe this to a second command which would set the attributes for each user in that OU. To set attributes you can use set-qaduser.

get-qaduser -ou "domain/ou/ou" | foreach {set-qaduser -identity $_.logonname -streetaddress "<address>" -PostalCode "<ZipCode>" -City "<City>" -StateOrProvince "<State>"}

The next part would be a bit trickier. I needed to change the format of the mobile phone number. It was in the format: 0031 #########, I had to change this to +31 #########. Basically I need to cut off the first 2 zero’s and replace it with a “+” sign.

I used the substring method for this. You can pass the substring method a single parameter: the starting position where we want to begin extracting characters. And to concatenate the “+” sign in front of the string, you can just use the + sign  …get it? :-)

get-qaduser -ou "domain/ou/ou" | foreach {set-qaduser -identity $_.logonname -MobilePhone ("+"+$_.MobilePhone.substring(2))}

note: you can shorten the last 2 commands, into 1 command.