Master dashbord

Alternate version of Grafana Unbound dashboard(Elasticsearch)
Pro:

Less CPU/Disk space
Con:
No ad-hoc filter on Grafana

Install software

pkg_add logstash timescaledb grafana

Config sysctl

vi /etc/sysctl.conf

# shared memory limits
kern.shminfo.shmall=3145728
kern.shminfo.shmmax=2147483647
kern.shminfo.shmmni=1024

# semaphores
kern.shminfo.shmseg=1024
kern.seminfo.semmns=4096
kern.seminfo.semmni=1024

Set up logstash

Config

vi /etc/logstash/conf.d/dns.yml

input { 
  syslog { 
    port => "8514" 
    type => "syslog" 
  } 
} 
filter { 
  grok { 
    match => { 
      "message" => [ 
        "\[%{INT:pid}:%{INT:thread}\] info: %{IPORHOST:clientip} %{USERNAME:ns_record_name} %{WORD:ns_record_type} %{WORD:ns_record_class} %{WORD:ns_rcode} %{SECOND:time_to_resolv} %{INT:from_cache} %{INT:bytes}", 
        # Other messages 
        "%{GREEDYDATA:drop-document}" 
      ] 
    } 
    remove_field => ["message"] 
  } 
  if [drop-document] { 
    drop {} 
  } 
#  if [ns_record_type] == 'TYPE0' {
#    drop {}
#  }
} 
output { 
  jdbc {
    connection_string => 'jdbc:postgresql://127.0.0.1:5432/dns'
    username => 'dns'
    statement => [ "INSERT INTO query VALUES(NOW(), CAST (? AS inet), CAST (? AS inet), CAST (? AS rcode), CAST (? AS rtype), ?, CAST (? AS integer), CAST (? AS smallint), CAST (? AS integer), CAST (? AS boolean), CAST (? AS double precision))",
      "host", "clientip", "ns_rcode", "ns_record_type", "ns_record_name", "pid", "thread", "bytes", "from_cache", "time_to_resolv" ]
  }
#  file { 
#    path => "/tmp/test" 
#  } 
}

vi /etc/logstash/pipelines.yml

- pipeline.id: dns
  path.config: "/etc/logstash/conf.d/dns.yml"

Note: additional memoory for logstatsh vi /etc/logstash/jvm.options

# Xms represents the initial size of total heap space
# Xmx represents the maximum size of total heap space
-Xms1g
-Xmx4g

Install driver

cd /usr/local/logstash/bin/
./logstash-plugin install logstash-output-jdbc

mkdir -p /usr/local/logstash/vendor/jar/jdbc
cd /usr/local/logstash/vendor/jar/jdbc
ftp 'https://jdbc.postgresql.org/download/postgresql-42.5.0.jar'

Init DB

rcctl set postgresql flags "-D /home/postgresql/data"
mkdir -p /home/postgresql/data
chown _postgresql:_postgresql /home/postgresql/data

su - _postgresql
initdb -D /home/postgresql/data -E utf-8

vi /home/postgresql/data/postgresql.conf

shared_preload_libraries = 'timescaledb'

Set up DB

rcctl start postgresql

su - _postgresql
psql -d template1

CREATE DATABASE dns;
CREATE USER dns WITH PASSWORD 'dns';
GRANT ALL PRIVILEGES ON DATABASE dns TO dns;
ALTER DATABASE dns SET statement_timeout = '60s';

su - _postgresql
psql -d dns

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
ALTER DATABASE "dns" SET timescaledb.telemetry_level = 'basic';

CREATE TYPE rcode AS ENUM ('NOERROR', 'FORMERR', 'SERVFAIL', 'NXDOMAIN',
  'NOTIMPL', 'REFUSED', 'YXDOMAIN', 'YXRRSET', 'NXRRSET', 'NOTAUTH', 'NOTZONE');
CREATE TYPE rtype AS ENUM ('TYPE0', 'A', 'NS', 'MD', 'MF', 'CNAME', 'SOA',
  'MB', 'MG', 'MR', '"NULL"', 'WKS', 'PTR', 'HINFO', 'MINFO', 'MX', 'TXT',
  'RP', 'AFSDB', 'X25', 'ISDN', 'RT', 'NSAP', 'NSAP_PTR', 'SIG', 'KEY', 'PX',
  'GPOS', 'AAAA', 'LOC', 'NXT', 'EID', 'NIMLOC', 'SRV', 'ATMA', 'NAPTR', 'KX',
  'CERT', 'A6', 'DNAME', 'SINK', 'OPT', 'APL', 'DS', 'SSHFP', 'IPSECKEY',
  'RRSIG', 'NSEC', 'DNSKEY', 'DHCID', 'NSEC3', 'NSEC3PARAM', 'NSEC3PARAMS',
  'TLSA', 'SMIMEA', 'HIP', 'NINFO', 'RKEY', 'TALINK', 'CDS', 'CDNSKEY',
  'OPENPGPKEY', 'CSYNC', 'ZONEMD', 'SVCB', 'HTTPS', 'SPF', 'UINFO', 'UID',
  'GID', 'UNSPEC', 'NID', 'L32', 'L64', 'LP', 'EUI48', 'EUI64', 'TKEY', 'TSIG',
  'IXFR', 'AXFR', 'MAILB', 'MAILA', 'ANY', 'URI', 'CAA', 'AVC', 'TA', 'DLV');

DROP TABLE query;
CREATE TABLE IF NOT EXISTS query (
   timestamp timestamp(3) with time zone NOT NULL DEFAULT current_timestamp,
   
   host inet NOT NULL,
   clientip inet,
   ns_rcode rcode,
   ns_rtype rtype,
   ns_rname text,
   pid integer,
   thread smallint,
   bytes integer,
   cache boolean,
   time_use double precision
);

GRANT ALL PRIVILEGES ON TABLE query TO dns;
SELECT create_hypertable('query', 'timestamp', 'host', 1, chunk_time_interval => INTERVAL '1 hour');
SELECT add_retention_policy('query', INTERVAL '6 hours');

Start service

rcctl enable postgresql
rcctl enable logstash
rcctl enable grafana
rcctl start postgresql
rcctl start logstash
rcctl start grafana

Set up grafana dashboard

Install plugin

grafana-cli plugins install grafana-piechart-panel

Config datasource

# PostgreSQL
Host: localhost:5432
Database: dns
User: dns
TLS/SSL Mode: disable
Version: 14
TimescaleDB: enable

Import JSON

Unbound Global.json

Monitor Unbound

Enable log reply and syslog

server:
        use-syslog: yes 
        log-queries: no 
        log-replies: yes

Config syslog

vi /etc/syslog.conf Add at start of file

!!unbound 
*.* @tcp://172.16.215.65:8514 
!*

Grafana Unbound dashboard(Postgresql)

Set up Grafana dashbord to monitor unbound DNS resolver using logstash and PostgreSQL with TimescaleDB on OpenBSD