PowerShell.com
Library » Script Library » MySQL » Slideshow

MySQL Slideshow

  1. <# 
  2. .SYNOPSIS 
  3.     Queries database for all servers designated to an owner. 
  4. .DESCRIPTION 
  5.     Uses MySql .Net Connector to perform query and outputs results to an excel spreadsheet. 
  6. .NOTES 
  7.     List filters based on POC_Primary, for full listing remove Where clause. 
  8. .LINK 
  9.     http://www.mysql.com/downloads/connector/net/6.2.html 
  10. #> 
  11. $ErrorActionPreference = "SilentlyContinue" 
  12. # Requires MySQL .Net Connector installed see link above 
  13. [void][system.reflection.Assembly]::LoadFrom("C:\Program Files\MySQL\MySQL Connector Net 6.1.2\Assemblies\MySQL.Data.dll") 
  14. #region Build instance to write output to Excel 
  15. $a = New-Object -comobject Excel.Application 
  16. $a.Visible = $True 
  17. $b = $a.Workbooks.Add() 
  18. $c = $b.Worksheets.Item(1) 
  19. # Create header Row 
  20. $c.Cells.Item(1,2) = "App Owner" 
  21. $c.Cells.Item(1,1) = "Server Name" 
  22. $c.Cells.Item(1,4) = "SLA" 
  23. $c.Cells.Item(1,3) = "OS Version" 
  24. $c.Cells.Item(1,5) = "Application" 
  25. $c.Cells.Item(1,6) = "Status" 
  26. #endregion 
  27. #region Acquire Data 
  28. $myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection 
  29. $myconnection.ConnectionString = "server=server.domain.com;user id=sa;password=sapassword;database=server;pooling=false" 
  30. $myconnection.Open() 
  31.  
  32. $mycommand = New-Object MySql.Data.MySqlClient.MySqlCommand 
  33. $mycommand.Connection = $myconnection 
  34. $mycommand.CommandText = "SELECT Device_Name, POC_Primary, App_Owner1, Metal_Level, OS, Application, Status FROM Computers WHERE POC_Primary LIKE '%Name%' Order By Device_Name" 
  35. $myreader = $mycommand.ExecuteReader()  
  36. #endregion 
  37. $rowval=2 
  38. while($myreader.Read()) 
  39. {  
  40.     Write-Progress "Reading record" ($rowval - 1) 
  41.     Write-Host "Looking at:  " $myreader.GetString('Device_Name') 
  42.     $c.Cells.Item($rowval,1) =$myreader.GetString('Device_Name') 
  43.     $c.Cells.Item($rowval,2) =$myreader.GetString('App_Owner1') 
  44.     $c.Cells.Item($rowval,3) =$myreader.GetString('OS') 
  45.     $c.Cells.Item($rowval,4) =$myreader.GetString('Metal_Level') 
  46.     $c.Cells.Item($rowval,5) =$myreader.GetString('Application') 
  47.     $c.Cells.Item($rowval,6) =$myreader.GetString('Status') 
  48.     $rowval=$rowval + 1 
  49. }  
  50.  
  51. $rowval = $rowval + 2 
  52.  
  53. $WorkBook= $c.UsedRange 
  54. $WorkBook.EntireColumn.AutoFit() 
  55. #region filename construction 
  56. $curdate = Get-Date 
  57. # Numeric date 
  58. $curday=$curdate.Day 
  59. $curday = $curday.ToString("00") 
  60. # Numeric month 
  61. $curmonth=$curdate.Month 
  62. $curmonth=$curmonth.ToString("00") 
  63. # Numeric year 
  64. $curyear=$curdate.Year 
  65. # Create a single value 
  66. $dateval = $curday + $curmonth + $curyear 
  67. $listname="C:\temp\myservers" + $dateval + ".xls" 
  68. #endregion 
  69. $b.SaveAs($listname) 
  70. Write-Host "Closing connection" 
  71. $myreader.close() 
  72. $myconnection.close() 
  73.  
  74.  
  75. # SIG # Begin signature block 
  76. # MIIEGQYJKoZIhvcNAQcCoIIECjCCBAYCAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB 
  77. # gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR 
  78. # AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUJs+TIYuE+yNbUtaZkJ32kBTP 
  79. # 5A6gggIuMIICKjCCAZegAwIBAgIQBWvR4TvkQb1Nsuz2LJJOnjAJBgUrDgMCHQUA 
  80. # MCExHzAdBgNVBAMTFlBvd2VyU2hlbGwgQ2VydGlmaWNhdGUwHhcNMDkxMTIwMTU1 
  81. # NDE5WhcNMzkxMjMxMjM1OTU5WjAhMR8wHQYDVQQDExZQb3dlclNoZWxsIENlcnRp 
  82. # ZmljYXRlMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCn3YPl6WlH5G0smXVS 
  83. # tLd8PlpEppqe7197qlmDzDOsfIFrJyw5CtHAw20j6bzhADsI2QJrpQcRvO0mcrdn 
  84. # GNJoXsYH/aHdMzRiybPVTgZK305oNLkQ3bn8YPMkfGbXgwZT2uAI7cL42RJC6MFq 
  85. # OF3/sHrjeu0IkpKCfbW7cK5o4QIDAQABo2swaTATBgNVHSUEDDAKBggrBgEFBQcD 
  86. # AzBSBgNVHQEESzBJgBCl9FjEF23wFZFOSUNYP5QNoSMwITEfMB0GA1UEAxMWUG93 
  87. # ZXJTaGVsbCBDZXJ0aWZpY2F0ZYIQBWvR4TvkQb1Nsuz2LJJOnjAJBgUrDgMCHQUA 
  88. # A4GBAHbFtF3acwjL2jkgcharfvkgAmDMI5BWvyJ4i7pyK4JcILPXWusK+f7P/wVN 
  89. # XH0sf/EgofRI3/hHSx52YkcT7vjMhix8+GyS+Du9UB/ocLgmZFS9VK1o97mO1Qnp 
  90. # dBroPJKzAvPnfSzHO6xOOGT6i5MMlQOdXzp2+8yFJY5P5lcdMYIBVTCCAVECAQEw 
  91. # NTAhMR8wHQYDVQQDExZQb3dlclNoZWxsIENlcnRpZmljYXRlAhAFa9HhO+RBvU2y 
  92. # 7PYskk6eMAkGBSsOAwIaBQCgeDAYBgorBgEEAYI3AgEMMQowCKACgAChAoAAMBkG 
  93. # CSqGSIb3DQEJAzEMBgorBgEEAYI3AgEEMBwGCisGAQQBgjcCAQsxDjAMBgorBgEE 
  94. # AYI3AgEVMCMGCSqGSIb3DQEJBDEWBBTwG3TN2AhsGp/MEEst/djD6dJOwjANBgkq 
  95. # hkiG9w0BAQEFAASBgCwFMsx/sx6prEXVlGJ6D1j1AsBgZZz7NCWpc6xDUTyq1LWF 
  96. # IN5sBnwyg95lk3Y/qlSRGM8WWmI5qNuMyYgi9eVyYaXHF+LCNooEAqSRer45oKC5 
  97. # yrUnXk1CmVhja/w35zWrCdBiN0pCIjF9oEkRpHrVVqnSQvYGhcgpQAY7vIqs 
  98. # SIG # End signature block 
 
Loading...
View all files
Concentrated Tech NSoftware Dell Compellent Sponsored by Idera and Concentrated Tech and NSoftware and Dell Compellent
Copyright 2011 PowerShell.com. All rights reserved.