Powershell script data into SQL

[Nikumba]

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

Cheers

Kimbie

# 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     = "[email protected]"	# List of users to email your report to (separate by comma)
$fromemail = "[email protected]"
$server    = "1.2.3.4"	#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
#endregion

$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)
	 }
  #endregion
  
  #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
    }
  }
  #endregion
}

$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