Confluence

Atlassian Confluence - Collecting Usage Metrics

January 30, 2014

Below are the scripts I used to collect usage stats from Confluence 3.5.x

-- Average Number Of Edits
select contenttype, min(number_of_changes), max(number_of_changes), avg(number_of_changes)
from (
    select contenttype, cast(lastmoddate As Date) as changedate, count(*) as number_of_changes
    from content
    where content.creationdate > DATEADD(year,-2,getDate()) and version=1
    group by contenttype, cast(lastmoddate As Date)       
     ) as dates
group by contenttype
-- Number of users
select lastmodifier,count(*) from content where lastmoddate>DATEADD(year,-2,getDate())
group by lastmodifier having count(*)>10 order by 2 desc

-- New pages create in X years
select contenttype, count(*) from content where lastmoddate>DATEADD(year,-2,getDate()) AND prevver is NULL group by content.contenttype

-- Total number of pages
SELECT COUNT(*) FROM content WHERE contenttype='PAGE' AND 
prevver is NULL;
-- Links per page
select https, max(linkcount), min(linkcount), avg(linkcount), stdevp(linkcount), stdev(linkcount), varp(linkcount), var(linkcount)
from
(
    select contentid, 
    case 
    when links.destspacekey='http' then 1 
    when links.destspacekey!='http' then 0
    end as https, count(*) as linkcount
    from links group by contentid, 
    case 
    when links.destspacekey='http' then 1 
    when links.destspacekey!='http' then 0
    end
) as links_per_page
group by https

-- Attachment Size

select count(*), max(filesize), min(filesize), avg(filesize), stdev(filesize), sum(filesize) from attachments
--Users (based on your model)
select count(*) from cwd_user where active='T'
select entity_name, entity_key, date_val from os_propertyentry where entity_key like '%2014%' and date_val < (some date value);

select u.name from external_entities u where u.name not in (select creator from content where contenttype in ('BLOGPOST', 'COMMENT', 'PAGE') and year(creationdate) > 2010);

select distinct entity_name from os_propertyentry where 
entity_key like '%user.last.login.date%' 
and date_val > DATEADD(year,-1,getDate())

--entity_name like '%username%'

select distinct entity_name from os_propertyentry

select distinct entity_key from os_propertyentry  where entity_key like '%user.%'

select distinct entity_name from os_propertyentry where 
entity_key like '%user.last.login.date%' 
and date_val > DATEADD(month,-6,getDate()) and date_val <DATEADD(month,-1,getDate())

--Last login in last 300 days
select distinct entity_name from os_propertyentry where 
entity_key like '%user.last.login.date%' 
and date_val > DATEADD(day,-300,getDate()) 
-- Previous login in last 300 days
select distinct entity_name from os_propertyentry where 
entity_key like '%user.previous.login.date%' 
and date_val > DATEADD(day,-300,getDate()) 

-- Not so frequent users
select ll.entity_name,DATEDIFF(day,pl.date_val,ll.date_val),ll.date_val,pl.date_val
from ( select entity_name,date_val from os_propertyentry where 
entity_key like '%user.last.login.date%' 
--and date_val > DATEADD(day,-300,getDate()) 
) as ll inner join
(select entity_name,date_val from os_propertyentry where 
entity_key like '%user.previous.login.date%' 
--and date_val > DATEADD(day,-300,getDate()) 
) as pl 
on ll.entity_name=pl.entity_name
where DATEDIFF(day,pl.date_val,ll.date_val)>60
--and ll.date_val<DATEADD(month,-12,getDate())
order by 2 desc

-- last login grouped per day
select cast(date_val As Date), count(*) from os_propertyentry where 
entity_key like '%user.last.login.date%' 
group by cast(date_val As Date)
order by 1 desc

-- Avg
select max(logincnt) from (select cast(date_val As Date) as dd, count(*) logincnt 
from os_propertyentry where 
entity_key like '%user.last.login.date%'
and date_val > DATEADD(year,-1,getDate())  
group by cast(date_val As Date)) as cnts

select entity_name,cast(date_val As Date) as dd, count(*) logincnt 
from os_propertyentry where 
entity_key like '%user.last.login.date%'
and date_val > DATEADD(year,-1,getDate())   
group by cast(date_val As Date), entity_name
order by dd desc


select top 100 entity_name, entity_key, date_val from os_propertyentry order by date_val desc

select  u.username, p.date_val from os_user u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.last.login.date'
order by date_val desc;

select u.name, p.date_val from external_entities u
join OS_PROPERTYENTRY p on u.id = p.entity_ID
where entity_key='confluence.user.last.login.date'

order by date_val desc;

References:

@mention

Sample code to upload a file to chatter feed using REST API

January 06, 2014


Step 1: Download required files


  • Download the following files from internet
    • httpcomponents-client-4.2.3-bin.zip
    • org.apache.commons.httpclient.jar
  • From the files, I have added following JARS to my project in eclipse.


  
Screenshot from Ecclipse
Step 2: Get Access Token Through Chrome


  • Login to your Salesforce instance and navigate to Setup -> Create -> Apps. 
  • Create a custom app and fill Client ID and CallBack URL (ex: http://www.google.com) in the following URL. Encode the CallBack URL before passing it as a parameter. 
https://<instance_name>.salesforce.com/services/oauth2/authorize?response_type=token&client_id=<Client_ID_Goes_Here>&redirect_uri=<CallBack_URL>


  • Paste this URL in chrome
    • In Developer Tool -> Network select "preserve log upon navigation"
    • Load the page. We see below page.













    • The page will take us to RemoteAccessAuthorizationPage.apexp which inturn will lead us to _ui/identity/oauth/ui/AuthorizationPage
https://na1.salesforce.com/setup/secur/RemoteAccessAuthorizationPage.apexp?source=l86Hcy6qEKtWNqzXVErZDlmcRQs0D_HBE55MLVuP3x8t5wXkyHYyrVx2Xb5JoWzyVOJrnTqMMaWy_Sfomv.j1xklSQ67NPiEbdAN9EqSTtlyQTuT0J6sGYCxxEBUymytEb6FVKEujyXsKzK3DpNAoCkHeQCmRIT8T2L3ZehdpwPLnRIZlGKw7bNfpwOJmlk4r9NDu8Z7OR9OINbszxRBzD96QX4BjlVItSuo3ftBQLrL2nIq9IMvHo_gVw_wG8M7mj2ySe.3l.MkJBNSbqgqrFdD_1wb47QzQ3.B9iTAWrvAhsuzHdYSicN63Udz.ozgMst_9grKjyv7sB_pOn3HAUxevoeaWVsmttTEwRSI1tA%3D%3D
    • Finally the page will take you to CallBack URL providing it with Access Id. Get Access Id from CallBack URL.  

https://www.google.co.in/#access_token=00DQ0000001f30y%21ARcAQERZPayrNLaOZhOXqH0lbrlTF4sV5iwmc6bzH8Y9VP6uhv77gL9.8YZuT8l1d4e4Km7nVosNbanBaXnQxKV16Res7uZl&instance_url=https%3A%2F%2Fcs3.salesforce.com&id=https%3A%2F%2Ftest.salesforce.com%2Fid%2F00DQ0000001f30yMAA%2F005Q0000001ALKCIA4&issued_at=1370413672564&signature=oyJ0fyAQ0HkTDeY1MO4G1YAdw7ied0BLssxHQ9T7ur8%3D&scope=id+api


  • And fetch the URL fragment to get the access token. We can use this in the program. 

Step 3: Code to post content to Chatter

I have started out of samples posted at https://github.com/forcedotcom/JavaChatterRESTApi
  • In this step we will create post in Account feed  with a file attachment and mention a person (@mention) in the body of the post.
  • The following example posts a comment to a feed and uploads a binary attachment

POST /services/data/v28.0/chatter/feed-items/0D5x00000000RryCAE/comments HTTP/1.1
    Authorization: OAuth 00DD0000000Jhd2!AQIAQC.lh4qTQcBhOPm4TZom5IaOOZLVPVK4wI_rPYJvmE8r2VW8XA.
      OZ7S29JEM_7Ctq1lst2dzoV.owisJc0KacUbDxyae
        Accept: application/json
          User-Agent: Jakarta Commons-HttpClient/3.0.1
            12
              Introducing Chatter REST API Using Chatter API Inputs
                Host: instance_name
                  Content-Length: 978
                    Content-Type: multipart/form-data; boundary=F9jBDELnfBLAVmLNbnLIYibT5Icp0h3VJ7mkI
                      --F9jBDELnfBLAVmLNbnLIYibT5Icp0h3VJ7mkI
                        Content-Disposition: form-data; name="json"
                          Content-Type: application/json; charset=UTF-8
                            { "body":
                              {
                                "messageSegments" : [
                                  {
                                    "type" : "Text",
                                      "text" : "Here's another file for review."
                                        },{

                                        "type": "mention",
                                        "id" : "005D0000001GpHp"
                                        }, {
                                          "type" : "Hashtag",
                                            "tag" : "important"
                                              }, {
                                                "type" : "Text",
                                                  "text" : "Again, please review this as soon as possible."
                                                    }]
                                                      },
                                                        "attachment":
                                                          {
                                                            "attachmentType" : "NewFile",
                                                              "description": "Quarterly review 2012 Q2",
                                                                "title" : "2012_q2"
                                                                  }
                                                                    }
                                                                      --F9jBDELnfBLAVmLNbnLIYibT5Icp0h3VJ7mkI
                                                                        Content-Disposition: form-data; name="feedItemFileUpload"; filename="foo"
                                                                          Content-Type: application/octet-stream; charset=ISO-8859-1
                                                                            This is the content of the file.
                                                                              --F9jBDELnfBLAVmLNbnLIYibT5Icp0h3VJ7mkI--

                                                                              Ubuntu

                                                                              How to expand VirtualBox's Virtual Hard Disk

                                                                              January 05, 2014

                                                                              Last month, I was trying to expand my Ubuntu 12.04 Guest OS's virtual hard disk size from 8 GB to 30 GB. After some time I realized that the process is not so straight forward. Finally I relied on two blog posts to finish my task. 

                                                                              As mentioned in the first article, it's important to understand the prerequisites. 

                                                                              Prerequisites:
                                                                              • Make sure you have the VBoxManage command-line tool installed on your host system.
                                                                              • Download the ISO for a GParted Live CD or else a Linux Live CD using a Linux distribution that includes the GParted partition editor utility.
                                                                              • IMPORTANT: If the virtual disk you want to resize is attached to a virtual machine with snapshots, you will need to delete these snapshots so that all disk state information is merged into the base virtual disk VDI file.

                                                                               I was using VirtualBox 4.3.4.  And, I partitioned the virtual hard disk by using GParted. Somehow, Linux Live CD didn't work for me. As indicated deleting snapshots is mandatory to proceed further. It may take a while based on the number of snapshots you have. 

                                                                              Resize Virtual Disk Using Command Line:

                                                                              As my host system was windows, I ran the following commands to expand the disk size. Syntax of the commands will vary based on the host system.

                                                                              cd "C:\Documents and Settings\myusername\.VirtualBox\HardDisks"
                                                                              "C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" modifyhd OldDisk.vdi --resize 30000 

                                                                              Expand the partition in the resized virtual disk:

                                                                              I found reference#2 very helpful in this regard. The goal is to migrate the newly created space to precede swap partition. 

                                                                              1. Resize your extended partition to include free space
                                                                              2. Move the swap partition (linux-swap) to the end of extended partition
                                                                              3. Shrink (Resize) the extended partition to just fit linux-swap. At this point you can see the unallocated space lying between primary partition and extended partition. As author suggested, apply your steps now. 
                                                                              4. Now, resize the primary partition to include the unallocated space. Apply your steps. 
                                                                              Resize your extended partition to include free space (partition in between ext3 and unallocated)


                                                                              Move the right arrow until Free Space Following becomes zero

                                                                              Click resize/move button


                                                                              Move the swap partition (linux-swap) to the end of extended partition


                                                                              Move the entire linux-swap block to the end block (until free space following is zero. New size remains the same)



                                                                              Shrink (Resize) the extended partition to just fit linux-swap
                                                                              Move left arrow until Free Space Preceding becomes zero



                                                                              Apply your steps before going forward

                                                                              Expand Primary Partition to include unallocated space

                                                                              Resizing of virtual disk is complete. 

                                                                              References: 

                                                                              1. Re-sizing A Virtual-box Virtual Hard Disk http://trivialproof.blogspot.in/2011/01/resizing-virtualbox-virtual-hard-disk.html
                                                                              2. Expand a Linux Disk with Gparted http://blog.mwpreston.net/2012/06/22/expanding-a-linux-disk-with-gparted-and-getting-swap-out-of-the-way/ (All the images in this post are from this blog)

                                                                              Android

                                                                              Rooting HTC Explorer a310e

                                                                              January 02, 2014


                                                                              Step 1: Precautions

                                                                              • Charge your phone fully. I recommend 100%. General recommendation is above 70%.
                                                                              • Enable USB debugging on the phone. (Settings -> Applications -> USB Debugging)
                                                                              • Install following software on your PC. If they already exist, please make sure they are up to date. 
                                                                                • Google Android SDK zip
                                                                                • Java Runtime
                                                                                • HTC Sync (Windows Only)
                                                                              • Backup data. Below are the steps I have done. But, the steps are not exhaustive. Don't neglect these steps as unlocking bootloader may delete all your personal data.  
                                                                                • I backed up my data using HTC Sync
                                                                                • Additionally, I backed up the entire SD Card on my local machine
                                                                              • One useful app I found is Backup SMS Contacts https://play.google.com/store/apps/details?id=com.idea.backup.smscontacts

                                                                              Step 2: Prepare

                                                                              • Download A310E Recovery File & Superuser.zip
                                                                              • Get ready to unlock your bootloader
                                                                                • Navigate to  http://www.htcdev.com/bootloader/ 
                                                                                • Create an account
                                                                                • In the supported devices drop down select "All Other Supported Models" & Click "Begin Unlock Bootloader"
                                                                                • Read the prompt, accept legal terms and go on to unlock instructions.
                                                                                • Switch off your mobile (Should be charged 100%) and follow the steps
                                                                                • After selecting "Fastboot", connect your device to PC via USB cable. The message the screen should read as "Fastboot USB"
                                                                                • No need to download the files mentioned in Step - 5. Navigate to Android SDK folder. In the command prompt navigate to platform-tools. The following files are available in the folder. 
                                                                                  • fastboot.exe
                                                                                  • adb.exe
                                                                                  • AdbWinApi.dll
                                                                                  • AdbWinUsbApi.dll
                                                                                  • source.properties
                                                                                  • Notice
                                                                                • In the command prompt,  type the following command fastboot oem get_identifier_token
                                                                                • Copy token without spaces at the start and end




















                                                                                • Paste the code in Step 7 & Submit. HTC will send a mail to the registered email address. 
                                                                                • Download the Unlock_code.bin to selected folder (I copied the file to "platform-tools")  folder. 
                                                                                • Run the following command in command prompt. 
                                                                                  • fastboot flash unlocktoken Unlock_code.bin
                                                                                • Read Disclaimer carefully. Select "Yes" in response to the disclaimer. 
                                                                                • Phone will reboot.
                                                                                • Take a note of Step -10. But, you don't need to execute it. 
                                                                              Step 3: Root the device
                                                                              • Extract A310E Recovery folder to where fastboot.exe is present (I copied them to "platform tools" folder)
                                                                              • Copy Superuser.zip to your memory card
                                                                              • Switch off the phone and start the phone in Bootloader mode by simultaniously pressing Volume down and Power buttons 
                                                                              • Select Fastboot and connect the phone to PC
                                                                              • Run recovery.bat
                                                                              • Reboot the phone and move Superuser.zip to SD Card
                                                                              • Get ready to flash (install) Superuser.zip
                                                                                • Disconnect phone from PC
                                                                                • Switch off the phone and start the phone in Bootloader mode by simultaneously pressing Volume down and Power buttons
                                                                                • Select Recovery -> Choose Zip from SD Card using power button
                                                                                • Select Superuser.zip and Confirm the installation
                                                                                • Once the installation is finished, navigate back to the main menu and reboot the phone. Your phone is successfully rooted. 
                                                                              Below are my observations from this experience
                                                                              1. I have never removed battery in the whole process, even though  removal is suggested  in some blog posts. Instead, I made sure I have given enough time for the phone to shutdown. 
                                                                              2. In my case, personal data is not erased. So, I didn't restore my data from backups. Remember, you shouldn't do a factory reset after rooting the phone.  If you did, you have to unroot the phone and repeat the rooting process. 
                                                                              3. Overall, it's an easy process provided one pay attention to detail. 

                                                                              Next, I am going to try increasing internal memory

                                                                              References:

                                                                              1. http://hackayingtricks.wordpress.com/2013/04/09/how-to-root-htc-explorer-a310e/
                                                                              2. Unlock bootloader http://www.creotix.com/tech/how-to-unlock-bootloader-on-htc-explorer-a310e-pico/1574/
                                                                              3.  Unlock bootloader http://xperiahtcrootingrom.com/how-to-unlock-htc-explorer-bootloader-pico/
                                                                              4. http://www.htcdev.com/bootloader/

                                                                              Popular Posts

                                                                              Twitter