PowerShell.com
Library » Script Library » MySQL » Query MySql database

Query MySql database

Previous | Next | View all files | View Slideshow
posted by jkavanagh58
02-16-2010
Downloads: 464
File size: 4.1kB
Views: 4,287
Embed
Query MySql database
  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 

get-myservers

Script will query a mySQL database for all servers assigned to a specific person, then write the resulting data to an excel worksheet.
This script was written without paramaters, version 2 will include parameters for Name to be used in query and a filename for the output.  The Connection string details (server;user id;password;database) were changed to protect the innocent.

Comments

foreman wrote re: Query MySql database
on 06-25-2010 12:31 PM

Oh, It took me some time until I got it to work. I am just starting to learn how to use scripts and such, so I appreciate your help.

Great site too.

jkavanagh58 wrote re: Query MySql database
on 08-16-2010 7:54 PM

Not sure what you had to work around but I know the version and path to the MySql .Net connector was always an issue when I upgrade to the latest version.

mivpl wrote re: Query MySql database
on 10-12-2010 12:55 PM

# $c.Cells.Item(1,2) = "App Owner"

# $c.Cells.Item(1,1) = "Server Name"   across your blog and have been reading along. I thought I would leave my first comment. I dont know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.<a href="www.quotesby.co.uk/.../">Famous People Quotes and Sayings</a>

ellyka19 wrote re: Query MySql database
on 10-14-2010 11:59 AM

I like your blog, currently we are looking for a part time article writer would you be interested?

simulationassuranceauto.org/comparatif-assurance-auto

kobirana wrote re: Query MySql database
on 01-01-2011 10:07 AM

This is really very nice site where you can get all the required information. <a href="http://www.checkcancersymptoms.com">cancer symptoms</a>

kobirana wrote re: Query MySql database
on 01-01-2011 10:08 AM

good one indeed. www.checkcancersymptoms.com/lip-cancer-symptoms.html

Concentrated Tech NSoftware Dell Compellent Sponsored by Idera and Concentrated Tech and NSoftware and Dell Compellent
Copyright 2011 PowerShell.com. All rights reserved.