1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Powershell script data into SQL

Discussion in 'Servers and Enterprise Solutions' started by Nikumba, Jun 23, 2014.

  1. Nikumba


    Joined: Dec 4, 2002

    Posts: 3,662

    Location: Bourne, Lincs

    I have a powershell script that runs twice a day and retrieves various stats that we use to build a daily report, however currently this goes into a csv and we have to then copy it into our rag etc.

    Is it possible to have this data going into SQL db, so can have historical record, have graphs made against it etc?

    Code is below



    # PowerShell Systems Report
    # Example usage: .DailyReport-CSV.ps1 .\list.txt
    # Remember that list.txt is the file containing a list of Server names to run this against
    #region Variables and Arguments
    $users     = "infrastructureteam@domain.co.uk"	# List of users to email your report to (separate by comma)
    $fromemail = "infrastructureteam@domain.co.uk"
    $server    = ""	#enter your own SMTP server DNS name / IP address here
    #$list      = $args[0]	#This accepts the argument you add to your scheduled task for the list of servers. i.e. list.txt
    [int]$EventNum = 5
    # [int]$ProccessNumToFetch = 10  ## not used
    $DiskInfo     = @()
    $SystemInfo   = @()
    $SystemEvents = @()
    $AppEvents    = @()
    foreach ($computer in get-content C:\Scripts\DailyReport_CSV\list.txt) {
      $DiskInfo += Get-WMIObject -ComputerName $computer Win32_LogicalDisk |
                     Where-Object { $_.DriveType -eq 3} |
     #                Where-Object { ($_.freespace/$_.Size)*100 -lt $thresholdspace} |
                     Select-Object @{n='ComputerName'  ; e={$Computer}},
                                   DriveType, VolumeName, Name,
                                   @{n='Size (GB)'     ; e={"{0:n2}" -f ($_.size/1gb)}},
                                   @{n='FreeSpace (GB)'; e={"{0:n2}" -f ($_.freespace/1gb)}},
                                   @{n='Used (GB)'     ; e={"{0:n2}" -f (($_.size/1gb)-($_.freespace/1GB))}}
      #region System Info
      $SysInfo = Get-WmiObject -Class Win32_OperatingSystem -computername $computer |
                   Select-Object Caption, Name, TotalVisibleMemorySize, FreePhysicalMemory, ServicePackMajorVersion
      $SystemInfo += New-Object PsObject -Property @{
         ComputerName   = $Computer   
         OS             = $SysInfo.caption
         ServicePack    = $SysInfo.ServicePackMajorVersion
        # AvgCPU         = gwmi win32_processor -computername $computer | Measure-Object -property LoadPercentage -Average | Select Average
         AvgCPU         = [Math]::Round((gwmi win32_processor -computername $computer | Measure-Object -property LoadPercentage -Average).Average,0)
    	 TotalRAM       = [Math]::Round($SysInfo.TotalVisibleMemorySize/1MB, 2)
         FreeRAM        = [Math]::Round($SysInfo.FreePhysicalMemory    /1MB, 2)
         UsedRAM        = [Math]::Round(($SysInfo.TotalVisibleMemorySize - $SysInfo.FreePhysicalMemory)/1MB, 2)
         RAMPercentFree = [Math]::Round((   $SysInfo.FreePhysicalMemory / $SysInfo.TotalVisibleMemorySize) * 100, 2)
         RAMPercentUsed = [Math]::Round((1- $SysInfo.FreePhysicalMemory / $SysInfo.TotalVisibleMemorySize) * 100, 2)
      #region Event Logs Report
      foreach ($event in Get-EventLog -ComputerName $computer -LogName System -EntryType Error,Warning -Newest $EventNum) {
        $SystemEvents += New-Object -Type PSObject -Property @{
          ComputerName  = $Computer
          TimeGenerated = $event.TimeGenerated
          EntryType     = $event.EntryType
          Source        = $event.Source
          Message       = $event.Message
      $ApplicationEvents = 
      foreach ($event in Get-EventLog -ComputerName $computer -LogName Application -EntryType Error,Warning -Newest $EventNum) {
        $AppEvents += New-Object -Type PSObject -Property @{
          ComputerName  = $Computer
          TimeGenerated = $event.TimeGenerated
          EntryType     = $event.EntryType
          Source        = $event.Source
          Message       = $event.Message
    $DiskInfo                  | Export-CSV c:\temp\DailyReport-DiskInfo.csv -NoType
    $SystemInfo                | Export-CSV c:\temp\DailyReport-SystemInfo.csv -NoType
    $SystemEvents + $AppEvents | Export-CSV c:\temp\DailyReport-Events.csv -NoType
    Send-MailMessage -SMTPServer $server -From $fromemail -To $users -Subject 'Daily Report CSVs' `
      -Attachments c:\temp\DailyReport-DiskInfo.csv, c:\temp\DailyReport-SystemInfo.csv, c:\temp\DailyReport-Events.csv
  2. The_Max_Sti


    Joined: Aug 7, 2003

    Posts: 204

    Location: Bristol

    Simple answer is yes you can..

    You can do a simple

    ##Connect to the SQL server and the Database
          $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source={ServerName}; Initial Catalog={DATABASE YOU WANT TO INSERT INFO}A; Integrated Security=SSPI")
     ## Open DB Connection
    $insert_stmt = "
                INSERT INTO [dbo].[TABLE_NAME]([Feilds],[Go],[In],[Here]) 
    	    VALUES ($values,$go,$in,$here) 
    ## Create your command
             $cmd = $conn.CreateCommand()
             $cmd.CommandText = $insert_stmt
    ## Invoke the Insert statement
    And stick this in a loop..

    I do this for Several hundred SQL servers collecting stats on all of them and reporting back to one server.

  3. Showboat

    Wise Guy

    Joined: Sep 11, 2009

    Posts: 2,005

    Location: UK

    I can give you the commands you need to get it into sql, you would really need to play around editing your script to get it working as you need.
    You would also need a sql server qith a sql db setup with the columns you will use...


  4. smargh


    Joined: Dec 29, 2010

    Posts: 74

    Beware SQL injection, or reserved character errors, from the previous two bits of code.

    Use parameterised queries if at all possible.