Howto: Hardening your environment, step 2: use grafana to visualize CIS benchmarks

About us

Posted on March 8, 2021

To have a good monitoring solution is essential for managing availability at enterprise level. So many organizations start with open source technology to visualize parameters. Grafana is used often and there are many complementing software pieces, which together create a mature environment. This tends even to influence the security management as well. A customer required us to integrate the CIS benchmarks into the existing grafana dashboards.

For step 1 we ended up with the CIS Assessor installed and with the dashboard which can be used to show the results and download the reports. The grafana dashboards here will not replace the CIS dashboard but the additional benefit of grafana is to have a customizable overview on hundreds or even thousends of VMs, containers, etc. The first approach is figure 1 which can easily be adopted to other needs.

figure 1: dashboard first usable draft

The minimal requirement is to have an immediate impression of the overall status, the number of tests, which were performed, the scores achieved and to be able to dig into the systems level. Entry points are the benchmark types as well as the enterprise architecture. The grafana way to realize this is by dashboard variables that show up as dropdown lists in the header of the dashboards. ,These variables have a hierarchy, first you choose between linux and windows objects, then among the available benchmarks, the systems which were tested according to these benachmarks etc.

Below we have the big numbers: tests performed in total, tests per timespan choosen in the upper right corner, the overall status. Then you can drill down by choosing benchmarks, systems and categories, good enough to monitor the “continous improvement process”.

So what do we have to do?

Step1: decision where do we get the data from?

Step2: create a mechanism to import the reports in to the database

Step3: install grafana

Step4: create the dashboards

You can find all resources on github so I will just document the design decisions.

Step 1: where do we get the data from?

The Assessor uses a generic data model which is hardly understandable.We can deduce the benchmarks, the main scores and some further data that we could use. I started with this approach and there are code snippets for a dashboard on github if you want to continue this way. The downside is that we are not allowed to make any changes on the data model and the queries get very complicated for what we want to do. So the approach was to parse the generated reports because they are easy to understand and put them in a separate schema of the mysql database.

figure 2: ER rmodel of grafdb

We have the t_benchmark table which contains the “definition” of a benchmark. Each benchmark consists of n single tests, stored in table t_testname, with an understandable “headline” and a technical “name”. There is a hierarchy among tests which is managed by attributes “layer” and “layerposition”. On the right side we see tables t_benchresult each with n t_testresults which represent the performed tests and their results. Pretty simple and a base for all kinds of diagrams.

The model is created by the MySQL Workbench. The forward engineering mechanism is used to create the database instance called “grafdb”. The database user has to get the right to access the database from the jump host as well the host where grafana is running.

Step2: create a mechanism to import the reports in to the database

The Assessor is controlled by config files and crontab entries, so it suits to have a python skript to parse the reports and put them in the db. Please find it on github.

[ec2-user@ip-10-0-41-145 python]$ python3.8 pmtest.py -h
pmtest.py -p  -h -d  -u  -s

The code is straight forward: the

def import_reports(path):

reads all *.html files from the given directory “path”.

figure 3: Test summary

Each report contains the summary -see figure 3- which is parsed by

def read_report(file):

which extracts the meta_information target machine, benchmark-type, testtime etc. These data are stored in a structure

test_result = {'title': "", 'category': "", 'time': "", 'level': "", 'target': "0.0.0.0", 'results': []}

and later inserted into the database by

def store(test_result):

Not all reports follow exactly the structure so we have minimal adjustments per benchmark.

Step3: install python, grafana

We start with the Amazon Linux 2 image used for the Assessor. We need a recent MySQL driver and the matching python version. The following snippets from my history show the necessary steps..

[ec2-user@ip-10-0-41-145 ~]$ history
1 sudo yum update
2 amazon-linux-extras | grep -i python
380 sudo amazon-linux-extras enable python3.8
381 yum clean metadata
383 sudo yum install python38
384 python -V
385 python3.8 -V

411 yum search pip
412 sudo yum install python38-pip.noarch
417 sudo pip3.8 install mysql-connector
418 sudo pip3.8 install mysql-connector-python
426 mysql -u root -p

[create user ‘grafdb’@’your-ip-address’ identified by ‘anotherComplexPassword’;]

427 mysql -u grafdb -p

450 vi pmtest.py
451 python3.8 pmtest.py -p ../Assessor-CLI/reports

// revert the renamed reports during developement

453 for file in ../Assessor-CLI/reports/*.htmlimp; do mv “$file” “${file/.htmlimp/.html}”; done;

For grafana there are helpfull step by step guides, i.e. here.

Step 4: create the dashboards

We have two dashboards: one based on the CCPD database, which is part of the Assessor itself and the other which is a rather stable base for further improvements based on the grafdb database in Step 1.

figure 5 CCPD based dashboard

The dashboard variables

Benchmark defined by

SELECT identifier from benchmark;

The Panels

Benchmarks up to now

SELECT
id
FROM test_result

tests on benchmark within timeframe

SELECT
count(tr.id)
FROM test_result tr JOIN system_identifier ti ON tr.target_system_id=ti.target_system_id and ti.type_id=1426
JOIN benchmark b ON tr.benchmark_id=b.id and b.identifier IN ($Benchmark)
WHERE
$__timeFilter(start_time)

overall performance

SELECT
tr.start_time as "time",
avg(tr.score_dbl) as "score",
ti.value as metric
FROM test_result tr JOIN system_identifier ti ON tr.target_system_id=ti.target_system_id and ti.type_id=1426
JOIN benchmark b ON tr.benchmark_id=b.id and b.identifier IN ($Benchmark)
WHERE
$__timeFilter(tr.start_time)
group by ti.value,1
order by time

general score over time

SELECT
tr.start_time as "time",
avg(tr.score_dbl) as "score",
ti.value as metric
from test_result tr JOIN system_identifier ti ON tr.target_system_id=ti.target_system_id and ti.type_id=1426
group by ti.value,1
order by time

This gives a general overview on the status, but lacks possibility to dig into the system or benchmark layer. Lets have a look on the second dashboard, based on the grafdb.

figure 6: proposed dashboard with overview and specific details

We have chained variables:

select name from t_systemtype

Benchmark

select bm.name from t_benchmark bm JOIN t_systemtype type ON bm.t_systemtype_id=type.id and type.name IN ($os)

system

select br.target from t_benchresult br JOIN t_benchmark bm ON br.t_benchmark_id=bm.id and bm.name IN ($Benchmark)

testcategory

select distinct tn.headline from t_testresult tr JOIN t_benchresult br ON tr.t_benchresult_id=br.id JOIN t_testname tn ON tr.t_testname_id=tn.id where tn.layer=0 AND br.target=”$system”

category_layer1

select distinct tn.position_in_benchmark from t_testresult tr JOIN t_benchresult br ON tr.t_benchresult_id=br.id and br.target=”$system” JOIN t_testname tn ON tr.t_testname_id=tn.id where tn.layer=0 and tn.headline=”$testcategory”

lasttestid

select max(br.id) from t_benchresult br where br.target=”$system”

The Panels

Benchmarks up to now

SELECT id
FROM t_benchresult

tests on benchmark within timeframe

SELECT
count(tr.id)
FROM t_benchresult tr JOIN t_benchmark bm ON tr.t_benchmark_id=bm.id and bm.name IN ($Benchmark)
WHERE
$__timeFilter(tr.executed)

overall performance (gauge)

SELECT
br.executed as "time",
avg(br.score) as "score",
bm.name as metric
from t_benchresult br JOIN t_benchmark bm ON br.t_benchmark_id=bm.id and bm.name IN ($Benchmark)
where 
$__timeFilter(br.executed)
group by bm.name,1
order by time

performance on categories (bar gauge)

SELECT distinct 
tr.percent as "score",
tn.headline as metric
from t_testresult tr JOIN t_benchresult br ON tr.t_benchresult_id=br.id and br.target="$system"
JOIN t_testname tn ON tr.t_testname_id=tn.id
where tn.layer=0 and br.id="$lasttestid"

system score on $system over time

SELECT
br.executed as "time",
avg(tr.score) as "score",
tn.headline as metric
from t_testresult tr JOIN t_benchresult br ON tr.t_benchresult_id=br.id and br.target="$system"
JOIN t_testname tn ON tr.t_testname_id=tn.id
where tn.layer=0 
group by tn.headline,1
order by time

system score on category $testcategory

SELECT
br.executed as "time",
avg(tr.score) as "score",
tn.headline as metric
from t_testresult tr JOIN t_benchresult br ON tr.t_benchresult_id=br.id and br.target="$system"
JOIN t_testname tn ON tr.t_testname_id=tn.id
where tn.layer=1 AND tn.position_in_benchmark like "$category_layer1%" 
group by tn.headline,1
order by time

The bar gauge is rather tricky. To show the “headline” attribute near the bar we need to specify $__cell_1 as the display name.

figure 7: field options for bar gauge panel

The queries are best formed within the SQL Editor. Very simple queries could be created with help of the grafana Query Builder. It is much a try and error thing, the grafana log file /var/log/grafana/grafana.log contains every sql error in detail. Some effects are not clear, please have a look at the following panel “tests on benchmark within timeframe“

figure 8: effect on upper middle panel

Discussion

We have seen that the monitoring of our “continous improvement process” can be done by grafana. It is quite comfortable for developers and could be easy to adopt the next requirement. For the less experienced the creation of sql queries is rather error prone.