Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
api_csv_export_for_grafana [01.09.2020 15:34] – created Pascal Suter | api_csv_export_for_grafana [01.09.2020 16:02] (current) – Pascal Suter | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== API CSV data Export for Grafana ====== | ||
+ | currently (Grafana version 7) it seems to not be possible to create a useful CSV table directly out of a Grafana Panel via the API. It is possible on the GUI via the " | ||
+ | |||
+ | looking at the requests sent to the grafana server during the Inspect action, i found a query like this one: | ||
+ | http:// | ||
+ | which provides the entire dataset for the given targets for the given time frame in json. You can actually change the '' | ||
+ | < | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase1.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase2.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | powermeter.phase3.watt, | ||
+ | </ | ||
+ | while this is technically speaking csv, it is of little use if you want to import the dataset into excel for visualisation or any other purposes. | ||
+ | |||
+ | so i wrote a little PHP script which i am hosting on the grafana server itself and which allows me to get a nice table with a separate column for each of the requested targets like so: | ||
+ | < | ||
+ | curl -s " | ||
+ | time, | ||
+ | 2020-09-01T07: | ||
+ | 2020-09-01T07: | ||
+ | 2020-09-01T07: | ||
+ | 2020-09-01T07: | ||
+ | 2020-09-01T07: | ||
+ | 2020-09-01T07: | ||
+ | 2020-09-01T07: | ||
+ | 2020-09-01T07: | ||
+ | 2020-09-01T07: | ||
+ | 2020-09-01T07: | ||
+ | </ | ||
+ | |||
+ | to specify multiple targets the '' | ||
+ | |||
+ | < | ||
+ | curl -s " | ||
+ | </ | ||
+ | would produce the same output as the above query | ||
+ | |||
+ | this makes it much easier for me to process the data afterwards in libre office calc or excel or similar tools. | ||
+ | |||
+ | here is the quick and dirty php script that i put into the document root of my grafana host, serving it via apache. | ||
+ | |||
+ | //**please note:** i have saved the API key directly into my script, as this server is not publicly accessible and holds no confidential data anyways.. however, consider moving the API key out of the script if you use id on a public server// | ||
+ | <code php csv.php> | ||
+ | <?php | ||
+ | $SEPARATOR=","; | ||
+ | $APIKEY="< | ||
+ | $HOST=" | ||
+ | $PROT=" | ||
+ | |||
+ | # | ||
+ | $queryTargets=$_REQUEST[" | ||
+ | if (!is_array($queryTargets)){ | ||
+ | $queryTargets=array($_REQUEST[" | ||
+ | } | ||
+ | if (count($queryTargets)> | ||
+ | $queryTargetsString=" | ||
+ | foreach ($queryTargets as $qt){ | ||
+ | $queryTargetsString.=urlencode($qt)."& | ||
+ | } | ||
+ | $queryTargetsString=substr($queryTargetsString, | ||
+ | } else { | ||
+ | throw new Exception(" | ||
+ | } | ||
+ | $from=(int)$_REQUEST[" | ||
+ | $until=(int)$_REQUEST[" | ||
+ | |||
+ | if ($from <= 0 || $until <=0 ){ | ||
+ | throw new Exception(" | ||
+ | } | ||
+ | header(' | ||
+ | header(' | ||
+ | |||
+ | $json = file_get_contents(" | ||
+ | " | ||
+ | $queryTargetsString. | ||
+ | "& | ||
+ | "& | ||
+ | $data = json_decode($json, | ||
+ | if (!$data){ | ||
+ | die(); | ||
+ | } | ||
+ | $table=array(); | ||
+ | $targets=array(); | ||
+ | if(count($data) > 0 ){ | ||
+ | foreach ($data as $dataset){ | ||
+ | $target=$dataset[" | ||
+ | $targets[]=$target; | ||
+ | foreach ($dataset[" | ||
+ | $table[$point[1]][$target]=$point[0]; | ||
+ | } | ||
+ | |||
+ | } | ||
+ | |||
+ | echo " | ||
+ | foreach ($table as $date=> | ||
+ | echo date(" | ||
+ | foreach($targets as $target){ | ||
+ | echo $SEPARATOR.$line[$target]; | ||
+ | } | ||
+ | echo " | ||
+ | } | ||
+ | } | ||
+ | ?> | ||
+ | </ | ||