LoginSignup
4
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@celery

ZendeskのデータをDBに格納して可視化する

Posted at

#最終的にはこんな感じに

image.png

#これがあると大体できます
PHP
PostgreSQL
PDO
pear/HTTP/Request2
Restlet Client(あれば開発しやすいchromeの拡張機能)

#Zendeskの可視化とデータ構成
そもそもZendeskのデータはあまり横断的に見ることができず、もともとのInsight情報ではユーザーにあまり優しくないようです。
Zendeskのデータは主にTicketsのAPIで取得できますが、それに関連した初回返信時間や解決までにかかった時間は別のTicket MetricsのAPIで取得しなければならないため、ちょっと面倒な構成になっています。また、コメントはTicket Comments APIで取得しなければなりません。。
さらに、Tickets APIで取得できる人のデータ、組織のデータはidで持っており、それぞれUsers API,Organizations APIと別でマスタのようになっているため、これらも取得して、Ticketのデータをより見やすくするのが目的です。

#注意点
このAPI取得の方法では、CloseされArchiveされた古いデータは取得できないようなので、そこのデータまで取りたい場合は、テーブルに連結してぶち込んであげる必要があります。
ZendeskAPIは1回のリクエストにつき、100件しか取得できないため、複数回pagingして取得する必要があります。
また、それぞれ契約しているプランによって、1分間にリクエストできる回数が限られているので、リミットに触れないようにうまく、loopの間にsleepなどを挟んであげる必要があります。
変な点が合ったら補足・コメントください。。。

#初期処理(前回実行時間取得)

zendesk.php

<?php

// 初期設定
require_once '/usr/share/pear/HTTP/Request2.php';
require_once '/usr/share/php/sendSlack.php'; //errorはSlackに飛ばす

require_once '/var/sett.php'; //setting系は別ファイルへ

date_default_timezone_set('Asia/Tokyo');
$update_time = date('Y-m-d H:i:s');

$seltime = "";

try {

    // データベースに接続
    $pdo = new PDO(
        $dsn,
        $user,
        $pass,
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        )
    );

    //Ticketは数が多いので、前に起動した時間と比べてupdated_atが新しければinsertかupdateを行う
    $sql = 'select * from tsys001 where tname = \'TZND001\'';
$result = $pdo->query($sql);
foreach ($result as $row) {
    $seltime = $row['seltime'];
}

$pdo->exec("DELETE FROM TZND004"); //Organizations Table
$pdo->exec("DELETE FROM TZND003"); //Users Table
$pdo->exec("DELETE FROM TZND002"); //TicketMetrics table


} catch (PDOException $e) {

    // 例外メッセージを表示
    sendSlack("BZND001",$e->getMessage());
    echo $e->getMessage();
    $pdo = null;

}

//pdoオブジェクトを初期化
$pdo = null;

print_r("seltime : ".$seltime);
$seltime = strtotime($seltime);

#一番重要なcurlを使うfunction
全てのAPIはここで投げられ、連想配列で返すfunctionです。

zendesk.php
function exec_curl($url){

    $credentials = "xxxxxxxxxxx@xxxxxxxxx.co.jp:xxxxxxxxxxx"; //zendeskのID:pass

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL,$url);
    curl_setopt($ch, CURLOPT_USERPWD,$credentials);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_TIMEOUT, 60);

    $data = curl_exec($ch);
    curl_close($ch);

    if (curl_errno($ch)) {
        print "Error: " . curl_error($ch) ."  ". $url;
    } else {
        $json = mb_convert_encoding($data, 'UTF8', 'ASCII,JIS,UTF-8,EUC-JP,SJIS-WIN');
        $arr = json_decode($json,true);
        return $arr;
    }
}

#Ticket(メインテーブル)の取得
フィールドの取得はTicket FieldsAPIで取得できますが、ここもIDになっているので、固定(決め打ち)で指定しています。
IDと中身についてはRestlet Clientかなにかで確認すると楽です。
https://developer.zendesk.com/rest_api/docs/core/ticket_fields
あと、コメントの数も集計しています。pulicコメントかprivateコメント、総コメント数ですね。
データを取得した際に、次のデータが有ればnext_pageに次のページのURLがついてくるので、それを使い自己呼び出しを行います。この際、コメントの取得で最大100リクエストくらいするので60秒sleep処理を入れています。

zendesk.php
function getTickets($url,$seltime){

    global $dsn;
    global $user;
    global $pass;

    $arr = exec_curl($url);

    $upsert_pdo = new PDO(
    $dsn,
    $user,
    $pass,
    array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        )
    );


    try {

        if ($arr === NULL) {
            return;
        }else{
            $json_count = count($arr["tickets"]);

            for($i=$json_count-1;$i>=0;$i--){
                $updt = $arr["tickets"][$i]["updated_at"];

                $tdate1 = substr($updt,0,10);
                $tdate2 = substr($updt,11,8);
                //$seltime = strtotime($seltime);
                $rectime = strtotime($tdate1." ".$tdate2);
                
                $diff = $rectime - $seltime;

                if ($diff < 0) {
                    # code...
                } else {

                    $tid = $arr["tickets"][$i]["id"];
                    $subject = $arr["tickets"][$i]["subject"];
                    $status = $arr["tickets"][$i]["status"];
                    $via = $arr["tickets"][$i]["via"]["channel"];
                    $crdt = $arr["tickets"][$i]["created_at"];
                    $orgid = $arr["tickets"][$i]["organization_id"];
                    $euser = $arr["tickets"][$i]["requester_id"];
                    $priority = $arr["tickets"][$i]["priority"];

                    $segment = "";
                    $class = "";
                    $ver = "";
                    $category = "";
                    $jira = "";
                    $function = "";
                    $rdb = "";
                    $adb = "";
                    $browser = "";
                    $platform = "";
                    $assignee = "";
                    $sscore = "";
                    $scomment = "";

                    $fields_count = count($arr["tickets"][$i]["fields"]);
                    for($j=$fields_count-1;$j>=0;$j--){

                        if($arr["tickets"][$i]["fields"][$j]["id"]==26705928){
                            $segment = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==26327017){
                            $class = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==26302728){
                            $ver = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==81157147){
                            $category = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==26327047){
                            $browser = $arr["tickets"][$i]["fields"][$j]["value"];
                        }
                        if($arr["tickets"][$i]["fields"][$j]["id"]==26327037){
                            $platform = $arr["tickets"][$i]["fields"][$j]["value"];
                        }

                    }

                    $assignee = $arr["tickets"][$i]["assignee_id"];
                    $sscore = $arr["tickets"][$i]["satisfaction_rating"]["score"];
                    $scomment = $arr["tickets"][$i]["satisfaction_rating"]["comment"];

                    $comurl = "https://xxxxxxxxxx.zendesk.com/api/v2/tickets/".$tid."/comments.json";

                    $comments = exec_curl($comurl);
                    $totalc = count($comments["comments"]);
                    $publicc = 0;
                    for($k=$totalc-1;$k>=0;$k--){
                        if ($comments["comments"][$k]["public"]==true) {
                            $publicc = $publicc + 1;
                        }
                    }
                    $privatec = $totalc - $publicc;
                    //print_r("commentsum : ".$totalc."<br>public : ".$publicc."<br>private : ".$privatec."<br>");

                    $sql = 'select count(1) as cnt from TZND001 where tid ='.$tid;
                    $result = $upsert_pdo->query($sql);
                    $result = $result->fetch();
                    if ($result['cnt'] == 0) {
                        //insert
                        $stmt_insert = $upsert_pdo->prepare("INSERT INTO TZND001 ( tid,subject,status,via,crdt,updt,orgid,euser,priority,segment,class,ver,category,browser,platform,assignee,totalc,publicc,privatec,sscore,scomment)
                         VALUES (:tid,:subject,:status,:via,:crdt,:updt,:orgid,:euser,:priority,:segment,:class,:ver,:category,:browser,:platform,:assignee,:totalc,:publicc,:privatec,:sscore,:scomment)");
                        $stmt_insert->execute(compact('tid','subject','status','via','crdt','updt','orgid','euser','priority','segment','class','ver','category','browser','platform','assignee','totalc','publicc','privatec','sscore','scomment'));
                    } else {
                        //update
                        $stmt_insert = $upsert_pdo->prepare("UPDATE TZND001 SET subject = :subject, status = :status, via = :via, crdt = :crdt, updt = :updt, orgid = :orgid, euser = :euser, priority = :priority, segment = :segment, class = :class, ver = :ver, category = :category, browser = :browser, platform = :platform, assignee = :assignee, totalc = :totalc, publicc = :publicc, privatec = :privatec, sscore = :sscore, scomment = :scomment WHERE tid = :tid"); 
                        $stmt_insert->execute(compact('tid','subject','status','via','crdt','updt','orgid','euser','priority','segment','class','ver','category','browser','platform','assignee','totalc','publicc','privatec','sscore','scomment'));
                    }

                }//if ($diff < 0) ここまで

            }
            if($arr["next_page"]!=null){
                sleep(60);
                getTickets($arr["next_page"],$seltime);
            }
        }
        
    } catch (PDOException $e) {
        sendSlack("BZND001","ticketID ： ".$tid."
    ".$e->getMessage());
        echo "tID ： ".$tid;
        $upsert_pdo = null;
        // 外側のTryブロックに対してスロー
        throw $e;
    }
    $upsert_pdo = null;
}

#Ticket Metricsの取得
Ticketに比べてはるかに楽です。あと、UsersとOrganizationsもほとんど同じ(idとnameだけの取得)なので割愛します。

zendesk.php
function getTicketMetrics($url){

    global $dsn;
    global $user;
    global $pass;

    $arr = exec_curl($url);

    $upsert_pdo = new PDO(
        $dsn,
        $user,
        $pass,
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        )
    );


    try {

            if ($arr === NULL) {
                return;
            }else{
                $json_count = count($arr["ticket_metrics"]);
                $tid = 0;
                $freply = 0;
                $resolve = 0;

                for($i=$json_count-1;$i>=0;$i--){
                    $tid = $arr["ticket_metrics"][$i]["ticket_id"];
                    $freply = $arr["ticket_metrics"][$i]["reply_time_in_minutes"]["business"];
                    $resolve = $arr["ticket_metrics"][$i]["full_resolution_time_in_minutes"]["business"];

                    $stmt_insert = $upsert_pdo->prepare("INSERT INTO TZND002 ( tid,freply,resolve) VALUES (:tid, :freply, :resolve)");
                    $stmt_insert->execute(compact('tid','freply','resolve'));

                }
                if($arr["next_page"]!=null){
                    getTicketMetrics($arr["next_page"]);
                }
            }
        
    } catch (PDOException $e) {
        sendSlack("BZND002","ticketID ： ".$tid."
    ".$e->getMessage());
        echo "ticketID ： ".$tid;
        $upsert_pdo = null;
        // 外側のTryブロックに対してスロー
        throw $e;
    }
    $upsert_pdo = null;
}

#メインストリーム続き
各functionを起動させて、起動時間をupdateして終了です。

zendesk.php

$url = "https://xxxxxxxxxx.zendesk.com/api/v2/organizations.json";
getOrganizations($url);

$url = "https://xxxxxxxxxx.zendesk.com/api/v2/users.json";
getUsers($url);

$url = "https://xxxxxxxxxx.zendesk.com/api/v2/ticket_metrics.json";
getTicketMetrics($url);

$url = "https://xxxxxxxxxx.zendesk.com/api/v2/tickets.json";
getTickets($url,$seltime);


try {

    // データベースに接続
    $up = new PDO(
        $dsn,
        $user,
        $pass,
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        )
    );

$result = $up->prepare('UPDATE TSYS001 SET seltime = :update_time, upr = \'BZND001\', updt = date_trunc(\'second\',CURRENT_TIMESTAMP) WHERE tname = \'TZND001\'');
$result->execute(compact('update_time'));


} catch (PDOException $e) {

    // 例外メッセージを表示
    sendSlack("BZND001",$e->getMessage());
    echo $e->getMessage();
    $up = null;

}

$up = null;

echo $update_time." : BZND001 : 処理が最後まで終了しました。\n";

#可視化について
最初のテーブルも合わせて、可視化はYellowfinで行います。
入れたデータを結合するだけでも断然見やすくなりますね！
image.png
image.png
image.png

#参考にさせて頂きました
PHPでJSONデータの取得の仕方
https://qiita.com/fantm21/items/603cbabf2e78cb08133e

4
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?