How to Find Data in Bitbucket to Monitor IT Compliance Standards

January 10, 2023
#Reporting#How To#Bitbucket
20 min

Compliance management is essential for businesses that provide financial services and deal with personal information. Banks, insurance companies, and other financial institutions must adhere to a range of external as well as internal IT compliance standards and policies to ensure data integrity, mitigate security risks in product development and avoid reputational damage caused by cyberattacks. Moreover, these regulatory requirements are constantly updating and changing, making it challenging to stay on top of compliance.

That’s why almost every financial organization has a dedicated compliance person or team who deals with IT controls, supports audits, and manages regulatory risks. However, as engineering data is scattered across different tools and some don’t provide aggregated information, compliance officers face difficulties finding, collecting, and reporting on the data needed to pass audits. Sometimes they even have to build custom tooling to ensure the organization’s security and compliance posture.

With all that in mind, we decided to show how to quickly find data in Bitbucket to track several most common violations of secure code development using Awesome Graphs for Bitbucket and comply with IT standards. Here they are:

  1. The author of the pull request approved their own pull request
  2. The committers of the pull request approved the pull request they contributed to
  3. The pull request was merged with fewer than two approvals
  4. The pull request was merged without the reviewer
  5. Unauthorized people merged or approved a pull request

How to get data with Awesome Graphs for Bitbucket

First, let’s explore features of Awesome Graphs that help find information needed to comply with IT controls and meet regulatory requirements.

Graphs & Reports

Awesome Graphs provides a set of commit and pull request graphs and reports to show what is happening inside the project development. They help analyze activity in repositories and projects as well as track all changes.

To view graphs and reports in Bitbucket, you need to go to the project or repository you’d like to review and click on the Reports or Graphs icon in the navigation bar.

Export to CSV

Awesome Graphs for Bitbucket also enables users to export data to CSV directly from Bitbucket. It can be done in several ways. In the first option, you need to go to the People page and choose CSV in the Export menu at the top-right corner.

The outcome will be a list of pull requests or commits with detailed information helpful for audit or compliance teams.

Export data to CSV to find necessary data and meet IT compliance standards

Alternatively, the Awesome Graphs REST API can be used to export data to a CSV file. To find the documentation, you need to go to the People page, choose REST API in the Export menu, or visit our documentation website.

Export via REST API

As mentioned above, Awesome Graphs provides an opportunity to export raw data about commits, lines of code, and pull requests via REST API. You can retrieve data on the global, project, or repository level and get three types of outputs:

  • List of PRs or commits with detailed information
  • Export of data to a CSV file
  • Raw statistics with the number of PRs or commits

It gives access to resources via URI paths, uses JSON as its communication format, and the standard HTTP methods like GET, PUT, POST, and DELETE.

For more detailed information, you may refer to our article How to Search for Commits in Bitbucket Server and Data Center or our documentation.

Below, we’ll show how to use these features of Awesome Graphs to detect the most common violations of secure code development and meet compliance requirements.

How to check the most common violations with Awesome Graphs for Bitbucket

The author of the pull request approved their own pull request

1st option – Quick check using Reports

To find authors and reviewers of pull requests, you can navigate to the Pie Chart Report, group data by Author in the Statistic type field, and choose a time period or a team if needed.

As a result, it displays the list of all pull requests with their authors for the selected time span. Click on a particular author and check if they approved (green checkmark) their own pull request.

2nd option – Export pull request data to CSV

To check pull requests in bulk or prepare custom reports for audits, you can export them to a CSV file. Here is an example of working with it in Google Sheets, but the same can be done in Excel as well.

Then use Conditional formatting for the Reviewers’ Names column: Format → Conditional formatting → Format cells if Text contains =C1 (Author Name). Authors who have reviewed their own pull requests will be highlighted, making it easy to find them.

The committers of the pull request approved the pull request they contributed to

This information can be obtained using the Awesome Graphs REST API and the standard REST API of Bitbucket. Here is a Python script you can use. It retrieves data on the project level and identifies pull requests where committers approved their contributions.

import sys
import requests
import csv

bitbucket_url = sys.argv[1]  # https://bitbucket.your-company-name.com
login = sys.argv[2]
password = sys.argv[3]
project = sys.argv[4]
since = sys.argv[5]  # YYYY-MM-DD
until = sys.argv[6]  # YYYY-MM-DD

s = requests.Session()
s.auth = (login, password)


class PullRequest:

    def __init__(self, created, closed, repo_slug, pr_id, title, state, author, reviewers):
        
        self.created = created
        self.closed = closed
        self.repo_slug = repo_slug
        self.pr_id = pr_id
        self.title = title
        self.state = state
        self.author = author
        self.reviewers = reviewers


def get_pull_requests():

    pull_request_list = []

    get_prs_url = bitbucket_url + '/rest/awesome-graphs-api/latest/projects/' + project + '/pull-requests'

    is_last_page = False

    while not is_last_page:

        response = s.get(get_prs_url, params={'start': len(pull_request_list), 
                                              'limit': 1000,
                                              'sinceDate': since, 
                                              'untilDate': until, 
                                              'state': 'merged'}).json()

        for pr_details in response['values']:
            
            reviewers = []
            
            for reviewer in pr_details['reviewers']:
                reviewers.append(reviewer['user']['name'])
            
            created = pr_details['createdDate']
            closed = pr_details['closedDate']
            repo_slug = pr_details['toRef']['repository']['slug']
            pd_id = pr_details['id']
            title = pr_details['title']
            state = pr_details['state']
            author = pr_details['author']['user']['name']
            
            pull_request_list.append(PullRequest(created, closed, repo_slug, pd_id, title, state, author, reviewers))

        is_last_page = response['isLastPage']

    return pull_request_list


def get_pr_committers(pull_request):
    
    committers = []
    
    commits_url = bitbucket_url + '/rest/api/latest/projects/' + project + '/repos/' + pull_request.repo_slug + '/pull-requests/' + str(pull_request.pr_id) + '/commits'
    
    is_last_page = False

    while not is_last_page:

        commits_response = s.get(commits_url, params={'start': len(committers), 'limit': 500}).json()

        for commit in commits_response['values']:
            
            committer = commit['author']['name']
            committers.append(committer)

        is_last_page = commits_response['isLastPage']
        
    committers_wo_duplicates = list(dict.fromkeys(committers))

    return committers_wo_duplicates


print(f'Collecting a list of pull requests from project {project}')

pr_list = get_pull_requests()

committers_are_reviewers_prs = []

print('Processing the list of pull requests')

for pull_request in pr_list:

    committers = get_pr_committers(pull_request)

    for committer in committers:

        if committer in pull_request.reviewers:
            
            committers_are_reviewers_prs.append(pull_request)


with open(f'{project}_pull_requests_{since}_{until}.csv', mode='a', newline='') as report_file:
    
    report_writer = csv.writer(report_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    report_writer.writerow(['created',
                            'closed',
                            'project',
                            'repo_slug',
                            'pr_id',
                            'title',
                            'state',
                            'author',
                            'reviewers'])    

    for pr in committers_are_reviewers_prs:

        report_writer.writerow([pr.created, 
                                pr.closed, 
                                project, 
                                pr.repo_slug, 
                                pr.pr_id, 
                                pr.title, 
                                pr.state, 
                                pr.author,
                                pr.reviewers])

print('The resulting CSV file is saved to the current folder')

To make this script work, you’ll need to pre-install the requests module. The csv and sys are available in Python out of the box. You need to pass the following arguments to the script when executed:

  • the URL of your Bitbucket,
  • login,
  • password,
  • project key,
  • since date (to include PRs created after),
  • until date (to include PRs created before).

Here’s an example:

py script.py https://bitbucket.your-company-name.com login password PRKEY 2020-11-30 2021-02-01

Once the script’s executed, the resulting file will be saved to the same folder as the script. Following is an example of the response:

The pull request was merged with fewer than two approvals 

1st option – Quick check using Reports

To find the list of pull requests with the reviewers who approved them, use the Pie Chart Report. In the configuration tab in the Statistic type field, select grouping by State, and choose a time period or a team if necessary.

You can view all merged pull requests with their reviewers below the report by clicking the Merged sector. The reviewers with the green checkmark are those who approved pull requests. You can scroll the list and find merged PRs with fewer than two approvals.

2nd option – Export pull request data via REST API

You can get the status of each reviewer using Awesome Graphs REST API. Here is an example to retrieve data on the project level:

https://bitbucket.your-company-name.com/rest/awesome-graphs-api/latest/projects/{projectKey}/pull-requests?state=merged

Then you can display this information in Confluence using, for example, Table from JSON macro (a feature of Table Filter and Charts for Confluence) and add a pivot table or export to CSV and apply Conditional formatting in Google Sheets or Excel. Thus, you’ll get a list of pull requests merged with fewer than two approvals.

Additionally, Bitbucket lets you set up checks for merging pull requests. It’s a list of restrictions that can be imposed to maintain high code quality standards and comply with IT regulatory requirements. You can specify a minimum number of approvals needed to merge pull requests.

The pull request was merged without the reviewer

1st option – Quick check using Reports

To find pull requests merged without reviewers, use the Pie Chart Report. Group data by Reviewer in the Statistic type field and click on the No reviewer slice in the chart. Below the report, you’ll see all pull requests merged without a reviewer.

2nd option – Export pull request data to CSV

To check pull requests in bulk or prepare reports for audit or compliance teams, you can export them to a CSV file and apply filters to the State column (choose Merged) and the Reviewers’ Names column (choose Blanks). Thus, you’ll get the list of pull requests that were merged without reviewers.

Apart from this, there is also an option to retrieve data using REST API with a filter by pull request state. Here is a request example on the project level:

https://bitbucket.your-company-name.com/rest/awesome-graphs-api/latest/projects/{projectKey}/pull-requests?state=merged

Unauthorized people merged or approved a pull request

Logging all changes and restricting access to unauthorized people is one more principle of protecting production code. However, the concept of authorized people may vary depending on the organization, project, or industry requirements. But as was shown above, with Awesome Graphs, you can quickly get a list of individuals who made changes and compare it with those who had the rights.

It’s also worth noting that Bitbucket has built-in features to control access to code and adhere to regulatory requirements:

As can be seen, all of the most common violations can be easily identified using Awesome Graphs for Bitbucket. With it, compliance officers can quickly check if all processes comply with IT controls and standards, as well as collect engineering data to ensure the organization’s security posture and facilitate compliance management.

If you need to meet other IT controls, you can contact our support team, and we’ll share our ideas on how our app may help.