Curl Cloudflare API To Mysql And Display On Web

最近使用的Cloudflare API 非常的方便

剛好也比較閒一些些，來試試把抓到的東西弄到網頁顯示一下

https://api.cloudflare.com/#getting-started-endpoints

先來抓出主機名稱

curl -s -X GET "$HOST?status=active&;page=1&per_page=50&order=status&direction=desc&match=all" \
             -H "X-Auth-Email: "$MAIL"" \
             -H "X-Auth-Key: "$KEY"" \
             -H "Content-Type: application/json"

取出我們要的dashboard資料

curl -s -X GET "$HOST/$ID/analytics/dashboard?since=-30&;continuous=true" \
             -H "X-Auth-Email: "$MAIL"" \
             -H "X-Auth-Key: "$KEY"" \
             -H "Content-Type: application/json"

以上取得最新的30分鐘內資料，然後再濾出最後的10分鐘(10分鐘是比較常有資料的，5-9分的時常取不到，甚至到30分鐘都有機率是無資料)

以上都是代入參數，所以也請另外寫在bash裡面

再來是將取得並過濾後的資料，建立新的資料庫及新的表

root@debian:~# mysql -uroot -p

mysql> CREATE DATABASE yoyoyo;
mysql> use yoyoyo;

mysql> CREATE TABLE IF NOT EXISTS cf ( id INT(11) NOT NULL AUTO_INCREMENT UNIQUE KEY, host VARCHAR(50) NOT NULL PRIMARY KEY, request INT(32) NOT NULL, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, bw BIGINT(20) NOT NULL);

mysql> describe cf;
+---------+-------------+------+-----+-------------------+----------------+
| Field   | Type        | Null | Key | Default           | Extra          |
+---------+-------------+------+-----+-------------------+----------------+
| id      | int(11)     | NO   | UNI | NULL              | auto_increment |
| host    | varchar(50) | NO   | PRI | NULL              |                |
| request | int(32)     | NO   |     | NULL              |                |
| ts      | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| bw      | bigint(20)  | NO   |     | NULL              |                |
+---------+-------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

再來弄一個簡單的網頁

<?php
$servername = "localhost";
$username = "123";
$password = "123";
$dbname = "yoyoyo";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

/* 因為使用replace into 所以主機名都只會有一筆資料，所以直接select * 也是可以的，不需要加where 條件都行 */
$sql = "SELECT host, ts, request, bw FROM cf";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "HOST: " . $row["host"]. "      - TIME: " . $row["ts"]. "     REQUEST: " .  $row["request"]. "     BANDWIDTH: " . $row["bw"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

這樣就完成了

新加一段自動更新語法

$url1=$_SERVER['REQUEST_URI'];
    header("Refresh: 30; URL=$url1");

今天改了一下資料型態，將寫入Bandwidth的資料轉成了KB，也加上小數點

mysql> select * from cf;
+-------+--------------------+---------+---------------------+---------+
| id    | host               | request | ts                  | bw      |
+-------+--------------------+---------+---------------------+---------+
| 13566 | xx                 |      10 | 2017-05-23 16:28:00 |   6.301 |
| 13567 | xx                 |      99 | 2017-05-23 16:28:00 | 160.524 |
| 13568 | x.com              |       4 | 2017-05-23 16:28:00 |   1.478 |
| 13569 | xy.com             |      62 | 2017-05-23 16:28:00 | 14395.4 |
| 13570 | xc.com             |       3 | 2017-05-23 16:28:00 |   2.183 |
| 13536 | scccde.info        |       0 | 2017-05-23 16:27:00 |       0 |

新加一行指令，用來刪除超過10天沒更新到的記錄(表示這個域名已掛點)

mysql -u$DB_USER -p$DB_PASSWD -Bse 'DELETE FROM yoyoyo.cf WHERE ts < DATE(NOW() - INTERVAL 10 DAY)'

• Prev
• Next