getclients.ps1

#############################################################################
#																			#
# name: getclients.ps1														#
# script version 0.9														#
# 																			#	
# author: edi																#
#																			#
# comment: 	get clientlist from matrix42 sql db							 	#
#																			#
# 20.02.2013 :: release														#
#																			#
#############################################################################
[CmdletBinding()]
param (
	[string]$server = "t5sv04\empirum1",
	[string]$instance = "uniwient5",
	[string]$query = "select [name], [macaddress], [ipaddress]  from [$instance].[dbo].[clients] where [name] != 't5sv02'"	
)


if(-not($server)) { throw "a server name is required" }
if(-not($instance)) { throw "a database name is required" }
if(-not($query)) { throw "a sql query is required" }

$nl = [environment]::newline
$clientfile = "$env:emp_utils\clients.csv"

# ---------------------------------------------------------------------------
#logging to customize
# ---------------------------------------------------------------------------
$logdir = "$env:emp_utils\logs\"

# ---------------------------------------------------------------------------
#create log directories, if needed
# ---------------------------------------------------------------------------
if (!$(test-path $logdir)) {new-item $logdir -itemtype directory | out-null}
$log = [string]::join('',($logdir, "getclients.log"))
if (!$(test-path $log)) {new-item -type file $log -force}

# ---------------------------------------------------------------------------
#logging
# ---------------------------------------------------------------------------
function writelog($msg)
{
	$logrecord = "------------------------------------------------------------$nl $(get-date)"
	$logrecord += $msg
	add-content -path $log -encoding utf8 -value $logrecord #-passthru
}	

try{
	$connection = new-object system.data.sqlclient.sqlconnection("data source=$server;initial catalog=$instance;integrated security=sspi;");
		
	$adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
	$set = new-object system.data.dataset
	 
	$rows = $adapter.fill($set)
	 
	$table = new-object system.data.datatable
	$table = $set.tables[0]
	#$table.rows[0].delete()
}
catch{ 
		$errormessage = $_.exception.message                                                            
		$logmsg = "an error has occurred, the error message was: $errormessage."
		write-host $logmsg
		writelog $logmsg
		break		
}




$logmsg =  " client´s on server: $server with instance: $instance  found:  $rows "
write-host $logmsg -foregroundcolor green
writelog $logmsg 

 #return table
$table | epcsv –path $clientfile -encoding utf8 -notypeinformation -force
#$table | convertto-csv -notype |% {$_ -replace '"'} | out-file c:\clients.csv
#($data = get-content –path $clientfile  #| % {$_.split(" ")}

(get-content $clientfile | select -skip 1) | foreach-object {$_ -replace """", ""} | set-content $clientfile

write-host "$nl path to logs: $logdir " -foregroundcolor green
write-host "$nl path to clientlist: $clientfile " -foregroundcolor green 
write-host $nl