by cweislak at 2012-11-02 21:14:02
I might have an odd question with this one… I created a script that will run a query and check for database locks on a couple of tables in our ERP. If It detects any locks it will send an email out to our Temp DBA "Me" and the Help desk. I want to be able to take the script and make it a module. I Don’t know how to break out the Oracle Connections and query’s to keep a persistent connection to oracle so I don’t have to keep Sending the credentials? Also Not sure how to make it more robust. The email section I would separate and the AD query I would remove for the module. I am hoping I might be able to get some guidance. Thank you, Chrisby DonJ at 2012-11-05 12:04:56Param (
[Parameter(ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage='SMTP Server')]
[String]$SMTP,
[Parameter(ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage='Who will Receive the email')]
[String]$SendTo,
[Parameter(ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage='Who The email is from')]
[String]$From,
[String]$subject = "Detected long locks",
[Parameter(ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$True,
HelpMessage='SQL Script to run on Oracle Database')]
[String]$SQL
)
if ($smtp -eq $null){
$smtp = "Server"
}
if ($SendTo -eq $null){
$SendTo = @("name@gmail.com")
}
if ($From -eq $null){
$From = "Server@Do-not-reply.com"
}
if ($subject -eq $null){
$subject = "Detected long locks"
}
if ($SQL -eq $null){
$SQL = @("SELECT A.SESSION_ID"sid", C.SERIAL#"Serial", B.OBJECT_NAME"Object",
C.MACHINE,
A.OS_USER_NAME NT_ID,
D.FNAME,
D.LNAME,
A.ORACLE_USERNAME"Locker", NVL(C.LOCKWAIT, 'active')"Wait",
DECODE(A.LOCKED_MODE, 2,'row share', 3,'row exclusive', 4,'share', 5,'share row exclusive', 6,'exclusive','unknown')"Lockmode",
B.OBJECT_TYPE"Type", C.SECONDS_IN_WAIT
FROM SYS.V_$LOCKED_OBJECT A<br>LEFT OUTER JOIN USER_DIR.NT_USERS D ON A.OS_USER_NAME ='TRD`' || D.ID
Inner Join SYS.V_$SESSION C ON A.SESSION_ID = C.SID<br>INNER JOIN SYS.ALL_OBJECTS B ON A.OBJECT_ID = B.OBJECT_ID<br>WHERE b.object_name LIKE'%REF_NUM%'<br> OR b.object_name LIKE'%A1_OBJECT%`'")
}
#Import ActiveDirectory Modual
if (!(Get-Module | Where-Object {$_.Name -eq "ActiveDirectory"})){
Import-Module -Name ActiveDirectory
}
#Function to import Credentials for AD user query and Oracle DB ID
function Import-Credential {
Param (
$path
)
Begin {
if ($path -eq $null) {
$path = Read-Host -Prompt "Please enter path to credential file"
}
}
Process {
$cred = Import-Clixml $path
$cred.password = $cred.Password | ConvertTo-SecureString
Write-Output -InputObject (New-Object System.Management.Automation.PSCredential($cred.username,$cred.password))
}
END {}
}
#Convert Securestring from imported credentials used for Oracle ID
function ConvertFrom-SecureToPlain {
param(
[Parameter(Mandatory=$true)][System.Security.SecureString]$SecurePassword
)
$PasswordPointer = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecurePassword)
$PlainTextPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto($PasswordPointer)
[Runtime.InteropServices.Marshal]::ZeroFreeBSTR($PasswordPointer)
$PlainTextPassword
}
#Import the Credentials Calling from exported Credential files
$ADCred = Import-Credential -path C:\CheckLocks\ldapid.xml
$DatabaseCred = Import-Credential -path C:\CheckLocks\databaseid.xml
$DBid = $DatabaseCred.UserName
$DBPass = ConvertFrom-SecureToPlain -SecurePassword $DatabaseCred.Password
#If the default path for assembley is not there for Oracle Search for it
Function getassembly {
Begin{}
Process{
$path = ($env:Path).split(";") |Select-String -Pattern Oracle1
if ($path -eq $null) {
$path = ($env:Path).split(";") |Select-String -Pattern product
}
$path = ($path.ToString()).Trim("\bin")
if (test-path -Path "$path\ODP.NET\bin\4\Oracle.DataAccess.dll"){
$Assembly = "$path\ODP.NET\bin\4\Oracle.DataAccess.dll"
} elseif (test-path -Path "$path\ODP.NET\bin\2.x\Oracle.DataAccess.dll"){
$Assembly = "$path\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
} else {
$Assembly = $null
}
Write-Output $Assembly
}
End {}
}
Function checkforlocks {
<#
.SYNOPSIS
Get Table Locks.
.DESCRIPTION
This will use the .net 4 or .net 2 oracle dll to query Oracle and return locked tables for 3rdWave
.PARAMETER SQL
SQL Statement To Run
.PARAMETER ConnectionString
Connection String to use to connect to the database format of "User ID=username;Password=password;Data Source=tnsname"
by default it will use "User ID=/;Data Source=w3prod"
.PARAMETER AssemblyFile
The Assembly File to use for quering Oracle by default it will use "C:\Oracle\product\11.2.0\client1\ODP.NET\bin\4\Oracle.DataAccess.dll".
you must have the data access for orcale .net installed to run this.
.EXAMPLE
Get-Locks -SQL "SQL Query without tailing ;" -ConnectionString "User ID=username;Password=password;Data Source=tnsname" -AssemblyFile C:\Oracle\product\11.2.0\client1\ODP.NET\bin\4\Oracle.DataAccess.dll
#>
[CmdletBinding()]
Param(
[String]$SQL,
[String]$ConnectionString = "User ID=$DBid;Password=$DBPass;Data Source=w3prod",
[String]$AssemblyFile = "C:\Oracle\product\11.2.0\client_1\ODP.NET\bin\4\Oracle.DataAccess.dll"
)
Begin {
if (-not (Test-Path $AssemblyFile)) {
$AssemblyFile = getassembly
if ($AssemblyFile -ne $null){
[Reflection.Assembly]::LoadFile($AssemblyFile) | Out-Null
}
} else {
[Reflection.Assembly]::LoadFile($AssemblyFile) | Out-Null
}
}
Process {
$continue = $True
try {
Get-Item -Path $AssemblyFile -ErrorAction Stop |Out-Null
} Catch {
Write-Host "Missing Assembly File"
$continue = $false
}
if ($continue -eq $true){
$OracleConnection = New-Object -TypeName Oracle.DataAccess.Client.OracleConnection
$OracleConnection.ConnectionString = $ConnectionString
$OracleConnection.Open()
$OracleCommand = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand
$OracleCommand.CommandText = $SQL
$OracleCommand.Connection = $OracleConnection
$OracleDataAdapter = New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter
$OracleDataAdapter.SelectCommand = $OracleCommand
$DataSet = New-Object -TypeName System.Data.DataSet
$OracleDataAdapter.Fill($DataSet) | Out-Null
$OracleDataAdapter.Dispose()
$OracleCommand.Dispose()
$DataSet.Tables[0]
}
}
END {}
}
Function get-userinfo {
param(
[String]$users,
$ADCred
)
Process {
Foreach ($user in $users){
$userinfo = Get-ADUser -Filter {SamAccountName -eq $user} -Properties * -Credential $ADCred
$name = $userinfo.get_Item("CN").Value
$phone = $userinfo.get_Item("OfficePhone").Value
$mobile = $userinfo.get_Item("MobilePhone").Value
Write-Output $userinfo
}
}
}
Function getinf {
Param ( $locks)
Process {
foreach ($lock in $locks){
if ($lock.get_Item('SECONDS_IN_WAIT') -ge 60){
if ($lock.get_Item('NT_ID') -ne "TRD\HAPI_SCALE"){
$sam = ($lock.get_Item('NT_ID')).TrimStart("TRD")
$ObjName = $lock.get_Item('Object')
$ServerName = ($lock.get_Item('MACHINE')).TrimStart("TRD")
$Serial = $lock.get_Item('Serial')
$Wait = $lock.get_Item('SECONDS_IN_WAIT')
$userinf = get-userinfo $sam -ADCred $ADCred
$data = @{
'Name' = $userinf.get_Item("CN").Value;
'Phone' = $userinf.get_Item("OfficePhone").Value;
'Mobile' = $userinf.get_Item("MobilePhone").Value;
'ObjName' = $ObjName;
'ServerName'= $ServerName;
'Serial' = $Serial;
'SecInWait' = $Wait
}
$info = New-Object -Type PSObject -Prop $data
Write-Output $info
}
}
}
}
}
Function Get-Locks{
[CmdletBinding()]
Param (
$smtp,
$SendTo,
$From,
$subject,
$SQL,
$DBID,
$DBPass,
$ADCred
)
Process {
$locks = checkforlocks -SQL $SQL
if ($locks -ne $null){
$inf = getinf -locks $locks
if ($inf -ne $null){
$body = $inf | ConvertTo-Html -As List |Out-String
Send-MailMessage -To $SendTo -From $From -Subject $subject -SmtpServer $smtp -Body $body -BodyAsHtml -Priority High
#Write-Output "Sent"
}
}
#Write-Output "Done"
}
}
Get-Locks -smtp $smtp -SendTo $SendTo -From $From -Subject $subject -SQL $SQL -DBID $DBid -DBPass $DBPass -ADCred $ADCred
Wow, that’s a lot of code.by cweislak at 2012-11-05 21:49:03
So, making it a module is straightforward. You save it as a .psm1 file in one of the Modules folders, such as [My ]Documents\WindowsPowerShell\Modules\MyOracle\MyOracle.psm1, noting that the module folder and file names must match.
Modules don’t normally have parameters of their own, though. They’d just contain functions and variables. You could certainly make variables and set them to defaults, instead of defining parameters. You could always change those values after importing the module, just as you might change a built-in variable like $ErrorActionPreference.
Any variables created at the top level of the module will get exported into the shell’s global scope when the module is loaded, so they’ll remain persistent once set.
But module design at this level is a pretty involved topic; it’s a little tough to do an entire module-making course in a forum post :). Can we start with maybe one question, "how would I do ______?" solve that, and then move on to the next one? It’d be easier to maybe tackle one question at a time with a very short example that you could adapt to your longer script.
Thank you for the response. I guess I would like to be able to break out all to oracle functions. For instance have it work more like the netapp cmdlet’s or the XenServer cmdlets. They have a Connect-NcController that opens a session that you can run everything against. I am not a DBA by trade "But play one on TV". I wanted to be able to create a session and run multiple query’s against. If I could make a function to open the connection and one to close it I could break the others out.by DonJ at 2012-11-06 06:56:07
Ok, that’s straightforward. The proper pattern for that is demonstrated by the Remoting cmdlets in PowerShell:
$session = New-PSSession -computername SERVER2
Enter-PSSession -Session $session
So your connect command outputs a connection, which you save into a variable when using the command. The query command accepts that connection as a parameter.