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

Tagged: , ,

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Graham Harmsworth-Blyth Graham Harmsworth-Blyth 3 months, 1 week ago.

  • Author
    Posts
  • #60636

    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.

  • #60649
    Profile photo of Rob Simmers
    Rob Simmers
    Participant
    • 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}}
      
  • #60654

    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

You must be logged in to reply to this topic.