TLC Execution Statistics Ingress into SQL for Analytics (Metabase)

apache2 vhost

	WSGIScriptAlias / /var/www/flask/log_exec_stats.wsgi
	<Directory /var/www/flask/>
		Options FollowSymLinks
		AllowOverride None
		Require all granted
	</Directory>

gistfile1.txt

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.5 (Debian 11.5-1+deb10u1)
-- Dumped by pg_dump version 11.5 (Debian 11.5-1+deb10u1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: execution_description; Type: TABLE; Schema: public; Owner: metabase
--

CREATE TABLE public.execution_description (
    id integer NOT NULL,
    installation_id integer NOT NULL,
    execution_timestamp timestamp without time zone NOT NULL,
    toolbox_launched boolean NOT NULL,
    queue_type character varying(64),
    fp_set_type character varying(64),
    off_heap_memory integer NOT NULL,
    heap_memory integer NOT NULL,
    jvm_architecture character varying(8) NOT NULL,
    jvm_version character varying(12) NOT NULL,
    jvm_vendor character varying(64) NOT NULL,
    os_architecture character varying(8) NOT NULL,
    os_version character varying(48) NOT NULL,
    os_name character varying(24) NOT NULL,
    core_count integer NOT NULL,
    worker_count integer NOT NULL,
    mode character varying(16) NOT NULL,
    version character varying(16) NOT NULL,
    ide character varying(255) DEFAULT 'cli'::character varying NOT NULL
);


ALTER TABLE public.execution_description OWNER TO metabase;

--
-- Name: distinctenvironments; Type: VIEW; Schema: public; Owner: metabase
--

CREATE VIEW public.distinctenvironments AS
 SELECT DISTINCT execution_description.heap_memory,
    execution_description.jvm_vendor,
    execution_description.jvm_architecture,
    execution_description.jvm_version,
    execution_description.os_version,
    execution_description.os_name,
    execution_description.os_architecture,
    execution_description.core_count
   FROM public.execution_description;


ALTER TABLE public.distinctenvironments OWNER TO metabase;

--
-- Name: execution_description_id_seq; Type: SEQUENCE; Schema: public; Owner: metabase
--

CREATE SEQUENCE public.execution_description_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.execution_description_id_seq OWNER TO metabase;

--
-- Name: execution_description_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: metabase
--

ALTER SEQUENCE public.execution_description_id_seq OWNED BY public.execution_description.id;


--
-- Name: installation; Type: TABLE; Schema: public; Owner: metabase
--

CREATE TABLE public.installation (
    id integer NOT NULL,
    unique_id character varying(32) NOT NULL
);


ALTER TABLE public.installation OWNER TO metabase;

--
-- Name: installation_id_seq; Type: SEQUENCE; Schema: public; Owner: metabase
--

CREATE SEQUENCE public.installation_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.installation_id_seq OWNER TO metabase;

--
-- Name: installation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: metabase
--

ALTER SEQUENCE public.installation_id_seq OWNED BY public.installation.id;


--
-- Name: nonrandominstallationidentifier; Type: VIEW; Schema: public; Owner: metabase
--

CREATE VIEW public.nonrandominstallationidentifier AS
 SELECT execution_description.installation_id
   FROM public.execution_description
  GROUP BY execution_description.installation_id
 HAVING (count(*) > 1);


ALTER TABLE public.nonrandominstallationidentifier OWNER TO metabase;

--
-- Name: execution_description id; Type: DEFAULT; Schema: public; Owner: metabase
--

ALTER TABLE ONLY public.execution_description ALTER COLUMN id SET DEFAULT nextval('public.execution_description_id_seq'::regclass);


--
-- Name: installation id; Type: DEFAULT; Schema: public; Owner: metabase
--

ALTER TABLE ONLY public.installation ALTER COLUMN id SET DEFAULT nextval('public.installation_id_seq'::regclass);


--
-- Name: execution_description execution_description_pkey; Type: CONSTRAINT; Schema: public; Owner: metabase
--

ALTER TABLE ONLY public.execution_description
    ADD CONSTRAINT execution_description_pkey PRIMARY KEY (id);


--
-- Name: installation installation_pkey; Type: CONSTRAINT; Schema: public; Owner: metabase
--

ALTER TABLE ONLY public.installation
    ADD CONSTRAINT installation_pkey PRIMARY KEY (id);


--
-- Name: installation installation_unique_id_key; Type: CONSTRAINT; Schema: public; Owner: metabase
--

ALTER TABLE ONLY public.installation
    ADD CONSTRAINT installation_unique_id_key UNIQUE (unique_id);


--
-- Name: execution_description execution_description_installation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: metabase
--

ALTER TABLE ONLY public.execution_description
    ADD CONSTRAINT execution_description_installation_id_fkey FOREIGN KEY (installation_id) REFERENCES public.installation(id);


--
-- PostgreSQL database dump complete
--

Installation

apt-get install postgresql postgresql-11 postgresql-common postgresql-client-11 postgresql-client-common
apt-get install psycopg2 libpq-dev python-dev
apt-get install libapache2-mod-wsgi-py3 python-dev
apt-get install python3-flask
apt-get install python3-psycopg2

log_exec_stats.py

import datetime

from flask import Flask, request
app = Flask(__name__)

## sql connection
import psycopg2
cnx = psycopg2.connect(host="localhost", database="tlaplus", user="abcdefg", password="1234567890")

@app.route("/") #, methods=['HEAD'])
def head():
    ## Just a HEAD request or one we want to handle?
    if request.args.get('ver') is None:
        return "kthxbye"

    ## Get the request's query part. TLC first sends "ver".
    query = "ver=" + request.args.get('ver')

    ## Convert the query "ver=dev,mode=bfs,osName=..." into
    ## a dictionary (key/values).
    #params = dict(e.split('=') for e in query.split(','))
    ## Azul VM's string gets split into 'jvmVendor=Azul Systems', ' Inc'.
    ## We get rid of ' Inc' with the filter.
    params = dict(e.split('=') for e in filter(lambda s : '=' in s, query.split(',')))

    ## Check if the dict contains the pairs we need.
    if 'id' not in params:
        return "kthxbye2"

    #print(params['id'])


    ##
    with cnx.cursor() as cursor:
          #cursor.execute("SELECT id FROM installation WHERE unique_id = '{0}';".format(params['id']))
          #row = cursor.fetchone()
          ##print(str(row[0]))

          cursor.execute("INSERT INTO installation (unique_id) VALUES ('{0}') ON CONFLICT (unique_id) DO UPDATE SET unique_id = '{0}' RETURNING id".format(params['id']))
          row = cursor.fetchone()

          if row is not None:
               id_to_use = row[0]
               timestamp = datetime.datetime.fromtimestamp(float(params['ts'])/1000.0)
               toolbox_launched = params['toolbox'] == "true"

               queue_type = None
               if ('queue' in params) and (len(params['queue']) > 0):
                     queue_type = params['queue']

               fp_set_type = None
               if ('fpset' in params) and (len(params['fpset']) > 0):
                     fp_set_type = params['fpset']

               ide = 'cli'
               if ('ide' in params) and (len(params['ide']) > 0):
                     ide = params['ide'][:255]

               insrt = ("INSERT INTO execution_description "
                        "(installation_id, execution_timestamp, toolbox_launched, queue_type, "
                        "fp_set_type, off_heap_memory, heap_memory, jvm_architecture, jvm_version, "
                        "jvm_vendor, os_architecture, os_version, os_name, core_count, worker_count, "
                        "mode, version, ide) "
                    "VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}')".format(id_to_use, timestamp, toolbox_launched, queue_type, fp_set_type,
                   int(params['jvmOffHeapMem']), int(params['jvmHeapMem']), 
                   params['jvmArch'], params['jvmVersion'], params['jvmVendor'],
                   params['osArch'], params['osVersion'], params['osName'],
                   int(params['cores']), int(params['workers']), params['mode'][:16], params['ver'][:16], ide))
               #print(insrt)
               cursor.execute(insrt)
               cnx.commit()
               #cursor.close()
               #cnx.close()

          return "Thank You!"


if __name__ == "__main__":
    app.run()

log_exec_stats.wsgi

#! /usr/bin/python3

import logging
import sys
logging.basicConfig(stream=sys.stderr)
sys.path.insert(0, '/var/www/flask/')
from log_exec_stats import app as application
application.secret_key = 'cqfralkijczn8947z5'