Jump to content
ShawnSack

Grafana and Labtech

Recommended Posts

6pyov9.jpg

Hi all, first write up so bear with me.

Dashing was giving me a lot of issues with ruby dependencies and I eventually gave up.

In comes Grafana. Grafana is a web-based, time-series visualization tool generally used by cloud services providers and other industries where decisions are influenced by data.

I recently found a RDBMS plugin for grafana that allows us to connect to labtech and queries data. There are a few gotchas but I found that it's a little easier to setup and manage than Dashing was.

One of the big catches to this is that Grafana needs time data, so feeding it a COUNT() query is not enough(except for singlestat, looks like that was fixed). The way around this though is to feed it 0 or NOW() as the first field.

 

This one is more for testing only until they merge the changes. Once that happens I will rewrite this guide for a more prod ready environment.

I am not responsible if something get borked up somewhere, standard disclaimer.

 

Install:

(I will edit this when they merge the changes to master branch)

This will need to run on a linux machine until the changes are merged to master, you can spin up a ubuntu or centos server for this but I am going to focus on setting up with Ubuntu 16.04 LTS.

 

Spin up you linux distro and make sure openssh, wget, git is installed.

sudo apt update && sudo apt install openssh-server wget git

Connect to your server with SSH and login

Download the prebuilt binary (credit to sraoss on github for creating this plugin.)

wget https://github.com/sraoss/grafana-sqldb-datasource/releases/download/V0.1/grafana-sqldb_20160615.tar.gz -O /tmp/grafana.tar.gz

And extract to /opt

sudo tar xzf /tmp/grafana.tar.gz -C /opt

Lets also open our port in firewall

sudo iptables -A INPUT -p tcp --dport 3000 -j ACCEPT

Update plugin to newest version

cd /opt/grafana-sqldb/data/plugins/grafana-sqldb-datasource
sudo rm dist/plugin.json && sudo git pull

And start the server

cd /opt/grafana-sqldb
sudo ./bin/grafana-server web

 

You should now be able to browse to http://yourip:3000 and login with default admin/admin login.

Now we set up the data source.

After logging in, open the dropdown in the top left corner and click Datasources and click Add Data Source.

Title your datasource as Labtech, select SQLDB as the Type and fill out login data for your mySQL server. I strongly recommend creating a user that can only perform SELECT queries.

v5jrll.jpg

Save & Test should succeed if everything is entered correctly.

 

Create your dashboard. Most of this is up to your needs and imagination, but I'll get you started.

At the top left dropdown select Dashboards>New.

Click the green tab on the left and select Add Panel>Singlestat

In the General Tab, name the panel Agent Count.

In the Metrics tab, delete the fake datasource metric and add a Labtech panel data source.

Under GROUP BY, remove the time($interval) field. This is only needed if your data is time series.

On the right of the metric click the 3 bars button(I have no idea if this has a technical term) and toggle edit mode. Enter:

SELECT count(*) FROM labtech.computers

If you click in some empty space, it should update to show you your current agent count.

 

And that's the basic gist of it. The are some nuances here and there to find. I find the query builder requires a time field, but you can bypass it on some panels by switching edit mode and typing the query. You can also configure the thresholds and gauges and such. Have fun with it!

Remember, not quite prime time ready but give it a few weeks and we should be there.

Edited by Guest

Share this post


Link to post
Share on other sites

This is awesome, I can see the management of this being much easier than dashing.

 

Everything went well for me up until entering the query, I'm getting a N/A. Database connection is successful. Am I missing something?

 

veCWxHN.png

Share this post


Link to post
Share on other sites

Looks like I forgot to add step. Switch back to query builder and under GROUP BY, remove the time(interval) field. You only need that if you are attaching a time field to your metric. I have one that shows me computers added in last $interval.

Share this post


Link to post
Share on other sites

I spent yesterday afternoon setting up Grafana on CentOS and connecting it to LabTech. This thing rocks!

 

With SQL queries in hand, it was very easy to add Single Text numbers such as Agent Count.

 

It was also very easy to feed it a list of info and have it format it into a very nice looking Table.

 

And to think, I was about to throw down money on BrightGauge. :P

Share this post


Link to post
Share on other sites

I was checking out the pull request on github the other day. It may be a little while before they make the merge.

I'm really hoping to see what panels you all come up with. I was trying some ideas a few weeks ago, but I'm having a tough time coming up with any practical metrics that can be used by our techs for decision making. Makes for good Executive Summaries though.

 

Here are some panels I think are useful: (You will need to edit the data source to match yours)

To use these, just create a new random panel. Exit editor and select the panel and select "Panel JSON" and paste the code in.

Script History:

{
 "aliasColors": {},
 "bars": false,
 "datasource": "Labtech",
 "editable": true,
 "error": false,
 "fill": 1,
 "grid": {
   "threshold1": null,
   "threshold1Color": "rgba(216, 200, 27, 0.27)",
   "threshold2": null,
   "threshold2Color": "rgba(234, 112, 112, 0.22)",
   "thresholdLine": false
 },
 "id": 6,
 "interval": ">60s",
 "isNew": true,
 "legend": {
   "avg": true,
   "current": false,
   "max": true,
   "min": true,
   "show": true,
   "total": false,
   "values": true
 },
 "lines": true,
 "linewidth": 1,
 "links": [],
 "nullPointMode": "connected",
 "percentage": false,
 "pointradius": 5,
 "points": false,
 "renderer": "flot",
 "seriesOverrides": [],
 "span": 6,
 "stack": false,
 "steppedLine": true,
 "targets": [
   {
     "alias": "$t.$col",
     "dsType": "sqldb",
     "groupBy": [
       {
         "params": [
           "auto"
         ],
         "type": "time"
       }
     ],
     "refId": "A",
     "resultFormat": "time_series",
     "schema": "labtech",
     "table": "h_scripts",
     "tags": [],
     "targetLists": [
       [
         {
           "params": [
             "*"
           ],
           "type": "field"
         },
         {
           "params": [],
           "type": "count"
         }
       ]
     ],
     "timeCol": "HistoryDate",
     "timeColDataType": "HistoryDate : timestamp",
     "timeDataType": "timestamp"
   }
 ],
 "timeFrom": null,
 "timeShift": null,
 "title": "Script History",
 "tooltip": {
   "msResolution": true,
   "ordering": "alphabetical",
   "shared": true,
   "value_type": "cumulative"
 },
 "type": "graph",
 "xaxis": {
   "show": true
 },
 "yaxes": [
   {
     "format": "short",
     "label": "Script Count",
     "logBase": 1,
     "max": null,
     "min": null,
     "show": true
   },
   {
     "format": "short",
     "label": null,
     "logBase": 1,
     "max": null,
     "min": null,
     "show": true
   }
 ]
}

 

Script Schedules (shows concentration of scheduled scripts):

{
 "aliasColors": {},
 "bars": true,
 "datasource": "Labtech",
 "editable": true,
 "error": false,
 "fill": 1,
 "grid": {
   "threshold1": null,
   "threshold1Color": "rgba(216, 200, 27, 0.27)",
   "threshold2": null,
   "threshold2Color": "rgba(234, 112, 112, 0.22)"
 },
 "id": 1,
 "interval": ">60s",
 "isNew": true,
 "legend": {
   "alignAsTable": false,
   "avg": false,
   "current": false,
   "max": false,
   "min": false,
   "show": true,
   "total": false,
   "values": false
 },
 "lines": false,
 "linewidth": 2,
 "links": [],
 "nullPointMode": "connected",
 "percentage": false,
 "pointradius": 5,
 "points": false,
 "renderer": "flot",
 "seriesOverrides": [],
 "span": 6,
 "stack": true,
 "steppedLine": false,
 "targets": [
   {
     "alias": "$col",
     "dsType": "sqldb",
     "groupBy": [
       {
         "params": [
           "5m"
         ],
         "type": "time"
       }
     ],
     "query": "SELECT $unixtimeColumn * 1000 AS time_msec, count(*) FROM labtech.scheduledscripts WHERE $timeFilter GROUP BY $unixtimeColumn ORDER BY $unixtimeColumn",
     "rawQuery": false,
     "refId": "A",
     "resultFormat": "time_series",
     "schema": "labtech",
     "table": "scheduledscripts",
     "tags": [],
     "targetLists": [
       [
         {
           "params": [
             "NextSchedule"
           ],
           "type": "field"
         },
         {
           "params": [],
           "type": "count"
         },
         {
           "params": [
             "Scheduled"
           ],
           "type": "alias"
         }
       ]
     ],
     "timeCol": "NextSchedule",
     "timeColDataType": "NextSchedule : datetime",
     "timeDataType": "datetime"
   },
   {
     "alias": "$col",
     "dsType": "sqldb",
     "groupBy": [
       {
         "params": [
           "5m"
         ],
         "type": "time"
       }
     ],
     "refId": "B",
     "resultFormat": "time_series",
     "schema": "labtech",
     "table": "scheduledscripts",
     "tags": [],
     "targetLists": [
       [
         {
           "params": [
             "*"
           ],
           "type": "field"
         },
         {
           "params": [],
           "type": "count"
         },
         {
           "params": [
             "Last Executed"
           ],
           "type": "alias"
         }
       ]
     ],
     "timeCol": "LastSchedule",
     "timeColDataType": "LastSchedule : datetime",
     "timeDataType": "datetime"
   }
 ],
 "timeFrom": null,
 "timeShift": null,
 "title": "Schedules Scripts",
 "tooltip": {
   "msResolution": true,
   "ordering": "alphabetical",
   "shared": true,
   "value_type": "cumulative"
 },
 "type": "graph",
 "xaxis": {
   "show": true
 },
 "yaxes": [
   {
     "format": "short",
     "label": "Scripts",
     "logBase": 1,
     "max": null,
     "min": null,
     "show": true
   },
   {
     "format": "short",
     "label": null,
     "logBase": 1,
     "max": null,
     "min": null,
     "show": true
   }
 ]
} 

 

Scripts Pending:

{
 "cacheTimeout": null,
 "colorBackground": false,
 "colorValue": true,
 "colors": [
   "rgba(50, 172, 45, 0.97)",
   "rgba(237, 129, 40, 0.89)",
   "rgba(245, 54, 54, 0.9)"
 ],
 "datasource": "Labtech",
 "editable": true,
 "error": false,
 "format": "none",
 "gauge": {
   "maxValue": 100,
   "minValue": 0,
   "show": false,
   "thresholdLabels": false,
   "thresholdMarkers": true
 },
 "hideTimeOverride": true,
 "id": 4,
 "interval": null,
 "isNew": true,
 "links": [],
 "mappingType": 1,
 "mappingTypes": [
   {
     "name": "value to text",
     "value": 1
   },
   {
     "name": "range to text",
     "value": 2
   }
 ],
 "maxDataPoints": 100,
 "minSpan": 2,
 "nullPointMode": "connected",
 "nullText": null,
 "postfix": "",
 "postfixFontSize": "50%",
 "prefix": "",
 "prefixFontSize": "50%",
 "rangeMaps": [
   {
     "from": "null",
     "text": "N/A",
     "to": "null"
   }
 ],
 "span": 2,
 "sparkline": {
   "fillColor": "rgba(31, 118, 189, 0.18)",
   "full": false,
   "lineColor": "rgb(31, 120, 193)",
   "show": false
 },
 "targets": [
   {
     "alias": "$t.$col",
     "dsType": "sqldb",
     "groupBy": [],
     "query": "SELECT $unixtimeColumn * 1000 AS time_msec, count(*) FROM labtech.runningscripts WHERE Step > 0 AND $timeFilter ORDER BY $unixtimeColumn",
     "rawQuery": false,
     "refId": "A",
     "resultFormat": "time_series",
     "schema": "labtech",
     "table": "runningscripts",
     "tags": [
       {
         "key": "Step",
         "operator": "=",
         "value": "0"
       },
       {
         "condition": "AND",
         "key": "Start",
         "operator": "<",
         "value": "DATE_SUB(NOW(), INTERVAL 30 MINUTE)"
       }
     ],
     "targetLists": [
       [
         {
           "params": [
             "*"
           ],
           "type": "field"
         },
         {
           "params": [],
           "type": "count"
         }
       ]
     ],
     "timeCol": "Start",
     "timeColDataType": "Start : timestamp",
     "timeDataType": "timestamp"
   }
 ],
 "thresholds": "50,80",
 "timeFrom": "3y",
 "title": "Scripts Pending",
 "type": "singlestat",
 "valueFontSize": "80%",
 "valueMaps": [
   {
     "op": "=",
     "text": "N/A",
     "value": "null"
   }
 ],
 "valueName": "avg"
}

 

Machines with Malware-bytes installed(stuff like this is good for quickly seeing license utilization):

{
 "cacheTimeout": null,
 "colorBackground": false,
 "colorValue": true,
 "colors": [
   "rgba(50, 172, 45, 0.97)",
   "rgba(237, 129, 40, 0.89)",
   "rgba(245, 54, 54, 0.9)"
 ],
 "datasource": "Labtech",
 "editable": true,
 "error": false,
 "format": "none",
 "gauge": {
   "maxValue": 100,
   "minValue": 0,
   "show": false,
   "thresholdLabels": false,
   "thresholdMarkers": true
 },
 "id": 3,
 "interval": null,
 "isNew": true,
 "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": 2,
 "sparkline": {
   "fillColor": "rgba(31, 118, 189, 0.18)",
   "full": false,
   "lineColor": "rgb(31, 120, 193)",
   "show": false
 },
 "targets": [
   {
     "alias": "$t.$col",
     "dsType": "sqldb",
     "groupBy": [
       {
         "params": [
           "$interval"
         ],
         "type": "time"
       }
     ],
     "query": "SELECT 0, count(*) FROM labtech.v_software WHERE SoftwareName LIKE '%Malwarebytes Anti-Malware version 1.80.0.1010%'",
     "rawQuery": true,
     "refId": "A",
     "resultFormat": "time_series",
     "schema": "labtech",
     "table": "v_software",
     "tags": [
       {
         "key": "SoftwareName",
         "operator": "=",
         "value": "%AVG%"
       }
     ],
     "targetLists": [
       [
         {
           "params": [
             "*"
           ],
           "type": "field"
         },
         {
           "params": [],
           "type": "count"
         }
       ]
     ],
     "timeCol": "DateInstalled",
     "timeColDataType": "DateInstalled : varchar",
     "timeDataType": "varchar"
   }
 ],
 "thresholds": "720,760",
 "timeFrom": null,
 "title": "Malwarebytes Installed",
 "type": "singlestat",
 "valueFontSize": "80%",
 "valueMaps": [
   {
     "op": "=",
     "text": "N/A",
     "value": "null"
   }
 ],
 "valueName": "avg"
}

Share this post


Link to post
Share on other sites

I started to build a dashboard also.

 

I'm also interested in what other people are coming up with to display.

 

I've included a screenshot of our current dashboard.

Mainly ticketing statistics, since we're using that.

 

grfana.png.6201c06bf75b7650fbc69afab2d4b35c.png

Share this post


Link to post
Share on other sites

I'd love to see the tickets SQL. I'm by far not an expert in SQL, so how you get the proper names for status would teach me a lot. I'm trying, but my results are less than stellar.

Share this post


Link to post
Share on other sites

Oh, you were manually doing it. I guess that is the easy way. I was trying to be complex and create a join, which I have no idea how to do. Oh yeah, FYI, you can pull the "code" for the panels by clicking on their name and selecting the hamburger menu, then "Panel JSON", but I got what I needed, thanks. Also, I guess on the two that you scrubbed info on it might be harder to share those directly.

Share this post


Link to post
Share on other sites

If you click on the "cog" at the top and then select "Export". This will download a ".json" file that you can share with others that contains all the settings for the dashboard.

 

This will help greatly in sharing and improving dashboards!

Share this post


Link to post
Share on other sites

Great tips, thanks! One of the things that prevented me from trying Grafana in the past is that I was certain it didn't support SQL sources.

 

For those who would like to try this quickly on Windows, here's an alternative

 

1. Download Docker for Windows (newer versions require Hyper-V to be enabled on host)

2. Run this command is PowerShell : docker run -d -p 3000:3000 --name grafana grafana/grafana

3. Login to http://localhost:3000

 

Docker won't "save" your changes when the image terminates, but its a good way to play around with Grafana.

 

*** Turns out the docker image doesn't include the pre-built SQL support, so you might not get very far with that option :(

Share this post


Link to post
Share on other sites

Where do you set the time (in seconds) to refresh the data?

I've created as test 3 SingleStat panels (Agent Count - Alerts Count - 1mo Reboot) ... they all give me a number, but I don't know when this is updated.

Can anyone point me in the right direction?

Has anyone have some beginners documentation for me. I've already searched the net, but didn't find descent info ... some examples or tutorials ...

Many thanks

Cheers

Kurt

Share this post


Link to post
Share on other sites

Also anyone can help to create the following:

I need a panel with the last alerts + tickets created as text. So a list of the last alerts and tickets.

 

The code I'm using is correct and gives me the correct information:

SELECT Source AS VALUE FROM Alerts WHERE (Alerts.Severity=4) ORDER BY Alertdate DESC LIMIT 30

 

When I create SingleStat panel I only see 1 line.

 

When I create table it show me the info, but in 2 columns and I just wanted to delete I column and change column title, but I can't delete, change column.

 

Anyone has an idea?

 

Cheers

Kurt

Share this post


Link to post
Share on other sites

Hello,

Does anyone know how to autorefresh the dashboard? It doesn't seem to do that atomatically.

I've found something under Settings --> TIme Picker, but that's not it.

Anyone?

Thnx

K

Share this post


Link to post
Share on other sites

Click left next to the refresh button and you will get a whole lot of options on the timeframe. Including the auto-refresh

Share this post


Link to post
Share on other sites

Awesome Wupsje,

Ik didn't find it ... now thnx to you I did :-)

Great product this Grafana ... not a lot of support / forums to be found unless you know one?

Cheers

Kurt

Share this post


Link to post
Share on other sites

Hi ...

Can anyone help me with the build of graphic dashboard.

For example I want a graphic on my dashboard where I can see alerts / warnings / informational stacked up to each other.

I make the query like:

SELECT count(*) FROM labtech.alerts WHERE Severity = '1' --> informational
SELECT count(*) FROM labtech.alerts WHERE Severity = '2' --> warnings
SELECT count(*) FROM labtech.alerts WHERE Severity = '4' --> alerts

 

I each get a number, but it doesn't fill up the graph over time.

 

Has not recording it or saving the current and previous information to build of the graph.

 

Anyone has an idea?

 

Many thanks

K

Share this post


Link to post
Share on other sites
I started to build a dashboard also.

 

I'm also interested in what other people are coming up with to display.

 

I've included a screenshot of our current dashboard.

Mainly ticketing statistics, since we're using that.

 

grfana.png

 

Hi Wupsje,

How did you create that ticket graph?

(ik vermoed dat je nl bent dus in nl mag :-))

Thnx

K

Share this post


Link to post
Share on other sites

Here's the JSON for the ticket panel. Hopefully that will help you. I was unable to figure out how to have it start at 0.

 

{

"aliasColors": {},

"bars": false,

"datasource": "labtech",

"decimals": 0,

"editable": true,

"error": false,

"fill": 1,

"grid": {

"threshold1": null,

"threshold1Color": "rgba(216, 200, 27, 0.27)",

"threshold2": null,

"threshold2Color": "rgba(234, 112, 112, 0.22)",

"thresholdLine": false

},

"height": "200",

"hideTimeOverride": false,

"id": 5,

"interval": "60s",

"isNew": true,

"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

}

],

"span": 10,

"stack": false,

"steppedLine": false,

"targets": [

{

"alias": "$col",

"dsType": "sqldb",

"groupBy": [

{

"params": [

"auto"

],

"type": "time"

}

],

"hide": false,

"query": "SELECT $unixtimeColumn * 1000 AS time_msec, count(TicketID) AS \"Tickets\" FROM labtech.tickets WHERE $timeFilter GROUP BY $unixtimeColumn * 1000 ORDER BY $unixtimeColumn * 1000",

"rawQuery": false,

"refId": "C",

"resultFormat": "time_series",

"schema": "labtech",

"table": "tickets",

"tags": [],

"targetLists": [

[

{

"params": [

"TicketID"

],

"type": "field"

},

{

"params": [],

"type": "count"

},

{

"params": [

"Tickets"

],

"type": "alias"

}

]

],

"timeCol": "StartedDate",

"timeColDataType": "StartedDate : datetime",

"timeDataType": "datetime"

},

{

"alias": "$col",

"dsType": "sqldb",

"groupBy": [

{

"params": [

"auto"

],

"type": "time"

}

],

"hide": false,

"query": "SELECT $unixtimeColumn * 1000 AS time_msec, count(TicketID) AS \"Tickets\" FROM labtech.tickets WHERE $timeFilter GROUP BY $unixtimeColumn * 1000 ORDER BY $unixtimeColumn * 1000",

"rawQuery": false,

"refId": "A",

"resultFormat": "time_series",

"schema": "labtech",

"table": "tickets",

"tags": [

{

"key": "Status",

"operator": "=",

"value": "4"

}

],

"targetLists": [

[

{

"params": [

"TicketID"

],

"type": "field"

},

{

"params": [],

"type": "count"

},

{

"params": [

"Tickets Closed"

],

"type": "alias"

}

]

],

"timeCol": "UpdateDate",

"timeColDataType": "UpdateDate : datetime",

"timeDataType": "datetime"

}

],

"timeFrom": null,

"timeShift": null,

"title": "Tickets",

"tooltip": {

"msResolution": true,

"shared": true,

"sort": 0,

"value_type": "cumulative"

},

"type": "graph",

"xaxis": {

"show": true

},

"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

}

]

}

Share this post


Link to post
Share on other sites

Hi Wupsje ...

Can you create some also like for Alerts?

3 metrics for Alerts / Warnings / Informational

(severity = 4 / 2 /1)

Tried it with example you send with tickets, but I can't get it to work.

Many thanks :-)

Cheers

Kurt

Share this post


Link to post
Share on other sites

Alerts are different.

they only have a creationdate. They can't get closed.

and it seems that table (alerts) doesn't have any history to it.

Share this post


Link to post
Share on other sites

ah ok ... thnx ... What are the requirements for creating a graphic?

How can I install the Piechart plugin?

 

Then I have another question (using table h_clientstatsdaily / weekly / monthly)

I was trying to create a graph for example h_clientstatsdaily --> diplaying per day Y the amount of TotalTime X minutes worked (we bill per 15min).

Then the same for per week and month

Could you help me with that?

Cheers

Kurt

Share this post


Link to post
Share on other sites

Importing a JSON from this forum page (for which, thank you!) doesn't seem to bring the queries into the metrics on 4.3.2 :\

 

I'm also trying to figure out how to make the pie charts, graphs, etc. from scratch

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...