Webelight Solutions Blog: Insights on IT, Innovation, and Digital Trends

Auto-track Work Logs with Jira, Slack, and Open-Source Tools

Aiyaj Khalani

NOV 08, 2024

Blog-hero

Automating Jira Worklog tracking

Introduction

In our fast-growing startup, we noticed an urgent need for agile processes for efficient project management projects and tracking costs. With a team of almost 100 employees, tracking hours became essential for project planning and cost management for any time-tracking software. The need for tracking time led us to implement Jira automation for project tracking due to its robust features. However, as the team grew, we faced a challenge: ensuring every team member logged their 8 hours daily in Jira and notifying managers if their team members were behind to boost employee productivity and keep all logs organized.

Despite Jira's extensive features, we needed help finding an existing, cost-effective project-tracking solution for automating work logs in Jira, especially with Slack, our organization's primary communication tool. Here's how we created our custom solution to automatically notify employees and managers of their daily logged hours in Jira.

The Problem

As part of our agile transformation, we rolled out a new policy where employees must log 8 hours daily in Jira. However, managers needed a way to track compliance to ensure their teams adhered to this policy. We needed an automated system to:

1) Remind employees to log their hours if they haven't done so at the end of the day.

2) Notify project managers about their team's daily logged hours.

3) Provide an overview to management regarding employees' compliance with logging hours.

We aimed to make this process seamless and cost-effective, leveraging open-source tools where possible.

Researching a Solution

To address the requirements, we broke down the solution into four main tasks:

1) Avoid Overloading Jira's API: Directly polling Jira's API for every user's work log across multiple projects was impractical.

2) Sync Data from Jira to a Database: We needed a way to sync Jira work log data into a local database for easy querying.

3) Expose Data via API: Create an API endpoint to fetch daily work log data for each user.

4) Automate Notifications: Use a workflow automation tool to send reminders and summaries to Slack.

After extensive research, we settled on three tools to create this automation:

1) Airbyte is used to sync data from Jira to PostgreSQL.

2) Cube.js to create APIs for fetching data.

3) n8n for workflow automation to notify users via Slack.

Step 1: Setting Up Data Sync with Airbyte

Our first step was configuring Airbyte to sync work log data from Jira to PostgreSQL. Airbyte allowed us to automate this process with minimal coding. We set up a connection where Jira was the source while PostgreSQL was the destination.

1) In Airbyte, we configured Jira as a source and PostgreSQL as a destination.

2) We scheduled the sync process every hour to update our database with the latest work log entries.

 

Configuration of Airbyte sync from Jira to PostgreSQL

             Configuration of Airbyte sync from Jira to PostgreSQL

 

Step 2: Exposing Data via API with Cube.js

With the data now in PostgreSQL, the next step was to expose it via an API that could handle custom date range queries. We used Cube.js, a powerful analytics API tool that allowed us to create metrics and dimensions based on the data in PostgreSQL.

1) We set up Cube.js to connect to our PostgreSQL instance.

2) Created a metric for (worklogSpentTimeMinutes) to aggregate daily work logs by the user.

3) Configured filters to fetch data based on employee name and date ranges.

This API made retrieving specific information for tracking each employee's work log easy, enabling us to build a customized notification system.

 

Cube js query configuration

        Cube.js query configuration

 

Step 3: Automating Notifications with n8n

The final piece was to conduct n8n workflow automation and automate notifications. n8n is a useful no-code automation tool because of its integration capabilities with both Slack and custom APIs. Since our organization uses the same email addresses in Slack and Jira, we could map users effectively.

1) In n8n, we created a daily workflow. We scheduled it to run at 11 pm.

2) The workflow queries the Cube.js API for each employee's logged daily hours.

3) It then sends notifications to:

  • Employees: Notifying them of their logged hours for the day.
  • Project Managers: A summary of their team's logged hours.
  • Management: A high-level overview showing total logged hours and employees who logged less than the required hours.

 

n8n Workflow

n8n Workflow

 

Sample Slack Notifications

Our custom solution automates work log tracking by syncing Jira data with PostgreSQL, querying it via Cube.js, and sending automated notifications via n8n and Slack.

1) Employee Notification: Each employee receives a message detailing the hours they logged that day, with a reminder to complete 8 hours if they haven't.

2) Manager Summary: Managers get a list of their team members with the hours logged, making it easy to follow up if any team member is behind.

3) Management Summary: Management receives an overview with a breakdown of the number of employees who logged eight or more hours, fewer than eight hours, and zero hours.

With the right tools and setup, organizations can create customized project-tracking solutions that integrate seamlessly with communication tools.

 

Daily Logged Hours Report Notification (Warning)

    Daily Logged Hours Report Notification (Warning)

 

Daily Logged Hours Report Notification (Positive)

 Daily Logged Hours Report Notification (Positive)

 

Summary Notification For Management                                                                    Summary Notification For Manager

 

Summary Notification For Management

Summary Notification For Management

 

Results and Benefits

Implementing Jira automation helped us streamline our time-tracking process and ensure consistent employee accountability. This automated solution has made a significant impact on our organization's workflow.

  • Increased Compliance: Employees are more consistent with logging hours, knowing they'll receive daily reminders.
  • Improved Accountability: Managers are now aware of their team's daily work log status without needing to chase down individual reports.
  • Efficient Project Tracking: Management can quickly assess logged hours and project costs, making financial tracking easier.

This solution has streamlined our operations, making our agile transformation smoother and more effective.

Conclusion

Through Airbyte, Cube.js, and n8n workflow automation, we created an end-to-end automated solution for work log tracking and notifications with minimal cost and effort. This approach demonstrates the power of open-source tools in addressing real-world business challenges, especially for startups like ours, where resource efficiency is critical.

If your team faces similar challenges in project management, consider exploring these tools to build time-tracking software and project-tracking solutions tailored to your needs and boost employee productivity. With a bit of setup, you can automate processes, increase employee productivity, and keep your team informed and accountable.

Aiyaj Khalani

Tech Lead & DevOps Enthusiast

Aiyaj is the kind of tech mind who thrives on building seamless systems and tackling challenges head-on. A natural problem-solver, he’s happiest when fine-tuning processes and diving into the intricacies of DevOps. While he’s all about efficiency, don’t be surprised to see him experiment with new tech or streamline a setup just for the joy of it.

FAQ's