none
SC Data Protection Manger Custom Reporting with SQL Report Builder RRS feed

  • Question

  • Hi all,

    We had moved to DPM 2012 SP1 from Backup Exec 2010.  we have no issues with DPM other than backup reports!

    Can anyone tell me is there a way to generate Backup Job report including below:(may be using SQL report builder 3.00)

    1. with final result of the daily protection group sync to tape job result (Success or Fail!) 

    2. What are the folder's been backup, and if can what are pending or failed to sync.

    3. additionally byte count and time spent !

    yes everyone knows backup exec had nice daily job status reporting including final result, what was backup, what was not backup and why it get failed!!. can I get same or equivalent report without buying third-party software for the same.

    Thanks

    JCK


    janakackv

    Monday, December 2, 2013 8:05 PM

All replies

  • Hi,

    You can get 1, and 3, using a simple jobs custom filter.  You can automate the process and have report generated using a power shell script.   Download the DPMJOBS-V3.0 from here: https://skydrive.live.com/?cid=b03306b628ab886f&id=B03306B628AB886F%21524and read the instructions in the DPMJobs.mht

    You will need to use a modified version of the DPMJobsQ.ps1 script below for DPM 2012 SP1since the job filter xml file location changed since the original script was created.

     

    #Author  : Ruud Baars
    #Updated : 11/25/2010
    #Updated  : 04/12/2012 Mike Jacquet
    #
    # v2.3 added boolean -batch parameter returning jobs collection and exit
    # v2.4 rigidized
    # v2.5 added statistics for multi purpose use
    # v2.6 added status as first column
    # v2.7 fixed some batch aspects
    # v2.8
    #  - changed boolean inputs to switches and use default filter 'Todays all jobs'
    #  - enabled cancelledjobs in filter
    # v2.9 remove unconditional disconnect (served IDE debug only)
    # v3.0
    #  - No longer filter on Datasize=0 to we do see shadowcopies
    #  - Include cancelled jobs
    #  - Include only elapsed time >0 (excluding scheduled/inprogress) avoiding negative stats
    #  - If status failed then report 'ErrorCode' rather then 'Status' so we see "Cancelled" etc..
    # v3.1
    #  - inserted 'data source type' after 'data source name'
    #  - added size stats min/max/sum
    # v3.2
    #   - Changed jobsfilter.xml path after DPMQFE Update
    # $path = "$env:APPDATA\Microsoft\Microsoft System Center Data Protection Manager"
    # $filterfile = Resolve-Path $path\JobsFilter.xml
    #
    #
    #Synopsis : Writes job information to tab separated file, strings are quoted.
    #     Jobs are selected through standard DPM filter mechanism by filtername
    #     Filters are composed & saved through Monotoring-Jobs pane in DPM UI console
    #
    # as ref:  [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.JobManagement.Filter]
    #$confirmpreference = "None"


    param(
     [string]$dpmservername,
     [string]$filtername="Todays all jobs(default)",
     [string]$outputpath = ".\", [switch]$debug,
     [switch]$append, [switch]$reportfilters ,
     [string]$mailto,
     [switch]$batch)

    function DoStats
    {
     param($minutes, $size, $type)
     #add key if not exists
     $size = $size/$MB
     if (!$statsMax.ContainsKey($type))
     {
      $statsMin.Add($type, $minutes)
      $statsMax.Add($type, $minutes)
      $statsSum.Add($type, 0)
      $statscount.Add($type,0)
      $statsSizeSum.Add($type,0)
      $statsSizeMax.Add($type,$size)
      $statsSizeMin.Add($type,$size)
     }
     #store min/max/sum/count
     if ($minutes -lt $statsMin[$type]) {$statsMin[$type]=$minutes}
     if ($minutes -gt $statsMax[$type]) {$statsMax[$type]=$minutes}
     if ($size -lt $statsSizeMin[$type]) {$statsSizeMin[$type]= $size}
     if ($size -gt $statsSizeMax[$type]) {$statsSizeMax[$type]=$size}
     $statsSum[$type] += $minutes
     $statsSizeSum[$type] += $size
     $statsCount[$type] += 1

    }
    function DoWork {
     param($dpm, $filter, $outputfile)

     #Get desired filter from DPM filter specs
     #This filter must be composed and saved in DPM GUI Monitoring-Job pane
     writelog "Getting filter $filter"
     $f = GetFilter $filter $FALSE
     if (!$f) { writelog "Filter `"$filter`" not found!"; return 2}
     
     #get the desired jobs
     writelog "Connecting to DPM server $dpm..."
     $dpmobj = Connect-DPMServer $dpm
     $jq = $dpmobj.JobQuery($false) #use events
     
     if ($jq)
     {
      $jq.CancelledJobs=$true
      $jq.GetAllJobs($f)
     }
     else
     {
      Throw "No jobs object returned!"
     }
      
     if ($batch) {
      #while (!$jq.IsQueryComplete) {Start-Sleep 1} 
      return $jq
     }

     #could take a while and must wait for usable object
     $begin = get-date
     while (!$jq.IsQueryComplete) {Start-Sleep 5; writelog "Waiting for query to complete"}
     $took = (get-date).Subtract($begin).seconds
     writelog "Waited $took seconds for query to complete"

     #create job info output
     writelog "Starting jobs info output..."
     Writelog ""
     $MB = 1024 * 1024
     $i = 0
     $j = 0
     $k = 0
     $showlines = $jq.jobs.count
     if ($debug -and $showlines -gt 10)
     {
      Writelog "There are $showlines jobs found with 1 or more tasks"
      $answer = 0
      while($answer -lt 1) {$answer = Read-Host "How many task item lines do you want to display? "}
      $showlines = $answer
      Write-Host `n
     }
     foreach ($job in $jq.jobs)
     {
      $j ++
      foreach ($task in $job.Tasks)
      {
       $mns = ($task.endtime-$task.starttime).totalminutes
       if ($mns -gt 0) {
        DoStats $mns $task.TaskInfo.DataSize $task.type
        $endtime = $task.endtime}
       else {
        $endtime=$task.starttime
       }
       if ($task.Status -eq "Failed") {$tstat = $task.ErrorCode} else {$tstat=$task.status}
       $line = "`""+ $tstat + "`"`t"
       $line = $line + "`""+ $task.taskinfo.datasource.psinfo.machinename + "`"`t" + "`"" + $task.taskinfo.datasource.name +   "`"`t" + "`"" + $task.taskinfo.datasource.type.name +  "`"`t" + ([math]::round(($task.taskinfo.datasize/$MB),3))
       $line = $line + "`t" + ($task.starttime) + "`t" + ($endtime) + "`t" + ([math]::Round(($endtime-$task.starttime).totalminutes,2)) + "`t" + "`"" + $task.Type + "`""
       if ($task.TaskInfo.Library)
       {
        $line += "`t" + "`"" + $task.TaskInfo.Library.Trim() + "`""
       }
       else
       {
        $line += "`t" + "`"" + "`""
       }
       if ($task.TaskInfo.MediaList)
       {
        $line = $line + "`t" + "`"" + $task.TaskInfo.MediaList.trim() + "`""
       }
       else
       {
        $line += "`t" + "`"" + "`""
       }

       #if ($task.taskinfo.datasize -gt 0) {$line >> $outputfile; $k ++ }
       $line >> $outputfile; $k ++
       if ($debug) {if ($i -lt $showlines) {Write-Host $line}}
       $i ++
      }
     }

     WriteLog "`nProcessed $j jobs with $i tasks and wrote $k tasks to $outputfile"

     #show statistics
     writelog "`n==< STATISTICS >=="
     $keys = $statsSum.keys
     foreach ($k in $keys)
     {
      $count = $statsCount[$k]
      writelog "`n`t$k COUNT = $count"
      
      $avg = [math]::round($statsSum[$k]/$count,2)
      writelog "`t$k AVERAGE job time = $avg minutes"
      $tmp = [math]::round($statsSum[$k],2)
      writelog "`t$k TOTAL job time   = $tmp minutes "
      $tmp = [math]::round($statsMax[$k],2)
      writelog "`t$k LONGEST job time = $tmp minutes"
      $tmp = [math]::round($statsMin[$k],2)
      writelog "`t$k SHORTEST job time = $tmp minutes`n"
      
      $avg = [math]::round($statsSizeSum[$k]/$count,2)
      writelog "`t$k AVERAGE job size = $avg MB"
      $tmp = [math]::round($statsSizeSum[$k],2)
      writelog "`t$k TOTAL data   = $tmp MB "
      $tmp = [math]::round($statsSizeMax[$k],2)
      writelog "`t$k LARGEST job = $tmp MB"
      $tmp = [math]::round($statsSizeMin[$k],2)
      writelog "`t$k SMALLEST job = $tmp MB"
     }
     writelog "==< DONE >==`n"
     sendmail $mailto $outputfile
     Writelog "=== FINISHED ==="
     writelog ""
    }
    function Writelog {
     param([string]$msg)
     if ($batch) {
      $msg = "BATCHED: " + $msg
      $msg >> $logfile
      if ($debug) {Write-Host $msg}
     }
     else {
      $msg >> $logfile
      if ($debug) {Write-Host $msg}
     }
    }
    function GetFilter{
     param([string]$fltnam, [boolean]$report)

     #Need to do these steps to properly deserialize "JosFilter.XML" into arraylist of filter objects
     $filterfile = Resolve-Path $path\JobsFilter.xml
     writelog "Processing JobsFilter.XML"
     $sr = New-Object System.IO.StreamReader($filterfile,$TRUE)
     $result = $sr.ReadToEnd()
     #dispose explicitly to avoid conflicts with DPM GUI
     $sr.dispose()
     [System.IO.TextReader]$reader = New-Object System.IO.StringReader($result)

     #create the type we need to pass as deserialisation type parameter
     [Microsoft.Internal.EnterpriseStorage.Dls.UI.ObjectModel.JobManagement.Filter[]]$farr = @()
     writelog "XML done and deserializing..."
     #create deserialiser for arraylist of filter types
     $deser = New-Object System.Xml.Serialization.XmlSerializer($farr.gettype())
     #deserialize and return desired filter object or all
     if (!$report)
     {
      return ($deser.Deserialize($reader) | where {$_.FilterName -eq $fltnam})
     }
     else
     {
      return ($deser.Deserialize($reader) )
     }
    }
    function ShowFilters{
     write-host "Retrieving filter list...`n"
     $f = GetFilter "dummy" $TRUE
     Write-Host "`tAvailable filters are" -ForegroundColor white
     Write-Host "`t====================="
     foreach ($fn in $f) {Write-Host `t$fn -ForegroundColor white}
     Write-Host `n`n
    }
    function show_help {
     Write-Host
     Write-Host "DPMJobsQ $version"
     write-host "Usage:: DPMJobQ.Ps1 -filtername <filter name> [-dpmservername <servername>] [-outputpath=<any valid directory>] [-debug] [-append] [-reportfilters]" -foregroundcolor yellow
     Write-Host ""
     Write-Host "Use DPM UI console Monitoring-Jobs pane to create and save the filter to use" -ForegroundColor white
     Write-Host "The script assumes the same filtername exists when specifying a remote dpmserver" -ForegroundColor white
     Write-Host ""
     Write-Host `tThe output file will be named like DPMJobQ_<dpmservername>_<filtername>.CSV in current or specified path -ForegroundColor green
     Write-Host `tOnly Jobs with datasize greater than 0 are written to the output file -foregroundcolor green
     Write-Host `tUse "-append 1" to append to existing file`, by default existing files will be deleted -ForegroundColor magenta
     Write-Host ""
     Write-Host `tOutput format is string quoted TAB separated`:`n -ForegroundColor green
     Write-Host `t`"Protected server`" [T] `n`t`"Datasource name`" [T] `n`t`"Datasource Type`" [T]`n`tDatasize in MB [T] `n`tStarttime [T] `n`tEndtime [T] `n`tElapsed decimal minutes [T]  -ForegroundColor green
     Write-Host `t`"Tasktype [T]`" `n`t`"Library [T]`"`n`t`"Medialist`"  -ForegroundColor green
     Write-Host `n`tUse "-mailto name@mail.domain" to send output as attachment -foregroundcolor yellow
     Write-Host ""
     Write-Host `tUse "-debug 1" to get console output as well -ForegroundColor cyan
     Write-Host `tDebug output shows all jobs regardless of size -ForegroundColor cyan
     Write-Host `tDebug output shows number of processed jobs and number written to file -ForegroundColor cyan
     Write-Host ""
     Write-Host `tUse "-batch 1" to return the filtered job collection object and exit -ForegroundColor cyan
     Write-Host `tThis parameter is intended for use with other scripts -ForegroundColor cyan
     ShowFilters
     exit 0
    }
    function SendMail {
     param($mailto, $mailfile)
     if (!$mailto) {(writelog "No mail recipient"); return }
     if (!$mailfile) {(writelog "No attachement"); return }
     writelog "Sending mail..."
     [system.reflection.assembly]::loadwithpartialname("system.web") >> $logfile
     $msg = New-Object System.Web.Mail.MailMessage
     $att0 = new-object System.web.mail.MailAttachment ((resolve-path $mailfile),"UUENCODE")
     $att1 = new-object System.web.mail.MailAttachment ((resolve-path $logfile),"UUENCODE")
     $msg.From = $mailfrom
     $msg.to = $mailto
     $msg.Subject = "DPMJobsQ output by filter $filtername"
     $msg.Body = "Output of DPMJobsQ is attached"
     $msg.Attachments.Add($att0) > $null
     $msg.Attachments.Add($att1) > $null
     [System.Web.Mail.SmtpMail]::SmtpServer=$smtprelay
     [System.Web.Mail.SmtpMail]::Send($msg)
     writelog "Mail done"
    }
    #
    # START
    #
    $path = "$env:APPDATA\Microsoft\Microsoft System Center Data Protection Manager"
    $confirmpreference = "None"
    $version = "v3.1"
    $logfile = "DPMJobsQ.LOG"
    $statsMin = @{}
    $statsMax = @{}
    $statsSum = @{}
    $statsCount = @{}
    $statsSizeMin = @{}
    $statsSizeMax = @{}
    $statsSizeSum = @{}

    writelog ""
    Writelog " === START DPMJobsQ $version ==="
    writelog "Debug = $debug"
    writelog "Append = $append"
    writelog "Reportfilters = $reportfilters"

    trap [Exception] {
     writelog "<<< ERROR >>>"
     writelog $("TRAPPED: " + $_.Exception.GetType().FullName);
     #writelog $("TRAPPED: " + $_.Exception.Message);
     $Error
     writelog "<<< end >>>"
     $log = Get-EventLog -List | Where-Object { $_.Log -eq "Application" }
     if ($batch) {$log.Source = "DPMJobsQ_BATCHED"} else {$log.source ="DPMjobsQ"}
     $log.WriteEntry("TRAPPED: $error", [system.Diagnostics.EventLogEntryType]::Error,9911)
     $Error.Clear()
     continue;
    }

    #Default to run locally in DPM bin directory
    if (!$dpmservername) {$dpmservername = hostname}
    if ($mailto -ne "")
    {
     writelog "Obtaining mail settings..."
     $dpmobj = Connect-DPMServer $dpmservername
     $smtprelay = $dpmobj.GetGlobalPropertyValue("Smtpservername")
     $mailfrom = $dpmobj.GetGlobalPropertyValue("Smtpserversenderaddress")
     writelog "`tSMTP server = $smtprelay"
     writelog "`tMail from = $mailfrom"
     writelog "`tMail to = $mailto"
    }
    #if just looking for filters report and exit
    if ($reportfilters) {ShowFilters; exit 0}

    #validate required input
    if(("-?","-help") -contains $Args[0]) {show_help}
    if (!$filtername) {show_help}
    writelog "Used filtername = $filtername"

    #normalize and construct output spec
    if (!$outputpath.EndsWith("\")){$outputpath += "\"}
    $outputfile = $outputpath + "DPMJobsQ_" + $dpmservername + "_" + $filtername + ".CSV"
    writelog "Output file = $outputfile"

    #clean-up exting file unless append selected and go to work
    if (!$append) {if (test-path $outputfile) {Remove-Item $outputfile}}

    Dowork $dpmservername $filtername $outputfile

    Set-executionpolicy remotesigned
    $VerbosePreference = "Continue"
    $confirmpreference = "None"



    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.


    Tuesday, December 3, 2013 12:36 AM
    Moderator
  • im getting this error!!! plz help

    PS C:\Program Files\Microsoft System Center 2012\DPM\DPM\bin> .\DPMJobsQ.ps1 -fi
    ltername DailyBackup -debug

     === START DPMJobsQ v3.1 ===
    Debug = True
    Append = False
    Reportfilters = False
    Used filtername = DailyBackup
    Output file = .\DPMJobsQ_BACKUP_DailyBackup.CSV
    Getting filter DailyBackup
    <<< ERROR >>>
    TRAPPED: System.Management.Automation.ParameterBindingException
    Resolve-Path : A positional parameter cannot be found that accepts argument
    'System'.
    At C:\Program Files\Microsoft System Center 2012\DPM\DPM\bin\DPMJobsQ.ps1:214
    char:16
    +     $filterfile = Resolve-Path $path\APPDATA\Roaming\Microsoft\Microsoft
    System Cen ...
    +    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ~~~~~~
        + CategoryInfo          : InvalidArgument: (:) [Resolve-Path], ParameterBi
       ndingException
        + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell
       .Commands.ResolvePathCommand

    <<< end >>>
    PS C:\Program Files\Microsoft System Center 2012\DPM\DPM\bin>



    janakackv

    Tuesday, December 3, 2013 9:22 PM
  • sorry Change the $path and script is running now

    ==< DONE >==

    No mail recipient
    === FINISHED ===

    but hw can i configure email alert attached with output?

    already add email address but its not sending email.

    Thanks


    janakackv

    Tuesday, December 3, 2013 10:41 PM