Grafana: Top N values: Unterschied zwischen den Versionen
Admin (Diskussion | Beiträge) |
Admin (Diskussion | Beiträge) |
||
Zeile 21: | Zeile 21: | ||
'''FileSystemLabel''' = Is the name of the CSV <br> | '''FileSystemLabel''' = Is the name of the CSV <br> | ||
− | '''InfluxQL | + | '''InfluxQL:''' |
<pre> | <pre> | ||
− | SELECT top("UsedSpace (%)","FileSystemLabel", | + | SELECT top("UsedSpace (%)","FileSystemLabel",5) AS "UsedSpace (%)" FROM ( |
SELECT (100 / mean("Size")) * mean("SizeUsed") AS "UsedSpace (%)" | SELECT (100 / mean("Size")) * mean("SizeUsed") AS "UsedSpace (%)" | ||
FROM "$rp"."cluster_csv" | FROM "$rp"."cluster_csv" | ||
Zeile 29: | Zeile 29: | ||
GROUP BY "FileSystemLabel" | GROUP BY "FileSystemLabel" | ||
) </pre> | ) </pre> | ||
+ | |||
+ | Description of the Query: | ||
+ | ''$rp: is a Grafana template variable to select InfluxDB retention policy. If you use this in as a template variable you can change the retention policy for the whole dashboard (you don't have to change each panel).'' | ||
+ | ''First Query: Grafana selects and calculates the percentage of used space per CSV in the timerange of the dashboard.'' <br> | ||
+ | ''Second Query: Grafana selects the top 5 CSV's based on the value of "UsedSpace (%)" per "FileSystemLabel" (CSV-Name).'' <br> | ||
+ | ''Conclusion: With this query you have a table of the 5 most used space per CSV's.'' <br> | ||
'''Grafana Panel settings:''' <br> | '''Grafana Panel settings:''' <br> | ||
'''FORMAT AS''' = Table <br> | '''FORMAT AS''' = Table <br> | ||
'''Visualization''' = Table <br> | '''Visualization''' = Table <br> | ||
+ | '''Transform''' = Organize fields = Hiding "Time" and renaming FileSystemLabel to CSV | ||
'''Overrides''' = Fields with name = "UsedSpace (%)" -> Unit = "Percent (0-100)", Decimals = 1, Cell Display mode = "Gradient gauge"<br> | '''Overrides''' = Fields with name = "UsedSpace (%)" -> Unit = "Percent (0-100)", Decimals = 1, Cell Display mode = "Gradient gauge"<br> | ||
Version vom 6. Oktober 2020, 15:57 Uhr
Top N values single
Telegraf configuration
Because there is no Perfmon-Counter for the total size of Cluster Shared Volumes (CSV), I wrote a PowerShell CMDLET.
[[inputs.exec]] commands = ['''powershell.exe -NoProfile -Command "Get-Volume | Where-Object {$_.FileSystem -eq 'CSVFS'} | select FileSystemLabel, AllocationUnitSize, Size, SizeRemaining, @{N='SizeUsed';E={$_.Size - $_.SizeRemaining}} | ConvertTo-Json"'''] name_override = "cluster_csv" data_format = "json" data_type = "float" tag_keys = ["FileSystemLabel"]
Measurement = cluster_csv
Tags = FileSystemLabel
InfluxQL in Grafana
Size = Total size of the CSV
SizeUsed = Used size of the CSV
FileSystemLabel = Is the name of the CSV
InfluxQL:
SELECT top("UsedSpace (%)","FileSystemLabel",5) AS "UsedSpace (%)" FROM ( SELECT (100 / mean("Size")) * mean("SizeUsed") AS "UsedSpace (%)" FROM "$rp"."cluster_csv" WHERE $timeFilter GROUP BY "FileSystemLabel" )
Description of the Query:
$rp: is a Grafana template variable to select InfluxDB retention policy. If you use this in as a template variable you can change the retention policy for the whole dashboard (you don't have to change each panel).
First Query: Grafana selects and calculates the percentage of used space per CSV in the timerange of the dashboard.
Second Query: Grafana selects the top 5 CSV's based on the value of "UsedSpace (%)" per "FileSystemLabel" (CSV-Name).
Conclusion: With this query you have a table of the 5 most used space per CSV's.
Grafana Panel settings:
FORMAT AS = Table
Visualization = Table
Transform = Organize fields = Hiding "Time" and renaming FileSystemLabel to CSV
Overrides = Fields with name = "UsedSpace (%)" -> Unit = "Percent (0-100)", Decimals = 1, Cell Display mode = "Gradient gauge"
SELECT top("Average","host",$top) AS "Average" FROM (
SELECT sum("Average") AS "Average" FROM ( SELECT ((100 / (mean("Current_Bandwidth") / 8)) * (mean("Bytes_Sent_persec") + mean("Bytes_Received_persec"))) AS "Average" FROM "$rp"."win_net" WHERE $timeFilter AND "host" =~ $hostfilter AND "instance" !~ /^Hyper-V.+/ GROUP BY "host", "instance" ) GROUP BY "host"
)