I have been working on this script below with several different scripts amalgamated into one from various sources. I am trying to get the output out from the file check which without the Out-Datatable is the actual result as if I were to use it on a single location but with every server pulled from some SQL scripts.The result it displays with Out-Datatable however is the properties of the script and it brings out the length of the string rather than the actual string.
I am still not very proficient with PowerShell and I have been hashing scripts together to learn and help with what I am trying to achieve.
The end goal is to put this into a SQL table so I can report on it each day. I know its the way the Out-Datatable outputs the data and its reading the line length but I don't know anything about the PSObject.Properties function at all to get the right bit to work.
The same results happen if I were to out-file to txt and if I were to export-csv.
IF I can figure out how to attach a screenshot I will do so. My scripts are a has of a few into one because I am fairly new to PowerShell and don't use it every day. I am trying to build something to make my life and my teams lives easier.
If anyone could point me in the right direction I would be most appreciative.
The script is
#SQL Query for WClusters
$SQLServer = "Server"
$SQLDBName = "Database"
$SqlQuery = "SELECT Clusters from dbo.SERVERLIST WHERE something = 1 and AOCluster = 0;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
#SQL Query for AO folders
$SQLServer2 = "Server"
$SQLDBName2 = "Database"
$SqlQuery2 = "SELECT [replica_server_name] as 'ReplicaName' FROM [dbo].[SQLAOStatus] GHB JOIN [dbo].[ServerList] SL on GHB.dns_name = SL.Clusters
Where SL.DAOPS = 1;"
$SqlConnection2 = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection2.ConnectionString = "Server = $SQLServer2; Database = $SQLDBName2; Integrated Security = True;"
$SqlCmd2 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd2.CommandText = $SqlQuery2
$SqlCmd2.Connection = $SqlConnection2
$SqlAdapter2 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter2.SelectCommand = $SqlCmd2
$DataSet2 = New-Object System.Data.DataSet
$SqlAdapter2.Fill($DataSet2)
#data table to hold results
Function Out-DataTable
{
$dt = new-object Data.datatable
$First = $true
foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.properties | foreach {
if ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
elseif ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
else {
$DR.Item($_.Name) = [string]::Join($item ,";")
}
}
$DT.Rows.Add($DR)
$First = $false
}
return @(,($dt))
}
clear
#Create Function for output data
Function SQLForEach
{
#Loop through results from the WCluster SQL Query
foreach ($cluster in $DataSet.Tables[0].clusters)
{
$f = "\\$Cluster\EMSCC\SAPClientCodes.csv"
$OutPut = "\\Server\C$\Temp\MorningChecks\EMSCCCheck"
$nowtime = Get-Date
$files = ls $f
Foreach ($files in $files)
{
$filelocal = $f
$LastWriteTime = $files.LastWriteTime
if (($nowtime - $LastWriteTime).totalhours -gt 24)
{"$f last modified more than 24 hours ago"}
Else
{"$f modified within the last 24 hours"}
}
}
#Loop through results from the SQL AO Query
foreach ($ReplicaName in $DataSet2.Tables[0].ReplicaName)
{
$f = "\\$ReplicaName\EMSCC\SAPClientCodes.csv"
$OutPut = "\\Server\C$\Temp\MorningChecks\EMSCCCheck"
$nowtime = Get-Date
$files = ls $f
Foreach ($files in $files)
{
$filelocal = $f
$LastWriteTime = $files.LastWriteTime
if (($nowtime - $LastWriteTime).totalhours -gt 24)
{"$f last modified more than 24 hours ago"}
Else
{"$f modified within the last 24 hours"}
}
}
}
$date = Get-Date
$OutPut = "\\Server\C$\Temp\MorningChecks\EMSCCCheck"
$EMSCCSQL = SQLForEach | select | Out-DataTable
Write-Output $EMSCCSQL #| out-file C:\TEMP\EMSCCCodes.txt
#foreach ($EMSCCSQL in $EMSCCSQL)
# {
# $datatable = SQLForEach | select | Out-DataTable
# $connectionString = "Data Source=Server; Integrated Security=True;Initial Catalog=Database;"
# $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
# $bulkCopy.DestinationTableName = "dbo.SiteCheck1"
# $bulkCopy.WriteToServer($datatable)
# }
#$bulkCopy.DestinationTableName = "dbo.somedatabase"
#$bulkCopy.WriteToServer($EMSCCSQL)
$SqlConnection.Close()
Eventually it will be pushed to a DB table in SQL which is why i have commented it out for now. This is so I can report on it in furture.