SQL to check Bitbucket user permission

Posted on by 0 comment

Bitbucket permission can be assigned at project level or repository level for a group and user. The SQL (MSSQL Server) below can be used to check all projects & repositories permission for a given user, include the access assigned to an individual user or a group.

with repo_access as (
select distinct p.id                                       as "Project ID",
                p.name                                     as "Project Name",
				r.name									   as "Repo Name",
                cu.id                                      as "User ID (Individual)",
                cu.user_name                               as "Username (Individual)",
                Concat(cu.first_name, ' ', cu.last_name)   as "Fullname (Individual)",
                srp.group_name                              as "Group Access",
                cu1.id                                     as "User ID (Group)",
                cu1.user_name                              as "Username (Group)",
                Concat(cu1.first_name, ' ', cu1.last_name) as "Fullname (Group)"
from   dbo.project p
       join dbo.sta_normal_project np
         on np.project_id = p.id
       left join dbo.repository r on r.project_id = p.id
	   left join dbo.sta_repo_permission srp on srp.repo_id = r.id
       left join dbo.sta_normal_user u
              on srp.user_id = u.user_id
       left join dbo.cwd_user cu
              on cu.lower_user_name = u.name
       left join dbo.cwd_membership cm
              on cm.lower_parent_name = srp.group_name
       left join dbo.cwd_user cu1
              on cu1.lower_user_name = cm.lower_child_name

where  srp.group_name is not null  or srp.user_id is not null

union
select distinct p.id                                       as "Project ID",
                p.name                                     as "Project Name",
				''										   as "Repo Name",
                cu.id                                      as "User ID (Individual)",
                cu.user_name                               as "Username (Individual)",
                Concat(cu.first_name, ' ', cu.last_name)   as "Fullname (Individual)",
                pp.group_name                              as "Group Access",
                cu1.id                                     as "User ID (Group)",
                cu1.user_name                              as "Username (Group)",
                Concat(cu1.first_name, ' ', cu1.last_name) as "Fullname (Group)"
from   dbo.project p
       join dbo.sta_normal_project np
         on np.project_id = p.id
       left join dbo.sta_project_permission pp
              on pp.project_id = p.id
       left join dbo.sta_normal_user u
              on pp.user_id = u.user_id
       left join dbo.cwd_user cu
              on cu.lower_user_name = u.name
       left join dbo.cwd_membership cm
              on cm.lower_parent_name = pp.group_name
       left join dbo.cwd_user cu1
              on cu1.lower_user_name = cm.lower_child_name
where  pp.group_name is not null  or pp.user_id is not null
)

select * from repo_access where [Username (Individual)] = 'Replace with UserId' or [Username (Group)] = 'Replace with UserID'
order  by [Project ID] ;

Category: BitBucket, MSSQL

Retieve and Export BitBucket server users

Posted on by 0 comment

Sometimes, we wish to check if a BitBucket user has actually used his account or not. There is a post Query for inactive or idle users which has explained few different ways. I will try the API method.

In this exercise, I try to achieve few things below with Python

  • Send request to BitBucket server and get the list of users
  • Convert the Unix timestamp to readable date time
  • Export the user data to a csv file

This is the sample code to get the information and export to csv file.

import requests
from datetime import datetime
from csv import writer
from base64 import b64encode

url = "https://BitbucketServerUrl/rest/api/1.0/admin/users?limit=150"
out = writer(open("bitbucket_users.csv","w",newline=''), delimiter=",")

out.writerow(["name","email","displayName","active","lastAuthenticationTimestamp"])

userAndPass = b64encode(b"username:password").decode("ascii")

payload = {}
headers = {
  'Authorization': 'Basic %s' % userAndPass 
}

response = requests.get(url, headers=headers, data = payload)

data = response.json()

for row in data['values']:
    if "lastAuthenticationTimestamp" in row:
        dt_lastAuthentication = datetime.fromtimestamp(int(row['lastAuthenticationTimestamp'])/1000).strftime('%Y-%m-%d %H:%M:%S')
    else:
        dt_lastAuthentication = "unknown"

    out.writerow([row["name"],row["emailAddress"],row["displayName"],row["active"],dt_lastAuthentication])


Category: BitBucket, Python

Install Docker on Ubuntu 18.04

Posted on by 0 comment

Docker is very popular tool which creates container in the application development, same software, configuration will be packaged and used for testing and production.

In this exercise, we will install docker in a Ubuntu server

Check Ubuntu version

lsb_release -a

No LSB modules are available.
 Distributor ID: Ubuntu
 Description:    Ubuntu 18.04.3 LTS
 Release:        18.04
 Codename:       bionic

Update package

sudo apt-get update 

Remove old version if exists

sudo apt-get remove docker docker-engine docker.io

Install Docker

sudo apt install docker.io 

Auto start Docker service

sudo systemctl start docker
sudo systemctl enable docker

Synchronizing state of docker.service with SysV service script with /lib/systemd/systemd-sysv-install.
 Executing: /lib/systemd/systemd-sysv-install enable docker

Check Docker version

docker -v
Docker version 18.09.7, build 2d0083d

Hello world

docker run hello-world

Hello from Docker!
This message shows that your installation appears to be working correctly.
Category: Docker

Api test automation with mocha and chai

Posted on by 0 comment

Mocha is a JavaScript test framework running on Node.js and in the browser, which can be used for unit testing, and api and ui automated testing, here we will use it along with chai to do some api automated testing in nodejs with typescript.

Packages

We will install packages below with yarn add or npm install

  • @types/chai-http
  • @types/mocha
  • @types/node
  • chai
  • chai-http
  • mocha
  • mochawesome
  • ts-mocha
  • ts-node
  • typescript
  • typings

Writing tests

Mocha BDD interface will be used to create the test case. chai-http

describe('test get findByStatus', () => {

    it(`should succeed when findByStatus`, (done) => {
        chai.request(baseUrl)
        .get('/v2/pet/findByStatus')
        .query({status:'available'})
        .end(function(err,res){
            expect(res).to.have.status(200);
            expect(res.body).to.to.have.length(432);
            done();
           });

    }).timeout(30000);

});
describe('test get findByStatus', () => {

    it(`should succeed when findByStatus-async`, async () => {
        const res = await chai.request(baseUrl)
        .get('/v2/pet/findByStatus')
        .query({status:'available'});
        expect(res).to.have.status(200);
        expect(res.body).to.to.have.length(4320);

    }).timeout(30000);

});

Data driven

We have created one test case, but we have some more status to check, so let’s make it data driven.

describe('test get findByStatus', () => {

    var tests : {status:string, expectedCount: number } [] = [
        {status:"available", expectedCount:1000},
        {status:"pending", expectedCount:300},
        {status:"sold", expectedCount:500}
    ];

    tests.forEach(function(test) 
    {
        it(`should succeed when findByStatus - ${test.status}`, async () => {
            const res = await chai.request(baseUrl)
            .get('/v2/pet/findByStatus')
            .query({status:'available'});
            expect(res).to.have.status(200);
            expect(res.body).to.to.have.length(test.expectedCount);
    
        }).timeout(30000);

    });
});

Execution and reporting

//Add content below in the scripts section of package.json
"test": "ts-mocha --file src/test/tw.test.ts",
"test:report": "ts-mocha  --reporter mochawesome --file src/test/tw.test.ts"
Category: javascript

Reading and writing serial port data with PowerShell

Posted on by 0 comment

List com ports

To see a list of available com ports,

Read or write data

To read or write data with given details, such as port, Baudrate etc.

Write unix time

I have a clock which accepts unix time for syncing the time, so I use script below to sync the time if the clock being off for few minutes after some time.

Category: PowerShell, uart