import java.sql.*; /** Analyze aggregate chat session 101220 in tenant DB */ public class QueryAggregateChatSession101220 { public static void main(String[] args) throws Exception { String host = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/"; String user = "root"; String pass = "Ylrz_1q2w3e4r5t6y"; String db = "fs_tenant_cs1"; try (Connection c = DriverManager.getConnection(host + db + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", user, pass)) { querySession(c, 101220L); queryMsgByContact(c, 755L); queryDuplicateOutbound(c); querySilentInstances(c); queryInstanceForSession(c, 101220L); queryPrompts(c); queryTasks(c); } } private static void querySession(Connection c, long sessionId) throws SQLException { System.out.println("--- chat_session " + sessionId + " ---"); try (Statement st = c.createStatement()) { try (ResultSet rs = st.executeQuery( "SELECT session_id, company_id, contact_id, channel_type, external_user_id, " + "instance_id, last_msg, last_msg_time, status, create_time, update_time " + "FROM chat_session WHERE session_id=" + sessionId)) { if (rs.next()) { for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { System.out.println(rs.getMetaData().getColumnLabel(i) + "=" + rs.getString(i)); } } else { System.out.println("(not found)"); } } System.out.println("--- chat_msg chronological ---"); try (ResultSet rs = st.executeQuery( "SELECT msg_id, send_type, LEFT(content,160) AS content, create_time " + "FROM chat_msg WHERE session_id=" + sessionId + " ORDER BY create_time ASC")) { int n = 0, cust = 0, ai = 0; while (rs.next()) { n++; int stype = rs.getInt("send_type"); if (stype == 1) cust++; else if (stype == 2) ai++; System.out.printf("%s | send_type=%s | %s%n", rs.getString("create_time"), stype, rs.getString("content")); } System.out.println("total=" + n + " customer=" + cust + " ai=" + ai); } } } private static void queryMsgByContact(Connection c, long contactId) throws SQLException { System.out.println("\n--- sessions contact_id=" + contactId + " ---"); String sql = "SELECT session_id, channel_type, instance_id, last_msg, last_msg_time " + "FROM chat_session WHERE contact_id=" + contactId + " ORDER BY last_msg_time DESC LIMIT 5"; try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) { while (rs.next()) { System.out.printf("sess=%s ch=%s inst=%s time=%s msg=%s%n", rs.getString("session_id"), rs.getString("channel_type"), rs.getString("instance_id"), rs.getString("last_msg_time"), rs.getString("last_msg")); } } } private static void queryDuplicateOutbound(Connection c) throws SQLException { System.out.println("\n--- duplicate outbound send_type=2 (7d, cnt>=3) ---"); String sql = "SELECT LEFT(content,90) AS snippet, COUNT(*) AS cnt, " + "MIN(create_time) AS first_at, MAX(create_time) AS last_at " + "FROM chat_msg WHERE send_type=2 " + "AND create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) " + "GROUP BY LEFT(content,90) HAVING cnt >= 3 ORDER BY cnt DESC LIMIT 12"; try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) { while (rs.next()) { System.out.printf("cnt=%s | %s ~ %s | %s%n", rs.getString("cnt"), rs.getString("first_at"), rs.getString("last_at"), rs.getString("snippet")); } } } private static void querySilentInstances(Connection c) throws SQLException { System.out.println("\n--- running instances: 0 inbound chat, >=2 outbound ---"); String sql = "SELECT i.id, i.workflow_id, i.contact_id, i.current_node_name, i.start_time, " + "(SELECT COUNT(*) FROM chat_msg m JOIN chat_session s ON m.session_id=s.session_id " + " WHERE s.instance_id=i.id AND m.send_type=1) AS inbound, " + "(SELECT COUNT(*) FROM chat_msg m JOIN chat_session s ON m.session_id=s.session_id " + " WHERE s.instance_id=i.id AND m.send_type=2) AS outbound " + "FROM lobster_workflow_instance i WHERE i.status='running' " + "HAVING inbound=0 AND outbound>=2 ORDER BY outbound DESC LIMIT 8"; try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) { int n = 0; while (rs.next()) { n++; System.out.printf("inst=%s wf=%s contact=%s out=%s in=%s node=%s%n", rs.getString("id"), rs.getString("workflow_id"), rs.getString("contact_id"), rs.getString("outbound"), rs.getString("inbound"), rs.getString("current_node_name")); } if (n == 0) System.out.println("(none)"); } catch (SQLException e) { System.out.println("skip: " + e.getMessage()); } } private static void queryInstanceForSession(Connection c, long sessionId) throws SQLException { System.out.println("\n--- workflow instance for session ---"); try (Statement st = c.createStatement()) { try (ResultSet rs = st.executeQuery( "SELECT id, workflow_id, contact_id, status, current_node_code, current_node_name, " + "start_time, last_activity_time, context_json " + "FROM lobster_workflow_instance WHERE id=(SELECT instance_id FROM chat_session WHERE session_id=" + sessionId + ")")) { if (rs.next()) { System.out.println("id=" + rs.getString("id")); System.out.println("workflow_id=" + rs.getString("workflow_id")); System.out.println("status=" + rs.getString("status")); System.out.println("node=" + rs.getString("current_node_code") + " / " + rs.getString("current_node_name")); System.out.println("start=" + rs.getString("start_time")); System.out.println("last_activity=" + rs.getString("last_activity_time")); String ctx = rs.getString("context_json"); if (ctx != null) { System.out.println("context_json(len)=" + ctx.length()); if (ctx.contains("_proactiveGreetCount")) System.out.println(" has _proactiveGreetCount"); if (ctx.contains("_lastProactiveGreetText")) System.out.println(" has _lastProactiveGreetText"); } } else { System.out.println("(no instance)"); } } } catch (SQLException e) { System.out.println("skip: " + e.getMessage()); } } private static void queryPrompts(Connection c) throws SQLException { System.out.println("\n--- lobster_system_prompt (instruction keys) ---"); try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery( "SELECT prompt_key, LEFT(prompt_content,120) AS c FROM lobster_system_prompt " + "WHERE prompt_key LIKE '%instruction%' OR prompt_key='system' " + "OR prompt_content LIKE '%Please answer%' LIMIT 15")) { while (rs.next()) { System.out.printf("%s | %s%n", rs.getString("prompt_key"), rs.getString("c")); } } catch (SQLException e) { System.out.println("skip: " + e.getMessage()); } } private static void queryTasks(Connection c) throws SQLException { System.out.println("\n--- recent lobster tasks (pending/done) ---"); try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery( "SELECT id, workflow_id, task_type, execute_status, cron_expression, " + "last_execute_time, create_time FROM company_workflow_lobster_task " + "ORDER BY id DESC LIMIT 8")) { while (rs.next()) { System.out.printf("task=%s wf=%s type=%s status=%s cron=%s last=%s%n", rs.getString("id"), rs.getString("workflow_id"), rs.getString("task_type"), rs.getString("execute_status"), rs.getString("cron_expression"), rs.getString("last_execute_time")); } } catch (SQLException e) { System.out.println("skip: " + e.getMessage()); } } }