Jump to content
ShawnSack

Grafana and Labtech

Recommended Posts

Hi Fitzz,

Great Dashboard.

I'm looking for similar dashboard for RAM and CPU.

Can you send me the Metrics info how you writen your syntac of it, plz?

Many htanks

Kurt

Share this post


Link to post
Share on other sites

Hi Kurt!

No problem, here are the queries I've got for those two dashboards.

 

CPU:

SELECT 
  pcstats.computerid,
  computers.name,
  pcstats.cpu,
  pcstats.mem,
  pcstats.datain,
  pcstats.dataout,
  IF(INSTR(computers.os, 'server')>0, 1, 0) as `Computer.OS.IsServer`
FROM h_computerstats AS pcstats
INNER JOIN computers ON labtech.pcstats.computerid=computers.computerid
WHERE pcstats.cpu >=50 AND (IF(INSTR(computers.os, 'server')>0, 1, 0)<>0)

 

And for RAM:

SELECT 
  pcstats.computerid,
  computers.name,
  pcstats.cpu,
  pcstats.mem,
  pcstats.datain,
  pcstats.dataout, 
  IF(INSTR(computers.os, 'server')>0, 1, 0) as `Computer.OS.IsServer`
FROM h_computerstats AS pcstats
INNER JOIN computers ON labtech.pcstats.computerid=computers.computerid
WHERE (pcstats.mem >=75) AND (IF(INSTR(computers.os, 'server')>0, 1, 0)<>0)

Share this post


Link to post
Share on other sites

Thnx Fitzzz ...

Little request ... I want the name of the clients also ... I've tried to modify the syntax, but I can't get it to work.

It shows me the client name, but pc name stays the same everytime.

Cheers

Kurt

Share this post


Link to post
Share on other sites

Got it :-)

 

SELECT
clients.name,
computers.name,
h_computerstats.mem
FROM h_computerstats
INNER JOIN computers ON labtech.h_computerstats.computerid=computers.computerid
INNER JOIN clients ON computers.clientid=clients.clientid
WHERE (h_computerstats.mem >=80) AND (IF(INSTR(computers.os, 'server')>0, 1, 0)<>0)

 

I'm not an expert in sql but managed to do it.

Complexer stuff like graph I really also like, but can't it to work (nor understand) ... any tips?

Cheers

Kurt

Share this post


Link to post
Share on other sites

Hi Fitzzz,

Is there a way to create a graph of CPU from a server?

I think you can use h_computerstatshourly ... but thats hourly.

Can you create a realtime graph with h_computerstats?

 

I also see that in table "computers" you also have cpu and memory stats ... maybe an idea?

 

Can you help me on the way with 1 server?

(if I have code I can use it for memory and disk also ... and maybe network devices?)

 

Many thanks in advance.

 

Cheers

Share this post


Link to post
Share on other sites

Hello all, long time lurker here, but I want to share my dashboards and panels.

I installed latest Grafana straight from the source without the data-sqldb-datasource.

 

They have just released support for MySQL as datasource so I just went to work and I think I kind of nailed it.

I have panels that show graphs of CPU/ Memory, network trafic , tickets , alerts and more.

Most of my experimental stuff lies under the Snippets file, it is there where most of the dynamic stuff is.

 

I uploaded the dashboards on github https://github.com/PatrikErn/Grafana_Labtech

 

Please refer to http://docs.grafana.org/reference/export_import/ on how to import a dashboard.

Share this post


Link to post
Share on other sites

Hi P_ern,

 

Many thanks for the examples ... really appreciate it man.

But I have question about the Snippets Daskboard: on top you can sellect 1 or multiple servers to see cpu or mem, but when I select multiple servers it shows me (for example) 5 deperate graphs with servername, but the values are 5 time the same (see printscreen)

Question1: how can this be?

Question2: can you add 5 servers in 1 graphs when selecting multiple servers?

Question3: how do you add the dropdownbox on top selecting this servers?

Question4: when cpu graphs is showing there a a lot of line the last minutes and before less lines/points.  Why is that the last minutes there are a ton of values? (like in printscreen from 13:50 till before 14h 4 points and then around 14h 30 points and the from 14h till 14:10h 1 point and then from 14:10 again a lot op points / values... How is that?

Question5: when I look in h_computerstatshourly for a server I can see cpu and memory .. ok ... but the values I see there are different.  I have a Exchange servers with the last week around 90% memory usage.  When I open computer management in Control Center I clearly see (right bottom) that memory usage is 89% ... When I look in h_computerstatshourly and daily those vallues are not in there ... values go from 4% till maybe 16% ... so not accurate.  When I login to that computer and open TaskManager it showing dat 98% memory is in use, so those stats in that table are not accurate and I don't know where there are comming from.  Do you have any idea? 

Question6: I tried to use your CPU template and add servers to it.  So I modified the computerid everytime to the desired id.  So I added for example 5 metrix code and changed the computer id everytime.  But all the vallues in that graph for those 5 servers are the same ... It appears that he doesn't give me 5 different graphes... Any idea?

Many thanks

Kurt

grafana_snippets_cpu.JPG

Share this post


Link to post
Share on other sites
On 10/19/2017 at 4:12 PM, p_ern said:

Hello all, long time lurker here, but I want to share my dashboards and panels.

I installed latest Grafana straight from the source without the data-sqldb-datasource.

 

They have just released support for MySQL as datasource so I just went to work and I think I kind of nailed it.

I have panels that show graphs of CPU/ Memory, network trafic , tickets , alerts and more.

Most of my experimental stuff lies under the Snippets file, it is there where most of the dynamic stuff is.

 

I uploaded the dashboards on github https://github.com/PatrikErn/Grafana_Labtech

 

Please refer to http://docs.grafana.org/reference/export_import/ on how to import a dashboard.

Hi P_ern,

 

Many thanks for the examples ... really appreciate it man.

But I have question about the Snippets Daskboard: on top you can sellect 1 or multiple servers to see cpu or mem, but when I select multiple servers it shows me (for example) 5 deperate graphs with servername, but the values are 5 time the same (see printscreen)

Question1: how can this be?

Question2: can you add 5 servers in 1 graphs when selecting multiple servers?

Question3: how do you add the dropdownbox on top selecting this servers?

Question4: when cpu graphs is showing there a a lot of line the last minutes and before less lines/points.  Why is that the last minutes there are a ton of values? (like in printscreen from 13:50 till before 14h 4 points and then around 14h 30 points and the from 14h till 14:10h 1 point and then from 14:10 again a lot op points / values... How is that?

Question5: when I look in h_computerstatshourly for a server I can see cpu and memory .. ok ... but the values I see there are different.  I have a Exchange servers with the last week around 90% memory usage.  When I open computer management in Control Center I clearly see (right bottom) that memory usage is 89% ... When I look in h_computerstatshourly and daily those vallues are not in there ... values go from 4% till maybe 16% ... so not accurate.  When I login to that computer and open TaskManager it showing dat 98% memory is in use, so those stats in that table are not accurate and I don't know where there are comming from.  Do you have any idea? 

Question6: I tried to use your CPU template and add servers to it.  So I modified the computerid everytime to the desired id.  So I added for example 5 metrix code and changed the computer id everytime.  But all the vallues in that graph for those 5 servers are the same ... It appears that he doesn't give me 5 different graphes... Any idea?

Many thanks

Kurt

grafana_snippets_cpu.JPG

Share this post


Link to post
Share on other sites

@kurtdejaeger

Happy you liked it. I wanted to post this so I can get some input on everything and also make some more progress and have a discussion on this, since Automate lacks a good dashboard function.

For templating queries: Templates Query  : There are 2 queries in the text.

Question 1 , 2: 
Working on figuring that out . I think it is due to the MySQL connector is still quite new, and the templating is not all functional yet,
or it might be wrong from my side of the query.

Question 3:
It is called templating: http://docs.grafana.org/reference/templating/
Still working out how this works and how it affects the dashboard. There are several places this one kicks in. Both panels and/or the dashboard.
It must be set per dashboard though, so it does not affect everything.

Question 4: Could be with the refresh rate of the dashboard. In the top right corner, check if it is set on refresh every x minutes. Change to 30 minutes, and check how it affects trhe curve.
See if thats related to Question 5. 

Question 5: Noticed that too, could be that it is counted from CPU time, and not in %, which might explain the numbers.

Question 6: Related to question 1 and 2.
I treated the templating thing when it comes to repeating panels as "Nice to have", and hope it will be better soon.
With the hard coded on the top of the dashboard, copy the JSON and paste it in a another panel, edit the Computerid to what you want. That works for me. See screenshots below.


The templating is what I try to figure out, on why it is not giving correct values.
Fort the values on panels with the same metric and same result, I am not really sure why it is that way. 

 

For importing JSON

Press panel title.

image.png.d1e98f42bc412bd78a3bd574b9939a03.png

Copy the text that show up.

After you done that, add a new panel and then paste that in the same way. It dont matter what type of panel you make, it will be overwritten.

Add the new ComputerID in the query 

image.png.a3e94c59553cc017d03a102d06f15285.png

Edited by p_ern

Share this post


Link to post
Share on other sites
On 10/23/2017 at 3:12 PM, p_ern said:

@kurtdejaeger

Happy you liked it. I wanted to post this so I can get some input on everything and also make some more progress and have a discussion on this, since Automate lacks a good dashboard function.

For templating queries: Templates Query  : There are 2 queries in the text.

Question 1 , 2: 
Working on figuring that out . I think it is due to the MySQL connector is still quite new, and the templating is not all functional yet,
or it might be wrong from my side of the query.

Question 3:
It is called templating: http://docs.grafana.org/reference/templating/
Still working out how this works and how it affects the dashboard. There are several places this one kicks in. Both panels and/or the dashboard.
It must be set per dashboard though, so it does not affect everything.

Question 4: Could be with the refresh rate of the dashboard. In the top right corner, check if it is set on refresh every x minutes. Change to 30 minutes, and check how it affects trhe curve.
See if thats related to Question 5. 

Question 5: Noticed that too, could be that it is counted from CPU time, and not in %, which might explain the numbers.

Question 6: Related to question 1 and 2.
I treated the templating thing when it comes to repeating panels as "Nice to have", and hope it will be better soon.
With the hard coded on the top of the dashboard, copy the JSON and paste it in a another panel, edit the Computerid to what you want. That works for me. See screenshots below.


The templating is what I try to figure out, on why it is not giving correct values.
Fort the values on panels with the same metric and same result, I am not really sure why it is that way. 

 

For importing JSON

Press panel title.

image.png.d1e98f42bc412bd78a3bd574b9939a03.png

Copy the text that show up.

After you done that, add a new panel and then paste that in the same way. It dont matter what type of panel you make, it will be overwritten.

Add the new ComputerID in the query 

image.png.a3e94c59553cc017d03a102d06f15285.png

 

 

Hi P_ERN,
Thnx for the reply.
Just wanted to keep you up to date about the problem about the values in h_computerstatshourly and daily ... and the differents between the history panel.
Oh man this is so stupid ... the only value that is inverted is the MEM ... every other value is the used value ... but the MEM is wat is left.
So for calculating you must do 100-MEM = memory in use.
So stupid :-) ... CPU is the actual value ... So if CPU is 13, it means that CPU is at 13% ... 
Hope it helps.

Yes about the same graph for every CPU for the last 30 min is unexplainable ... I did was you proposed in the first please, but still no result.
This is my query:

A

SELECT
computers.name,
 (UNIX_TIMESTAMP(LastUpdate)) as  time_sec ,
  (cpu) as value,
  'ARCHIE' as metric
FROM h_computerstatshourly 
INNER JOIN computers ON h_computerstatshourly.computerid
WHERE $__timeFilter(LastUpdate) and CPU Between 0 and 100 and LastUpdate > DATE_ADD(NOW(),INTERVAL -7 DAY) and computers.computerid = 387
ORDER BY time_sec ASC

 

B

SELECT
computers.name,
 (UNIX_TIMESTAMP(LastUpdate)) as  time_sec ,
  (cpu) as value,
  'D0SAEYAP01' as metric
FROM h_computerstatshourly 
INNER JOIN computers ON h_computerstatshourly.computerid
WHERE $__timeFilter(LastUpdate) and CPU Between 0 and 100 and LastUpdate > DATE_ADD(NOW(),INTERVAL -7 DAY) and computers.computerid = 31
ORDER BY time_sec ASC

C

SELECT
computers.name,
 (UNIX_TIMESTAMP(LastUpdate)) as  time_sec ,
  (cpu) as value,
  'D0SAEYAP02' as metric
FROM h_computerstatshourly 
INNER JOIN computers ON h_computerstatshourly.computerid
WHERE $__timeFilter(LastUpdate) and CPU Between 0 and 100 and LastUpdate > DATE_ADD(NOW(),INTERVAL -7 DAY) and computers.computerid = 32
ORDER BY time_sec ASC

 

But still for every different server I get the same graph:      -->    cpu_same_values_different_servers.JPG

 

Cheers m8

Kurt

 

cpu_same_values_different_servers.JPG

Share this post


Link to post
Share on other sites

@kurtdejaeger 

Nice tip on the Memory thing.

I solved the duplicating problem on my end. Copy and paste the code, and change the computers to yours.  Import this on any panel, as I described in earlier post. 

I think it is how the display options are setup on the graph. Compare your settings with those in my panel.

 

{
  "aliasColors": {},
  "bars": false,
  "dashLength": 10,
  "dashes": false,
  "datasource": null,
  "decimals": null,
  "description": "Static on ComputerID",
  "fill": 1,
  "hideTimeOverride": false,
  "id": 34,
  "legend": {
    "alignAsTable": true,
    "avg": true,
    "current": true,
    "hideEmpty": true,
    "hideZero": true,
    "max": true,
    "min": true,
    "rightSide": true,
    "show": true,
    "sort": "max",
    "sortDesc": true,
    "total": false,
    "values": true
  },
  "lines": true,
  "linewidth": 1,
  "links": [],
  "nullPointMode": "null as zero",
  "percentage": false,
  "pointradius": 1,
  "points": true,
  "renderer": "flot",
  "seriesOverrides": [
    {
      "alias": "Brewdog CPU",
      "color": "#0A437C"
    },
    {
      "alias": "Brewdog Memory",
      "color": "#EF843C"
    }
  ],
  "spaceLength": 10,
  "span": 6,
  "stack": false,
  "steppedLine": false,
  "targets": [
    {
      "alias": "",
      "format": "time_series",
      "hide": false,
      "rawSql": "SELECT\r\ncomputerid,\r\n (UNIX_TIMESTAMP(LastUpdate))  as  time_sec ,\r\n  (CPU) as value,\r\n  'VEAAM01 CPU' as metric\r\nFROM h_computerstatshourly \r\nWHERE $__timeFilter(LastUpdate) and CPU Between 0 and 100 and ComputerID='94' and LastUpdate > DATE_ADD(NOW(),INTERVAL -7 DAY)\r\nORDER BY time_sec ASC",
      "refId": "A"
    },
    {
      "alias": "",
      "format": "time_series",
      "hide": false,
      "rawSql": "SELECT\r\ncomputerid,\r\n (UNIX_TIMESTAMP(LastUpdate))  as  time_sec ,\r\n  (100-MEM) as value,\r\n  'VEEAM Memory' as metric\r\nFROM h_computerstatshourly \r\nWHERE $__timeFilter(LastUpdate)  and ComputerID='94' and LastUpdate > DATE_ADD(NOW(),INTERVAL -7 DAY)\r\nORDER BY time_sec ASC",
      "refId": "B"
    },
    {
      "alias": "",
      "format": "time_series",
      "hide": false,
      "rawSql": "SELECT\r\ncomputerid,\r\n (UNIX_TIMESTAMP(LastUpdate))  as  time_sec ,\r\n  (CPU) as value,\r\n  'ADSRV01 CPU' as metric\r\nFROM h_computerstatshourly \r\nWHERE $__timeFilter(LastUpdate) and CPU Between 0 and 100 and ComputerID='1' and LastUpdate > DATE_ADD(NOW(),INTERVAL -7 DAY)\r\nORDER BY time_sec ASC",
      "refId": "C"
    },
    {
      "alias": "",
      "format": "time_series",
      "hide": false,
      "rawSql": "SELECT\r\ncomputerid,\r\n (UNIX_TIMESTAMP(LastUpdate))  as  time_sec ,\r\n  (100-MEM) as value,\r\n  'ADSRV01 Memory' as metric\r\nFROM h_computerstatshourly \r\nWHERE $__timeFilter(LastUpdate)  and ComputerID='1' and LastUpdate > DATE_ADD(NOW(),INTERVAL -7 DAY)\r\nORDER BY time_sec ASC",
      "refId": "D"
    }
  ],
  "thresholds": [
    {
      "colorMode": "ok",
      "fill": true,
      "line": false,
      "op": "lt",
      "value": 95
    },
    {
      "colorMode": "critical",
      "fill": true,
      "line": false,
      "op": "gt",
      "value": 95
    }
  ],
  "timeFrom": "24h",
  "timeShift": null,
  "title": "CPU / Memory",
  "tooltip": {
    "shared": false,
    "sort": 0,
    "value_type": "individual"
  },
  "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
    }
  ]
}

 

Edited by p_ern

Share this post


Link to post
Share on other sites

Updated my dashboards a bit to reflect some changes made, most importantly, the graphs for multiple devices and memory load.

 

Memory_CPU_Load.PNG

DataIn_Out_Graph.JPG

Dynamic_PieChart.JPG

Share this post


Link to post
Share on other sites
2 hours ago, p_ern said:

Updated my dashboards a bit to reflect some changes made, most importantly, the graphs for multiple devices and memory load.

 

Memory_CPU_Load.PNG

DataIn_Out_Graph.JPG

Dynamic_PieChart.JPG

Hi.

Just tried to use your dashboards but somehow can't get them to work. When I import the alert.json file it only shows some of the panels that are specified in the .json file. Also it does not pick up any data, all blank panels.

I am new to the Grafana and not a mySQL guru for that matter.

What I am looking for is to make some sort of Dashboard that shows only the most critical alert, for example a "single stat" that shows missed heartbeats to servers. Normaly I would like that to be grey and when we get 1 or more missed heartbeats I would like it to turn red. As it is today we have it configured so we get a e-mail when we have missed heartbeats.

My thoughts is to have 1 or more screens placed around our office so we visually get alarms.

 

Share this post


Link to post
Share on other sites

@Lars

I shall see if I can help you.

How the Alert Dashboard works:

Alerts-panel: Shows Error and Warnings, so if you get "No data to show" it might mean you have no alerts in your system.

Server Agent Offline-Panel: This shows if a agent and on a server has lost contact the last 5 minutes. You can try this one by stopping the service.

Low HD-Panel: An entry shows up when a HDD has under 8gb of space left. 

-----------------

On github repository all json under Dashboard are one dashboard for each file. So basically, there are 4 dashboards. 

Snippets: Some experiments and Nice to have

Details: Health Scores and devices that need reboot.

Statistik: Singlestat panels, with various panels and metrics. 

Alerts: Error and warnings, offline LTservice and low disk.

 

 

So if you only imported the Alert.json you only get 3 panels: Server Agent Offline, Alerts and Low HD.

-------------------------------------------------------------------------------------------------------------------

I hope you changed the datasource to your own in the SQL Query under metric for the panels.

Also, install these plugins:

Datatable Panel https://github.com/briangann/grafana-datatable-panel

Pie Chart  https://github.com/grafana/piechart-panel

----------------------------------

 

 

Btw, are you from Sweden?

Share this post


Link to post
Share on other sites
24 minutes ago, p_ern said:

@Lars

I shall see if I can help you.

How the Alert Dashboard works:

Alerts-panel: Shows Error and Warnings, so if you get "No data to show" it might mean you have no alerts in your system.

Server Agent Offline-Panel: This shows if a agent and on a server has lost contact the last 5 minutes. You can try this one by stopping the service.

Low HD-Panel: An entry shows up when a HDD has under 8gb of space left. 

-----------------

On github repository all json under Dashboard are one dashboard for each file. So basically, there are 4 dashboards. 

Snippets: Some experiments and Nice to have

Details: Health Scores and devices that need reboot.

Statistik: Singlestat panels, with various panels and metrics. 

Alerts: Error and warnings, offline LTservice and low disk.

 

 

So if you only imported the Alert.json you only get 3 panels: Server Agent Offline, Alerts and Low HD.

-------------------------------------------------------------------------------------------------------------------

I hope you changed the datasource to your own in the SQL Query under metric for the panels.

Also, install these plugins:

Datatable Panel https://github.com/briangann/grafana-datatable-panel

Pie Chart  https://github.com/grafana/piechart-panel

----------------------------------

 

 

Btw, are you from Sweden?

Yes I am, I saw that you where as well.

There was Swedish typing in the files.

 

Okey nice to have some explanation. I will check and see of I can generate some error.

 

 

Share this post


Link to post
Share on other sites

Updated the snippets Dashboard - Which is my "playground" for testing out queries and so forth.

Cleaned up some code ,

Added multistat .  You can find it here 

Made performance graphs so it filters on clients ( No need to hard code every server)

Share this post


Link to post
Share on other sites

Thought I'd give this a whack.

I just used the built in MySQL database connector, it works.

ClientId ='1' is where all new agents go and ad-hoc agents go. Devices related to clientid 1 are IGNORED.

[removed code]

2018-01-12_17h19_02.png

Edited by david.insane

Share this post


Link to post
Share on other sites

If you want to use grafana for pulling data from SQL server as well, someone has pushed out a fork which works until they merge that back into the main product. You can pull it down and compile it using the following steps:

You will need to  install golang and the github client to get started.

Once both are installed, open a command shell and the 'go' command should work:

go get github.com/linuxchips/grafana

If you have a vanilla install like me, this will pull down a copy to c:\users\yourprofile\go
You will then need to do:

setx gopath c:\users\yourprofile\go\

close and reopen command shell so it picks up gopath as an environment variable

cd %GOPATH%
cd src\github.com\
rename linuxchips grafana
cd grafana\grafana
go run build.go setup
go run build.go build

The 'go run build.go setup' was rather critical as the build process couldn't find files until I had done this.

Download and install the gcc compiler from here http://tdm-gcc.tdragon.net/download
Download and install nodejs from here https://nodejs.org/en/download/
Installing nodejs will mean you can execute npm.exe. Open a command shell as administrator and do the following:

npm --add-python-to-path='true' --debug install --global windows-build-tools

now back to your normal shell window in the grafana directory:

npm install -g yarn
yarn install --pure-lockfile
npm run build

This last stage took for ever on my machine. Node.exe ate cpu for the best part of an hour. Just leave it run.

Once all this finishes, you will have a compiled working copy of grafana 5.0.0 (beta) with a data source of MSSQL available.

 

Share this post


Link to post
Share on other sites

 

Request / Question:

Been trying to figure out a good way to monitor Virtual Manager from Grafana.

For example :

I would like to get CPU percentage per core as a graph.

Same with Memory usage.

Anyone got an idea?

Right now I solved this with following Table:

JSON: 

{
  "columns": [],
  "datasource": null,
  "fontSize": "100%",
  "gridPos": {
    "h": 8,
    "w": 12,
    "x": 12,
    "y": 0
  },
  "id": 3,
  "links": [],
  "pageSize": null,
  "scroll": true,
  "showHeader": true,
  "sort": {
    "col": 6,
    "desc": true
  },
  "styles": [
    {
      "alias": "Time",
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "pattern": "time_sec",
      "type": "hidden",
      "unit": "percent"
    },
    {
      "alias": "",
      "colorMode": null,
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "decimals": 2,
      "pattern": "DeviceName",
      "thresholds": [],
      "type": "string",
      "unit": "short"
    },
    {
      "alias": "CPU MHZ Total",
      "colorMode": "cell",
      "colors": [
        "#0a437c",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 0,
      "pattern": "TotalCPU",
      "thresholds": [],
      "type": "number",
      "unit": "none"
    },
    {
      "alias": "CPU Usage MHz",
      "colorMode": "cell",
      "colors": [
        "#447ebc",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 0,
      "pattern": "OverallCPUUsage",
      "thresholds": [],
      "type": "number",
      "unit": "none"
    },
    {
      "alias": "",
      "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": "CPU Free %",
      "thresholds": [
        "5",
        "10"
      ],
      "type": "number",
      "unit": "percent"
    },
    {
      "alias": "",
      "colorMode": "cell",
      "colors": [
        "#614d93",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "pattern": "TotalRAM",
      "thresholds": [],
      "type": "number",
      "unit": "decmbytes"
    },
    {
      "alias": "RAM Used MB",
      "colorMode": "cell",
      "colors": [
        "#d683ce",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "pattern": "OverallMemoryUsage",
      "thresholds": [],
      "type": "number",
      "unit": "decmbytes"
    },
    {
      "alias": "",
      "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": "RAM Free %",
      "thresholds": [
        "5",
        "20"
      ],
      "type": "number",
      "unit": "percent"
    }
  ],
  "targets": [
    {
      "alias": "",
      "format": "table",
      "rawSql": "SELECT \r\n(UNIX_TIMESTAMP(LastupDate)) as time_sec,\r\nDeviceName ,\r\n(CPUGhz *1000 *NumberCores) AS TotalCPU ,\r\nOverallCPUUsage,\r\n(CPUGhz *1000*NumberCores -OverallCPUUsage) / (CPUGhz *1000*NumberCores) *100 AS \"CPU Free %\",\r\n\r\nRAMmb AS TotalRAM,\r\nOverallMemoryUsage,\r\n(RAMmb - OverallMemoryUsage ) / RAMmb *100 AS \"RAM Free %\"\r\nFROM \r\nplugin_vm_esxhosts\r\n\r\nGroup BY Devicename",
      "refId": "A"
    }
  ],
  "title": "Average ESX Host CPU / RAM Last 24H",
  "transform": "table",
  "type": "table"
}

That gives me a general view based on latest data, but no historical values.

Heres the SQL:

SELECT 
(UNIX_TIMESTAMP(LastupDate)) as time_sec,
DeviceName ,
(CPUGhz *1000 *NumberCores) AS TotalCPU ,
OverallCPUUsage,
(CPUGhz *1000*NumberCores -OverallCPUUsage) / (CPUGhz *1000*NumberCores) *100 AS "CPU Free %",

RAMmb AS TotalRAM,
OverallMemoryUsage,
(RAMmb - OverallMemoryUsage ) / RAMmb *100 AS "RAM Free %"
FROM 
plugin_vm_esxhosts

Group BY Devicename

 

 

Share this post


Link to post
Share on other sites

Been checking through the DB and cant see where there is a link between computers and their installed AV. Does anyone have a query for that? 

I effectively want to have a count and list of devices without AV and a pie of the AV breakdown across devices.

 

IGNORE ME, I found it

 

SELECT 
   computers.computerid as `Computer Id`,
   computers.name as `Computer Name`,
   clients.name as `Client Name`,
   computers.domain as `Computer Domain`,
   computers.username as `Computer User`,
   IF(Computers.VirusScanner>0, 1, 0) as `Computer.Antivirus.IsInstalled`
FROM Computers 
LEFT JOIN inv_operatingsystem ON (Computers.ComputerId=inv_operatingsystem.ComputerId)
LEFT JOIN Clients ON (Computers.ClientId=Clients.ClientId)
LEFT JOIN Locations ON (Computers.LocationId=Locations.LocationID)
 WHERE 
((IF(Computers.VirusScanner>0, 1, 0)=0))
 

Edited by itinfserv

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