Jump to content
ShawnSack

Grafana and Labtech

Recommended Posts

After following @ShawnSack's directions, how did you guys get this set up as a service? I'm trying to get this to start when my server comes up, but I'm not having any luck.

Edited by NeonBlueHDD
Forgot @ in front of username

Share this post


Link to post
Share on other sites

@NeonBlueHDD

Hi , I would recommend that you redo your Grafana installation since ShawnSack´s instruction was before Grafana Labs included the mysql-datasource as part of their package.

Heres the official documentation : http://docs.grafana.org/installation/debian/

You can try to upgrade your current , to the latest version.

 

On to connection issues:

Try to get your service running:

sudo service grafana-server start

Make Grafana start up after reboot:

To configure the Grafana server to start at boot time:

sudo update-rc.d grafana-server defaults


Start the server (via systemd)
To start the service using systemd:

systemctl daemon-reload
systemctl start grafana-server
systemctl status grafana-server


Enable the systemd service so that Grafana starts at boot.

sudo systemctl enable grafana-server.service

Make sure when you have installed everything official you use these settings.

 

Datasources settings:

Name : Whatever you like | Make default

Type: MySQL

MySQL Connection

Host: your.server.ip.here:3306

Database: labtech   | Important, since thats the database where Automate stores everything.

User: A user account in Automate | Recommend something with only read permissions.| Remember that Automate user names are Case-Sensitive

Password: No need to explain that one

 

Hope that helps

Share this post


Link to post
Share on other sites

Sorry to necromance this thread but was wondering will this work on labtech hosted environments?  If so how hard is it to make the connection to the hosted environment to accomplish this?

Share this post


Link to post
Share on other sites

Hi , I am not really sure , it needs to be able to connect to your database in some way.Check your documentation on how to connect with connectors to your hosted enviroment.

 

Share this post


Link to post
Share on other sites

I want to say that i am a huge fan of Grafana I have 9k+ agents and the CWA interface is very anti productive slow even with a ton or resources for the hosts/split server config. If you are interested in fast meaninful NOC type displays...this is your tool. Totally custom for your needs. Not what CWA thinks you should have. Great for real time views of incidents. Great for whitebox monitoring of scripts, problem scripts, runaway scripts. 

Works against a ton of datasources even SQL/Azure so you can even display your CWM metrics. 

Grafana_Dynamic_Client.png

  • Thanks 1

Share this post


Link to post
Share on other sites

For CWA it's just MySQL queries to display the info already in the DB.  I can show you some samples but once you get started..it gets quite addicting.

 

Share this post


Link to post
Share on other sites

I have some, but some of yours are much fancier.

Would appreciate if you could share the json.:)

Share this post


Link to post
Share on other sites

also a Guy who is addicted to Grafana Dashboards :D

FYI, at Columns you can also doing linking to Web CC with variables. 

p.e. : https://[[ServerFQDN]]/automate/computer/${__cell_0}/normal-tiles

 

  • Thanks 1

Share this post


Link to post
Share on other sites

I got the install all setup, the sql connector works fine, but following the instructions to add an agent count just produces an N/A. Anyone have updated into on that or how to setup manual SQL queries?

Share this post


Link to post
Share on other sites
23 hours ago, bschmiedlin said:

I got the install all setup, the sql connector works fine, but following the instructions to add an agent count just produces an N/A. Anyone have updated into on that or how to setup manual SQL queries?

You need to use the right panel and watch out for the time series stuff. Time series is cool but its a different syntax. Also i like the SQL editor over the new editor they use. You can toggle it on/off with the layer icon 

Here is a simple query to get agents count in the Singlestat panel. 

SingleStat.png

Share this post


Link to post
Share on other sites
On 3/8/2019 at 12:54 PM, jasperlax4 said:

You need to use the right panel and watch out for the time series stuff. Time series is cool but its a different syntax. Also i like the SQL editor over the new editor they use. You can toggle it on/off with the layer icon 

Here is a simple query to get agents count in the Singlestat panel. 

SingleStat.png

Thanks for the info. Installing the latest version helped as well lol. 

Share this post


Link to post
Share on other sites

For anyone who wants to get a quick start page, here's the json of mine.

 

{
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": "-- Grafana --",
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "limit": 100,
        "name": "Annotations & Alerts",
        "rawQuery": "SELECT\n    UNIX_TIMESTAMP(<time_column>) as time_sec,\n    <text_column> as text,\n    <tags_column> as tags\n  FROM <table name>\n  WHERE $__timeFilter(time_column)\n  ORDER BY <time_column> ASC\n  LIMIT 100\n  ",
        "showIn": 0,
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "gnetId": null,
  "graphTooltip": 0,
  "id": 18,
  "links": [],
  "panels": [
    {
      "columns": [],
      "compactRowsEnabled": true,
      "datasource": "Automate",
      "datatablePagingType": "numbers",
      "datatableTheme": "basic_theme",
      "emptyData": false,
      "fontSize": "80%",
      "gridPos": {
        "h": 5,
        "w": 12,
        "x": 0,
        "y": 0
      },
      "hoverEnabled": true,
      "id": 28,
      "infoEnabled": true,
      "lengthChangeEnabled": true,
      "links": [],
      "orderColumnEnabled": true,
      "pagingTypes": [
        {
          "text": "Page number buttons only",
          "value": "numbers"
        },
        {
          "text": "'Previous' and 'Next' buttons only",
          "value": "simple"
        },
        {
          "text": "'Previous' and 'Next' buttons, plus page numbers",
          "value": "simple_numbers"
        },
        {
          "text": "'First', 'Previous', 'Next' and 'Last' buttons",
          "value": "full"
        },
        {
          "text": "'First', 'Previous', 'Next' and 'Last' buttons, plus page numbers",
          "value": "full_numbers"
        },
        {
          "text": "'First' and 'Last' buttons, plus page numbers",
          "value": "first_last_numbers"
        }
      ],
      "panelHeight": 161,
      "rowNumbersEnabled": false,
      "rowsPerPage": 5,
      "scroll": true,
      "scrollHeight": "default",
      "searchEnabled": false,
      "showCellBorders": false,
      "showHeader": true,
      "showRowBorders": true,
      "sort": {
        "col": 0,
        "desc": true
      },
      "stripedRowsEnabled": true,
      "styles": [
        {
          "colorMode": "cell",
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 1,
          "pattern": "DiskHealth",
          "thresholds": [
            "0",
            "74",
            "101"
          ],
          "type": "number",
          "unit": "short"
        },
        {
          "colorMode": "cell",
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "pattern": "IntrusionHealth",
          "thresholds": [
            "0",
            "74",
            "101"
          ],
          "type": "number",
          "unit": "short"
        },
        {
          "colorMode": "cell",
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "pattern": "UsabilityHealth",
          "thresholds": [
            "0",
            "74",
            "101"
          ],
          "type": "number",
          "unit": "short"
        },
        {
          "colorMode": "cell",
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "pattern": "ServiceHealth",
          "thresholds": [
            "0",
            "74",
            "101"
          ],
          "type": "number",
          "unit": "short"
        },
        {
          "colorMode": "cell",
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "pattern": "EventHealth",
          "thresholds": [
            "0",
            "74",
            "101"
          ],
          "type": "number",
          "unit": "short"
        },
        {
          "colorMode": "cell",
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "pattern": "Avg Score",
          "thresholds": [
            "0",
            "74",
            "101"
          ],
          "type": "number",
          "unit": "short"
        },
        {
          "colorMode": "cell",
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "pattern": "UpdateHealth",
          "thresholds": [
            "0",
            "50",
            "101"
          ],
          "type": "number",
          "unit": "short"
        }
      ],
      "targets": [
        {
          "alias": "",
          "format": "table",
          "rawSql": "SELECT\r\n  clients.clientid AS 'ClientID',\r\n  ROUND(AVG(IF((LENGTH(`Stat15`) < 1),NULL,ROUND((LEFT(`Stat15`,4) * 100),0))),1) AS `AVHealth`,\r\n  ROUND(AVG(IF((LENGTH(`Stat16`) < 1),NULL,ROUND((LEFT(`Stat16`,4) * 100),0))),1) AS `DiskHealth`,\r\n  ROUND(AVG(IF((LENGTH(`Stat17`) < 1),NULL,ROUND((LEFT(`Stat17`,4) * 100),0))),1) AS `IntrusionHealth`,\r\n  ROUND(AVG(IF((LENGTH(`Stat18`) < 1),NULL,ROUND((LEFT(`Stat18`,4) * 100),0))),1) AS `UsabilityHealth`,\r\n  ROUND(AVG(IF((LENGTH(`Stat19`) < 1),NULL,ROUND((LEFT(`Stat19`,4) * 100),0))),1) AS `ServiceHealth`,\r\n  ROUND(AVG(IF((LENGTH(`Stat20`) < 1),NULL,ROUND((LEFT(`Stat20`,4) * 100),0))),1) AS `UpdateHealth`,\r\n  ROUND(AVG(IF((LENGTH(`Stat14`) < 1),NULL,ROUND((LEFT(`Stat14`,4) * 100),0))),1) AS `EventHealth`,\r\n  ROUND(((ROUND(AVG(IF((LENGTH(`Stat15`) < 1),NULL,ROUND((LEFT(`Stat15`,4) * 100),0))),1)+ROUND(AVG(IF((LENGTH(`Stat16`) < 1),NULL,ROUND((LEFT(`Stat16`,4) * 100),0))),1)+ROUND(AVG(IF((LENGTH(`Stat17`) < 1),NULL,ROUND((LEFT(`Stat17`,4) * 100),0))),1)+ROUND(AVG(IF((LENGTH(`Stat18`) < 1),NULL,ROUND((LEFT(`Stat18`,4) * 100),0))),1)+ROUND(AVG(IF((LENGTH(`Stat19`) < 1),NULL,ROUND((LEFT(`Stat19`,4) * 100),0))),1)+ROUND(AVG(IF((LENGTH(`Stat20`) < 1),NULL,ROUND((LEFT(`Stat20`,4) * 100),0))),1)+ROUND(AVG(IF((LENGTH(`Stat14`) < 1),NULL,ROUND((LEFT(`Stat14`,4) * 100),0))),1))/7),1) AS 'Avg Score'\r\nFROM v_ltcr_h_extrastats\r\n JOIN computers USING (computerid)\r\n  LEFT JOIN clients USING (clientid) \r\n    WHERE (`Stat14` <> '-1') AND eventdate > DATE_ADD(NOW(),INTERVAL -1 MONTH)\r\n     GROUP BY clients.name\r\n     ORDER BY 'Avg Score' DESC LIMIT 5;\r\n",
          "refId": "A"
        }
      ],
      "themeOptions": {
        "dark": "./css/datatable-dark.css",
        "light": "./css/datatable-light.css"
      },
      "themes": [
        {
          "disabled": false,
          "text": "Basic",
          "value": "basic_theme"
        },
        {
          "disabled": true,
          "text": "Bootstrap",
          "value": "bootstrap_theme"
        },
        {
          "disabled": true,
          "text": "Foundation",
          "value": "foundation_theme"
        },
        {
          "disabled": true,
          "text": "ThemeRoller",
          "value": "themeroller_theme"
        }
      ],
      "title": "Health Scores",
      "transform": "table",
      "type": "briangann-datatable-panel"
    },
    {
      "aliasColors": {},
      "bars": false,
      "dashLength": 10,
      "dashes": false,
      "datasource": "Automate",
      "fill": 1,
      "gridPos": {
        "h": 5,
        "w": 12,
        "x": 12,
        "y": 0
      },
      "hideTimeOverride": true,
      "id": 12,
      "legend": {
        "alignAsTable": false,
        "avg": true,
        "current": true,
        "max": false,
        "min": false,
        "rightSide": false,
        "show": true,
        "sort": null,
        "sortDesc": null,
        "total": false,
        "values": true
      },
      "lines": true,
      "linewidth": 2,
      "links": [],
      "nullPointMode": "null as zero",
      "percentage": false,
      "pointradius": 1,
      "points": false,
      "renderer": "flot",
      "seriesOverrides": [
        {
          "alias": "Brewdog OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "Becks OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "Lagunita OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "Duvel OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "VEEAM OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "ObligoWDS OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "Corona OUT",
          "transform": "negative-Y"
        }
      ],
      "spaceLength": 10,
      "stack": false,
      "steppedLine": false,
      "targets": [
        {
          "alias": "",
          "format": "time_series",
          "rawSql": "SELECT\r\n(h_computerstatshourly.Datain) AS value,\r\n (UNIX_TIMESTAMP(LastUpdate))  AS  time_sec ,\r\n  \r\n  name AS metric\r\nFROM h_computerstatshourly \r\nINNER JOIN computers ON h_computerstatshourly.Computerid=computers.computerid\r\nWHERE $__timeFilter(LastUpdate) AND os LIKE '%server%' \r\nGROUP BY time_sec ASC\r\n",
          "refId": "A"
        }
      ],
      "thresholds": [],
      "timeFrom": "24h",
      "timeShift": null,
      "title": "Network traffic in",
      "tooltip": {
        "shared": true,
        "sort": 1,
        "value_type": "individual"
      },
      "transparent": false,
      "type": "graph",
      "xaxis": {
        "buckets": null,
        "mode": "time",
        "name": null,
        "show": true,
        "values": []
      },
      "yaxes": [
        {
          "format": "Bps",
          "label": null,
          "logBase": 1,
          "max": null,
          "min": null,
          "show": true
        },
        {
          "format": "short",
          "label": null,
          "logBase": 1,
          "max": null,
          "min": null,
          "show": false
        }
      ],
      "yaxis": {
        "align": false,
        "alignLevel": null
      }
    },
    {
      "columns": [],
      "datasource": "Automate",
      "description": "",
      "fontSize": "100%",
      "gridPos": {
        "h": 5,
        "w": 6,
        "x": 0,
        "y": 5
      },
      "height": "210",
      "hideTimeOverride": true,
      "id": 41,
      "links": [],
      "pageSize": null,
      "scroll": false,
      "showHeader": true,
      "sort": {
        "col": 2,
        "desc": true
      },
      "styles": [
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "TestValue",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "LastContact",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "LastHeartbeatTime",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        }
      ],
      "targets": [
        {
          "alias": "",
          "format": "table",
          "hide": false,
          "rawSql": "SELECT TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, \"0000-00-00 00:00:00\")) AS TestValue,\r\n    c.name AS IdentityField,          \r\n    c.ComputerID AS ComputerID,    \r\n    c.LastContact,      \r\n    h.LastHeartbeatTime,    \r\n    acd.NoAlerts,    \r\n    acd.UpTimeStart,    \r\n    acd.UpTimeEnd    \r\nFROM Computers AS c     \r\nLEFT JOIN HeartBeatComputers AS h ON h.ComputerID = c.ComputerID\r\nLEFT JOIN AgentComputerData AS acd ON c.ComputerID = acd.ComputerID\r\nLEFT JOIN Clients ON Clients.ClientID = c.clientid\r\nWHERE (c.LastContact > NOW() - INTERVAL 30 MINUTE OR h.LastHeartbeatTime > NOW() - INTERVAL 30 MINUTE) AND (TIMESTAMPDIFF(MINUTE,c.LastContact, \r\nIFNULL(LastHeartbeatTime, \"0000-00-00 00:00:00\")) < -6 OR TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, \"0000-00-00 00:00:00\")) > 6)",
          "refId": "A"
        }
      ],
      "timeFrom": null,
      "title": "Missing Automate<>SC Sync",
      "transform": "table",
      "transparent": false,
      "type": "table"
    },
    {
      "columns": [],
      "datasource": "Automate",
      "description": "",
      "fontSize": "100%",
      "gridPos": {
        "h": 5,
        "w": 6,
        "x": 6,
        "y": 5
      },
      "height": "210",
      "id": 30,
      "links": [],
      "pageSize": null,
      "repeat": null,
      "scroll": true,
      "showHeader": true,
      "sort": {
        "col": 2,
        "desc": true
      },
      "styles": [
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Severity",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        }
      ],
      "targets": [
        {
          "alias": "",
          "format": "table",
          "hide": false,
          "rawSql": "SELECT Severity, \r\nSource AS VALUE ,\r\nAlertdate as 'Date'\r\nFROM `alerts`\r\nWHERE (Alerts.Severity=3  OR Alerts.Severity=2 OR Alerts.Severity=4)\r\nORDER BY Alertdate DESC LIMIT 4",
          "refId": "A"
        }
      ],
      "timeFrom": null,
      "title": "Alerts",
      "transform": "table",
      "transparent": false,
      "type": "table"
    },
    {
      "aliasColors": {},
      "bars": false,
      "dashLength": 10,
      "dashes": false,
      "datasource": "Automate",
      "fill": 1,
      "gridPos": {
        "h": 5,
        "w": 12,
        "x": 12,
        "y": 5
      },
      "hideTimeOverride": true,
      "id": 16,
      "legend": {
        "alignAsTable": false,
        "avg": true,
        "current": true,
        "max": false,
        "min": false,
        "rightSide": false,
        "show": true,
        "sort": null,
        "sortDesc": null,
        "total": false,
        "values": true
      },
      "lines": true,
      "linewidth": 2,
      "links": [],
      "nullPointMode": "null as zero",
      "percentage": false,
      "pointradius": 1,
      "points": false,
      "renderer": "flot",
      "seriesOverrides": [
        {
          "alias": "Brewdog OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "Becks OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "Lagunita OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "Duvel OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "VEEAM OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "ObligoWDS OUT",
          "transform": "negative-Y"
        },
        {
          "alias": "Corona OUT",
          "transform": "negative-Y"
        }
      ],
      "spaceLength": 10,
      "stack": false,
      "steppedLine": false,
      "targets": [
        {
          "alias": "",
          "format": "time_series",
          "rawSql": "SELECT\r\n(h_computerstatshourly.Dataout) AS value,\r\n (UNIX_TIMESTAMP(LastUpdate))  AS  time_sec ,\r\n  \r\n  name AS metric\r\nFROM h_computerstatshourly \r\nINNER JOIN computers ON h_computerstatshourly.Computerid=computers.computerid\r\nWHERE $__timeFilter(LastUpdate) AND os LIKE '%server%' \r\nGROUP BY time_sec ASC\r\n",
          "refId": "A"
        }
      ],
      "thresholds": [],
      "timeFrom": "24h",
      "timeShift": null,
      "title": "Network traffic out",
      "tooltip": {
        "shared": true,
        "sort": 1,
        "value_type": "individual"
      },
      "transparent": false,
      "type": "graph",
      "xaxis": {
        "buckets": null,
        "mode": "time",
        "name": null,
        "show": true,
        "values": []
      },
      "yaxes": [
        {
          "format": "Bps",
          "label": null,
          "logBase": 1,
          "max": null,
          "min": null,
          "show": true
        },
        {
          "format": "short",
          "label": null,
          "logBase": 1,
          "max": null,
          "min": null,
          "show": false
        }
      ],
      "yaxis": {
        "align": false,
        "alignLevel": null
      }
    },
    {
      "columns": [],
      "datasource": "Automate",
      "description": "",
      "fontSize": "100%",
      "gridPos": {
        "h": 4,
        "w": 6,
        "x": 0,
        "y": 10
      },
      "height": "210",
      "id": 39,
      "links": [],
      "pageSize": null,
      "scroll": false,
      "showHeader": true,
      "sort": {
        "col": 2,
        "desc": true
      },
      "styles": [
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "TestValue",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Client",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Location",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Reboot Pending",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Oldest Patch Missing",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "UpTimeStart",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "NoAlerts",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Uptime in Days",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        }
      ],
      "targets": [
        {
          "alias": "",
          "format": "table",
          "hide": false,
          "rawSql": "\nSELECT\n\tCOUNT(DISTINCT hfd.KBID) AS 'TestValue'\n\t,c.Name AS 'IdentityField'\n\t,hf.ComputerID AS 'ComputerID'\n\t,cl.Name AS 'Client'\n\t,l.Name AS 'Location'\n\t,CASE\n\t\tWHEN (c.flags & 1024) = 1024 THEN 'Reboot Pending'\n\t\tELSE 'No Reboot Pending'\n\t\tEND AS 'Reboot Pending'\n\t,(c.Uptime) DIV 1440 AS 'Uptime in Days'\n\t,CAST(MIN(hfd.Date_Added) AS DATE) AS 'Oldest Patch Missing'\n\t,acd.NoAlerts\n\t,acd.UpTimeStart\n\t,acd.UpTimeEnd\nFROM\n\thotfix hf\nJOIN\n\thotfixdata hfd\n\tON hf.HotFixID = hfd.HotFixID\nJOIN\n\tcomputers c\n\tON hf.ComputerID = c.ComputerID\nJOIN\n\tclients cl\n\tON cl.ClientID = c.ClientID\nJOIN\n\tlocations l\n\tON c.LocationID = l.LocationID\nJOIN\n\tAgentComputerData acd\n\tON acd.ComputerID = c.ComputerID\nWHERE\n\thfd.Date_Added < DATE_ADD(NOW(),INTERVAL -100 DAY)\n\tAND hfd.CategoryName <> 'Drivers'\n\tAND hf.Approved = 2\n\tAND hf.Installed <> 1\n\tAND c.LastContact > DATE_ADD(NOW(),INTERVAL -30 DAY)\n\tAND c.LocationID <> 1\nGROUP BY\n\tc.Name\nORDER BY\n\tcl.Name",
          "refId": "A"
        }
      ],
      "timeFrom": null,
      "title": "Missing Patches",
      "transform": "table",
      "transparent": false,
      "type": "table"
    },
    {
      "columns": [],
      "datasource": "Automate",
      "description": "",
      "fontSize": "100%",
      "gridPos": {
        "h": 4,
        "w": 6,
        "x": 6,
        "y": 10
      },
      "height": "210",
      "id": 40,
      "links": [],
      "pageSize": null,
      "scroll": false,
      "showHeader": true,
      "sort": {
        "col": 2,
        "desc": true
      },
      "styles": [
        {
          "alias": "Alert",
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "pattern": "VALUE",
          "sanitize": false,
          "type": "string"
        },
        {
          "alias": "",
          "colorMode": "cell",
          "colors": [
            "rgba(45, 45, 204, 0.9)",
            "rgba(194, 197, 33, 0.89)",
            "rgba(183, 44, 44, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "pattern": "Severity",
          "preserveFormat": true,
          "thresholds": [
            "2.00",
            "4.00"
          ],
          "type": "number",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "MMMM D, YYYY LT",
          "decimals": 2,
          "pattern": "Date",
          "thresholds": [],
          "type": "date",
          "unit": "short"
        }
      ],
      "targets": [
        {
          "alias": "",
          "format": "table",
          "hide": false,
          "rawSql": "SELECT computerid,Logname,COUNT(*) AS 'count'\n    FROM eventlogs\n    GROUP BY computerid, Logname\nORDER BY `count` DESC LIMIT 4",
          "refId": "A"
        }
      ],
      "timeFrom": null,
      "title": "Noisy Event Logs",
      "transform": "table",
      "transparent": false,
      "type": "table"
    },
    {
      "aliasColors": {},
      "bars": false,
      "dashLength": 10,
      "dashes": false,
      "datasource": "Automate",
      "decimals": null,
      "description": "",
      "fill": 0,
      "gridPos": {
        "h": 4,
        "w": 12,
        "x": 12,
        "y": 10
      },
      "hideTimeOverride": true,
      "id": 10,
      "legend": {
        "alignAsTable": false,
        "avg": true,
        "current": true,
        "hideEmpty": true,
        "hideZero": true,
        "max": false,
        "min": false,
        "rightSide": false,
        "show": true,
        "sort": "current",
        "sortDesc": true,
        "total": false,
        "values": true
      },
      "lines": true,
      "linewidth": 1,
      "links": [],
      "nullPointMode": "null as zero",
      "percentage": false,
      "pointradius": 1,
      "points": false,
      "renderer": "flot",
      "seriesOverrides": [],
      "spaceLength": 10,
      "stack": false,
      "steppedLine": false,
      "targets": [
        {
          "alias": "",
          "format": "time_series",
          "hide": false,
          "rawSql": "SELECT\r\n(cpu) AS value,\r\n (UNIX_TIMESTAMP(LastUpdate))  AS  time_sec ,\r\n  \r\n  name AS metric\r\nFROM h_computerstatshourly \r\nINNER JOIN computers ON h_computerstatshourly.Computerid=computers.computerid\r\nWHERE $__timeFilter(LastUpdate) AND os LIKE '%server%' \r\nGROUP BY time_sec ASC\r\n",
          "refId": "A"
        }
      ],
      "thresholds": [
        {
          "colorMode": "ok",
          "fill": true,
          "line": true,
          "op": "lt",
          "value": 95
        },
        {
          "colorMode": "critical",
          "fill": true,
          "line": true,
          "op": "gt",
          "value": 95
        }
      ],
      "timeFrom": "24h",
      "timeShift": null,
      "title": "CPU Load",
      "tooltip": {
        "shared": true,
        "sort": 1,
        "value_type": "individual"
      },
      "transparent": false,
      "type": "graph",
      "xaxis": {
        "buckets": null,
        "mode": "time",
        "name": null,
        "show": true,
        "values": []
      },
      "yaxes": [
        {
          "format": "percent",
          "label": "Max Load",
          "logBase": 1,
          "max": "110",
          "min": "0",
          "show": true
        },
        {
          "decimals": null,
          "format": "percent",
          "label": "",
          "logBase": 1,
          "max": "110",
          "min": "0",
          "show": true
        }
      ],
      "yaxis": {
        "align": false,
        "alignLevel": null
      }
    },
    {
      "columns": [],
      "datasource": "Automate",
      "description": "",
      "fontSize": "100%",
      "gridPos": {
        "h": 4,
        "w": 6,
        "x": 0,
        "y": 14
      },
      "height": "210",
      "id": 38,
      "links": [],
      "pageSize": null,
      "scroll": false,
      "showHeader": true,
      "sort": {
        "col": 2,
        "desc": true
      },
      "styles": [
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "locationid",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Computer Domain",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Computer User",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Volume Name",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "capacity",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "Computer.Drives.Type",
          "thresholds": [],
          "type": "string",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "GB Free",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        }
      ],
      "targets": [
        {
          "alias": "",
          "format": "table",
          "hide": false,
          "rawSql": "SELECT \r\ncomputers.locationid,\r\n   computers.computerid as 'Computer Id',\r\n   computers.name as 'Computer Name',\r\n   computers.domain as `Computer Domain`,\r\n   computers.username as `Computer User`,\r\n   drives.letter,\r\n   drives.VolumeName as 'Volume Name' ,\r\n   drives.Size/1024 as 'capacity' ,   \r\n   Drives.Free/1024 as `GB Free`,\r\n   \r\n   CASE WHEN LEFT(Drives.SmartStatus, INSTR(Drives.SmartStatus, ':')-1) IN('CD', 'DVD') THEN 3 WHEN LEFT(Drives.SmartStatus, INSTR(Drives.SmartStatus, ':')-1) = 'USB' THEN 2 WHEN LEFT(Drives.SmartStatus, INSTR(Drives.SmartStatus, ':')-1) IN('IDE', 'SCSI') THEN 1 WHEN Drives.FileSystem IN('NTFS', 'FAT32', 'FAT', 'HFS', 'ext2', 'ext3', 'ext4', 'btrfs', 'jfs', 'xfs', 'reiser4', 'reiserfs') THEN IF(INSTR(LOWER(Drives.Model), 'usb')>0, 2, 1) ELSE 4 END as `Computer.Drives.Type`\r\nFROM Computers \r\nLEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId)\r\nLEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId)\r\nLEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID)\r\nLEFT JOIN Drives ON (Drives.ComputerId=Computers.ComputerId)\r\n WHERE \r\n((((Drives.Free/1024 < 4) and (drives.Size/1024 > 10) and drives.VolumeName != 'backups' and drives.VolumeName != 'UNK' and drives.VolumeName != 'hp_recovery' and drives.VolumeName != 'recovery' And (CASE WHEN LEFT(Drives.SmartStatus, INSTR(Drives.SmartStatus, ':')-1) IN('CD', 'DVD') THEN 3 WHEN LEFT(Drives.SmartStatus, INSTR(Drives.SmartStatus, ':')-1) = 'USB' THEN 2 WHEN LEFT(Drives.SmartStatus, INSTR(Drives.SmartStatus, ':')-1) IN('IDE', 'SCSI') THEN 1 WHEN Drives.FileSystem IN('NTFS', 'FAT32', 'FAT', 'HFS', 'ext2', 'ext3', 'ext4', 'btrfs', 'jfs', 'xfs', 'reiser4', 'reiserfs') THEN IF(INSTR(LOWER(Drives.Model), 'usb')>0, 2, 1) ELSE 4 END = '1'))))\r\nORDER BY `GB Free` DESC LIMIT 5",
          "refId": "A"
        }
      ],
      "timeFrom": null,
      "title": "Low HDD",
      "transform": "table",
      "transparent": false,
      "type": "table"
    },
    {
      "columns": [],
      "datasource": "Automate",
      "description": "",
      "fontSize": "100%",
      "gridPos": {
        "h": 4,
        "w": 6,
        "x": 6,
        "y": 14
      },
      "height": "210",
      "id": 42,
      "links": [],
      "pageSize": null,
      "scroll": false,
      "showHeader": true,
      "sort": {
        "col": 2,
        "desc": true
      },
      "styles": [
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "externalID",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "StartedDate",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "monitorID",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "LocationID",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "NoAlerts",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "UpTimeEnd",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "UpTimeStart",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "status",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        },
        {
          "alias": "",
          "colorMode": null,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "dateFormat": "YYYY-MM-DD HH:mm:ss",
          "decimals": 2,
          "mappingType": 1,
          "pattern": "computerID",
          "thresholds": [],
          "type": "hidden",
          "unit": "short"
        }
      ],
      "targets": [
        {
          "alias": "",
          "format": "table",
          "hide": false,
          "rawSql": "Select tickets.ticketID as 'testValue', Computers.computerID, Computers.Name as 'IdentityField', tickets.externalID, tickets.status, tickets.StartedDate, tickets.Subject, tickets.monitorID, tickets.LocationID, agentComputerData.NoAlerts, agentComputerData.UpTimeStart, agentComputerData.UpTimeEnd from ((Tickets left join computers on tickets.computerID = computers.computerID) left join agentComputerData on computers.computerID = agentComputerData.computerID) where tickets.externalID = 0 and startedDate > date_sub(current_date(), interval 15 day);",
          "refId": "A"
        }
      ],
      "timeFrom": null,
      "title": "Ghost Tickets",
      "transform": "table",
      "transparent": false,
      "type": "table"
    },
    {
      "aliasColors": {},
      "bars": false,
      "dashLength": 10,
      "dashes": false,
      "datasource": "Automate",
      "decimals": null,
      "description": "",
      "fill": 0,
      "gridPos": {
        "h": 4,
        "w": 12,
        "x": 12,
        "y": 14
      },
      "hideTimeOverride": true,
      "id": 14,
      "legend": {
        "alignAsTable": false,
        "avg": true,
        "current": true,
        "hideEmpty": true,
        "hideZero": true,
        "max": false,
        "min": false,
        "rightSide": false,
        "show": true,
        "sort": "current",
        "sortDesc": true,
        "total": false,
        "values": true
      },
      "lines": true,
      "linewidth": 1,
      "links": [],
      "nullPointMode": "null as zero",
      "percentage": false,
      "pointradius": 1,
      "points": false,
      "renderer": "flot",
      "seriesOverrides": [
        {
          "alias": "Brewdog CPU",
          "color": "#0A437C"
        },
        {
          "alias": "Brewdog Memory",
          "color": "#EF843C"
        },
        {
          "alias": "Server CPU",
          "yaxis": 2
        },
        {
          "alias": "Server Memory",
          "yaxis": 2
        }
      ],
      "spaceLength": 10,
      "stack": false,
      "steppedLine": false,
      "targets": [
        {
          "alias": "",
          "format": "time_series",
          "rawSql": "SELECT\r\n(100-mem) AS value,\r\n (UNIX_TIMESTAMP(LastUpdate))  AS  time_sec ,\r\n  \r\n  name AS metric\r\nFROM h_computerstatshourly \r\nINNER JOIN computers ON h_computerstatshourly.Computerid=computers.computerid\r\nWHERE $__timeFilter(LastUpdate) AND os LIKE '%server%' \r\nGROUP BY time_sec ASC\r\n",
          "refId": "H"
        }
      ],
      "thresholds": [
        {
          "colorMode": "ok",
          "fill": true,
          "line": true,
          "op": "lt",
          "value": 95
        },
        {
          "colorMode": "critical",
          "fill": true,
          "line": true,
          "op": "gt",
          "value": 95
        }
      ],
      "timeFrom": "24h",
      "timeShift": null,
      "title": "Memory Load",
      "tooltip": {
        "shared": true,
        "sort": 1,
        "value_type": "individual"
      },
      "transparent": false,
      "type": "graph",
      "xaxis": {
        "buckets": null,
        "mode": "time",
        "name": null,
        "show": true,
        "values": []
      },
      "yaxes": [
        {
          "format": "percent",
          "label": "Max Load",
          "logBase": 1,
          "max": "110",
          "min": "0",
          "show": true
        },
        {
          "decimals": null,
          "format": "percent",
          "label": "",
          "logBase": 1,
          "max": "110",
          "min": "0",
          "show": true
        }
      ],
      "yaxis": {
        "align": false,
        "alignLevel": null
      }
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#508642"
      ],
      "datasource": "Automate",
      "format": "none",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 0,
        "y": 18
      },
      "id": 26,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "",
      "postfixFontSize": "80%",
      "prefix": "",
      "prefixFontSize": "50%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "Servers",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT COUNT(*) 'Servers'\r\nFROM Computers \r\nWHERE Computers.OS NOT LIKE '%server%'",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "",
      "title": "WS",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#508642"
      ],
      "datasource": "Automate",
      "format": "none",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 2,
        "y": 18
      },
      "id": 24,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "",
      "postfixFontSize": "80%",
      "prefix": "",
      "prefixFontSize": "50%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "Servers",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT COUNT(*) 'Servers'\r\nFROM Computers \r\nWHERE Computers.OS LIKE '%server%'",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "",
      "title": "SRV",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#508642"
      ],
      "datasource": "Automate",
      "format": "none",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 4,
        "y": 18
      },
      "id": 8,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "",
      "postfixFontSize": "50%",
      "prefix": "",
      "prefixFontSize": "50%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "New Computers last 30 days",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT \r\n  COUNT(computers.name) AS 'New Computers last 30 days'  \r\n   \r\nFROM Computers \r\nLEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId)\r\nLEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId)\r\nLEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID)\r\n WHERE \r\n((DATEDIFF(NOW(), Computers.DateAdded) < 30))\r\n",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "",
      "title": "New Last 30 days",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#508642"
      ],
      "datasource": "Automate",
      "decimals": null,
      "format": "none",
      "gauge": {
        "maxValue": 200,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 6,
        "y": 18
      },
      "id": 22,
      "interval": null,
      "links": [],
      "mappingType": 2,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": " ",
      "postfixFontSize": "70%",
      "prefix": "",
      "prefixFontSize": "70%",
      "rangeMaps": [],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "%Sophos Endpoint Agent%",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "rawSql": "SELECT count(*) as '%Sophos Endpoint Agent%'\r\n\r\nFROM Computers \r\nLEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId)\r\nLEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId)\r\nLEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID)\r\nLEFT JOIN Software ON (Software.ComputerId = Computers.ComputerId)\r\n WHERE \r\n((Software.Name LIKE '%Sophos Endpoint Agent%'))\r\n",
          "refId": "A"
        }
      ],
      "thresholds": "",
      "title": "Sophos",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#508642"
      ],
      "datasource": "Automate",
      "format": "none",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 8,
        "y": 18
      },
      "id": 20,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "  ",
      "postfixFontSize": "80%",
      "prefix": "",
      "prefixFontSize": "80%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "%bitdefender%",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT count(*) as '%bitdefender%'\r\n\r\nFROM Computers \r\nLEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId)\r\nLEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId)\r\nLEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID)\r\nLEFT JOIN Software ON (Software.ComputerId = Computers.ComputerId)\r\n WHERE \r\n((Software.Name LIKE '%bitdefender%'))\r\n",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "",
      "title": "Bitdefender",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#508642"
      ],
      "datasource": "Automate",
      "format": "none",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 10,
        "y": 18
      },
      "id": 32,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "  ",
      "postfixFontSize": "80%",
      "prefix": "",
      "prefixFontSize": "80%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "%bitdefender%",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT count(*) as '%bitdefender%'\r\n\r\nFROM Computers \r\nLEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId)\r\nLEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId)\r\nLEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID)\r\nLEFT JOIN Software ON (Software.ComputerId = Computers.ComputerId)\r\n WHERE \r\n((Software.Name LIKE '%ninite%'))\r\n",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "",
      "title": "Ninite",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": false,
      "colorValue": false,
      "colors": [
        "#508642",
        "#cca300",
        "#890f02"
      ],
      "datasource": "Automate",
      "format": "short",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": true,
        "thresholdLabels": false,
        "thresholdMarkers": false
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 12,
        "y": 18
      },
      "id": 2,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "",
      "postfixFontSize": "50%",
      "prefix": "",
      "prefixFontSize": "80%",
      "rangeMaps": [
        {
          "from": "0",
          "text": "N/A",
          "to": "200"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(63, 189, 31, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 193, 102)",
        "show": false
      },
      "tableColumn": "count(*)",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "rawSql": "SELECT count(*) FROM labtech.computers\r\n",
          "refId": "A"
        }
      ],
      "thresholds": "80,95",
      "title": "Deployed Agents",
      "transparent": false,
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "0"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": false,
      "colorValue": false,
      "colors": [
        "#508642",
        "rgba(237, 129, 40, 0.89)",
        "rgba(245, 54, 54, 0.9)"
      ],
      "datasource": "Automate",
      "format": "percent",
      "gauge": {
        "maxValue": 99,
        "minValue": 0,
        "show": true,
        "thresholdLabels": false,
        "thresholdMarkers": false
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 20,
        "y": 18
      },
      "id": 6,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "minSpan": null,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "",
      "postfixFontSize": "50%",
      "prefix": "",
      "prefixFontSize": "80%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "value",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT ROUND((SUM(IF(hf.approved=2 AND hf.installed=1,1,0)))/SUM(IF(hf.approved=2,1,0))*100) AS `value` \r\nFROM Hotfix AS hf JOIN Computers AS c ON hf.Computerid = c.ComputerId \r\nWHERE (hf.Last_Date >= '0001-01-01 00:00:00') ",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "100,90,80",
      "title": "Patch Deployment",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": false,
      "colorValue": false,
      "colors": [
        "#508642",
        "rgba(196, 159, 11, 0.89)",
        "rgba(245, 54, 54, 0.9)"
      ],
      "datasource": "Automate",
      "decimals": null,
      "format": "percent",
      "gauge": {
        "maxValue": 99,
        "minValue": 0,
        "show": true,
        "thresholdLabels": false,
        "thresholdMarkers": false
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 22,
        "y": 18
      },
      "id": 4,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "",
      "postfixFontSize": "50%",
      "prefix": "",
      "prefixFontSize": "80%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "value",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT ROUND(AVG(TargetWeightedScore)) AS `value` \nFROM v_xr_sap_clienthealthchecks \nWHERE clientid=(1 OR 1=0) AND (Checkname LIKE 'Drive Fragmentation' OR Checkname LIKE 'Drive Stability' OR Checkname LIKE 'Drive Performance')",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "95,80,40",
      "title": "Disk Health",
      "transparent": false,
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#508642"
      ],
      "datasource": "Automate",
      "format": "none",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 0,
        "y": 21
      },
      "id": 35,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "  ",
      "postfixFontSize": "80%",
      "prefix": "",
      "prefixFontSize": "80%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "%java%",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT count(*) as '%java%'\r\n\r\nFROM Computers \r\nLEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId)\r\nLEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId)\r\nLEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID)\r\nLEFT JOIN Software ON (Software.ComputerId = Computers.ComputerId)\r\n WHERE \r\n((Software.Name LIKE '%java%'))\r\n",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "",
      "title": "Java",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#508642"
      ],
      "datasource": "Automate",
      "format": "none",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 2,
        "y": 21
      },
      "id": 34,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "  ",
      "postfixFontSize": "80%",
      "prefix": "",
      "prefixFontSize": "80%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "%microsoft office%",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT count(*) as '%microsoft office%'\r\n\r\nFROM Computers \r\nLEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId)\r\nLEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId)\r\nLEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID)\r\nLEFT JOIN Software ON (Software.ComputerId = Computers.ComputerId)\r\n WHERE \r\n((Software.Name LIKE '%microsoft office%'))\r\n",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "",
      "title": "Microsoft Office",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#890f02"
      ],
      "datasource": "Automate",
      "format": "none",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 0,
        "y": 24
      },
      "id": 36,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "  ",
      "postfixFontSize": "80%",
      "prefix": "",
      "prefixFontSize": "80%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "%Mcafee%",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT count(*) as '%Mcafee%'\r\n\r\nFROM Computers \r\nLEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId)\r\nLEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId)\r\nLEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID)\r\nLEFT JOIN Software ON (Software.ComputerId = Computers.ComputerId)\r\n WHERE \r\n((Software.Name LIKE '%Mcafee%'))\r\n",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "0,1",
      "title": "McAfee",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    },
    {
      "cacheTimeout": null,
      "colorBackground": true,
      "colorValue": false,
      "colors": [
        "#508642",
        "#508642",
        "#890f02"
      ],
      "datasource": "Automate",
      "format": "none",
      "gauge": {
        "maxValue": 100,
        "minValue": 0,
        "show": false,
        "thresholdLabels": false,
        "thresholdMarkers": true
      },
      "gridPos": {
        "h": 3,
        "w": 2,
        "x": 2,
        "y": 24
      },
      "id": 37,
      "interval": null,
      "links": [],
      "mappingType": 1,
      "mappingTypes": [
        {
          "name": "value to text",
          "value": 1
        },
        {
          "name": "range to text",
          "value": 2
        }
      ],
      "maxDataPoints": 100,
      "nullPointMode": "connected",
      "nullText": null,
      "postfix": "  ",
      "postfixFontSize": "80%",
      "prefix": "",
      "prefixFontSize": "80%",
      "rangeMaps": [
        {
          "from": "null",
          "text": "N/A",
          "to": "null"
        }
      ],
      "sparkline": {
        "fillColor": "rgba(31, 118, 189, 0.18)",
        "full": false,
        "lineColor": "rgb(31, 120, 193)",
        "show": false
      },
      "tableColumn": "count(*)",
      "targets": [
        {
          "alias": "",
          "format": "table",
          "groupBy": [
            {
              "params": [
                "$__interval"
              ],
              "type": "time"
            },
            {
              "params": [
                "null"
              ],
              "type": "fill"
            }
          ],
          "orderByTime": "ASC",
          "policy": "default",
          "rawSql": "SELECT count(*) \r\nFROM `alerts`\r\nWHERE (Alerts.Severity=3  OR Alerts.Severity=2 OR Alerts.Severity=4 OR Alerts.Severity=1)\r\n",
          "refId": "A",
          "resultFormat": "time_series",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "field"
              },
              {
                "params": [],
                "type": "mean"
              }
            ]
          ],
          "tags": []
        }
      ],
      "thresholds": "0,1",
      "title": "Open Alerts",
      "type": "singlestat",
      "valueFontSize": "100%",
      "valueMaps": [
        {
          "op": "=",
          "text": "N/A",
          "value": "null"
        }
      ],
      "valueName": "avg"
    }
  ],
  "refresh": false,
  "schemaVersion": 16,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": []
  },
  "time": {
    "from": "now-7d",
    "to": "now"
  },
  "timepicker": {
    "hidden": false,
    "refresh_intervals": [
      "30s"
    ],
    "time_options": [
      "5m",
      "15m",
      "1h",
      "6h",
      "12h",
      "24h",
      "2d",
      "7d",
      "30d"
    ]
  },
  "timezone": "browser",
  "title": "FOP_Automate",
  "uid": "5UrMHmzgk",
  "version": 112
}

 

Share this post


Link to post
Share on other sites

Any chance you can post that as a file? Copying and pasting is giving errors, and I think its the formatting coming from the forums.

Share this post


Link to post
Share on other sites
On 8/30/2016 at 2:46 PM, Wupsje said:

Does this help?

 

 

 

 

59ec944019d4f_ticketsfinished.PNG.681d9a4d66b481db8176863696434ece.PNG

59ec94401c272_ticketsstatus.PNG.4198c7492d3c9e96d737760de97f5fd3.PNG

Hi Wupsje,

Did you know you can also use the group by function? It saves you typing a lot of queries. Example:

SELECT
  NOW() AS "time",
  computers.os as 'Operating System',
  COUNT(ComputerID) AS 'Number'
FROM computers
GROUP BY computers.os
order by (Number) desc
 

Use this as Query A, remove B,C,and the rest and you've got yourself a nice Pie

 

  • Thanks 1

Share this post


Link to post
Share on other sites

Was anyone been able to successfully connect using the hosted version? 

I get the error "connection timedout"

anything that needs to be done on the automate server?

any assistance would be appreciated.

thanks


image.thumb.png.f29ae9bfb928ec1231596afd01db6222.png

 

Share this post


Link to post
Share on other sites

Assuming you have the firewall ACLs and DNS resolution  worked out try removing the mysql port in the host name.

 

Share this post


Link to post
Share on other sites

Been messing with my ticket timeseries.
I was bothered that it didn't start at 0.

I've been able to fix that:
image.thumb.png.bf5977ac96b81d2f23d51d16f3fb2d44.png

 

if you insert these into a time series it will display your closed and new/opened tickets correctly along the timeline.
Make sure you do the call to the generate_series_date_minute_base on the first query (A).

image.thumb.png.803bd74b72108a17480d4c2e9530d0af.png

 

Share this post


Link to post
Share on other sites
15 minutes ago, Wupsje said:

Been messing with my ticket timeseries.
I was bothered that it didn't start at 0.

I've been able to fix that:
image.thumb.png.bf5977ac96b81d2f23d51d16f3fb2d44.png

 

if you insert these into a time series it will display your closed and new/opened tickets correctly along the timeline.
Make sure you do the call to the generate_series_date_minute_base on the first query (A).

image.thumb.png.803bd74b72108a17480d4c2e9530d0af.png

 

Any chance you could please post the actual code?

Share this post


Link to post
Share on other sites
11 minutes ago, Dave M said:

Any chance you could please post the actual code?

{
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": "-- Grafana --",
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "gnetId": null,
  "graphTooltip": 0,
  "hideControls": true,
  "id": 12,
  "links": [],
  "refresh": false,
  "rows": [
    {
      "collapse": false,
      "height": "25px",
      "panels": [
        {
          "cacheTimeout": null,
          "colorBackground": false,
          "colorValue": false,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "format": "none",
          "gauge": {
            "maxValue": 100,
            "minValue": 0,
            "show": false,
            "thresholdLabels": false,
            "thresholdMarkers": true
          },
          "id": 1,
          "interval": null,
          "links": [],
          "mappingType": 1,
          "mappingTypes": [
            {
              "name": "value to text",
              "value": 1
            },
            {
              "name": "range to text",
              "value": 2
            }
          ],
          "maxDataPoints": 100,
          "nullPointMode": "connected",
          "nullText": null,
          "postfix": "",
          "postfixFontSize": "50%",
          "prefix": "",
          "prefixFontSize": "50%",
          "rangeMaps": [
            {
              "from": "null",
              "text": "N/A",
              "to": "null"
            }
          ],
          "span": 4,
          "sparkline": {
            "fillColor": "rgba(31, 118, 189, 0.18)",
            "full": false,
            "lineColor": "rgb(31, 120, 193)",
            "show": false
          },
          "tableColumn": "count(*)",
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "table",
              "groupBy": [],
              "hide": false,
              "query": "SELECT count(*) FROM labtech.computers",
              "rawQuery": true,
              "rawSql": "SELECT count(*) FROM labtech.computers",
              "refId": "A",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            }
          ],
          "thresholds": "",
          "title": "Agents",
          "type": "singlestat",
          "valueFontSize": "80%",
          "valueMaps": [
            {
              "op": "=",
              "text": "N/A",
              "value": "null"
            }
          ],
          "valueName": "avg"
        },
        {
          "cacheTimeout": null,
          "colorBackground": false,
          "colorValue": false,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "format": "none",
          "gauge": {
            "maxValue": 100,
            "minValue": 0,
            "show": false,
            "thresholdLabels": false,
            "thresholdMarkers": true
          },
          "id": 9,
          "interval": null,
          "links": [],
          "mappingType": 1,
          "mappingTypes": [
            {
              "name": "value to text",
              "value": 1
            },
            {
              "name": "range to text",
              "value": 2
            }
          ],
          "maxDataPoints": 100,
          "nullPointMode": "connected",
          "nullText": null,
          "postfix": "",
          "postfixFontSize": "50%",
          "prefix": "",
          "prefixFontSize": "50%",
          "rangeMaps": [
            {
              "from": "null",
              "text": "N/A",
              "to": "null"
            }
          ],
          "span": 4,
          "sparkline": {
            "fillColor": "rgba(31, 118, 189, 0.18)",
            "full": false,
            "lineColor": "rgb(31, 120, 193)",
            "show": false
          },
          "tableColumn": "COUNT(*)",
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "table",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(*) FROM computers WHERE $timeFilter",
              "rawQuery": true,
              "rawSql": "SELECT COUNT(*) FROM computers\r\nWHERE $__timeFilter(DateAdded)",
              "refId": "A",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "Computers",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "DateAdded",
              "timeColDataType": "DateAdded : datetime",
              "timeDataType": "datetime"
            }
          ],
          "thresholds": "",
          "title": "Agents Added",
          "type": "singlestat",
          "valueFontSize": "80%",
          "valueMaps": [
            {
              "op": "=",
              "text": "N/A",
              "value": "null"
            }
          ],
          "valueName": "avg"
        },
        {
          "cacheTimeout": null,
          "colorBackground": false,
          "colorValue": false,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "format": "none",
          "gauge": {
            "maxValue": 100,
            "minValue": 0,
            "show": false,
            "thresholdLabels": false,
            "thresholdMarkers": true
          },
          "id": 2,
          "interval": null,
          "links": [],
          "mappingType": 1,
          "mappingTypes": [
            {
              "name": "value to text",
              "value": 1
            },
            {
              "name": "range to text",
              "value": 2
            }
          ],
          "maxDataPoints": 100,
          "nullPointMode": "connected",
          "nullText": null,
          "postfix": "",
          "postfixFontSize": "50%",
          "prefix": "",
          "prefixFontSize": "50%",
          "rangeMaps": [
            {
              "from": "null",
              "text": "N/A",
              "to": "null"
            }
          ],
          "span": 4,
          "sparkline": {
            "fillColor": "rgba(31, 118, 189, 0.18)",
            "full": false,
            "lineColor": "rgb(31, 120, 193)",
            "show": false
          },
          "tableColumn": "COUNT(*)",
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "table",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(*) FROM clients",
              "rawQuery": true,
              "rawSql": "SELECT COUNT(*) FROM clients",
              "refId": "A",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            }
          ],
          "thresholds": "",
          "title": "Clients",
          "type": "singlestat",
          "valueFontSize": "80%",
          "valueMaps": [
            {
              "op": "=",
              "text": "N/A",
              "value": "null"
            }
          ],
          "valueName": "avg"
        },
        {
          "cacheTimeout": null,
          "colorBackground": false,
          "colorValue": false,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "format": "none",
          "gauge": {
            "maxValue": 100,
            "minValue": 0,
            "show": false,
            "thresholdLabels": false,
            "thresholdMarkers": true
          },
          "id": 14,
          "interval": null,
          "links": [],
          "mappingType": 1,
          "mappingTypes": [
            {
              "name": "value to text",
              "value": 1
            },
            {
              "name": "range to text",
              "value": 2
            }
          ],
          "maxDataPoints": 100,
          "nullPointMode": "connected",
          "nullText": null,
          "postfix": "",
          "postfixFontSize": "50%",
          "prefix": "",
          "prefixFontSize": "50%",
          "rangeMaps": [
            {
              "from": "null",
              "text": "N/A",
              "to": "null"
            }
          ],
          "span": 4,
          "sparkline": {
            "fillColor": "rgba(31, 118, 189, 0.18)",
            "full": false,
            "lineColor": "rgb(31, 120, 193)",
            "show": false
          },
          "tableColumn": "Patch Attempts",
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "table",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(DISTINCT computerid) AS 'Patch Attempts' FROM commands WHERE command = 100 AND `status` = 3 AND output LIKE '%downloaded and installed successfully%' AND dateupdated >=CURDATE()",
              "rawQuery": true,
              "rawSql": "SELECT COUNT(DISTINCT computerid) AS 'Patch Attempts' FROM commands WHERE command = 100 AND `status` = 3 AND output LIKE '%downloaded and installed successfully%' AND dateupdated >=CURDATE()",
              "refId": "A",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "Computers",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "DateAdded",
              "timeColDataType": "DateAdded : datetime",
              "timeDataType": "datetime"
            }
          ],
          "thresholds": "",
          "title": "Succesful patches today",
          "type": "singlestat",
          "valueFontSize": "80%",
          "valueMaps": [
            {
              "op": "=",
              "text": "N/A",
              "value": "null"
            }
          ],
          "valueName": "avg"
        },
        {
          "cacheTimeout": null,
          "colorBackground": false,
          "colorValue": false,
          "colors": [
            "rgba(245, 54, 54, 0.9)",
            "rgba(237, 129, 40, 0.89)",
            "rgba(50, 172, 45, 0.97)"
          ],
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "format": "none",
          "gauge": {
            "maxValue": 100,
            "minValue": 0,
            "show": false,
            "thresholdLabels": false,
            "thresholdMarkers": true
          },
          "id": 13,
          "interval": null,
          "links": [],
          "mappingType": 1,
          "mappingTypes": [
            {
              "name": "value to text",
              "value": 1
            },
            {
              "name": "range to text",
              "value": 2
            }
          ],
          "maxDataPoints": 100,
          "nullPointMode": "connected",
          "nullText": null,
          "postfix": "",
          "postfixFontSize": "50%",
          "prefix": "",
          "prefixFontSize": "50%",
          "rangeMaps": [
            {
              "from": "null",
              "text": "N/A",
              "to": "null"
            }
          ],
          "span": 4,
          "sparkline": {
            "fillColor": "rgba(31, 118, 189, 0.18)",
            "full": false,
            "lineColor": "rgb(31, 120, 193)",
            "show": false
          },
          "tableColumn": "Patch Attempts",
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "table",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(DISTINCT computerid) AS 'Patch Attempts'  FROM commands WHERE command = 100 AND `status` = 3 AND dateupdated >=CURDATE()",
              "rawQuery": true,
              "rawSql": "SELECT COUNT(DISTINCT computerid) AS 'Patch Attempts'  FROM commands WHERE command = 100 AND `status` = 3 AND dateupdated >=CURDATE()",
              "refId": "A",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "Computers",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "DateAdded",
              "timeColDataType": "DateAdded : datetime",
              "timeDataType": "datetime"
            }
          ],
          "thresholds": "",
          "title": "Patch attempts today",
          "type": "singlestat",
          "valueFontSize": "80%",
          "valueMaps": [
            {
              "op": "=",
              "text": "N/A",
              "value": "null"
            }
          ],
          "valueName": "avg"
        }
      ],
      "repeat": null,
      "repeatIteration": null,
      "repeatRowId": null,
      "showTitle": false,
      "title": "Agents & Clients",
      "titleSize": "h6"
    },
    {
      "collapse": false,
      "height": "150px",
      "panels": [
        {
          "aliasColors": {
            "Kristian": "#967302"
          },
          "cacheTimeout": null,
          "combine": {
            "label": "Others",
            "threshold": 0
          },
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "fontSize": "60%",
          "format": "short",
          "id": 11,
          "interval": null,
          "legend": {
            "percentage": true,
            "show": true,
            "sortDesc": true,
            "values": true
          },
          "legendType": "Right side",
          "links": [],
          "maxDataPoints": 3,
          "nullPointMode": "connected",
          "pieType": "pie",
          "span": 4,
          "strokeWidth": "1",
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT count(TicketID) As 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '4' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT \ncount(TicketID) As value,\n'xxx' as metric,\nIFNULL(UNIX_TIMESTAMP(TicketDataDate), UNIX_TIMESTAMP(NOW())) AS time_sec\nFROM labtech.ticketdata \nWHERE DataType = '6' AND UserID = '4' \nAND $__timeFilter(TicketDataDate)",
              "refId": "A",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT count(TicketID) As 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '9' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT \ncount(TicketID) As value,\n'xxx' as metric,\nIFNULL(UNIX_TIMESTAMP(TicketDataDate), UNIX_TIMESTAMP(NOW())) AS time_sec\nFROM labtech.ticketdata \nWHERE DataType = '6' AND UserID = '9' \nAND $__timeFilter(TicketDataDate)",
              "refId": "B",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT count(TicketID) As 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '10' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT \ncount(TicketID) As value,\n'xxx' as metric,\nIFNULL(UNIX_TIMESTAMP(TicketDataDate), UNIX_TIMESTAMP(NOW())) AS time_sec\nFROM labtech.ticketdata \nWHERE DataType = '6' AND UserID = '24' \nAND $__timeFilter(TicketDataDate)",
              "refId": "C",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT count(TicketID) As 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '14' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT \ncount(TicketID) As value,\n'xxx' as metric,\nIFNULL(UNIX_TIMESTAMP(TicketDataDate), UNIX_TIMESTAMP(NOW())) AS time_sec\nFROM labtech.ticketdata \nWHERE DataType = '6' AND UserID = '33' \nAND $__timeFilter(TicketDataDate)\n",
              "refId": "D",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT count(TicketID) As 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '24' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT \ncount(TicketID) As value,\n'xxx' as metric,\nIFNULL(UNIX_TIMESTAMP(TicketDataDate), UNIX_TIMESTAMP(NOW())) AS time_sec\nFROM labtech.ticketdata \nWHERE DataType = '6' AND UserID = '44' \nAND $__timeFilter(TicketDataDate)\n",
              "refId": "E",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT count(TicketID) As 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '33' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT \ncount(TicketID) As value,\n'xxx' as metric,\nIFNULL(UNIX_TIMESTAMP(TicketDataDate), UNIX_TIMESTAMP(NOW())) AS time_sec\nFROM labtech.ticketdata \nWHERE DataType = '6' AND UserID = '42' \nAND $__timeFilter(TicketDataDate)",
              "refId": "F",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT count(TicketID) As 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '35' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT \ncount(TicketID) As value,\n'xxx' as metric,\nIFNULL(UNIX_TIMESTAMP(TicketDataDate), UNIX_TIMESTAMP(NOW())) AS time_sec\nFROM labtech.ticketdata \nWHERE DataType = '6' AND UserID = '21' \nAND $__timeFilter(TicketDataDate)\n",
              "refId": "G",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": true,
              "query": "SELECT count(TicketID) As 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '44' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT\n  UNIX_TIMESTAMP(<time_column>) as time_sec,\n  <value column> as value,\n  <series name column> as metric\nFROM <table name>\nWHERE $__timeFilter(time_column)\nORDER BY <time_column> ASC\n",
              "refId": "H",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": true,
              "query": "SELECT count(TicketID) as 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '43' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT\n  UNIX_TIMESTAMP(<time_column>) as time_sec,\n  <value column> as value,\n  <series name column> as metric\nFROM <table name>\nWHERE $__timeFilter(time_column)\nORDER BY <time_column> ASC\n",
              "refId": "I",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": true,
              "query": "SELECT count(TicketID) as 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '42' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT\n  UNIX_TIMESTAMP(<time_column>) as time_sec,\n  <value column> as value,\n  <series name column> as metric\nFROM <table name>\nWHERE $__timeFilter(time_column)\nORDER BY <time_column> ASC\n",
              "refId": "J",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": true,
              "query": "SELECT count(TicketID) as 'xxx' FROM labtech.ticketdata WHERE DataType = '6' AND UserID = '21' AND $timeFilter ORDER BY count(TicketID)",
              "rawQuery": true,
              "rawSql": "SELECT\n  UNIX_TIMESTAMP(<time_column>) as time_sec,\n  <value column> as value,\n  <series name column> as metric\nFROM <table name>\nWHERE $__timeFilter(time_column)\nORDER BY <time_column> ASC\n",
              "refId": "K",
              "resultFormat": "time_series",
              "schema": "labtech",
              "table": "ticketdata",
              "tags": [
                {
                  "key": "DataType",
                  "operator": "=",
                  "value": "6"
                },
                {
                  "condition": "AND",
                  "key": "UserID",
                  "operator": "=",
                  "value": "4"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "TicketID"
                    ],
                    "type": "field"
                  },
                  {
                    "params": [],
                    "type": "count"
                  }
                ]
              ],
              "timeCol": "TicketDataDate",
              "timeColDataType": "TicketDataDate : timestamp",
              "timeDataType": "timestamp"
            }
          ],
          "title": "Tickets Finished",
          "transparent": false,
          "type": "grafana-piechart-panel",
          "valueName": "current"
        },
        {
          "aliasColors": {},
          "cacheTimeout": null,
          "combine": {
            "label": "Others",
            "threshold": 0
          },
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "fontSize": "80%",
          "format": "short",
          "id": 6,
          "interval": null,
          "legend": {
            "percentage": true,
            "show": true,
            "values": true
          },
          "legendType": "Right side",
          "links": [],
          "maxDataPoints": 3,
          "nullPointMode": "connected",
          "pieType": "pie",
          "span": 4,
          "strokeWidth": "1",
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(*) as \"New\" FROM tickets WHERE STATUS = 1",
              "rawQuery": true,
              "rawSql": "SELECT \n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\n  COUNT(*) as value,\n  'New' as metric\nFROM tickets \nWHERE STATUS = 1\nAND $__timeFilter(StartedDate)",
              "refId": "A",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            },
            {
              "alias": "",
              "format": "time_series",
              "hide": false,
              "rawSql": "SELECT \n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\n  COUNT(*) as value,\n  'Open' as metric\nFROM tickets \nWHERE STATUS = 2\nAND $__timeFilter(StartedDate)",
              "refId": "B"
            },
            {
              "alias": "",
              "format": "time_series",
              "hide": false,
              "rawSql": "SELECT \n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\n  COUNT(*) as value,\n  'Stalled' as metric\nFROM tickets \nWHERE STATUS = 3\nAND $__timeFilter(StartedDate)",
              "refId": "C"
            }
          ],
          "timeShift": null,
          "title": "Tickets",
          "transparent": false,
          "type": "grafana-piechart-panel",
          "valueName": "current"
        },
        {
          "aliasColors": {
            "Geen": "#CFFAFF",
            "Prio 2 - Critical": "#BF1B00",
            "Prio 4 - Normal": "#508642",
            "Prio 5 - Low": "#967302",
            "automated": "#447EBC"
          },
          "cacheTimeout": null,
          "combine": {
            "label": "Others",
            "threshold": 0
          },
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "fontSize": "80%",
          "format": "short",
          "hideTimeOverride": false,
          "id": 10,
          "interval": null,
          "legend": {
            "percentage": true,
            "show": true,
            "sortDesc": true,
            "values": true
          },
          "legendType": "Right side",
          "links": [],
          "maxDataPoints": 3,
          "nullPointMode": "connected",
          "pieType": "pie",
          "span": 4,
          "strokeWidth": 1,
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(*) AS Geen FROM tickets WHERE STATUS IN (1,2,3) AND (Priority = 2)",
              "rawQuery": true,
              "rawSql": "SELECT \r\n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\r\n  COUNT(*) AS VALUE,\r\n  'Geen' AS metric\r\nFROM tickets \r\nWHERE STATUS IN (1,2,3) AND (Priority = 2)",
              "refId": "A",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(*) AS 'Prio 5 - Low' FROM tickets WHERE STATUS IN (1,2,3) AND (Priority = 4)",
              "rawQuery": true,
              "rawSql": "SELECT \n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\n  COUNT(*) as value,\n  'Prio 5 - Low' as metric\nFROM tickets\nWHERE STATUS IN (1,2,3) AND (Priority = 4)\nAND $__timeFilter(StartedDate)",
              "refId": "B",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "query": "SELECT COUNT(*) AS 'Prio 4 - Normal' FROM tickets WHERE STATUS IN (1,2,3) AND (Priority = 10)",
              "rawQuery": true,
              "rawSql": "SELECT \n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\n  COUNT(*) as value,\n  'Prio 4 - Normal' as metric\nFROM tickets\nWHERE STATUS IN (1,2,3) AND (Priority = 10)\nAND $__timeFilter(StartedDate)",
              "refId": "C",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(*) AS 'Prio 3 - High' FROM tickets WHERE STATUS IN (1,2,3) AND (Priority = 14)",
              "rawQuery": true,
              "rawSql": "SELECT \n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\n  COUNT(*) as value,\n  'Prio 3 - High' as metric\nFROM tickets\nWHERE STATUS IN (1,2,3) AND (Priority = 14)\nAND $__timeFilter(StartedDate)\n",
              "refId": "D",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(*) AS 'Prio 1 - Emergency' FROM tickets WHERE STATUS IN (1,2,3) AND (Priority = 19)",
              "rawQuery": true,
              "rawSql": "SELECT \n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\n  COUNT(*) as value,\n  'Prio 1 - Emergency' as metric\nFROM tickets\nWHERE STATUS IN (1,2,3) AND (Priority = 19)\nAND $__timeFilter(StartedDate)\n",
              "refId": "E",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(*) AS 'Prio 2 - Critical' FROM tickets WHERE STATUS IN (1,2,3) AND (Priority = 17)",
              "rawQuery": true,
              "rawSql": "SELECT \n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\n  COUNT(*) as value,\n  'Prio 2 - Critical' as metric\nFROM tickets\nWHERE STATUS IN (1,2,3) AND (Priority = 17)\nAND $__timeFilter(StartedDate)\n",
              "refId": "F",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            },
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "time_series",
              "groupBy": [],
              "hide": false,
              "query": "SELECT COUNT(*) AS 'automated' FROM tickets WHERE STATUS IN (1,2,3) AND (Priority = 5)",
              "rawQuery": true,
              "rawSql": "SELECT \n  IFNULL(UNIX_TIMESTAMP(StartedDate), UNIX_TIMESTAMP(NOW())) AS time_sec,\n  COUNT(*) as value,\n  'Automatec' as metric\nFROM tickets\nWHERE STATUS IN (1,2,3) AND (Priority = 5)\nAND $__timeFilter(StartedDate)",
              "refId": "G",
              "resultFormat": "time_series",
              "schema": "labtech",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            }
          ],
          "title": "Ticket Priority",
          "transparent": false,
          "type": "grafana-piechart-panel",
          "valueName": "current"
        },
        {
          "aliasColors": {},
          "bars": false,
          "dashLength": 10,
          "dashes": false,
          "datasource": "labtech",
          "decimals": 0,
          "editable": true,
          "error": false,
          "fill": 1,
          "grid": {},
          "height": "200",
          "hideTimeOverride": false,
          "id": 5,
          "interval": "60s",
          "legend": {
            "alignAsTable": false,
            "avg": false,
            "current": false,
            "hideEmpty": false,
            "hideZero": false,
            "max": false,
            "min": false,
            "rightSide": false,
            "show": true,
            "sideWidth": 25,
            "total": false,
            "values": false
          },
          "lines": true,
          "linewidth": 2,
          "links": [
            {
              "type": "dashboard"
            }
          ],
          "nullPointMode": "connected",
          "percentage": false,
          "pointradius": 5,
          "points": false,
          "renderer": "flot",
          "seriesOverrides": [
            {
              "alias": "Tickets Closed",
              "yaxis": 1
            }
          ],
          "spaceLength": 10,
          "span": 10,
          "stack": false,
          "steppedLine": false,
          "targets": [
            {
              "alias": "",
              "format": "time_series",
              "hide": true,
              "rawSql": "SELECT\r\n  UNIX_TIMESTAMP(StartedDate) AS time_sec,\r\n  COUNT(TicketID) AS value,\r\n  \"Tickets Created\" AS metric\r\nFROM labtech.tickets\r\nWHERE STATUS IN (1,2)\r\nAND $__timeFilter(StartedDate)\r\nGROUP BY UNIX_TIMESTAMP(StartedDate)\r\nORDER BY StartedDate ASC\r\n",
              "refId": "B"
            },
            {
              "alias": "",
              "format": "time_series",
              "hide": true,
              "rawSql": "SELECT\n  UNIX_TIMESTAMP(UpdateDate) AS time_sec,\n  COUNT(TicketID) AS value,\n  \"Tickets Closed\" AS metric\nFROM labtech.tickets\nWHERE STATUS = 4\nAND $__timeFilter(UpdateDate)\nGROUP BY UNIX_TIMESTAMP(UpdateDate)\nORDER BY UpdateDate ASC\n",
              "refId": "D"
            },
            {
              "alias": "",
              "format": "time_series",
              "hide": false,
              "rawSql": "CALL generate_series_date_minute_base($__timeFrom(), $__timeTo(), 5);",
              "refId": "A"
            },
            {
              "alias": "",
              "format": "time_series",
              "rawSql": "SELECT UNIX_TIMESTAMP(series_tmp.series) as time_sec, IFNULL(tmp.Cnt,0) as value, \"Tickets Created\" AS metric FROM series_tmp\nLEFT JOIN (\nSELECT COUNT(*) AS Cnt, FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(tickets.StartedDate)/300)*300) AS datum FROM labtech.tickets\nWHERE tickets.STATUS IN (1,2)\nGROUP BY 2\n) tmp ON tmp.Datum = series_tmp.series\nGROUP BY series_tmp.series;\n",
              "refId": "C"
            },
            {
              "alias": "",
              "format": "time_series",
              "rawSql": "SELECT UNIX_TIMESTAMP(series_tmp.series) as time_sec, IFNULL(tmp.Cnt,0) as value, \"Tickets Closed\" AS metric FROM series_tmp\nLEFT JOIN (\nSELECT COUNT(*) AS Cnt, FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(tickets.StartedDate)/300)*300) AS datum FROM labtech.tickets\nWHERE tickets.STATUS  = 4\nGROUP BY 2\n) tmp ON tmp.Datum = series_tmp.series\nGROUP BY series_tmp.series;\n",
              "refId": "E"
            },
            {
              "alias": "",
              "format": "time_series",
              "rawSql": "SELECT UNIX_TIMESTAMP(series_tmp.series) as time_sec, IFNULL(tmp.Cnt,0) as value, \"Tickets Combined\" AS metric FROM series_tmp\nLEFT JOIN (\nSELECT COUNT(*) AS Cnt, FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(tickets.StartedDate)/300)*300) AS datum FROM labtech.tickets\nWHERE tickets.STATUS = 6\nGROUP BY 2\n) tmp ON tmp.Datum = series_tmp.series\nGROUP BY series_tmp.series;\n",
              "refId": "F"
            }
          ],
          "thresholds": [
            {
              "colorMode": "custom",
              "fill": true,
              "fillColor": "rgb(248, 214, 110)",
              "line": true,
              "lineColor": "rgb(233, 188, 188)",
              "op": "lt",
              "value": null
            }
          ],
          "timeFrom": null,
          "timeShift": null,
          "title": "Tickets",
          "tooltip": {
            "msResolution": true,
            "shared": true,
            "sort": 0,
            "value_type": "cumulative"
          },
          "type": "graph",
          "xaxis": {
            "buckets": null,
            "mode": "time",
            "name": null,
            "show": true,
            "values": []
          },
          "yaxes": [
            {
              "format": "short",
              "label": null,
              "logBase": 1,
              "max": null,
              "min": null,
              "show": true
            },
            {
              "format": "short",
              "label": null,
              "logBase": 1,
              "max": null,
              "min": null,
              "show": false
            }
          ]
        },
        {
          "columns": [],
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "fontSize": "80%",
          "id": 12,
          "links": [],
          "pageSize": 200,
          "scroll": false,
          "showHeader": true,
          "sort": {
            "col": 1,
            "desc": true
          },
          "span": 2,
          "styles": [
            {
              "colorMode": "row",
              "colors": [
                "rgba(245, 54, 54, 0.9)",
                "rgba(237, 129, 40, 0.89)",
                "rgba(50, 172, 45, 0.97)"
              ],
              "dateFormat": "YYYY-MM-DD HH:mm:ss",
              "decimals": 1,
              "pattern": "/.*/",
              "thresholds": [
                "3",
                "6"
              ],
              "type": "number",
              "unit": "short"
            }
          ],
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "table",
              "groupBy": [],
              "hide": false,
              "query": "SELECT Users.Name AS Engineer, ((SUM(hours)*60)+SUM(mins))/60 AS Hours  FROM (timeslips LEFT JOIN timecategory ON timecategory.id=timeslips.category)   LEFT JOIN users ON users.userid=timeslips.userid    WHERE  (timeslips.Date > CURDATE()) GROUP BY timeslips.userid ORDER BY Hours DESC",
              "rawQuery": true,
              "rawSql": "SELECT Users.Name AS Engineer, ((SUM(hours)*60)+SUM(mins))/60 AS hours\nFROM (timeslips LEFT JOIN timecategory ON timecategory.id=timeslips.category)\nLEFT JOIN users ON users.userid=timeslips.userid\nWHERE  (timeslips.Date > CURDATE()) \nGROUP BY timeslips.userid \nORDER BY Hours DESC",
              "refId": "A",
              "resultFormat": "table",
              "schema": "labtech",
              "table": "timeslips",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            }
          ],
          "title": "Labtech Timeslips",
          "transform": "table",
          "type": "table"
        }
      ],
      "repeat": null,
      "repeatIteration": null,
      "repeatRowId": null,
      "showTitle": false,
      "title": "Tickets",
      "titleSize": "h6"
    },
    {
      "collapse": false,
      "height": "450px",
      "panels": [
        {
          "columns": [],
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "fontSize": "80%",
          "hideTimeOverride": true,
          "id": 7,
          "links": [],
          "pageSize": null,
          "scroll": true,
          "showHeader": true,
          "sort": {
            "col": 6,
            "desc": true
          },
          "span": 6,
          "styles": [
            {
              "colorMode": null,
              "colors": [
                "rgba(245, 54, 54, 0.9)",
                "rgba(237, 129, 40, 0.89)",
                "rgba(50, 172, 45, 0.97)"
              ],
              "dateFormat": "YYYY-MM-DD HH:mm:ss",
              "decimals": 0,
              "pattern": "TicketID",
              "sanitize": false,
              "thresholds": [],
              "type": "string",
              "unit": "short"
            },
            {
              "colorMode": "cell",
              "colors": [
                "rgba(50, 172, 45, 0.97)",
                "rgba(237, 129, 40, 0.89)",
                "rgba(245, 54, 54, 0.9)"
              ],
              "dateFormat": "YYYY-MM-DD HH:mm:ss",
              "decimals": 0,
              "pattern": "Looptijd",
              "thresholds": [
                "8",
                "16"
              ],
              "type": "number",
              "unit": "none"
            }
          ],
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "table",
              "groupBy": [],
              "hide": false,
              "query": "SELECT Tickets.TicketID , Users.Name AS Engineer, Clients.Name AS CLIENT , TicketPriority.Name AS Priority, infocategory.CategoryName AS Categorie, Tickets.Subject,  IF (elapsed_working_hours(TD1.TicketDataDate, TD3.TicketDataDate) IS NULL,  elapsed_working_hours(TD1.TicketDataDate,NOW()), elapsed_working_hours(TD1.TicketDataDate, TD3.TicketDataDate)) AS Looptijd  FROM Tickets  LEFT JOIN TicketData TD1 ON TD1.TicketDataID = (SELECT MIN(TD.TicketDataID) FROM TicketData TD WHERE (Tickets.TicketID = TD.TicketID) AND (TD.DataType = 1))   LEFT JOIN TicketData TD3 ON TD3.TicketDataID = (SELECT MAX(TD.TicketDataID) FROM TicketData TD WHERE (Tickets.TicketID = TD.TicketID) AND (TD.DataType = 6))   LEFT JOIN Users ON Tickets.userID = Users.UserID LEFT JOIN Clients ON Clients.ClientID = Tickets.ClientID  LEFT JOIN TicketPriority ON TicketPriority.Priority = Tickets.Priority LEFT JOIN infocategory ON infocategory.ID = Tickets.Category  WHERE (Tickets.Status IN (2,3)) AND (infocategory.id IN (159, 160, 165, 166)) ORDER BY Looptijd DESC LIMIT 5",
              "rawQuery": true,
              "rawSql": "SELECT Tickets.TicketID , Users.Name AS Engineer, Clients.Name AS CLIENT , TicketPriority.Name AS Priority, infocategory.CategoryName AS Categorie, Tickets.Subject,  IF (elapsed_working_hours(TD1.TicketDataDate, TD3.TicketDataDate) IS NULL,  elapsed_working_hours(TD1.TicketDataDate,NOW()), elapsed_working_hours(TD1.TicketDataDate, TD3.TicketDataDate)) AS Looptijd  FROM Tickets  LEFT JOIN TicketData TD1 ON TD1.TicketDataID = (SELECT MIN(TD.TicketDataID) FROM TicketData TD WHERE (Tickets.TicketID = TD.TicketID) AND (TD.DataType = 1))   LEFT JOIN TicketData TD3 ON TD3.TicketDataID = (SELECT MAX(TD.TicketDataID) FROM TicketData TD WHERE (Tickets.TicketID = TD.TicketID) AND (TD.DataType = 6))   LEFT JOIN Users ON Tickets.userID = Users.UserID LEFT JOIN Clients ON Clients.ClientID = Tickets.ClientID  LEFT JOIN TicketPriority ON TicketPriority.Priority = Tickets.Priority LEFT JOIN infocategory ON infocategory.ID = Tickets.Category  WHERE (Tickets.Status IN (2,3)) AND (infocategory.id IN (159, 160, 165, 166)) ORDER BY Looptijd DESC LIMIT 5",
              "refId": "A",
              "resultFormat": "table",
              "schema": "labtech",
              "table": "tickets",
              "tags": [],
              "targetLists": [
                [
                  {
                    "params": [
                      "*"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "time",
              "timeColDataType": "time : type",
              "timeDataType": "type"
            }
          ],
          "title": "Lopende support tickets",
          "transform": "table",
          "transparent": true,
          "type": "table"
        },
        {
          "columns": [],
          "datasource": "labtech",
          "editable": true,
          "error": false,
          "fontSize": "100%",
          "id": 8,
          "links": [],
          "pageSize": null,
          "scroll": true,
          "showHeader": true,
          "sort": {
            "col": 3,
            "desc": false
          },
          "span": 6,
          "styles": [
            {
              "colorMode": null,
              "colors": [
                "rgba(245, 54, 54, 0.9)",
                "rgba(237, 129, 40, 0.89)",
                "rgba(50, 172, 45, 0.97)"
              ],
              "dateFormat": "YYYY-MM-DD HH:mm:ss",
              "decimals": 2,
              "pattern": "computerid",
              "sanitize": false,
              "thresholds": [],
              "type": "string",
              "unit": "short"
            },
            {
              "colorMode": null,
              "colors": [
                "rgba(245, 54, 54, 0.9)",
                "rgba(237, 129, 40, 0.89)",
                "rgba(50, 172, 45, 0.97)"
              ],
              "dateFormat": "YYYY-MM-DD HH:mm:ss",
              "decimals": 2,
              "pattern": "Last contact",
              "thresholds": [],
              "type": "date",
              "unit": "short"
            }
          ],
          "targets": [
            {
              "alias": "",
              "dsType": "sqldb",
              "format": "table",
              "groupBy": [],
              "hide": false,
              "query": "SELECT computers.computerid,computers.Name AS ComputerName,CONVERT(CONCAT(clients.name,' - ',locations.name) USING utf8) AS Location, computers.`LastContact` AS 'Last contact' FROM (computers LEFT JOIN  Locations ON Locations.LocationID=computers.Locationid) LEFT JOIN Clients ON Clients.ClientID=Computers.clientid  JOIN AgentComputerData ON Computers.ComputerID=AgentComputerData.ComputerID WHERE computers.`LastContact` < DATE_ADD(NOW(),INTERVAL -7 MINUTE) AND  ((Computers.OS LIKE '%server%'  OR Computers.OS LIKE '%linux%' )) ORDER BY LastContact DESC",
              "rawQuery": true,
              "rawSql": "SELECT computers.computerid,computers.Name AS ComputerName,CONVERT(CONCAT(clients.name,' - ',locations.name) USING utf8) AS Location, computers.`LastContact` AS 'Last contact' FROM (computers LEFT JOIN  Locations ON Locations.LocationID=computers.Locationid) LEFT JOIN Clients ON Clients.ClientID=Computers.clientid  JOIN AgentComputerData ON Computers.ComputerID=AgentComputerData.ComputerID WHERE computers.`LastContact` < DATE_ADD(NOW(),INTERVAL -7 MINUTE) AND  ((Computers.OS LIKE '%server%'  OR Computers.OS LIKE '%linux%' )) ORDER BY LastContact DESC",
              "refId": "A",
              "resultFormat": "table",
              "schema": "labtech",
              "table": "computers",
              "tags": [
                {
                  "key": "LastContact",
                  "operator": "<",
                  "value": "date_add(now(),interval -7 minute)"
                },
                {
                  "condition": "AND",
                  "key": "OS",
                  "operator": "=",
                  "value": "%server%"
                }
              ],
              "targetLists": [
                [
                  {
                    "params": [
                      "Name"
                    ],
                    "type": "field"
                  }
                ]
              ],
              "timeCol": "LastContact",
              "timeColDataType": "LastContact : datetime",
              "timeDataType": "datetime"
            }
          ],
          "title": "Offline servers",
          "transform": "table",
          "transparent": true,
          "type": "table"
        }
      ],
      "repeat": null,
      "repeatIteration": null,
      "repeatRowId": null,
      "showTitle": false,
      "title": "Lopende Tickets + Offline Server",
      "titleSize": "h6"
    },
    {
      "collapse": false,
      "height": "250px",
      "panels": [],
      "repeat": null,
      "repeatIteration": null,
      "repeatRowId": null,
      "showTitle": false,
      "title": "New row",
      "titleSize": "h6"
    }
  ],
  "schemaVersion": 14,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": []
  },
  "time": {
    "from": "now/d",
    "to": "now"
  },
  "timepicker": {
    "refresh_intervals": [
      "5m",
      "15m",
      "30m",
      "1h"
    ],
    "time_options": [
      "5m",
      "15m",
      "1h",
      "6h",
      "12h",
      "24h",
      "2d",
      "7d",
      "30d"
    ]
  },
  "timezone": "browser",
  "title": "Labtech",
  "version": 21
}

 

  • Thanks 2

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...