Tuesday, 23 April 2013

PowerShell Script to find SQL Agent Job details on a SQL server instance

The below script helps to find SQL Agent Job details on a SQL server instance
 
1:  # Script to find SQL Agent Job details on a SQL server instances  
2:  # Created by - Vinoth N Manoharan  
3:  # Version 1.1  
4:  # Date - 15/09/2011  
5:  # Script Help :-  
6:  #---------------  
7:  # Parameter 1 :- "-s" to run powershell for Single Instance of SQL  
8:  # Parameter 2 :- SQL Server name  
9:  # Example1:- FindSQLJob.ps1 -s <SQLservername\instancename>  
10:  Clear-Host  
11:  $List =@()  
12:  $srv = @()  
13:  $srvAgt = @()  
14:  $srvjob = @()  
15:  $schedule = @()  
16:  $Jobfrequency = $null  
17:  $jobout =@()  
18:  $joboutstr = @()  
19:  $srvjobname =$null  
20:  $srvjobstate = $null  
21:  if($args.Length -ne 2)  
22:  {  
23:  Write-Host "Incorrect Paramenter Count use either -s to specify the servername/Serverlist"  
24:  }  
25:  elseif(($args[0] -eq "-s") -or ($args[0] -eq "-S"))  
26:  {  
27:  $computer = $args[1]  
28:  if($computer -ne $null)  
29:  {  
30:  $srt = "Server Name :- " + $computer  
31:  $srt  
32:  Echo "*************************"  
33:  Echo "`n"  
34:  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null  
35:  $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$computer"  
36:  $srvAgt = $srv.JobServer  
37:  foreach($srvjob in $srvAgt.Jobs)  
38:  {  
39:  $srvjobname = 'JobName : '+ $srvjob.Name  
40:  $srvjobstate = 'JobEnabled : ' + $srvjob.IsEnabled  
41:  #Echo "`n"  
42:  Echo $srvjobname  
43:  Echo $srvjobstate  
44:  $jobcnt = 0  
45:  $jobout = @()  
46:  $joboutstr = @()  
47:        ForEach($schedule in $srvjob.JobSchedules)  
48:        {  
49:        $FrequencyTypes = $schedule.FrequencyTypes  
50:        $FrequencyInterval = $schedule.FrequencyInterval  
51:        $FrequencyRecurrenceFactor = $schedule.FrequencyRecurrenceFactor  
52:        $FrequencyRelativeIntervals = $schedule.FrequencyRelativeIntervals  
53:        $FrequencySubDayInterval = $schedule.FrequencySubDayInterval  
54:        $FrequencySubDayTypes = $schedule.FrequencySubDayTypes  
55:        $jobcnt = $jobcnt + 1  
56:        $jobcntstr = $null  
57:        $x = @()  
58:        $jobout = @()  
59:        $y = $null  
60:        $cnt = $null  
61:        $cnt1 = $null  
62:        $z = $null  
63:        $str1 = $null  
64:        $str2 = $null  
65:        $str3 = $null  
66:        $str4 = $null  
67:        $str5 = $null  
68:        $str6 = $null  
69:        $x11 = @(  
70:                @(1,"Sunday"),  
71:                @(2,"Monday"),  
72:                @(4,"Tuesday"),  
73:                @(8,"Wednesday"),  
74:                @(16,"Thursday"),  
75:                @(32,"Friday"),  
76:                @(64,"Saturday")  
77:              )     
78:            if($FrequencyTypes -eq "Daily")  
79:            {  
80:             $Jobfrequency = "Runs every " +$FrequencyInterval+ " Day/Days"     
81:            }  
82:            elseif($FrequencyTypes -eq "Weekly")  
83:            {  
84:             Switch ($FrequencyInterval)  
85:              {  
86:               1{$Weeklyschday = "Sunday"}  
87:               2{$Weeklyschday = "Monday"}  
88:               4{$Weeklyschday = "Tuesday"}  
89:               8{$Weeklyschday = "Wednesday"}  
90:               16{$Weeklyschday = "Thursday"}  
91:               32{$Weeklyschday = "Friday"}  
92:               64{$Weeklyschday = "Saturday"}  
93:               62{$Weeklyschday = "WeekDays"}  
94:               65{$Weeklyschday = "WeekEnds"}  
95:               127{$Weeklyschday = "EveryDay"}  
96:               Default {  
97:                       $x = @(1,2,4,8,16,32,64)  
98:                          $itr = 0  
99:                          $cnt = 0  
100:                          $cnt1 = 0  
101:                          $cnt2 = 0  
102:                          $cnt3 = 0  
103:                          $cnt4 = 0  
104:                          $flag = 0  
105:                          $day1 = 0  
106:                          $day2 = 0  
107:                          $day3 = 0  
108:                          $day4 = 0  
109:                          $day5 = 0  
110:                          $day6 = 0  
111:                          $total = 0  
112:                          $total1 = 0  
113:                          $total2 = 0  
114:                          $total3 = 0  
115:                          $total4 = 0  
116:                          for($itr=0;$itr -le 5;$itr++)  
117:                          {  
118:                             $itr1 = $itr  
119:                             $cnt = $itr + 1  
120:                             $cnt1 = $itr + 2  
121:                             $cnt2 = $itr + 3  
122:                             while($cnt -le 6)  
123:                             {  
124:                                $total = $x[$itr]+$x[$cnt]  
125:                                if($FrequencyInterval -eq $total)  
126:                                {  
127:                                   $flag = 2  
128:                                   $day1 = $itr  
129:                                   $day2 = $cnt  
130:                                }  
131:                                #write-host $x[$itr] '+' $x[$cnt] '=' $total  
132:                                $temp = $cnt1  
133:                                while($cnt1 -le 6)  
134:                                {  
135:                                   $total1 = $x[$itr]+$x[$cnt]+$x[$cnt1]  
136:                                   if($FrequencyInterval -eq $total1)  
137:                                   {  
138:                                         $flag = 3  
139:                                         $day1 = $itr  
140:                                         $day2 = $cnt  
141:                                         $day3 = $cnt1  
142:                                   }  
143:                                   #Write-host $x[$itr] '+' $x[$cnt] '+' $x[$cnt1] '=' $total1  
144:                                   #Write-Host $itr '--' $cnt '--' $cnt1 '--' $cnt2  
145:                                   $cnt2 = $cnt1 + 1  
146:                                   While($cnt2 -le 6)  
147:                                   {  
148:                                      $total2 = $x[$itr]+$x[$cnt]+$x[$cnt1]+$x[$cnt2]  
149:                                      if($FrequencyInterval -eq $total2)  
150:                                      {  
151:                                         $flag = 4  
152:                                         $day1 = $itr  
153:                                         $day2 = $cnt  
154:                                         $day3 = $cnt1  
155:                                         $day4 = $cnt2  
156:                                      }  
157:                                      #Write-host $x[$itr] '+' $x[$cnt] '+' $x[$cnt1] '+' $X[$cnt2] '=' $total2  
158:                                      #Write-Host $itr '--' $cnt '--' $cnt1 '--' $cnt2  
159:                                      $cnt3 = $cnt2 + 1  
160:                                      While($cnt3 -le 6)  
161:                                      {  
162:                                         $total3 = $x[$itr]+$x[$cnt]+$x[$cnt1]+$x[$cnt2]+$x[$cnt3]  
163:                                         if($FrequencyInterval -eq $total3)  
164:                                         {  
165:                                            $flag = 5  
166:                                            $day1 = $itr  
167:                                            $day2 = $cnt  
168:                                            $day3 = $cnt1  
169:                                            $day4 = $cnt2  
170:                                            $day5 = $cnt3  
171:                                         }  
172:                                         #Write-host $x[$itr] '+' $x[$cnt] '+' $x[$cnt1] '+' $X[$cnt2] '+' $x[$cnt3] '=' $total3  
173:                                         $cnt4 = $cnt3 + 1  
174:                                         While($cnt4 -le 6)  
175:                                         {  
176:                                            $total4 = $x[$itr]+$x[$cnt]+$x[$cnt1]+$x[$cnt2]+$x[$cnt3]+$x[$cnt4]  
177:                                            if($FrequencyInterval -eq $total3)  
178:                                            {  
179:                                               $flag = 6  
180:                                               $day1 = $itr  
181:                                               $day2 = $cnt  
182:                                               $day3 = $cnt1  
183:                                               $day4 = $cnt2  
184:                                               $day5 = $cnt3  
185:                                               $day6 = $cnt4  
186:                                            }  
187:                                            #Write-host $x[$itr] '+' $x[$cnt] '+' $x[$cnt1] '+' $X[$cnt2] '+' $x[$cnt3] '+' $x[$cnt4] '=' $total4  
188:                                            $cnt4 = $cnt4 + 1  
189:                                         }  
190:                                         $cnt3 = $cnt3 + 1  
191:                                      }  
192:                                      $cnt2 = $cnt2 + 1  
193:                                   }  
194:                                   $cnt1 = $cnt1 + 1  
195:                                }  
196:                                $cnt1 = $temp  
197:                                $cnt = $cnt + 1  
198:                                $cnt1 = $cnt1 + 1  
199:                                #Write-Host $cnt '--' $cnt1 '--' $cnt2  
200:                             }  
201:                          }  
202:                          Switch($flag)  
203:                          {  
204:                          0{$Weeklyschday = "No Corresponding Value found in the Schedule"}  
205:                          2{  
206:                            $str1 = ($x11[$day1][1])  
207:                            $str2 = ($x11[$day2][1])  
208:                            $Weeklyschday = $str1 + '--' + $str2  
209:                           }  
210:                          3{  
211:                            $str1 = ($x11[$day1][1])  
212:                            $str2 = ($x11[$day2][1])  
213:                            $str3 = ($x11[$day3][1])  
214:                            $Weeklyschday = $str1 + '--' + $str2 + '--' + $str3  
215:                           }  
216:                          4{  
217:                            $str1 = ($x11[$day1][1])  
218:                            $str2 = ($x11[$day2][1])  
219:                            $str3 = ($x11[$day3][1])  
220:                            $str4 = ($x11[$day4][1])  
221:                            $Weeklyschday = $str1 + '--' + $str2 + '--' + $str3 + '--' + $str4  
222:                           }  
223:                          5{  
224:                            $str1 = ($x11[$day1][1])  
225:                            $str2 = ($x11[$day2][1])  
226:                            $str3 = ($x11[$day3][1])  
227:                            $str4 = ($x11[$day4][1])  
228:                            $str5 = ($x11[$day5][1])  
229:                            $Weeklyschday = $str1 + '--' + $str2 + '--' + $str3 + '--' + $str4 + '--' + $str5  
230:                           }  
231:                          6{  
232:                            $str1 = ($x11[$day1][1])  
233:                            $str2 = ($x11[$day2][1])  
234:                            $str3 = ($x11[$day3][1])  
235:                            $str4 = ($x11[$day4][1])  
236:                            $str5 = ($x11[$day5][1])  
237:                            $str6 = ($x11[$day5][6])  
238:                            $Weeklyschday = $str1 + '--' + $str2 + '--' + $str3 + '--' + $str4 + '--' + $str5 + '--' + $str6  
239:                           }  
240:                          }  
241:                      }             
242:              }  
243:              $Jobfrequency = "Runs on "+$Weeklyschday  
244:            }elseif($FrequencyTypes -eq "OneTime")  
245:            {  
246:             $Jobfrequency = "Runs only One Time"          
247:            }elseif($FrequencyTypes -eq "Monthly")  
248:            {  
249:             $Jobfrequency = "Runs every " + $FrequencyInterval +" day of the month"  
250:            }elseif($FrequencyTypes -eq "MonthlyRelative")  
251:            {  
252:              Switch ($FrequencyInterval)  
253:              {  
254:               1{$Weeklymnday = "Sunday"}  
255:               2{$Weeklymnday = "Monday"}  
256:               3{$Weeklymnday = "Tuesday"}  
257:               4{$Weeklymnday = "Wednesday"}  
258:               5{$Weeklymnday = "Thursday"}  
259:               6{$Weeklymnday = "Friday"}  
260:               7{$Weeklymnday = "Saturday"}  
261:               8{$Weeklymnday = "EveryDay"}  
262:               9{$Weeklymnday = "WeekDays"}  
263:               10{$Weeklymnday = "WeekEnds"}  
264:              }  
265:              $Jobfrequency = "Runs every "+$FrequencyRelativeIntervals+" "+$Weeklymnday+ " of the month"  
266:            }else{$Jobfrequency = "No Valid schedule specified"}  
267:           $jobout = $schedule  
268:           #Echo "`n"  
269:           #$jobcntstr = "Schedule:"+$jobcnt+":-"  
270:           #$jobcntstr  
271:           $jobout = $jobout | Add-Member JobSchedule -MemberType NoteProperty -Value $Jobfrequency -PassThru|Add-Member -MemberType AliasProperty -name ScheduleName -value Name -SecondValue System.String -PassThru|  
272:           Add-Member -Name Schedule -MemberType NoteProperty -Value $jobcnt -PassThru  
273:           #|Format-List -Property ScheduleName,IsEnabled,JobSchedule,ActiveStartTimeOfDay  
274:           $joboutstr += $jobout  
275:        }  
276:  $joboutstr | Format-Table -Property Schedule,ScheduleName,IsEnabled,FrequencyTypes,JobSchedule,ActiveStartTimeOfDay,ActiveStartDate,ActiveEndDate -AutoSize  
277:  }  
278:  }  
279:  }  

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

No comments:

Post a Comment