question

EvertonOliveira avatar image
0 Votes"
EvertonOliveira asked JakubSkibicki-8583 answered

Programatically Get ADF pipeline consumption

Hi,

I'm interested in querying the pipeline consumption report that is available from the Data Factory monitor. Is there a table on Log Analytics or PowerShell cmdlet that would return this information? My goal is to aggregate the information available in this report to identify what are the most costly pipelines.

106545-2021-06-17-10-22-20.png




ref: https://techcommunity.microsoft.com/t5/azure-data-factory/new-adf-pipeline-consumption-report/ba-p/1394671


Thank you

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EvertonOliveira avatar image
0 Votes"
EvertonOliveira answered KranthiPakala-MSFT commented

Thanks @KranthiPakala-MSFT , below is my version of the script which is more aligned with what I was looking for.


 $startTime = "21/6/2021 7:00:00"
 $endTime = "21/6/2021 10:00:00"
 $adf = '<data factory name>'
 $rg = '<resrouce group name>'
        
    
 $outputObj = @()
 $pipelineRuns = Get-AzDataFactoryV2PipelineRun -ResourceGroupName $rg -DataFactoryName $adf -LastUpdatedAfter $startTime -LastUpdatedBefore $endTime
    
 # loop through all pipelines and child activities to return billable information
 foreach ($pipelineRun in $pipelineRuns) {
     $activtiyRuns = Get-AzDataFactoryV2ActivityRun -ResourceGroupName $rg -DataFactoryName $adf -pipelineRunId $pipelineRun.RunId -RunStartedAfter $startTime -RunStartedBefore $endTime
    
     foreach ($activtiyRun in $activtiyRuns) {
         if ($null -ne $activtiyRun.Output -and $null -ne $activtiyRun.Output.SelectToken("billingReference.billableDuration")) {            
                
             $obj = @()
             $obj = $activtiyRun.Output.SelectToken("billingReference.billableDuration").ToString() | ConvertFrom-Json
             $obj | Add-Member -MemberType NoteProperty -Name activityType -value $activtiyRun.Output.SelectToken("billingReference.activityType").ToString()
             $obj | Add-Member -MemberType NoteProperty -Name pipelineName -value $pipelineRun.PipelineName
             $obj | Add-Member -MemberType NoteProperty -Name activtiyRuns -value $activtiyRuns.Count             
    
             $outputObj += $obj
         }
         else {}
     }
 }
    
 # output aggregated result set as table
 $groupedObj = $outputObj | Group-Object -Property pipelineName, activityType, meterType
 $groupedObj | ForEach-Object {
     $value = $_.name -split ', '
     New-Object psobject -Property @{ 
                                   
         activityType              = $value[1];
         meterType                 = $value[2];
         pipelineName              = $value[0];
         executionHours            = [math]::Round(($_.Group | Measure-object -Property duration -sum).Sum, 4)
         orchestrationActivityRuns = $groupedObj.group.activtiyRuns[0]
     } 
 } | Sort-Object -Property meterType | Format-Table

Output sample

107612-image.png


Consumption report from the Data Factory monitor


107510-image.png





image.png (87.5 KiB)
image.png (27.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EvertonOliveira,

Glad that sample was helpful. Thanks for sharing your solution with community as it could be beneficial for others. :)

0 Votes 0 ·
JakubSkibicki-8583 avatar image
0 Votes"
JakubSkibicki-8583 answered

@EvertonOliveira

I took you script but did not like two items:
1. Due to if condition all activities that to not have an output or MeterType are excluded. Which is wrong because all such activities, like loops, waits, actually incur regular cost for activity orchestration with Azure Integration Runtime.

 if ($null -ne $activtiyRun.Output -and $null -ne $activtiyRun.Output.SelectToken("billingReference.billableDuration")) {    

  1. Your calculation of activity runs is also incorrect. For each activity run (single item) it appends a total of activites in pipeline run ($activtiyRun*s*.Count).


Hence I propose such version

 Connect-AzAccount
    
 $startTime = "2022-01-19 00:00:01"
 $endTime = "2022-01-19 23:59:59"
 $adf = 'yourADF'
 $rg = 'ADFsResourceGroup'
            
    
 #Get-AzDataFactoryV2PipelineRun -ResourceGroupName $rg -DataFactoryName $adf -LastUpdatedAfter $startTime -LastUpdatedBefore $endTime
        
  $outputObj = @()
  $pipelineRuns = Get-AzDataFactoryV2PipelineRun -ResourceGroupName $rg -DataFactoryName $adf -LastUpdatedAfter $startTime -LastUpdatedBefore $endTime
     
  $one = 1
  $otherActivity = "OtherActivity"
  $zero = 0
     
  $toAdd =@"
 {
   "meterType": "AzureIR"
   ,"duration": 0
   ,"Unit":"Hours"
 }
 "@
        
  # loop through all pipelines and child activities to return billable information
  foreach ($pipelineRun in $pipelineRuns) {
      $activtiyRuns = Get-AzDataFactoryV2ActivityRun -ResourceGroupName $rg -DataFactoryName $adf -pipelineRunId $pipelineRun.RunId -RunStartedAfter $startTime -RunStartedBefore $endTime
        
      foreach ($activtiyRun in $activtiyRuns) {
          if ($null -ne $activtiyRun.Output -and $null -ne $activtiyRun.Output.SelectToken("billingReference.billableDuration")) {
              $obj1 = @()
              $obj1 = $activtiyRun.Output.SelectToken("billingReference.billableDuration").ToString() | ConvertFrom-Json     
              $obj1 | Add-Member -MemberType NoteProperty -Name activityType -value $activtiyRun.Output.SelectToken("billingReference.activityType").ToString()
              $obj1 | Add-Member -MemberType NoteProperty -Name pipelineName -value $pipelineRun.PipelineName
              $obj1 | Add-Member -MemberType NoteProperty -Name activtiyRuns -value $one
              $obj1 | Add-Member -MemberType NoteProperty -Name PipelineRunId -value $activtiyRun.PipelineRunId 
              $obj1 | Add-Member -MemberType NoteProperty -Name ActivityRunId -value $activtiyRun.ActivityRunId
              $obj1 | Add-Member -MemberType NoteProperty -Name ActivityRunName -value $activtiyRun.ActivityName        
                 
              $outputObj += $obj1   
        
          }
          else {
             $obj2 = @()
             $obj2 = (ConvertFrom-Json -InputObject $toAdd)
             $obj2 | Add-Member -MemberType NoteProperty -Name activityType -value $otherActivity
             $obj2 | Add-Member -MemberType NoteProperty -Name pipelineName -value $pipelineRun.PipelineName
             $obj2 | Add-Member -MemberType NoteProperty -Name activtiyRuns -value $one
             $obj2 | Add-Member -MemberType NoteProperty -Name PipelineRunId -value $activtiyRun.PipelineRunId 
             $obj2 | Add-Member -MemberType NoteProperty -Name ActivityRunId -value $activtiyRun.ActivityRunId
             $obj2 | Add-Member -MemberType NoteProperty -Name ActivityRunName -value $activtiyRun.ActivityName    
                
             $outputObj += $obj2     
          }
    
    
    
      }
  }  
    
  # output aggregated result set as table
  $groupedObj = $outputObj | Group-Object -Property pipelineName, activityType, meterType
  $groupedObj | ForEach-Object {
      $value = $_.name -split ', '
      New-Object psobject -Property @{ 
                                       
          activityType              = $value[1];
          meterType                 = $value[2];
          pipelineName              = $value[0];
          executionHours            = [math]::Round(($_.Group | Measure-object -Property duration -sum).Sum, 4)
          orchestrationActivityRuns = ($_.Group | Measure-object -Property activtiyRuns -Sum).Sum 
      } 
  } | Sort-Object -Property meterType | Format-Table




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KranthiPakala-MSFT avatar image
1 Vote"
KranthiPakala-MSFT answered EvertonOliveira commented

Hi @EvertonOliveira ,

Welcome to Microsoft Q&A forum and thanks for your query.

ADF product team has recently published a sample PowerShell script in GitHub samples which can be used to print activity level run details in 45 day range.

This information can be useful for users who want to know which activity ran when and for how many hours and corehours. This data can be used:

  • To understand which activities ran for how long and corehours behind it.

  • To build future consumption models from past runs.

  • To budget future spend because corehours can be priced from data in pricing calculator.

Here is the link for the GitHub sample: Simple script that prints activity level run details in 45 day range

Sample script (please update with variable details accordingly):

 $startTime = "5/5/2021 4:00:00"
 $endTime = "5/5/2021 7:00:00"
     
 $pipelineRuns = Get-AzDataFactoryV2PipelineRun -ResourceGroupName ADFRG -DataFactoryName adfcontoso -LastUpdatedAfter $startTime -LastUpdatedBefore $endTime
     
 foreach($pipelineRun in $pipelineRuns) {
     $activtiyRuns = Get-AzDataFactoryV2ActivityRun -ResourceGroupName ADFRG -DataFactoryName adfcontoso -pipelineRunId $pipelineRun.RunId -RunStartedAfter $startTime -RunStartedBefore $endTime
     foreach($activtiyRun in $activtiyRuns) {
         if ($activtiyRun.Output -ne $null -and
                 $activtiyRun.Output.SelectToken("billingReference.billableDuration") -ne $null) {
             Write-Output $activtiyRun.Output.SelectToken("billingReference.billableDuration").ToString() for $activtiyRun.Output.SelectToken("billingReference.activityType").ToString()
         }
         else {
             Write-Output "Not Availble" for $activtiyRun.ActivityType
         }
     }
 }


Sample output:

 # Here is sample output. Azure IR is used with General Compute meter. It shows corehours and hours spent. 
 <# ExternalActivity
 [
   {
     "meterType": "General",
     "duration": 0.28871840533333332,
     "unit": "coreHour",
     "sessionType": "WarmCluster"
   }
 ]
 for
 executedataflow
 [
   {
     "meterType": "AzureIR",
     "duration": 0.016666666666666666,
     "unit": "Hours"
   }
 ]
 for
 ExternalActivity 
 #>


Hope this information helps to give you some pointers. Do let us know how it goes.



Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.










· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Hi @EvertonOliveira ,

Just checking in to see if the above suggestion was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·

Hi KranthiPakala,

Thank you for providing this answer. I believe the script can lead me to the output I'm looking for. I'm doing some changes in the code and I'll post a full answer here when I have it ready, in the meantime I up-voted your answer.

0 Votes 0 ·