PowerShell - Get Maintenance Plan of SQL servers

Some of us know SQL server maintenance plan is encrypted in xml file. I believe you can find a lot resources how to query SQL server maintenance plan. The hardest part of the query is I need to know how to unwrap xml and retrieve information.
I found very useful sites http://sqlchad.com/?p=339 and http://www.sqlballs.com/2013/01/how-do-you-query-maintenance-plan.html. They gave me what I am really looking for. I enhanced the script from the site and combine with powershell script to retrieve full and tlog backup maintenance plan from all SQL servers which includes maintenance clean up task. The script works in 2005/2008R2 and 2012.

Enjoy Scripting...

$($ServerList = 'E:\PowerShell\SQLServerList\SQLSvrList.txt'
$exportcsv ='E:\PowerShell\GetMaintenancePlan\BackupInfo.csv'
Clear-Content $exportcsv
$servername = Get-Content $ServerList
$errorlog = 'E:\PowerShell\GetMaintenancePlan\Error.txt'
$RunTime = $(get-date -f MM-dd-yyyy_HH_mm_ss)
if(!(test-path $ServerList))
{
    Write-Error "SQLSvr.txt is not Found" -ea Stop
}
if(test-path $ServerList)
{
foreach($server in $servername){
Try{
$sqlconn = new-object System.Data.SqlClient.SqlConnection("server=$server;Trusted_Connection=true");
$query = "

IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0,CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0)) = 9
BEGIN

          WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
          , 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
          ,ssis AS (
              SELECT name
                  , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
              FROM [msdb].[dbo].[sysdtspackages90]
              WHERE packagetype = 6

             )
          SELECT '$server' as ServerName,s.name as MaintenancePlanName,
               CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupAction)[1]', 'INT')
                    WHEN 0 THEN
                         CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupIsIncremental)[1]', 'bit')
                              WHEN 1 THEN 'DIFFERENTIAL'
                              WHEN 0 THEN 'FULL'
                              ELSE 'Maintenance clean Up'
                         END
                    WHEN 1 THEN 'FILES'
                    WHEN 2 THEN 'LOG'
                    ELSE 'UNKNOWN'
               END as BackupType,
               CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupCompressionAction)[1]', 'int')
                    WHEN 0 THEN 'SERVER DEFAULT CONFIG'
                    WHEN 1 THEN 'YES'
                    WHEN 2 THEN 'NO'
               END as Compressed,
               c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'VARCHAR(MAX)') as BackupLocation
          FROM ssis s
              CROSS APPLY package.nodes('//DTS:ObjectData') t(c)
          WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1
       

END


ELSE IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0,CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0)) >= 10

BEGIN

          WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
          , 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
          ,ssis AS (
              SELECT name
                  , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
              FROM [msdb].[dbo].[sysssispackages]
              WHERE packagetype = 6

             )
          SELECT '$server' as ServerName,s.name as MaintenancePlanName,
             CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupAction)[1]', 'INT')
                     WHEN 0 THEN
                         CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupIsIncremental)[1]', 'bit')
                              WHEN 1 THEN 'DIFFERENTIAL'
                              WHEN 0 THEN 'FULL'
                              ELSE 'Maintenance clean Up'
                         END
                    WHEN 1 THEN 'FILES'
                    WHEN 2 THEN 'LOG'
                    ELSE 'Maintenance clean Up'
               END as BackupType,
  COALESCE(c.value('(SQLTask:SqlTaskData/@SQLTask:BackupFileExtension)[1]','varchar(1000)'),
  c.value('(SQLTask:SqlTaskData/@SQLTask:FileExtension)[1]','varchar(1000)')) as Extension,
           COALESCE(c.value('(SQLTask:SqlTaskData/@SQLTask:BackupFileExtension)[1]','varchar(1000)'),
  c.value('(SQLTask:SqlTaskData/@SQLTask:FileExtension)[1]','varchar(1000)'))+'$server' as PK,

'DELETE backups older than ' + c.value('(SQLTask:SqlTaskData/@SQLTask:RemoveOlderThan)[1]','VARCHAR(3)') +
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:TimeUnitsType)[1]','VARCHAR(10)') --0=Daily,1=Weekly,2=Monthly,3=Yearly,5=Hourly
WHEN 0 THEN ' Day(s)'
WHEN 1 THEN ' Week(s)'
WHEN 2 THEN ' Month(s)'
WHEN 3 THEN ' Year(s)'
WHEN 4 THEN ' Minute(s)'
WHEN 5 THEN ' Hour(s)'
END AS Del_Freqency,
       CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupCompressionAction)[1]', 'int')
                    WHEN 0 THEN 'SERVER DEFAULT CONFIG'
                    WHEN 1 THEN 'YES'
                    WHEN 2 THEN 'NO'
               END as Compressed,
  c.value('(SQLTask:SqlTaskData/@SQLTask:BackupVerifyIntegrity)[1]', 'BIT') as BackupVerified,
               c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'VARCHAR(MAX)') as BackupLocation



          FROM ssis s
              CROSS APPLY package.nodes('//DTS:ObjectData') t(c)
 where s.name in('Full BackUp','Transaction Logs')
END

"
$sqlconn.Open()
$sqlcmd = new-object System.Data.SqlClient.SqlCommand ($query, $sqlconn);
$sqlcmd.CommandTimeout = 0;
#$dr = $sqlcmd.ExecuteReader();
$dr = New-Object System.Data.SqlClient.SqlDataAdapter
$dr.SelectCommand = $sqlcmd
$dt = New-Object System.Data.DataTable
$dr.fill($dt) | out-null
$dt
}
Catch
{    Clear-Content $errorlog
    "Fail to get information $server +' ' + $RunTime :$_" |Out-File $errorlog -Append
}
}
})|Export-csv $exportcsv -noType
Get-Job | Wait-Job | Out-Null
Remove-Job -State Completed


Thank you.