How to Manage Security Vulnerabilities Using Budibase—No Code Required

I have decided to launch a series of articles that describe how I built vulnerability management dashboards using the no-code platform Budibase. In this series, I will break down my custom prioritization system, demonstrate how the Exploit Prediction Scoring System (EPSS) can enhance remediation efficiency, and explore how to consolidate data from various cybersecurity tools into a unified view.

Vulnerability management presents various challenges that cannot be addressed with a one-size-fits-all solution. Each organization has its own structure, budget, and technical stack. Therefore, rather than providing a step-by-step tutorial, I have chosen to share my practical experiences and insights.

• The primary objective is to develop an internal application that provides all stakeholders — including the SOC team, system administrators, and management of relevant departments, with access to up-to-date statistics and a clear understanding of the current vulnerability landscape.
• The second objective is to develop a more effective method for prioritizing vulnerabilities.

Why Dashboards and a Unified Approach Are Essential?

One issue I've noticed is the absence of a unified solution that can aggregate data from various sources and involve all stakeholders in the vulnerability management process.

Also the IT department needs to clearly understand the significance of vulnerability remediation, as it is not only affects security but also the overall resilience of the infrastructure.

Why Budibase?

Simply scanning for vulnerabilities and sending out a report is no longer sufficient. It's essential for teams to hold meetings to discuss findings, review reports and charts, explore alternatives, and make decisions—especially when remediation is not possible.

Visualizing the results helps identify trends, creates a more dynamic pace for the team, and clearly illustrates the impact of the actions taken.

Budibase is an open-source solution that supports various database management systems(DBMS) and REST APIs. It deploys quickly and allows for user interface customization, making it an excellent choice for building lightweight internal applications.

Initial Setup of Budibase

To simplify the setup, I used Docker containers, which is the most effective deployment method.

1. Installing the PostgreSQL Database

To start, pull the PostgreSQL image:

docker pull postgres

Create a file to store the password, for example postgres-pwd:

echo -n "secretpassword" > postgres-pwd

Set appropriate permissions for the file:

chmod 600 ./postgres-pwd

Next, try running the container:

sudo docker run -p 5432:5432 \
--name postgres_budibase \
-e POSTGRES_USER=budiuser \
-e POSTGRES_PASSWORD_FILE=/run/secrets/pg_pass \
-v /home/user/postgres_budibase/postgres-pwd:/run/secrets/pg_pass:ro \
-v /var/budibase_test_postgre/pgdata:/var/lib/postgresql/data/ \
-v /home/user/postgres_budibase/csv/:/tmp/postgres/ \
-d postgres

You will need to modify this command to match your environment:

• Replace /home/user/postgres_budibase/postgres-pwd with the path to your password file.
• Replace /var/budibase_test_postgre/pgdata with the directory on your host where PostgreSQL data will be stored.
• The directory /home/user/postgres_budibase/csv/ is optional — I used it to import data from CSV files.

2. Installing Budibase

First, pull the Budibase image:

docker pull budibase/budibase:latest

Navigate to the directory where you will place the docker-compose.yml file, and don’t forget to replace the password values:

version: "3"

services:
  budibase:
    restart: unless-stopped
    image: budibase/budibase
    ports:
      - "8080:80"
    environment:
      JWT_SECRET: <secret>
      MINIO_ACCESS_KEY: <secret>
      MINIO_SECRET_KEY: <secret>
      REDIS_PASSWORD: <secret>
      COUCHDB_USER: <secret>
      COUCHDB_PASSWORD: <secret>
      INTERNAL_API_KEY: <secret>
    volumes:
      - ./budibase_store:/data

Create the local directory for persistent storage:

mkdir ./budibase_store

Then launch the container:

docker-compose up -d

After this, all data will be saved in the local ./budibase_store directory on the host. You can now open your browser and navigate to port 8080 to access the Budibase interface.

3. Importing Data from a Scanner

Regardless of the solution you use for scanning and asset inventory, the structure of the output tables will vary based on the tool's capabilities and your specific requirements.

In my case, I utilized data from a vulnerability scanner. I made slight modifications to the official Python API library and created a script to query the API endpoint, retrieving the results in CSV format.

Afterward, I parsed and merged files from multiple sources, enriching the data with additional context, such as adding the system name associated with each particular asset.

Although Budibase allows you to run SQL queries directly through its web interface, I also occasionally use the command line. To connect to the database from the host where it is installed, use the following command:

sudo docker exec -it postgres_budibase sh -c "psql -U budibase"

SQL query to create a table for data

CREATE TABLE allassets_tbl (
 record_id SERIAL PRIMARY KEY,
 VulnDiscoveryTime TIMESTAMP,
 AuditTime TIMESTAMP,
 IpAddress VARCHAR(255),
 Hostname VARCHAR(255),
 OsName VARCHAR(255),
 OsVersion VARCHAR(255),
 Release VARCHAR(255),
 VulnerableEntity VARCHAR(255),
 VulnerableEntityVersion VARCHAR(255),
 EntityPath VARCHAR(255),
 Vulnerability VARCHAR(255),
 VulnerabilityIssueTime TIMESTAMP,
 CVE VARCHAR(255),
 VulnDescription TEXT,
 HowToFix TEXT,
 Patch VARCHAR(255),
 PatchPublishDate TIMESTAMP,
 Severity VARCHAR(255),
 CVSS3 VARCHAR(255),
 Metrics VARCHAR(255),
 VulnerIsTrend VARCHAR(255),
 HostImportance VARCHAR(255),
 Status VARCHAR(255),
 System TEXT,
 epssScore smallint,
 epssPercentile smallint
);

Next, I moved the .csv file containing vulnerability data into a directory accessible to the Docker container running the database. Note: In my case, the file is over 5 GB in size, so importing it through Budibase is not possible.

Below is the SQL query used to import the .csv file into the table:

COPY allassets_tbl(VulnDiscoveryTime, AuditTime, IpAddress, Hostname, OsName, OsVersion, Release, VulnerableEntity, VulnerableEntityVersion, EntityPath, Vulnerability, VulnerabilityIssueTime, CVE, VulnDescription, HowToFix, Patch, PatchPublishDate, Severity, CVSS3, Metrics, VulnerIsTrend, HostImportance, Status, System, epssScore, epssPercentile)
FROM '/tmp/postgres/vulns.csv'
DELIMITER ';'
CSV HEADER;

I primarily utilized a single main table to store the data. I decided against creating a complex relational database structure because it would involve managing asset lifecycle tasks, such as removing old assets and keeping the database updated with new ones.

This approach would considerably increase the workload in the long term, complicate SQL queries, and place additional strain on the server's CPU. However, a relational structure can always be implemented later if the system needs to evolve. The key is to start with a simple solution and assess whether it meets the requirements.

As a result, I currently have a flat table in the database, with each row representing a vulnerability. This setup allows me to run SQL queries and build custom dashboards effectively.

Initial Interface:

When you log in to Budibase for the first time, you will need to create an application. Budibase allows you to create multiple applications, and within each application, multiple pages can be created.

After creating your application, connect it to the PostgreSQL database from the application editor under the Data tab.

Access Control

Budibase enables access control for pages and objects, allowing users to manage permissions through role-based access control (RBAC).

I adjusted the settings so that administrators of specific servers can only access data relevant to their assets. This also applies to each group responsible for updating a particular category of assets.

Optimizing SQL Queries with Materialized Views

Every dashboard is created by executing an SQL query on the database. Every query adds load to the database and takes time to compute statistics. When working with large datasets—such as millions of rows—this can quickly become a performance bottleneck.

A good way to optimize dashboards in PostgreSQL is by using materialized views. A materialized view is a physical copy of the result of a query, stored as a table. When created, the query is executed once, and the result is saved.

CREATE MATERIALIZED VIEW mat_assets AS
SELECT * FROM assets_tbl;

Once created, the data remains static until it is refreshed. This means that changes in the underlying database tables are not automatically reflected and you will need to manually update the view using the following query:

REFRESH MATERIALIZED VIEW mat_assets;

The queries are stored in Budibase under the Data tab of the specific application.

Setting Up Automation

Setting up automation is a useful feature of Budibase, allowing users to create automated tasks.

For instance, materialized views need to be recalculated periodically in order to update their tables with new data. I have created a task that runs automatically after data import. Within this task, I specified an SQL query that refreshes the view:

DO $$
BEGIN
REFRESH MATERIALIZED VIEW allassets;
REFRESH MATERIALIZED VIEW mat_Count_vuln_active_by_found_fixed;
REFRESH MATERIALIZED VIEW mat_Chart_area_count_month_severity_active;
END $$;

Examples of Dashboards for Asset Counts and Types.

This is an example of how the first page with asset counts appears.

Dashboards are created using SQL queries from materialized views, which I mentioned earlier. This approach significantly reduces query time and saves computing resources.

Dashboard 1 – displays the number of Windows servers among all assets.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_count_windows_server AS
SELECT COUNT(DISTINCT hostname) AS numofwin FROM allassets_tbl WHERE osname ILIKE '%windows 20%';

Query for the dashboard:

SELECT * FROM mat_count_windows_server;

Dashboard 2 – displays the total number of workstations among all assets.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_count_windows_arm AS
SELECT COUNT(DISTINCT hostname) AS numofwin FROM allassets_tbl WHERE osname ILIKE '%windows 1%';

Query for the dashboard:

SELECT * FROM mat_count_windows_arm;

Dashboard 3 – displays the total number of network devices included within all assets.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_cisco_ios AS
SELECT COUNT(DISTINCT hostname) numofios FROM allassets_tbl WHERE osname ILIKE '%ios%';

Query for the dashboard:

SELECT * FROM mat_cisco_ios;

Dashboard 4 – displays the total number of hypervisors.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_сount_esxi_os AS
SELECT COUNT(DISTINCT hostname) AS numoflin FROM allassets_tbl WHERE osname ILIKE '%esxi%';

Query for the dashboard:

SELECT * FROM mat_сount_esxi_os;

Dashboards for Windows Server Vulnerabilities

I created separate dashboards for each asset category. For Windows servers, I designed multiple tabs, with each tab featuring a variety of dashboards. In this article, I will focus on the dashboards from the first tab, which presents vulnerability trends. The next article will cover additional tabs that concentrate on vulnerability prioritization.

Dashboard 1 – Shows the total number of both active and resolved vulnerabilities for Windows servers.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_Count_vuln_windows_server AS
SELECT COUNT(hostname) AS numofvuln FROM allassets_tbl WHERE osname ILIKE '%windows 20%';

Query for the dashboard:

SELECT * FROM mat_Count_vuln_windows_server;

Dashboard 2 – Displays the total number of active vulnerabilities for Windows servers.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_Count_vuln_active_windows_server AS
SELECT COUNT(hostname) AS numofvuln FROM allassets_tbl WHERE osname ILIKE '%windows 20%' AND status = 'new';

Query for the dashboard:

SELECT * FROM mat_Count_vuln_active_windows_server;

Dashboard 3 – Indicates the percentage of active vulnerabilities relative to the total number for Windows servers.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_Count_vuln_active_windows_server_percent AS
SELECT
active.vuln,
ROUND(active.vuln * 100.0 / all_win.total_vuln , 0) AS percent
FROM 
( SELECT COUNT(hostname) AS vuln
FROM allassets_tbl 
WHERE osname ILIKE '%windows 20%' AND status = 'new' )
AS active,
( SELECT COUNT(hostname) AS total_vuln
FROM allassets_tbl
WHERE osname ILIKE '%windows 20%' )
AS all_win;

Query for the dashboard:

SELECT * FROM mat_Count_vuln_active_windows_server_percent;

Dashboard 4 – Highlights the number of critical vulnerabilities for Windows servers.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_Count_vuln_active_windows_server_critical AS
SELECT COUNT(hostname) AS numofvuln 
FROM allassets_tbl 
WHERE osname ILIKE '%windows 20%' AND status = 'new' AND severity = 'critical';

Query for the dashboard:

SELECT * FROM mat_Count_vuln_active_windows_server_critical;

Dashboard 5 – Presents the status of vulnerabilities on a monthly basis for Windows servers.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_Count_vuln_active_by_found_fixed AS
WITH VulnerabylitiesFixed AS (
SELECT COUNT(hostname) as fixed, EXTRACT(MONTH from VulnDiscoveryTime) AS month
FROM allassets_tbl
WHERE osname ILIKE '%windows 20%' AND status = 'fixed'
GROUP BY month
ORDER BY month),
VulnerabylitiesNew AS (
 SELECT COUNT(hostname) AS numvulns,
EXTRACT(MONTH from VulnDiscoveryTime) AS month
FROM allassets_tbl
WHERE osname ILIKE '%windows 20%' AND status = 'new'
GROUP BY month
ORDER BY month)
SELECT v.numvulns, vf.fixed, v.month
FROM VulnerabylitiesNew v
JOIN VulnerabylitiesFixed vf ON v.month = vf.month;

Query for the dashboard:

SELECT * FROM mat_Count_vuln_active_by_found_fixed;

Dashboard 6 – Illustrates the current status of vulnerabilities, categorized by risk rating, broken down by month for Windows servers.

Below is the SQL query to create the materialized view:

CREATE MATERIALIZED VIEW mat_Chart_area_count_month_severity_active AS
SELECT
EXTRACT(MONTH from VulnDiscoveryTime) AS month, 
COUNT(hostname) AS total,
COUNT(hostname) FILTER (WHERE severity = 'critical') AS critical,
COUNT(hostname) FILTER (WHERE severity = 'high') AS high,
COUNT(hostname) FILTER (WHERE severity = 'medium') AS medium,
COUNT(hostname) FILTER (WHERE severity = 'low') AS low,
COUNT(hostname) FILTER (WHERE severity = 'none') AS none
FROM allassets_tbl
WHERE osname ILIKE '%windows 20%' AND status = 'new'
GROUP BY month
ORDER BY month;

Query for the dashboard:

SELECT * FROM mat_Chart_area_count_month_severity_active;

Summary of Part One

It's a promising start — the dashboards are already providing valuable insights. In the next article, I will focus on prioritizing vulnerability remediation and share my approach to making it more effective. This content will be useful even if you don't plan on creating your own dashboards or developing a custom solution.

If you have any ideas or expertise in vulnerability management, I would love to hear your thoughts:

• How do you engage cross-functional teams in the remediation process?
• And how do you encourage your colleagues to apply patches more quickly?