Oracle Query Need to make it robust and a module

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 4 months ago.

  • Author
    Posts
  • #5945

    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, Chris
    Param (
    [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
    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
    INNER JOIN SYS.ALL_OBJECTS B ON A.OBJECT_ID = B.OBJECT_ID
    WHERE b.object_name LIKE `'%REF_NUM%`'
    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

    by DonJ at 2012-11-05 12:04:56

    Wow, that's a lot of code.

    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.

    by cweislak at 2012-11-05 21:49:03

    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.

You must be logged in to reply to this topic.