ࡱ> kmj 7bjbj $cc/@@ !|3#&xxx"""""""Y$&:"xxxx|""""""""x""""L,„Dv""#03#"5'"5'"""xxx@ :   # # The PowerShell CookBook # by Joe Webb # joew@webbtechsolutions.com # # Demonstrations # # cls ################# demo 1 # cmdlets #retrieve a list of services running on the local machine Get-Service #retrieve a list of process running on the local machine Get-Process #navigating the file system #can use dos-like syntax dir #or unix-like syntax ls #both are an alias for a cmdlet Get-ChildItem #can check the present working directory pwd #and change folders cd "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log" ls cd hklm:\ ls cd software ls #examine the contents of a file Get-Content ErrorLog; #what are the other cmdlets? Get-Command; #getting help on a cmdlet Get-Help Start-Job; #getting more help Get-Help Start-Job -detailed; #getting more help about a cmdlet Get-Service | Get-Member; Get-Service | Format-Table Name, DisplayName, MachineName, Status ################# demo 2 # scripting basics #using a variable $var1 = "localhost"; $var1; #populating at variable with a cmdlet (single file) $file = "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG"; $log = Get-Content $file; $log; #populating an array with a cmdlet (multiple files) $files = @("C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG", "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.1" "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.2"); $log = Get-Content $files; $log; #filtering with the where object; stopped services $services = Get-Service; $services | where {$_.Status -eq "Stopped"}; #filtering with the where object; sql server services $services | where {$_.Name -like "*SQL*" -and $_.Status -eq "Stopped"}; #formatting output in a list $services | Format-List; #formatting output in a table $services | Format-Table #formatting output in a table with additional attributes $services | Format-Table Name, DisplayName, Status, CanStop; #formatting output in a table with additional attributes and autosize $services | Format-Table Name, DisplayName, Status, CanStop -autosize; #foratting output with mulitple columns $services | Format-Wide; #sorting output $services | Sort-Object Status, Name; #exporting to csv files $services | Export-Csv c:\demo\services.txt; Notepad "c:\demo\services.txt"; #exporting to xml files $services | Export-CliXml c:\demo\services.xml; Notepad "c:\demo\services.xml"; #exporting to html files $services | ConvertTo-Html | out-file c:\demo\services.html; Notepad "c:\demo\services.html"; #clean up the files Remove-Item "c:\demo\services.html"; Remove-Item "c:\demo\services.*"; ls "c:\demo"; ################# demo 3 # parsing error Logs #parsing the SQL Server error logs $files = @("C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG", "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.1" "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.2"); $log = Get-Content $files; $log; Get-Help Select-String; $search = Severity: \d{2} $log | Select-String -pattern $search #parsing log files for failed login attempts $search = "Login Failed"; $log | Select-String -pattern $search ################# demo 4 # parsing the event log #parsing the event log Get-Help Get-EventLog -detailed; Get-EventLog Application; Get-EventLog Application -Newest 10; Get-EventLog Application -After "2010-09-30"; Get-EventLog Application -After "2010-09-30" -Before "2010-10-31"; Get-EventLog Application -EntryType Error; Get-EventLog Application -EntryType Error -After "2010-09-30" -Before "2010-10-31"; Get-EventLog Application -EntryType Error -After (Get-Date).AddDays(-7); Get-EventLog Application -Message "*failed*"; Get-EventLog Application | where {$_.EventId -eq 1309} | Format-Table -autosize; Get-EventLog Application -ComputerName localhost; ################# demo 5 # discovering SQL Server Instances #get a list of sql server instances sqlcmd -Lc; #write the list of servers to a text file $serverlist = "c:\demo\servers.txt"; sqlcmd -Lc > $serverlist; Notepad $serverlist; ################# demo 6 # retrieving server property information from a list of servers #load the assembly and read the list of servers from a file [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null; $servers = Get-Content $serverlist; $servers; #iterate through the list of servers to get properties foreach ($servername in $servers) { $servername = $servername.Trim(); if ($servername.Length -gt 0) { $instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername; Write-Host $servername; Write-Host "Product: " $instance.Information.Product; Write-Host "Edition: " $instance.Information.Edition; Write-Host "Version: " $instance.Information.Version; Write-Host "Product Level: " $instance.Information.ProductLevel; } } #clean up by removing the serverlist file Remove-Item "c:\demo\servers.txt"; ################# demo 7 # collecting other sql server instance properties #gather additional information $servername = "localhost"; $instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername; Write-Host $servername; Write-Host " Product: " $instance.Information.Product; Write-Host " Edition: " $instance.Information.Edition; Write-Host " Version: " $instance.Information.Version; Write-Host " Product Level: " $instance.Information.ProductLevel; Write-Host " Min. Server Memory: " $instance.Configuration.MinServerMemory.ConfigValue; Write-Host " Max. Server Memory: " $instance.Configuration.MaxServerMemory.ConfigValue; Write-Host " AWE Enabled: " $instance.Configuration.AweEnabled.ConfigValue; Write-Host " CLR Enabled: " $instance.Configuration.IsSqlClrEnabled.ConfigValue; Write-Host " XPCmdShell Enabled: " $instance.Configuration.XPCmdShellEnabled.ConfigValue; Write-Host " Number of Databases: " $instance.Databases.Count; Write-Host " Number of Logins: " $instance.Logins.Count; Write-Host " Number of Linked Servers: " $instance.LinkedServers.Count; Write-Host " Number of Jobs: " $instance.JobServer.Jobs.Count; Write-Host " OS Version: " $instance.Information.OSVersion; Write-Host " Physical Memory: " $instance.Information.PhysicalMemory; Write-Host " Platform: " $instance.Information.Platform; Write-Host " Number of Processors: " $instance.Information.Processors; Write-Host ""; Write-Host ""; ################# demo 8 # collecting database information #get database information $servername = "localhost"; $instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername; Write-Host $servername; $dbs = $instance.Databases; foreach($db in $dbs){ Write-Host ""; Write-Host "Database Name " $db.Name; Write-Host ""; Write-Host "Configuration Properties:" Write-Host " Database Owner " $db.Owner; Write-Host " Collation " $db.Collation; Write-Host " Primary File Path " $db.PrimaryFilePath; Write-Host " CompatibilityLevel " $db.CompatibilityLevel; Write-Host " AutoShrink " $db.AutoShrink; Write-Host " AutoClose " $db.AutoClose; Write-Host " Recovery Model " $db.RecoveryModel; Write-Host " Auto Create Statistics " $db.AutoCreateStatisticsEnabled; Write-Host " Auto Update Statistics " $db.AutoUpdateStatisticsEnabled; $dbSpaceAvailable = $db.SpaceAvailable/1KB; $dbDataSpaceUsage = $db.DataSpaceUsage/1KB; $dbIndexSpaceUsage = $db.IndexSpaceUsage/1KB; #Format the results to a number with three decimal places $dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable; $dbDataSpaceUsage = "{0:N3}" -f $dbDataSpaceUsage; $dbIndexSpaceUsage = "{0:N3}" -f $dbIndexSpaceUsage; Write-Host "" Write-Host "Statistical Information: " Write-Host " Create Date " $db.CreateDate; Write-Host " Space Available " $dbSpaceAvailable " MB"; Write-Host " Data Space Used " $dbDataSpaceUsage " MB"; Write-Host " Index Space Used " $dbIndexSpaceUsage " MB"; Write-Host "" Write-Host "Maintenance Information: "; Write-Host " Last Backup " $db.LastBackupDate; Write-Host " Last Differential Backup " $db.LastDifferentialBackupDate; Write-Host " Last Log Backup " $db.LastLogBackup; Write-Host "" Write-Host "Log File Information: "; $logs = $db.LogFiles; foreach ($log in $logs) { Write-Host " " $log.Name -nonewline; Write-Host ", " $log.FileName -nonewline; $logsize = $log.Size/1KB; $logsize = "{0:N3}" -f $logsize Write-Host ", " $logsize " MB"; } Write-Host "" Write-Host "Object Information:"; Write-Host " Number of Tables " $db.Tables.Count; Write-Host " Number of Views " $db.Views.Count; Write-Host " Number of Stored Procedures " $db.StoredProcedures.Count; Write-Host " Number of User Defined Functions " $db.UserDefinedFunctions.Count; Write-Host " Number of Triggers " $db.Triggers.Count; } ################# demo 9 # view job information for each server [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $servername = "localhost" $instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername; Write-Host $servername; Write-Host ""; foreach($job in $instance.JobServer.Jobs){ Write-Host "Job Information: "; Write-Host " Job Name " $job.Name; Write-Host " Job Enabled " $job.IsEnabled; Write-Host " Last Run Date " $job.LastRunDate; Write-Host " Last Run Outcome " $job.LastRunOutcome; } ################# demo 10 # delete old log files Set-Location "C:\Users\Joe Webb\sql\MSSQL10_50.MSSQLSERVER\MSSQL\Log\" foreach ($file in get-childitem) { if ($file.LastWriteTime -lt (Get-Date).AddDays(-30)) { Write-Host "Deleting File $file.Name"; del $File } } ################# demo 11 # sending emails #variables & constants $smtp_server = "localhost"; $to = "joew@webbtechsolutions.com"; $from = "joew@webbtechsolutions.com" $subject = "Something bad happened!" $body = "Something bad happened and your phone is about to start ringing." $name="localhost"; $service = Get-WmiObject Win32_Service -Computername $name -filter "name='SQLBrowser'"; if ($service.State -eq "Stopped") { #send-mailmessage -to $to -from $from -subject $subject -body $body -smtpserver $smtp_server; Write-Host "sending an email"; } ################# demo 12 # using functions function show-CurrentTime() { $current_time = [datetime]::Now.ToUniversalTime(); Write-Host "The current UTC time is: " $current_time; } show-CurrentTime; ################# demo 13 # calling stored procedures $SqlConnection = New-Object System.Data.SqlClient.SqlConnection; $SqlConnection.ConnectionString = "Server=(local);Database=master;Integrated Security=True"; $SqlCommand = New-Object System.Data.SqlClient.SqlCommand; $SqlCommand.CommandText = "sp_who"; $SqlCommand.Connection = $SqlConnection; $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter; $SqlAdapter.SelectCommand = $SqlCommand; $DataSet = New-Object System.Data.DataSet; $SqlAdapter.Fill($DataSet); $SqlConnection.Close(); $DataSet.Tables[0] | Format-Table; ################# demo 14 # incorporating best practices [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null; $servername = "localhost"; $instance = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $servername; Write-Host $servername; Write-Host " Product: " $instance.Information.Product; Write-Host " Number of Log Files " -nonewline; if ($instance.NumberOfLogFiles -lt 10) { Write-Host $instance.NumberOfLogFiles -ForegroundColor Red; } else { Write-Host $instance.NumberOfLogFiles; } Write-Host " Version: " $instance.Information.Version; 77 hf'@hqZhqZhf'@+HK\^`afg   4 5 N R S h l m gdqZ    ( + 7 : ; < \ r s  # = gdqZ= >  _ y   K 01ggdqZg Z&'Ohiz!QqrgdqZr#ESTUVWp@ 7gdqZ7Qwxyz%&TUcdgdqZ2VW|  #cd *+,cgdqZ5>^akqv=`JgdqZ() S !]!!!!!!!!""."Q""""""gdqZ""##@#S#~###$[$$$$A%%%%%%"&#&b&&& ''!'L''gdqZ''(B(G(Y((( )F)K)])))))*7*_*******+S+++8,gdqZ8,=,C,E,F,K,d,,,,,Q-b-z-----.5.k..........gdqZ.,/O////////////0*0N0s000000R1v111111gdqZ112*2+2I222222222233T33334:4;4z44445%5gdqZ%5)5-5G5g5h55596B6Z666666=7D7O7V777777gdqZ":pqZ/ =!"#$%666666666vvvvvvvvv666666>6666666666666666666666666666666666666666666666666hH6666666666666666666666666666666666666666666666666666666666666666666666666662 0@P`p2( 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p8XV~ OJPJQJ_HmH nH sH tH @`@ P$6NormalCJ_HaJmH sH tH DA D Default Paragraph FontRiR Table Normal4 l4a (k ( No List  |wThis Table Header;M >lNOPQB*CJOJQJaJphPK![Content_Types].xmlj0 u$Nwc$ans@8JbVKS(.Y$8MVgLYS]"(U֎_o[gv; f>KH|;\XV!]օ Oȥsh]Hg3߶PK!֧6 _rels/.relsj0 }Q%v/C/}(h"O = C?hv=Ʌ%[xp{۵_Pѣ<1H0ORBdJE4b$q_6LR7`0̞O,En7Lib/SeеPK!kytheme/theme/themeManager.xml M @}w7c(EbˮCAǠҟ7՛K Y, e.|,H,lxɴIsQ}#Ր ֵ+!,^$j=GW)E+& 8PK!\theme/theme/theme1.xmlYOoE#F{o'NDuر i-q;N3' G$$DAč*iEP~wq4;{o?g^;N:$BR64Mvsi-@R4Œ mUb V*XX! cyg$w.Q "@oWL8*Bycjđ0蠦r,[LC9VbX*x_yuoBL͐u_. DKfN1엓:+ۥ~`jn[Zp֖zg,tV@bW/Oټl6Ws[R?S֒7 _כ[֪7 _w]ŌShN'^Bxk_[dC]zOլ\K=.:@MgdCf/o\ycB95B24S CEL|gO'sקo>W=n#p̰ZN|ӪV:8z1f؃k;ڇcp7#z8]Y / \{t\}}spķ=ʠoRVL3N(B<|ݥuK>P.EMLhɦM .co;əmr"*0#̡=6Kր0i1;$P0!YݩjbiXJB5IgAФ޲a6{P g֢)҉-Ìq8RmcWyXg/u]6Q_Ê5H Z2PU]Ǽ"GGFbCSOD%,p 6ޚwq̲R_gJSbj9)ed(w:/ak;6jAq11_xzG~F<:ɮ>O&kNa4dht\?J&l O٠NRpwhpse)tp)af] 27n}mk]\S,+a2g^Az )˙>E G鿰L7)'PK! ѐ'theme/theme/_rels/themeManager.xml.relsM 0wooӺ&݈Э5 6?$Q ,.aic21h:qm@RN;d`o7gK(M&$R(.1r'JЊT8V"AȻHu}|$b{P8g/]QAsم(#L[PK-![Content_Types].xmlPK-!֧6 /_rels/.relsPK-!kytheme/theme/themeManager.xmlPK-!\theme/theme/theme1.xmlPK-! ѐ' theme/theme/_rels/themeManager.xml.relsPK] /74 = gr7"'8,.1%5756789:;<=>?@ABC8@0(  B S  ?//adW[hj !%(*+-79hp"_jlw!#SVy $(1>F4 : E G  * 2 Y a &4=V]hp  fi|cjmw$(2R\~>_>HLUy ,Hg%P':DQ5Qw>[/9=Fj6=.@DYoy}!?ogw ////!-Gady}#69NQTVhjnr(*79=Dtx `cz} 2;hp!+[c(1PXjq{  " * s | @ B E G i m r y  8 > R U U]+/4?X[| %/ei!(-4cjw| >Hai'Ub3<4D^j+9ht/9RZ6=)@jy A//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::qZ@/@@UnknownGTimes New Roman5Symbol3 Arial7Cambria7Calibri 1hy&y&u' T !hh4t0@HP?'f'@0"Joe Webb Oh+'0   , 8DLT\d' Normal.dotm Joe Webb1Microsoft Macintosh Word@F#@@ „ u'G`PICTVb HHb bHHHbb !! Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ Ƣco{{ް=o{o{cco{kZg9o{ cZo{g9o{g9o{o{g9Zo{o{c{?o{{sg9kZsskZo{kZo{g9g9kZskZg9kZg9kZkZg9c{' sso{ws{{o{g9{o{o{' kZwZcg9ccg9g9cZZֻ2{s{{{ww{=^o{VRZZNsZRNsNsZZRVR^^VRg9{wco{{ް-kZsVkZkZo{kZkZg9o{o{co{kZs+o{{g9o{kZo{g9sg9g9kZkZo{swkZw{{ޱ^s Ƣ Ƣ ƢwkZ{޲o{g9s Ƣ Ƣ'{{{{{E^VVZRNsR^RZVNsNsV^V^RNsRo{VZZcskZ{{!ckZg9cg9Zcc{޸wo{so{sso{ Ƣ Ƣ[ss{ss{ wwssw{{ww{ w{{{{kZ{{s{{s{o{ww{gg9c^kZZ^g9^kZc^^o{ZsZ^g9Zc^g9kZZVo{Zo{^ZkZ^Zg9ZkZZVZ^^Z{%{{{{{w# g9VVkZZZ^^Zo{ Ƣ Ƣ Ƣ]o{g9kZkZg9o{wkZwg9o{sso{kZo{o{kZo{skZsg9o{o{kZsso{kZso{co{kZo{go{o{kZo{kZkZo{kZo{o{kZg9sg9kZkZo{g9kZsg9kZkZo{c^o{kZso{cwkZkZg9sg9g9co{g9g9kZ# swsso{w{ww{޹# kZZcg9kZg9^c^g9 Ƣ Ƣ{޾7cZg9^c^ ckZZg9kZZg9kZcg9Zc^6{wssw^s cwsw{swwo{ws Ƣ Ƣ'{{wws{3cZRZcZVo{RZ^g9RVkZVZ{{޿g9ZkZ{w Ƣ Ƣ/ o{kZo{sso{o{g9kZswso{{/ kZkZg9scg9cwcckZkZcg9o{wg9kZ Ƣ Ƣ ƢAcRg9^Zwcg9scskZZkZkZ^kZo{kZscg9cZc? wwsso{wo{wwo{wo{ sws{o{wsso{o{s' o{skZsg9o{g9g9^skZw' s^kZo{kZkZg9kZcg9g9s Ƣ Ƣ/{{{w{{wU"^ZV^g9^VZVg9ZVo{VR^V^RkZ^ZRVZVkZRcZZVV^{'{wo{{{wo{^cckZwws Ƣ Ƣ1so{w{ssww{w{kZo{o{{w{-kZg9^Zo{^Nso{^^Z^kZg9{w{sw{s{{wwsww{swwswwssswws wso{o{wo{o{swwsw{ws%cNsg9o{o{V^VV^g9VZcZZRRo{ZVZ^ZVRc^cV{cVZVZV^VR^^Zc^Ns^^Z^g9cVR{M{s{w{sws{w{o{g9o{w{ޱ Ƣ Ƣwwg9RkZV^kZ{{޷g9kZ{{ޱ sZskZ^^g9kZ# skZ{o{sso{o{wwo{wo{o{ Ƣ Ƣ Ƣ Ƣ ƢAc^g9g9cg9cckZ^co{kZg9^kZg9ckZkZckZkZVg9? wwskZo{sso{{so{{o{o{so{sswo{wo{s.o{skZsso{kZo{o{so{so{{5s^kZo{kZg9kZckZg9skZsg9sg9g9Vw Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ Ƣ w ՜.+,0 hp  'WebbTech SolutionsTt0  Title  !"#$%&'()*+,-./0123456789:;<=>?@ABCDFGHIJKLMNOPQRSTUVWXZ[\]^_`acdefghilRoot Entry Fa,„n1TableE5'WordDocument$SummaryInformation(YDocumentSummaryInformation8bCompObj`ObjectPool`,„`,„ F Microsoft Word 97-2004 DocumentNB6WWord.Document.8