Jump to content

Dave M

Members
  • Content Count

    21
  • Joined

  • Last visited

Community Reputation

1 Neutral

My Information

  • Agent Count
    < 500 Agents

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. Dave M

    Grafana and Labtech

    Any chance you could please post the actual code?
  2. Easiest import I have ever done & worked like a charm. TY!
  3. Dave M

    Computer Warranty Updater (HP/Dell/Lenovo)

    This would not run for me, erroring out that null was not valid. The following did the job: UPDATE labtech.computers SET warrantyend='0000-00-00 00:00:00' WHERE biosmfg LIKE '%dell%';
  4. Dave M

    Grafana and Labtech

    https://pastebin.com/xRpAQMQi
  5. Dave M

    Grafana and Labtech

    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 }
  6. Dave M

    Grafana and Labtech

    I have some, but some of yours are much fancier. Would appreciate if you could share the json.:)
  7. Dave M

    Grafana and Labtech

    @jasperlax4, now that you've shown it, ya gotta share!
  8. Dave M

    Computer Warranty Updater (HP/Dell/Lenovo)

    It works! THANK YOU For anyone else trying this, You need to put in companyid+publickey:privatekey at https://www.base64encode.org/, then paste the full output in the parameter.
  9. Dave M

    Computer Warranty Updater (HP/Dell/Lenovo)

    @Joe.McCall I have registered and logged into https://developer.connectwise.com. Could you please point me in the right direction and let me know where I can find the API key, and how to implement this in Automate? I keep getting: Something went wrong. The remote server returned an error: (400) Bad Request. Much appreciated & TY Start SSI\Testing\CWA - SQL Function - Update WarrantyExpiration in Manage IF True Time Taken: 1.1406072 L1 Execute Script Time Taken: 1.1406072 L3 Script Log Message Message (P1): Woooo - Something went wrong. The remote server returned an error: (400) Bad Request. Time Taken: 15.1879583 Start CWA - SQL Function - Update WarrantyExpiration in Manage IF True Time Taken: 1.1406072 L1 Execute Script Time Taken: 1.1406072 L3 Script Log Message Message (P1): Something went wrong. The remote server returned an error: (400) Bad Request. Time Taken: 15.1879583
  10. Dave M

    Email a Uploaded File

    I think that would be problematic because I believe there is a link to it in the database.
  11. Dave M

    Email a Uploaded File

    @troms I tried getting the same exact thing to work before and gave up. Needless to say at least yours is getting some traction and hopefully a working script!
  12. @mike_judd, Thank you but as you mentioned, this is not exactly it. I'd want an alert/ticket because if the agent is there, odds are other things it installs (Ninite, AV, ETC) are back.
  13. Is it possible to setup an alert or ticket, based on a list of blacklisted MAC addresses? The idea is if a system is retired and deleted, and comes back, you get alerted about it rather than it just sitting there.
  14. Dave M

    Computer Warranty Updater (HP/Dell/Lenovo)

    @DarrenDK That you for the update! The Ps script now runs fine and produces results in ISE. However, I don't see anything updates in Manage. I have double checked to make sure my mapping is OK, manually ran the script against a machine, and forced an asset sync. Where else can I look to troubleshoot? I was able to find this in the executed script log, although it runs fine in ISE: # Warranty Lookup Result Trimming SerialNumbers Updating Dell Warranties No Dell computers missing warranty Updating Lenovo Warranties Submitting computers to Lenovo API: 1037921101087 New-Object : Cannot find type [â?TypeName PSObject â?Prop]: verify that the assembly containing this type is loaded. At C:\Windows\Temp\9cffbb28-1850-11e9-bdd6-00155d006e00.ps1:229 char:21 + ... $WarrantyInfo = New-Object â?"TypeName PSObject â?"Prop $properties + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentExcepti on + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewOb jectCommand 1/14/2019 6:03:46 PM ComputerID Hidden_ComputerID
×