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
}
}
