目的

For audit purposes, the Hipchat Server/Data Center admin may be asked to extract the private chat history between two users. This article lists the steps to be run in the backend. 

ソリューション

Hipchat Server

Chat history is stored in Elasticsearch. Run the two queries to get both sets of history:

1. Chat history from user 1 to user 216:

   curl -s 'http://localhost:9200/_all/document/_search?pretty' -d '{"from":0,"size":100000,"query":{"bool":{"must":[{"term":{"from.user_id": 1}},{"term":{"to.user_id": 216}},{"term":{"stanza_type": "message"}}]}}}' | jq .hits

2. Chat history from user 216 to user 1:

   curl -s 'http://localhost:9200/_all/document/_search?pretty' -d '{"from":0,"size":100000,"query":{"bool":{"must":[{"term":{"from.user_id": 216}},{"term":{"to.user_id": 1}},{"term":{"stanza_type": "message"}}]}}}' | jq .hits

The two queries above will return JSON files with many fields. The two relevant fields are date and body.

Hipchat Data Center

Chat history is stored in Postgres and spans across multiple tables. These commands will need to be ran on only one of the HipChat Data Center nodes: 

1. Gain root access: 

   sudo dont-blame-hipchat

2. Create a bash script called private_history.sh with the following content: 

   #!/bin/sh
   
   PGHOST=$(cat /hipchat/config/site.json | jq -r '.databases.hipchat_postgres.servers[0]' | cut -d: -f1) && PGUSER=$(cat /hipchat/config/site.json | jq -r '.databases.hipchat_postgres.user') && PGSCHEMA=$(cat /hipchat/config/site.json | jq -r '.databases.hipchat_postgres.schema') && export PG_CONNECT="psql -h $PGHOST -U $PGUSER -d $PGSCHEMA" && export PGPASSWORD=$(cat /hipchat/config/site.json | jq -r '.databases.hipchat_postgres.pass')
   USER1=1
   USER2=216
   count=0
   # Loop over room history tables from 2012 to 2020
   for year in $(seq 2012 2020); do
     for month in '01' '02' '03' '04' '05' '06' '07' '08' '09' '10' '11' '12'; do
         history=`echo | $PG_CONNECT -A -c "SELECT * FROM \"private-${year}.${month}\" WHERE fromid IN ($USER1,$USER2) AND toid IN ($USER1,$USER2);"`
         empty=$(echo $history | grep -c "(0 rows)")
         if [ $empty -eq 0 ]; then
           echo "######################## History for ${year}/${month} ########################"
           echo "$history"
         fi
     done
   done

3. Make the script executable and run it: 

   chmod +x /tmp/private_history.sh
   /tmp/private_history.sh