Posts Tagged ‘Powershell’

Tool to query SQL databases

Friday, January 15th, 2010

For some tasks it is useful to run a custom query against a database.
The application using the database perhaps does not have the ability to generate certain reports.
Using SQL manager is a viable option, but then everyone who needs to have this installed to be able to run a query.
It is however possible to create some Excel-files which have the queries embedded.

But as an alternative solution I have created this PowerShell app.
It is a GUI-based tool allowing you to select a saved script or enter a custom query and run it.
The output is by default displayed in a datatable but the output can also be exported to an Excel-document.

The available SQL servers are defined in servers.xml
And to avoid the hassle of selecting a server and database after selecting a script you can predefine a combination of a shortname, server and database in shortnames.xml
Eg. if you have a SQL-file hlp_openincidents.sql and hlp is defined in shortnames.xml with server set to SQLSERVER and database to HELPDESK the tool will autoselect the server: SQLSERVER and the database: HELPDESK

SQL_tool.zip (381 downloads)

Script software deployment in PowerShell

Sunday, January 10th, 2010

This is an example in PowerShell of how you can schedule the installation a software package by Altiris DS.
Instead of using the API, the command-line tool axsched is being called to schedule the software package.

function DeploySoftware
{
    param( [String] $pcname,  [String] $package,  [String] $nexttime,  [String] $folder)
    #Write-Host "Deploy $pcname '$package' /t $nexttime /f '$folder'"
    #\\ALTIRIS\express\axsched.exe "$pcname" "$package" / t "$nexttime" / f "$folder"
         
    $StartInfo = New-Object System.Diagnostics.ProcessStartInfo
    $StartInfo.CreateNoWindow = $true
    $StartInfo.UseShellExecute = $false
    $StartInfo.FileName = "\\ALTIRIS\express\axsched.exe"
    $StartInfo.WorkingDirectory = "C:\Temp"
    $StartInfo.Arguments = "`"$pcname`" `"$package`" /t `"$nexttime`" /f `"$folder`""
    $p =  [System.Diagnostics.Process] ::Start($StartInfo)
    $p.WaitForExit()
    if ($p.ExitCode -eq 0) { $TRUE } else { $FALSE }
}
#This function adds a number of minutes to the given date time and outputs the addition in the right format for AxSched.
function Get-ScheduleTime
{
    param( [System.DateTime]  $starttime,  [int] $minutes)
    #$minutes = 12
    $tspan = New-Object System.TimeSpan(0, 0, $minutes, 0)
    $addtime =  [System.DateTime] ::op_Addition($starttime, $tspan)
    #"2008-10-20 15:00"
    $y = $addtime.Year
    $mo = $addtime.Month
    $d = $addtime.Day
    $h = $addtime.Hour
    $mi = $addtime.Minute
    $schedtime = "$y"
    $schedtime += "-"
    $schedtime += TwoDecimal("$mo")
    $schedtime += "-"
    $schedtime += TwoDecimal("$d")
    $schedtime += " "
    $schedtime += TwoDecimal("$h")
    $schedtime += ":"
    $schedtime += TwoDecimal("$mi")
    $schedtime
}
#Simple function to convert a single decimal to a two decimal, eg 7 becomes 07
function TwoDecimal
{
    param( [String] $Number)
    while ($number.Length -lt 2) { $number = "0$number" }
    $number
}
$now =  [System.DateTime] ::Now
$pcname = "COMP007"
$jobname = "Install Outlook 2007"
$jobfolder = "Software"
#The installation of the package needs to be started after 5 minutes.
$nexttime = Get-ScheduleTime $now 5
DeploySoftware $pcname $jobname $nexttime $jobfolder

Powershell check requirements and load addins

Sunday, January 10th, 2010

Powershell excerpts of how to check if .NET FrameWork 3.5, Quest AD, Altiris ASDK is installed

First the functions that execute the installer.

function Execute-Installer()
{
    param( [String] $parameters, [String] $msiworkingdir)
    $msiworkingdir = Resolve-Path $msiworkingdir
    $StartInfo = New-Object System.Diagnostics.ProcessStartInfo
    $StartInfo.CreateNoWindow = $true
    $StartInfo.UseShellExecute = $false
    $StartInfo.FileName = "c:\windows\system32\msiexec.exe"
    $StartInfo.WorkingDirectory = "$msiworkingdir"
    $StartInfo.Arguments = "$parameters"
    $p =  [System.Diagnostics.Process] ::Start($StartInfo)
    $p.WaitForExit()
    if ($p.ExitCode -eq 0) { $TRUE } else { $FALSE }
}
 
function Execute-Program()
{
    param( [String] $parameters, [String] $program)
    Write-Host "Execute-Program $program $parameters"
    #Write-Host "MSI WorkingDir: $msiworkingdir"
    #$msiworkingdir = Resolve-Path $msiworkingdir
    $program = Resolve-Path $program
    Write-Host "Program: $program"
    $StartInfo = New-Object System.Diagnostics.ProcessStartInfo
    $StartInfo.CreateNoWindow = $true
    $StartInfo.UseShellExecute = $false
    $StartInfo.FileName = $program
    $StartInfo.Arguments = "$parameters"
    $p =  [System.Diagnostics.Process] ::Start($StartInfo)
    $p.WaitForExit()
    if ($p.ExitCode -eq 0) { $TRUE } else { $FALSE }
}

The code that checks if .NET FrameWork 3.5 is installed.
A messagebox will be displayed if it is not installed.
If users answers yes to the messagebox the requirement will be installed.

#region Check if .NET 3.5 is installed (Requirement for QuestAD and AltirisASDK
function Check-DotNet35{
Write-Host "Check-DotNet35"
$net35 = get-itemproperty "hklm:\\SOFTWARE\Microsoft\NET Framework Setup\NDP\v3.5"
if($net35 -eq $null){
Write-Host ".NET 3.5 is not installed."
$install =  [Windows.Forms.MessageBox] ::Show(".NET 3.5 Framework is not installed. This is required. Would you like to install it?", "My Application",  [Windows.Forms.MessageBoxButtons] ::YesNo,  [Windows.Forms.MessageBoxIcon] ::Question)
if ($install -eq  [Windows.Forms.DialogResult] ::Yes){
Execute-Program "/PASSIVE /NORESTART" "D:\Software\DotNet\3.5\dotnetfx35SP1.exe"
}
}else{
Write-Host "DotNet 3.5 FrameWork is installed"
}
}
Check-DotNet35
#endregion

The code to check if Quest AD is installed.
A messagebox will be displayed if it is not installed.
If users answers yes to the messagebox the requirement will be installed.
Also if the users has an outdated version he will receive a messagebox asking to perform an upgrade.

#region Load Quest AD plugin
function Load-QuestAD-Plugin
{
Write-Host "Load-QuestAD-Plugin"
$retVal = (Get-PSSnapin Quest.ActiveRoles.ADManagement 2>$null)
$qadversion = "1.2.2.1254"
#Write-Host $retVal
if ($retVal -eq $null)
{
Write-Host "Quest.ActiveRoles.ADManagement"
Add-PSSnapin Quest.ActiveRoles.ADManagement
$retVal = "$?"
if ($retVal -eq $false)
{
$install =  [Windows.Forms.MessageBox] ::Show("Quest.ActiveRoles.ADManagement is not installed. Would you like to install it?", "My Application",  [Windows.Forms.MessageBoxButtons] ::YesNo,  [Windows.Forms.MessageBoxIcon] ::Question)
if ($install -eq  [Windows.Forms.DialogResult] ::Yes)
{
Write-Host "Installing Quest.ActiveRoles.ADManagement"
Write-Host "msiexec.exe /i `"$WorkingDir\Requirements\arps.$qadversion.msi`" /qn /norestart /l*c:\windows\temp\arsps.log"
$retVal = Execute-Installer "/i `"$WorkingDir\Requirements\arps.$qadversion.msi`" /qn /norestart /l* c:\windows\temp\arsps.log" "c:\temp"
if($retVal -eq $true){
Write-Host "Install finished"
}
}
else
{
Write-Host "No install of Quest.ActiveRoles.ADManagement"
}
}
else
{
Write-Host "QAD installed"
$pssnapinversion = (Get-PSSnapin Quest.ActiveRoles.ADManagement).version
$textversion = $pssnapinversion.Major.tostring() + "." + $pssnapinversion.Minor.tostring() + "." + $pssnapinversion.Build.tostring() + "." + $pssnapinversion.Revision.tostring()
Write-Host "Required version: $qadversion - Current version: $textversion "
if ($textversion -ne $qadversion)
{
$install =  [Windows.Forms.MessageBox] ::Show("Quest.ActiveRoles.ADManagement is not up to date. Would you like to update it?", "My Application",  [Windows.Forms.MessageBoxButtons] ::YesNo,  [Windows.Forms.MessageBoxIcon] ::Question)
#if ($install.tolower() -eq "y")
if ($install -eq  [Windows.Forms.DialogResult] ::Yes)
{
Remove-PSSnapin Quest.ActiveRoles.ADManagement
Write-Host "Installing Quest.ActiveRoles.ADManagement"
Write-Host "msiexec.exe /i `"$WorkingDir\Requirements\arps.$qadversion.msi`" /qn /norestart /l*c:\windows\temp\arsps.log"
$retVal = Execute-Installer "/i `"$WorkingDir\Requirements\arps.$qadversion.msi`" /qn /norestart /l* c:\windows\temp\arsps.log" "c:\temp"
if($retVal -eq $true){
Write-Host "Install finished"
}
}
else
{
Write-Host "No install of Quest.ActiveRoles.ADManagement"
}
}
}
}
}
Load-QuestAD-Plugin
#endregion

Check if Altiris ASDK for NS is installed.
A messagebox will be displayed if it is not installed.
If users answers yes to the messagebox the requirement will be installed.
If it is already installed the connections will be made to the server.

#region Load Altiris ASDK
function Load-AltirisASDK
{
Write-Host "Load-AltirisASDK"
$altirisserver="ALTIRIS"
$global:nsColl = New-Object -comObject Altiris.ASDK.NS.CollectionManagement
if($? -eq $false){
Write-Host "AltirisASDK is not installed"
$install =  [Windows.Forms.MessageBox] ::Show("Altiris ASDK is not installed. Would you like to install it?", "My Application",  [Windows.Forms.MessageBoxButtons] ::YesNo,  [Windows.Forms.MessageBoxIcon] ::Question)
#if ($install.tolower() -eq "y")
if ($install -eq  [Windows.Forms.DialogResult] ::Yes)
{
#Execute-Program "/S" "$WorkingDir\Requirements\Altiris_ASDK_1_4_209.exe"
$retVal = Execute-Installer "/i `"$WorkingDir\Requirements\Symantec_ASDK_NS_COM_x86.msi`" /qn /norestart /l* c:\windows\temp\asdkNS.log" "c:\temp"
if($retVal -eq $true){
Write-Host "ASDK has been installed"
}else{
Write-Host "ASDK installation failed"
}
}
 
}else{
$global:nsItem = New-Object -comObject Altiris.ASDk.NS.ItemManagement
$global:nsRes = New-Object -comObject Altiris.ASDK.NS.ResourceManagement
$global:nsReport = New-Object -comObject Altiris.ASDK.NS.ReportManagement
$global:nsReport.targetserver = $altirisserver
$global:nsReport.authenticate()
$global:nsItem.targetserver = $altirisserver
$global:nsItem.authenticate()
$global:nsColl.targetserver = $altirisserver
$global:nsColl.authenticate()
$global:nsRes.targetserver = $altirisserver
$global:nsRes.authenticate()
}
}
Load-AltirisASDK
#endregion