Powershell script output showing properties value in data table rather than the

PowerShell Help
1
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.
2
  • Your SQL query code is copy and pasted code and only a few parameters change. Anytime you are doing that, it should be a function. If you are running this script from the same place every time and you have SQL Management Studio installed, there is a SQLPS module available and you can use Invoke-SQLCmd to simplify things. Otherwise, you should build or or look for a function to perform the query and pass the required parameters.
  • It appears you are getting servers from your SQL query and then getting a single file in that path. Get-Item gets a single item. Get-ChildItem (ls) gets children or files\folders in a path. Next, I'm not really sure the purpose of writing to the host if a file is modified in the last 24 hours or not. You should either return the days modified or a True\False so that you can actually query it like $results | {Where $_.IsModifiedLast24hr -eq $true} 
    Get-Item C:\Users\Rob\Desktop\Archive\test.csv | 
Select Name,
       LastWriteTime,
       @{Name="ModifiedInDays";Expression={New-TimeSpan -Start $_.LastWriteTime -End (Get-Date) | Select -ExpandProperty Days}},
       @{Name="IsModifiedLast24hr";Expression={(New-TimeSpan -Start $_.LastWriteTime -End (Get-Date) | Select -ExpandProperty TotalHours) -le 24}}
3

Thank you so much for your help Rob, I tested your script and added the Cluster variable to display the cluster it is looking at going through the loop.

My next bit is to setup the SQL query to loop through the correct cluster names so it queries the right server.
Once that’s complete which I think I have done all be it the crude way I got it to work earlier.

Seem to be getting the output I desire through the 

SQLForEach | Out-DataTable | Format-Table -AutoSize

Only issue I still have is to try and push into a SQL table