Archive for August, 2010

PowerShell: count items per folder

August 26th, 2010 No comments

This script is created to count how many folders exist in a NAS Share. It loads the list from SQL Server and writes the result back to the same table. You can modify it to your own needs.

# **********************************************
# Created by SĂ©bastien Morel
# Last update: August 26th, 2010
# Version 1.00
# This script will loop NAS Folders
# and return number of homefolders per share
# **********************************************

# retrieve NAS Folders
$sqlsvr = '<MySQLServer>'
$database = '<MySQLDB>'
$table = 'tbl_homeshares_trees'

Write-Host "Please wait a moment, this can take a while..."
Write-Host " "

#Create SQL Connection
Write-Verbose "Creating SQL Connection..."
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=SSPI")
$conn2 = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=SSPI")

$cmd = $conn.CreateCommand()
$cmd2 = $conn2.CreateCommand()

$cmd.CommandText = "select tree_id, tree_path from $table"
$Reader = $cmd.ExecuteReader()

while ($Reader.Read()) {
	$sID = $Reader["tree_id"].Tostring()
	$sPath = $Reader["tree_path"].Tostring()
	Write-Host "Querying $sPath..."
	$sGo = Get-ChildItem $sPath
	# update table
	$cmd2.CommandText = "UPDATE $table SET tree_count = " + $sGo.Count + " WHERE tree_id = '" + $sID + "'"
	$cmd2.ExecuteNonQuery() | Out-Null


The results in the database

Categories: Powershell Tags: ,

PowerShell: delete files older than x days

August 25th, 2010 1 comment

Classical script and we all need it some day. Edit the folder path and adjust the number of days to your clean-up schedule. The script works recursive so it will also clean-up subfolders.

As always, make sure you can execute PowerShell scripts

Set-ExecutionPolicy RemoteSigned

The Script…

$Now = Get-Date
$Days = "30"
$TargetFolder = "C:WINDOWSsystem32LogFiles"
$LastWrite = $Now.AddDays(-$days)
$Files = get-childitem $TargetFolder -include *.log, *.txt -recurse | Where {$_.LastWriteTime -le "$LastWrite"} 

foreach ($File in $Files){
	write-host "Deleting File $File" -foregroundcolor "Red";  Remove-Item $File | out-null
Categories: Powershell Tags: , ,

Exchange 2000/2003: count mailboxes per store

August 24th, 2010 2 comments

A client of me wants to automate the creation of exchange mailboxes but the load needs to be spread among the 5 exchange servers. Each server has 4 storegroups and 4 stores making a total of 80 locations. So I wrote a PowerShell script that queries each Exchange Server and counts mailboxes per store. This job will run each night and the results are written to a SQL Server database (company policy)

The configuration is easy and is located at the top of the script. You can choose to send the results to the screen or to SQL Server

$tempfile = "Temp.csv"
$exportfile = "Exchange_stores.txt"
$exportcsv = "Exchange.csv"
$output = "screen"; # sql - screen

Decide if you want to load a serverlist from a text-file or from the script

# Get list from array or txt-file (un-comment what you want)
# -----------------------------------------------------------
$exServers = "Dummy1","Dummy2"
#$exServers = Get-Content ExchangeServers.txt

SQL-Server Configuration

# SQL Server configuration (only needed when output = sql)
# -----------------------------------------------------------
$sqlsvr = '<mysqlserver>'
$database = '<mysqldatabase>'
$table = 'tbl_exchange_servers'

The Create-table script is included and looks like this

CREATE TABLE [dbo].[tbl_exchange_servers](
	[server_id] [int] IDENTITY(1,1) NOT NULL,
	[server_name] [varchar](100) NOT NULL,
	[server_storegroup] [varchar](100) NOT NULL,
	[server_storename] [varchar](100) NOT NULL,
	[server_mailboxcount] [int] NOT NULL

The output to the screen will look like this


v1.03: fixes a bug for the export to SQL Server where the variables contained an unwanted space

All comments are welcome 🙂

Full Script

# **********************************************
# Created by SĂ©bastien Morel
# Last update: August 24th, 2010
# Version 1.03
# This script will loop exchange servers
# and return number of mailboxes per store
# **********************************************

$tempfile = "Temp.csv"
$exportfile = "Exchange_stores.txt"
$exportcsv = "Exchange.csv"
$output = "screen"; # sql - screen

# Get list from array or txt-file (un-comment what you want)
# -----------------------------------------------------------
$exServers = "Dummy1","Dummy2"
#$exServers = Get-Content ExchangeServers.txt

# SQL Server configuration (only needed when output = sql)
# -----------------------------------------------------------
$sqlsvr = '<mysqlserver>'
$database = '<mysqldatabase>'
$table = 'tbl_exchange_servers'

Write-Host "Please wait a moment, this can take a while..."
Write-Host " "

# Delete old output file when needed
if ([System.Io.File]::Exists($tempfile)) { [System.IO.File]::Delete($tempfile) }
if ([System.Io.File]::Exists($exportfile)) { [System.IO.File]::Delete($exportfile) }
if ([System.Io.File]::Exists($exportcsv)) { [System.IO.File]::Delete($exportcsv) } 

foreach ($server in $exServers) {
	Write-Host "Querying $server ..."
	$mbx = get-wmiobject -class Exchange_Mailbox -namespace RootMicrosoftExchangeV2 -computername $server
	$mbx | Group-Object StoreName,StorageGroupName,Servername -NoElement | Sort-Object Count -Descending | Export-Csv -NoType $tempfile -Delimiter (';')
	[System.IO.File]::ReadAllText($tempfile) | Out-File $exportfile -Append -Encoding Unicode

Write-Host " "
If ($output -eq "sql"){
	Write-Host "Importing results to SQL Server..."
	Write-Host -Fore Green "Updating Table"
	$myCollection = @()

# remove blank lines from the export-file
(get-content $exportfile) -replace '"','' | where {$_ -ne ""} | out-file $exportfile
# import results to SQL Server
$sr = [System.Io.File]::OpenText($exportfile)
$s = $sr.ReadLine()
While ($s) {
	$s = $sr.ReadLine()
	If ($s -ne $null -and $s.Contains("ArrayList")){
		$val = $s.split(";")
		$numMailbox = $val[1]
		$names = $val[3].split(",")
		$sStore = $names[0].Trim()
		$sStorageGroup = $names[1].Trim()
		$sServerName = $names[2].Trim()
		If ($output -eq "screen"){
			write-host $sServerName $sStorageGroup $sStore : $numMailbox
		} else {
			$myobj = "" | select Server,StoreGroup,StoreName,MailboxCount
			$myobj.Server = $sServerName
			$myobj.StoreGroup = $sStorageGroup
			$myobj.StoreName = $sStore
			$myobj.MailboxCount = $numMailbox
			$myCollection += $myobj
If ($output -eq "sql"){
	$myCollection | Export-Csv $exportcsv -notype
	#Create SQL Connection
	Write-Verbose "Creating SQL Connection..."
	$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsvr;
	Initial Catalog=$database; Integrated Security=SSPI")
	$cmd = $conn.CreateCommand()

	#truncate current table
	$cmd.CommandText = "TRUNCATE TABLE $table"
	$cmd.ExecuteNonQuery() | Out-Null

	Import-Csv .$exportcsv | % {

	#Create query string
	#Must matching the table layout (server_name, server_storegroup, server_storename, server_mailboxcount)
	$cmd.CommandText = "INSERT INTO $table (server_name, server_storegroup, server_storename, server_mailboxcount) VALUES ('$($_.Server)','$($_.StoreGroup)','$($_.StoreName)','$($_.MailboxCount)')"

	#Execute Query
	$cmd.ExecuteNonQuery() | Out-Null


Write-Host "Finished!"
Categories: Opalis Tags: , , , , , ,

“How Opalis and System Center can make your life so much easier” by (9 Sept in Brussels)

August 23rd, 2010 No comments

The SCUG group organizes an event in Brussels that I will attend 🙂 It’s free but you need to register!

The System Center User Group Belgium invites you to their next free event, where they’ll show you how we can use Opalis and the System Center suite together to make your life easier! With real life examples, like maintenance mode due to server patching, using System Center Operations Manager (SCOM) as the manager of managers, automating VM creation, Alert enrichment,  etc they will demonstrate how to use the full strength of Opalis as an orchestration tool together with Operations Manager, Data Protection Manager (SCDPM), Virtual Machine Manager (SCVMM), Service Manager (SCSM) and Configuration Manager (SCCM). If you want to be much more efficient in your day to day work, you will not want to miss this session!

Practical info

  • Date: 9 September 2010
  • Location: Microsoft Belgium, Da Vincilaan 3, 1935 Zaventem
  • Speaker: Alexandre Verkinderen
  • Registration is free but required

Read more at SCUG

Categories: Opalis Tags: , ,

Powershell WMI Explorer

August 20th, 2010 No comments

WMI was in the past commonly used for scripting tasks and sometimes it handy to know which classes are installed on a server/workstation. Today I found a WMI GUI that was written in Powershell by Marc van Orsouw. He has a blog called The PowerShell Guy and you can download it from here!

Categories: Powershell Tags:

Opalis: Integration Pack for Data Manipulation

August 18th, 2010 2 comments

There has been quite a bit of discussion in various places around manipulating data that is captured and processed through Opalis Integration Server. While Opalis Integration Server comes with many objects out of the box that make data management a breeze, there are opportunities for better data manipulation capabilities for the information on the data bus.

This is where the Integration Pack for Data Manipulation comes in. A series of objects that can be added into your Opalis Integration Server environment to provide extended data management capabilities. Read further at Source…

Integration Pack for Data Manipulation

I have installed the IP and these are the objects that are added

Source: Technet

Categories: Opalis Tags: , ,

QIK Wizard: add dependency files

August 17th, 2010 No comments

When you create a new Assembly that needs a dependency file to run you will need to add this file to the Integration Pack when building. In my case I wanted to execute some PowerShell commands from a file.

I installed PowerShell v2.0 so your command would be

powershell -file .<myscript.ps1> <func1> <arg0>

but this doesn’t work from an Integration Pack since it will look for the file in C:WindowsSystem32

The Integration Pack copies dependency files to

C:Program FilesCommon FilesOpalis SoftwareOpalis Integration ServerExtensionsSupportbin

and this folder is part of the Environment Variable PATH

So changing your command to this solves that problem and the Integration Pack executes the commands from the PowerShell file

powershell.exe <myscript.ps1> <func1> <arg0>

It’s off course better to use a custom dll-file but I don’t have any experience with C# so that’s for later.

I’ll post my own custom Integration Pack later this week!

Categories: QIK Tags: , ,

Opalis: working with XML-files

August 16th, 2010 No comments

I personally don’t like to store hard coded values in any of my scripts. They limit your abilities for future use. it’s the same with Opalis so I decided to store my configuration in an XML-file.

1) create a new .xml-file and create a new tree

<?xml version="1.0" encoding="ISO-8859-1"?>





Read more…

Categories: Opalis Tags: , ,

Opalis: Quick Integration Kit (QIK) videos

August 13th, 2010 No comments

Charles Joy has uploaded about 15 videos (YouTube) that describe how-to use the QIK tool to create your own Integration Pack. You can download the zip-file with the C# source-files on Technet Blog

Click here to see the videos

Categories: Opalis Tags: , , , ,

Powershell v2 released for pre Windows 7 machines

August 12th, 2010 No comments

Windows Management Framework, which includes Windows PowerShell 2.0, WinRM 2.0, and BITS 4.0, was officially released to the world this morning. By providing a consistent management interface across the various flavors of Windows, we are making our platform that much more attractive to deploy. IT Professionals can now easily manage their Windows XP, Windows Server 2003, Windows Vista, Windows Server 2008, Windows 7, and Windows Server 2008 R2 machines through PowerShell remoting – that’s a huge win!

You can download the packages here:

Categories: Powershell Tags: