1

I have created a script in PowerShell (utilising the PowerShell dbaTools) which collect information about SQ Server Estate in our environment. I need to insert the result from the PowerShell directly from the script into Oracle Database table.

Example:

#GET DISK INFO:
$DiskInfo=Get-DbaDiskSpace -ComputerName $Servers | Select   @{label='SERVER_NAME';expression={$_.ComputerName}}, @{label='DISK_DRIVE_NAME';expression={$_.Label}}, @{label='CAPACITY_GB_MSR';expression={$_.SizeInGB}}, @{label='USED_GB_MSR';expression={$_.SizeInGB - $_.FreeInGB}},@{label='FREE_SPACE_GB_MSR';expression={$_.FreeInGB}}, @{label='DISK_DRIVE_LETTER_CODE';expression={$_.Name}}, @{label='LINUX_MOUNT_POINT_PATH';expression={'N/A'}}  |Out-GridView

Result in $DiskInfo variable: enter image description here

Now I need to update the DiskInfo table in the Oracle database with the info in the PowerShell variable above (If not exist).

  1. My first challenge is I can't seem to find any script that provides a good test for connection to Oracle. I have tried several scripts from online but none seems to be working.

I have tried the connection script from following links and all seems to be erroring out: https://purple.telstra.com.au/blog/using-powershell-to-query-oracle-dbs-without-using-the-oracle-client-oracle-data-provider-for-net https://forums.oracle.com/ords/apexds/post/powershell-and-odp-net-how-do-run-update-and-commit-command-0848 https://www.experts-exchange.com/questions/29247739/Insert-Update-oracle-DB-tables-in-PowerShell.html

  1. How do I take the value from the PowerShell variable and insert it in the right columns accordingly?

Thank you in advance for any direction or guidance.

7
  • Might find a solution quicker exporting to csv and then inserting into SQL if that's an option to import that way in you configuration. I've automated both mssql and mysql both using PowerShell for imports of data this way. Just throwing that out there for something to Google up on if nothing else. Commented Feb 29 at 2:09
  • @ITThugNinja I was actually thinking the same :) and started working on that. Would you be able to share script for csv import to Oracle tables using the PowerShell?
    – Ali
    Commented Feb 29 at 2:14
  • Yep, I should have something, I'll compile as an answer for you here. Give me a minute to find and compile. Commented Feb 29 at 2:15
  • 1
    Superb, thank you!
    – Ali
    Commented Feb 29 at 2:15
  • I added another comment below my answer for you too. Commented Feb 29 at 2:43

1 Answer 1

1

Here's some PowerShell and SQL logic to serve as a foundation for importing CSV data. While you may need to refine the data and logic to better suit your specific requirements, I trust you can handle those minor adjustments to achieve your desired outcome effectively.

1. Powershell

Note: This script queries four domain controllers and extracts security event logs for event ID 4740 using Task Scheduler. It's executed from one of these domain controllers, and the Export-Csv command exports the results to a shared folder's UNC path on the Microsoft SQL Server for subsequent import.

$domain = "company";
$servers = 1..4 | ForEach-Object { "$domain`dc0$_"}

$servers | ForEach-Object { 
$z = "";
$z = Get-WinEvent -ComputerName $_ -FilterHashtable @{
         Logname = 'Security';
         Id = 4740
        } -ErrorAction SilentlyContinue
 $Array = $z | ForEach-Object {
 [PSCustomObject]@{
    "Time" = $_.TimeCreated.ToString("yyyy-MM-dd hh:mm:ss tt")
    "Account Name" = "$($_.Message.Split(":").Trim()[4].Split()[0])\$($_.Message.Split(":").Trim()[-3].Split()[0])";
    "Caller System" = $_.Message.Split(":").Trim()[-1]
    "Domain Controller" = $_.MachineName.Trim().Split(".")[0] 
    }  
    }  
 If ($Array) {$Array | Export-Csv "\\CompanySQL01\csv$\AccountLockouts\$((Get-Date).ToString("yyyy-MM-dd_HHmmss"))_CompanyADLockouts.txt" -NoTypeInformation -Append;}  
 };

2. PowerShell

Note: This script is executed on the Microsoft SQL Server itself, facilitating its insertion into the table through Task Scheduler for automation. The drive letters referenced here are local to the SQL Server and not presented as UNC paths from its perspective.

Get-ChildItem "E:\Csv\AccountLockouts\*.txt" -File | ForEach-Object {
    Invoke-Sqlcmd -Query "EXEC ImportCompanyDomainAccountLockout @filepath =  '$($_.FullName)'" -ServerInstance "CompanySQL01" -Database "Reporting";

    Sleep -Seconds 3;

    Copy-Item $_.FullName -Destination "E:\Csv\AccountLockouts\_Archived\$($_.Name)" -Force;
    If ( Test-Path "E:\Csv\AccountLockouts\_Archived\$($_.Name)" ) { Remove-Item $_.FullName -Force; }

    };

Get-ChildItem "E:\Csv\AccountLockouts\_Archived\*" | ForEach-Object {
    $_ |  ForEach-Object { Process { If( $_.LastWriteTime -lt (Get-Date).AddDays(-90)){ $_.FullName | Remove-Item -Force } } };
    };

3. SQL Stored Procedure (MS SQL)

Note: This stored procedure is defined within the SQL Server database where you intend to import the data into its respective table. PowerShell script #2 executes this procedure to manage the data import process accordingly.

USE [Reporting]
GO

/****** Object:  StoredProcedure [dbo].[ImportCompanyDomainAccountLockout]    Script Date: 2/28/2024 9:27:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ImportCompanyDomainAccountLockout] @filepath nvarchar(500)
AS
SET NOCOUNT ON
    DECLARE @bulkinsert NVARCHAR(MAX)
    SET @bulkinsert = 
           N'BULK INSERT [dbo].[company_lockedoutaccounts] FROM ''' + 
           @filepath + 
           N''' WITH (FORMAT=''CSV'',FIRSTROW = 2)'
    
    EXEC sp_executesql @bulkinsert
GO

4. PowerShell (MySQL Import)

$DBname  = $args[0]
$SQLPass = $args[1]

$SvrName = "localhost"
$SName   = "sqlloginaccount"
$SPort   = "3306"

$sourceFolder = "\\companyesync1\Exports$\$DBname"
$delim        = "#"
$destpath     = "S:/Backups/$DBname/Import/Rawdata/tmpImport"
New-Item -ItemType Directory -Force -Path $destpath
$finalPath    = "S:/Backups/$DBname/Import/Rawdata"
New-Item -ItemType Directory -Force -Path $finalPath

Get-Childitem -Path "$sourceFolder/*.csv" -Name | ForEach-Object {
    $nameArray = $_.Split($delim)
    $FName     = $nameArray[0]

   Get-Childitem -Path "$sourceFolder/$FName*.csv" -Name | Sort-Object | ForEach-Object {
        Move-Item "$sourceFolder/$_" "$destpath" -Force
        $TblName = $FName
        $ImportFile = "$destpath/$_"

        ## -- SQL Logic to loop
        [system.reflection.assembly]::LoadWithPartialName("MySql.Data")
        $mysqlConn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
        $mysqlConn.ConnectionString = "SERVER=$SvrName;PORT=$Sport;DATABASE=$DBname;UID=$SName;PWD=$SQLPass;Allow User Variables=True"
        $mysqlConn.Open()
        $MysqlQuery = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand
        $MysqlQuery.Connection = $mysqlConn

        $MysqlQuery.CommandText ="
        USE $DBname;
        CREATE TABLE IF NOT EXISTS $TblName 
        (   
          `TimeInt` VARCHAR(10) NOT NULL,
          `TimeStr` DATETIME NOT NULL,
          `IsInitValue` INT NOT NULL,
          `Value` DOUBLE NOT NULL,
          `IQuality` INT NOT NULL,
          UNIQUE KEY `uk_Times` (`TimeInt`,`TimeStr`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        
        LOAD DATA LOCAL INFILE '$ImportFile' 
        REPLACE INTO TABLE $TblName
        FIELDS TERMINATED BY ',' 
        LINES TERMINATED BY '\r\n'
        (TimeInt,TimeStr,IsInitValue,Value,IQuality)
        "
        $MysqlQuery.ExecuteNonQuery()

        Move-Item "$ImportFile" "$finalPath" -Force               
   }
 }
5
  • @Ali .... The main things will be #2 and #3 and working out this logic. For #1, you can likely just use Export-Csv using the same parameters I listed above. You will test the export first, confirm you have your data correct. Then test importing into the table with the SQL Logic and manually copying one file over using SQL logic. Once you work it out, then turn that into a Stored Proc. Then you can work out getting your shared folders and security set, testing #2 logic works manually with test data and imports, then get your tasks scheduled for automation. Hit me up, let me know how it goes. Commented Feb 29 at 2:42
  • Thank you I'm working on this and will let you know how it goes.
    – Ali
    Commented Feb 29 at 2:55
  • @Ali .... I added PowerShell #4 example but I don't have anything simpler than that one at the moment. It's one I did in 2018 a few years back I saved, but I don't maintain that system now and I'd be surprising if it's still running. iirc, it created dynamic procs and was using MySQL 5.7 back then for plant tag sensor data, it may be more complex than you need, but wanted to post in case it helps you. If none of this helps, I will gladly delete the answer. I posted some MS SQL logic before rereading and realizing you were Oracle, I assume MySQL is similar but tell me otherwise and I'll delete. Commented Feb 29 at 3:00
  • So while #3 may not apply, 1, 2, and 4 may be useful basically to assist. Sorry for the initial oversight, my mind is weird sometimes. Commented Feb 29 at 3:01
  • This #4 is from testing data system. I don't have access to the production logic I used anymore. I recall putting thing in try catch block and configuring the mysql connections to only use one connection per loop, maybe pooling logic or something like that. I definitely had the mysql stuff more optimized there so keep this in mind. Commented Feb 29 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.