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

 

[The_Max_Sti]

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
         $conn.Open()
##

$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
         $cmd.ExecuteNonQuery()

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.

Rob

 

[Showboat]

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...

Code:

$serverinstance = "your sql server"
$database = "dbname"
$user = "username to connect to sql"
$pass = "password for above user"

$insertcom = "insert into dbo.whatever_table (column,names,in,your,db,seperated,by,comma) values('$value1','$value2','$etc')"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
### If you want to use a non-standard sql port you can change 1433 to whatever, if you wont use it you can delete ",1433"
$SqlConnection.ConnectionString = "Server=$ServerInstance,1433;Database=$Database;UID=$User;PWD=$Pass"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "$insertcom"
$SqlCmd.Connection = $SqlConnection
$compname = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()

 

[smargh]

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

Use parameterised queries if at all possible.