Building Gantt Diagram using Vega-lite

Question

I am want to build a Gantt Diagram to see users connected to WireGuard in real-time. Logs are created by mine python script so I could make changes to it if needed.

Events are going to look like this: enter image description here

* log_in_time and log_out_time is in unix format

Logs are look like this:

May 10 15:49:13 wireguard: erste has logged_in log_in_time 1715356153 log_out_time 0 from 172.17.0.180 port 56750 May 10 15:52:13 wireguard: erste has logged_out log_in_time 1715356153 log_out_time 1715356333 from 172.17.0.180 port 56750 May 10 16:25:16 wireguard: erste has logged_in log_in_time 1715358316 log_out_time 0 from 172.17.0.180 port 65242

and Vega code looks like this:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "title": "Gantt diagram users online using VPN",
  "data": {
    "url": {
      "%context%": true,
      "%timefield%": "@timestamp",
      "index": "wazuh-alerts-*",
      "body": {
        "size": 10000
      }
    },
    "format": {"property": "hits.hits"}
  },
  
  "transform": [
    {
      "aggregate": [{
          "op": "max",
          "field": "_source.data.log_out_time",
          "as": "max_log_out_time"
      }],
      "groupby": [
        "_source.data.dstuser",
        "_source.data.log_in_time"
      ]
    },
    {
      "calculate": "toDate(datum._source.data.log_in_time)",
      "as": "log_in_time"
    },
    {
     "calculate": "if(max_log_out_time == 0, now(), max_log_out_time)",
      "as": "actual_unix_log_out_time"
    },
    {
      "calculate": "toDate(datum.actual_unix_log_out_time)",
      "as": "actual_log_out_time"
    },
  ],
  
  "layer": [
    {
      "mark": "bar",
      "encoding": {
        "y": {
          "field": "_source.data.dstuser", 
          "type": "ordinal", 
          "title": "User"
        },
        "x": {
          "field": "log_in_time", 
          "type": "temporal",  
          "title": "Time", 
          "axis": {
            "format": "%a %H:%M"
          }
        },
        "x2": {
          "field": "actual_log_out_time", 
          "type": "temporal"
        },
        "tooltip": [
          {
            "field": "_source.data.dstuser", 
            "title": "User"
          },
          {
            "field": "log_in_time",
            "timeUnit": "datemonthhoursminutes", 
            "title": "Logged in"
          },
          {
            "field": "actual_log_out_time", 
            "timeUnit": "datemonthhoursminutes", 
            "title": "Logged out"
          },
          // {"field": "agg", "title": "agg"}
        ],
        "color": {
          "field": "_source.data.dstuser",
          "type": "nominal",
          "title": "User"
        }
      }
    }
  ]
}

Current error is:

Unrecognized signal name: "max_log_out_time"

What can I do to make it work?

I have tried similar aggregation on another data and it was just fine so I don't think it is the issue. Probably something with log_in_time and log_out_time but I have no idea how to fix it.

Answer 1

Have a test of this code which should get you on the right path.

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "title": "Gantt diagram users online using VPN",
  "data": {
    "values": [
      {
        "dstuser": "erste",
        "log_in_time": 1715347362,
        "log_out_time": 0,
        "log": "erste has logged_in"
      },
      {
        "dstuser": "erste",
        "log_in_time": 1715347362,
        "log_out_time": 1715501362,
        "log": "erste has logged_out"
      },
      {
        "dstuser": "erste",
        "log_in_time": 1715501362,
        "log_out_time": 0,
        "log": "erste has logged_in"
      }
    ]
  },
  "transform": [
    {
      "window": [{"op": "rank", "as": "rank"}],
      "sort": [{"field": "log_out_time", "order": "descending"}],
      "groupby": ["dstuser", "log_in_time"]
    },
    {"filter": "datum.rank == 1"},
    {"calculate": "datetime(datum.log_in_time * 1000)", "as": "log_in_time"},
    {
      "calculate": "datum.log_out_time == 0 ? datetime(now()) : datetime(datum.log_out_time * 1000)",
      "as": "actual_log_out_time"
    },
    {
      "calculate": "datum.dstuser + ' (' + timeFormat(datum.log_in_time, '%Y-%m-%d %H:%M:%S') + ')'",
      "as": "dstuser_login"
    }
  ],
  "layer": [
    {
      "mark": "bar",
      "encoding": {
        "y": {"field": "dstuser_login", "type": "ordinal", "title": "User"},
        "x": {
          "field": "log_in_time",
          "type": "temporal",
          "title": "Time",
          "axis": {"format": "%Y-%m-%d %H:%M"}
        },
        "x2": {"field": "actual_log_out_time"},
        "tooltip": [
          {"field": "dstuser", "title": "User"},
          {"field": "log_in_time", "title": "Logged in"},
          {"field": "actual_log_out_time", "title": "Logged out"}
        ],
        "color": {"field": "dstuser", "type": "nominal", "title": "User"}
      }
    }
  ]
}

Adam (APB Reports)