Confluence Nested Macro Highlighter

Have you ever clicked edit on a Confluence page only to be presented with a screen that more or less looks like a jail cell with nested macro upon nested macro in a maze of indistinguishable grey borders? Cold sweats ensue as you realize you need to pick the correct spot to add your new content and if you pick the wrong spot the fate of the whole company … possibly the world could be placed in peril. So you fall back to your training. You place your finger on your screen making sure to keep it steady Then you slowly scroll the page following the line down the page till you hit the bottom of the macro. Making sure not to move that finger off the screen you move the mouser pointer just below bottom border and click. Cursor placed. You sigh with relief. Finally, you pull out a wet napkin and clean the finger print smudge off your screen. You’ve just prevented another catastrophe and the world will continue to turn for another day.

Seriously! You’ve never experienced that? Huh!? Ok, well maybe I’m the only person that happens to. However, I can bet you’ve still seen a Confluence page with LOTS of macros and gotten confused as to which was which.

That is the inspiration for this browser bookmarklet. Simply drag the below bookmarklet link to your browser’s bookmarks bar. When you are in edit mode you can click the bookmarklet to change the borders of your macros from this …

to this …

Each macro now has a distinctive color making it easy to see which border goes with which macro. Problem solved! Unfortunately, I could not change the color of the macro header. It’s actually a dynamically generated image that is LONG with the grey color hard-coded into it. Also, I tried hard to make sure that each color is distinct from the colors next to it so that it is easy to see which is which. I even thought about color-deficient people with the color choices … as I myself am red/green color-deficient. 🙂 Hope it is helpful. Let me know if you use it and it has helped you.

Bookmarklet:
Nested Macro Highlighter <- drag this to your bookmarks toolbar

If you would like to change out the colors or add more nesting levels the below JavaScript is the code for the bookmarklet. Just change/add values to the colors array to fit your needs. Once you’ve done that you can use a site like Bookmarklet Maker to turn it into your own personalized bookmarklet.

// Add/Change the hex color values to add more levels of nesting or choose different colors.
var colors = ['#800000','#F58231','#3CB44B','#42D4F4','#000075','#FFE119','#808000','#469990','#911EB4','#FFD8B1','#E6BEFF','#9A6324','#FABEBE','#000000'];

// Don't edit past this line
var styles = '';
for (var i = 0; i < colors.length; i++) {
	styles += '.wiki-content table.wysiwyg-macro table.wysiwyg-macro';
	for (var x = 0; x < i; x++) {
		styles += ' table.wysiwyg-macro';
	}
	styles += ' {\n';
	styles += '    background-color: ' + colors[i] + ';\n';
	styles += '}\n';
}
AJS.$('iframe').contents().find('head').append($('<style type="text/css">' + styles + '</style>'));

FUME – A Better Confluence User Macro Editor

One of the best and most overlooked aspects of Confluence, both by Atlassian and Confluence administrators, is user macros. There are so many useful scenarios for user macros. Here are some:

  • Templated snippets
  • Overriding built in macros example with task list report
  • Quickly creating your own macros
  • Inserting arbitrary html/css/javascript into a page without having to enabled the html macro

However, there are some big usability issues with the user macro editor. First it’s super easy to accidentally delete one. The delete link is right next to the edit link and seriously, there is no confirmation on the delete link. It’s just gone. Ack!

Second, the link to create new user macros is at the bottom of the page. If you have more that what can fit on a screen you have to scroll down to get to the link to create a new one … this just gets worse over time as you create more.

Third, the template box in the editor is just a plain old text area … no line numbers, not syntax highlighting, it’s not even a mono-spaced font! Grr!

Fourth, the cancel button doesn’t ask you to confirm canceling the edit if you have made changes to the user macro and since it sits right next to the save button it’s easy to miss. Hope you can recreate your work quickly.

Finally, every time you save it kicks you back to the list page. So, if you want to make some changes and try it out on a page you have to click back into the editor every time you save and whoops you accidentally just clicked delete instead of edit! There goes all that work.

So, without further ado … FUME. Fantastic user macro editor. The fantastic part is really just because I needed a word that ended in “ume” and that was the only word I could think of. Really it’s not all that fantastic … maybe just great, but gume isn’t even a word. Then I thought “How about  great looking user macro editor”, but that would be glume and … well … yeah, that kinda defeats the purpose. So, FUME it is. All in all I think it is a much better editing experience than the default setup. Here are some of the features:

  • Copy that “Create a User Macro” link to the top of the list page … no more scrolling
  • Delete confirmation on the list page
  • Template box changed to a source code editor with (Ace editor):
    • monospaced font
    • line numbers
    • syntax highlighting
    • find and replace
    • code folding
    • column select
  • Confirmation on cancelling edits of the user macro if the template has been changed
  • Asynchronous user macro saves
  • It will do your dishes and laundry … ok, not quite yet

Update 4/9/2018:

Ignore the “How to Setup” section below. I’ll leave it there, however, for the sake of continuity. I decided to package this up as an add-on in the Atlassian Marketplace. I named it Enhanced User Macro Editor (EUME … pronounced you-me … it’s a stretch I know). It seemed a bit more humble of a name and is more descriptive of what it is. I hope it is as useful for you as it has been for me. Marketplace link below.

Enhanced User Macro Editor

How to Setup

  • Download these CSS and Javascript files. (right click the links and choose “Save link as”)
  • Place them on a web server where they will be web accessible to your user macro editors.
  • Add this to the end of Confluence Admin -> Custom HTML -> At end of the BODY
<!--
*****************************************
* Fantastic User Macro Editor           * *****************************************
-->
<link rel="stylesheet" type="text/css" href="http(s)://{your server}/path/to/fume.css">
<script src="http(s)://{your server}/path/to/fume.js" type="text/javascript"></script>
  • Enjoy editing your user macros.  🙂

Screenshots

User Macro List

User Macro List Page

User Macro Template Editor

User Macro Template Editor

Remove Jira Issue Attachments by MD5 Hash Redux

In my previous post Remove Jira Issue Attachments by MD5 Hash I showed how to remove attachments from JIRA based on the MD5 hash of the attachment.

I was feeling pretty good after writing that post and having eaten my doughnut. So, I went to tell a couple of my colleagues about it. This was their reaction …

So, you expect me to …

  1. know what an MD5 hash is?
  2. know how to get the MD5 hash of a file?
  3. know where to find this script to add the hash to?
  4. not mess the whole thing up in the process?

Um … uhh … yes? Ok, so maybe my approach isn’t super easy except to the programmer type. And now that I think about it I don’t want to have to be the one to always fix these. So, back to the drawing board. Let’s get this right.

So, I need to make it easy for others than myself to help maintain. Maybe if I made a way for my colleagues to take an attachment from an issue ticket and simply drop to a centralized storage location that could be scanned by the script … yeah that could work. It involves no knowledge of MD5 hashes or scripting and should be easy for pretty much anyone to do.

Now if I only had a location where we could place these attachments. A place that JIRA is able to scan. A place that all my colleagues have easy access to. If only such a place actually existed … hmm … oh, wait!! I could just have them attach the files to another JIRA ticket that will be used as a control ticket of sorts. Any attachments attached to this ticket would be compared against by the script and if a match is found then the issue attachment is deleted. (insert Handel’s Messiah playing in my head here)

The great thing is that most of my script doesn’t really need to be changed. All I need to do is specify a control ticket key in the script and have the script build the list of hashes based on that ticket. Here is my ticket …

And here is the new script. I’ve cleaned it up a little from the last version and removed a call to a method that is currently set as deprecated. It still worked even with the call, but best to get rid of that call before Atlassian removes the method altogether. Simply replace “{Project Key}-{Issue Number}” on line 12 with the issue key that holds your attachments to remove. So, if for instance the issue is in the FOO project and the issue number is 789 then that line would look like this …

def controlIssue = “FOO-789”;

import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.issue.AttachmentManager;
import com.atlassian.jira.issue.attachment.FileSystemAttachmentDirectoryAccessor
import com.atlassian.jira.issue.Issue;
import com.atlassian.jira.issue.IssueManager;
import java.security.*;

/***********************************************************************************/
/* This is the ticket that has the attachments on it to compare MD5 hashes against */
/***********************************************************************************/

def controlIssue = "{Project Key}-{Issue Number}";

/***********************************************************************************/
/*                                                                                 */
/***********************************************************************************/

/************************************************************/
/* Don't edit below this unless you know what you are doing */
/************************************************************/

// Get the attachment hashes for our control issue to compare against
def attachmentHashes = getAttachmentHashesFromIssue(controlIssue);

// Obviously we don't want to run this on the control issue ... only on other issues.
if(event.issue.key != controlIssue) {
    deleteMatchingAttachments(attachmentHashes);
}

public void deleteMatchingAttachments(List<String> deleteHashes){
    def issue = event.issue;
    def attachmentManager = ComponentAccessor.getComponent(AttachmentManager);
    def attachments = issue.getAttachments();
    def attachmentFile = null;
    def bytes = null;
    def md = MessageDigest.getInstance("MD5");
    def digest = null;
    def hash = "";

    // Loop through each attachment on the issue
    for(a in attachments) {
        attachmentFile = getAttatchmentFile(issue, a.getId());
        bytes = getBytesFromFile(attachmentFile);
        digest = md.digest(bytes);
        hash = String.format("%032x", new BigInteger(1, digest));

        // Compare hash to the list of hashes we don't want
        for(h in deleteHashes) {
            if(hash == h) {
                attachmentManager.deleteAttachment(a);
                break;
            }
        }
    }
}

public List<String> getAttachmentHashesFromIssue(String controlIssueKey) {
    def deleteHashes = [];
    def attachmentManager = ComponentAccessor.getComponent(AttachmentManager);
    def issueManager = ComponentAccessor.getComponent(IssueManager);
    def issue = issueManager.getIssueObject(controlIssueKey);
    def controlIssueAttachments = attachmentManager.getAttachments(issue);
    def attachmentFile = null;
    def bytes = null;
    def md = MessageDigest.getInstance("MD5");
    def digest = null;
    def hash = "";

    // Get hashes for all the attachments in the control issue
    for(a in controlIssueAttachments) {
        attachmentFile = getAttatchmentFile(issue, a.getId());
        bytes = getBytesFromFile(attachmentFile);
        digest = md.digest(bytes);
        hash = String.format("%032x", new BigInteger(1, digest));

        deleteHashes.add(hash);
    }

    return deleteHashes;
}

public byte[] getBytesFromFile(File file) throws IOException {        
    def length = file.length();

    if (length > Integer.MAX_VALUE) {
        throw new IOException("File is too large!");
    }

    def bytes = new byte[(int)length];

    def offset = 0;
    def numRead = 0;

    def is = new FileInputStream(file);
    try {
        while (offset < bytes.length && (numRead=is.read(bytes, offset, bytes.length-offset)) >= 0) {
            offset += numRead;
        }
    } finally {
        is.close();
    }

    if (offset < bytes.length) {
        throw new IOException("Could not completely read file " + file.getName());
    }

    return bytes;
}

public File getAttatchmentFile(Issue issue, Long attatchmentId){
    return ComponentAccessor.getComponent(FileSystemAttachmentDirectoryAccessor.class).getAttachmentDirectory(issue).listFiles().find({
        File it->
         it.getName().equals(attatchmentId.toString())
    });
}

And now my colleagues sing my praises (in my dreams) instead of cursing my name (which maybe still happens when I make hard to update workflows). Oh well, you live and learn.

Remove Jira Issue Attachments by MD5 Hash

Recently, we started using Jira at work to track some IT related things. Thus, I have quickly had to learn how to administer Jira. One thing that I really wanted to get set up and working well was the ability to respond to an email sent by the system via a reply email and have that filed in the ticket. That wasn’t too terribly hard to set up. First I created a mailbox for Jira to check in our mail system. Then I set up a mail handler to pull the reply emails in. I settled for using a “Add a comment before a specified marker or separator in the email body” handler so that I could provide a regular expression to define how to extract just the reply. The below screenshots show my setup for this.

The Split Regex field below is this /[Ff]{1}rom:[^\n]+{myuser}@{mydomain}\.{extention}/. Replace the {myuser}, {mydomain}, and {extention} parts with the email address of the account that Jira mails as. So, if your Jira system sends email as jira@coolstuff.org the above expressions would look like this /[Ff]{1}rom:[^\n]+jira@coolstuff\.org/. This will split the email where it sees the first line that looks something like this From: Jira [mailto:jira@coolstuff.org] or from: Jira [mailto:jira@coolstuff.org] … which is how Outlook formats its replies.

So, I got that part working great now I can go get a doughnut right? Nope! Turns out every time I would reply the issue icon and the image attachment in my email signature would get attached to the issue … over and over and over. So, before long I had a veritable glut of the same images attached to the issue. Grrrr!! So, I asked myself “Self, what can we do about this?” To which I so helpfully replied to myself, “Go check the Atlassian Marketplace, Atlassian Community, and Google for an answer.” After a couple grueling hours of trying to find the answer I came to the stark conclusion that there wasn’t one. Double grrrr!!

After a bit of thinking I decided I could just scan the images against a set of MD5 hashes to exclude when the issue is updated and here is the fruit of my labor. This solution requires having ScriptRunner for Jira installed. If you don’t have it … well, you should. The possibilities are pretty endless with what you can do with it. I created a Script Listener that would respond to the “Issue Updated” event.

And the actual contents of the script file.

import java.security.*;
import com.atlassian.jira.issue.Issue;
import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.issue.AttachmentManager;
import com.atlassian.jira.util.AttachmentUtils;

// Add new MD5 hashes to the below array to auto remove them when they are attached to the ticket.
// This is helpful to get rid of things like images in email signatures, JIRA issue type icons in the email, etc.
def deleteHashes = [
    "eaf938ae5025889b60029d6d839d19db", //JIRA blue check mark
    "f370264d9a3d1b92666419e6ecc102ef", //email signature logo v1
    "662b051e6082e4499079ddc18e5eb302", //email signature logo v2
    "a4ab3c522859297084064502477effd8"  //Pulse line icon
];

def issue = event.getIssue();
def attachments = issue.getAttachments();
def attachmentFile = null;
def bytes = null;
def md = MessageDigest.getInstance("MD5");
def digest = null;
def hash = "";
def manager = ComponentAccessor.getComponent(AttachmentManager)

for(a in attachments) {
    attachmentFile = AttachmentUtils.getAttachmentFile(a);
    bytes = getBytesFromFile(attachmentFile);
    digest = md.digest(bytes);
    hash = String.format("%032x", new BigInteger(1, digest));

    for(h in deleteHashes) {
        if(hash == h) {
            manager.deleteAttachment(a);
            break;
        }
    }
}

public byte[] getBytesFromFile(File file) throws IOException {        
    // Get the size of the file
    long length = file.length();

    // You cannot create an array using a long type.
    // It needs to be an int type.
    // Before converting to an int type, check
    // to ensure that file is not larger than Integer.MAX_VALUE.
    if (length > Integer.MAX_VALUE) {
        log.info("File is too large!");

        // File is too large
        throw new IOException("File is too large!");
    }

    // Create the byte array to hold the data
    byte[] bytes = new byte[(int)length];

    // Read in the bytes
    int offset = 0;
    int numRead = 0;

    InputStream is = new FileInputStream(file);
    try {
        while (offset < bytes.length && (numRead=is.read(bytes, offset, bytes.length-offset)) >= 0) {
            offset += numRead;
        }
    } finally {
        is.close();
    }

    // Ensure all the bytes have been read in
    if (offset < bytes.length) {
        log.info("Could not completely read file " + file.getName());
        throw new IOException("Could not completely read file " + file.getName());
    }

    return bytes;
}

Now, when I respond to an issue via email if any of the attachments on the issue match any of the MD5 hashes at the top of the script that attachment will get deleted from the issue. And if I find that there are other attachments we start seeing like this on a regular basis all I have to do is add the MD5 hash to the list and save the script … problem solved.

Now, about that doughnut.

Update: So, the whole editing the script and having others put in MD5 hashes part … yeah, that went over like a lead balloon. Here is an updated version that is much easier to administer.

Parsing HL7 with Splunk

At my job I do a fair amount of work with HL7. If you work in the medical field you probably know that HL7 is the language that medical systems use to talk with each other. It’s a fairly simple format that uses carriage returns and pipes to delimit fields … ok there are a few other delimiters as well, but the carriage returns and pipes are the big ones. Below is an example HL7 message.

MSH|^~\&|MegaReg|XYZHospC|SuperOE|XYZImgCtr|20060529090131-0500||ADT^A08|01052901|P|2.3
EVN||200605290901||||200605290900
PID|||56782445^^^UAReg^PI||KLEINSAMPLE^BARRY^Q^JR||19620910|M||2028-9^^HL70005^RA99113^^XYZ|260 GOODWIN CREST DRIVE^^BIRMINGHAM^AL^35209^^M~NICKELL’S PICKLES^10000 W 100TH AVE^BIRMINGHAM^AL^35200^^O|||||||0105I30001^^^99DEF^AN
PV1||I|W^389^1^UABH^^^^3||||12345^MORGAN^REX^J^^^MD^0010^UAMC^L||67890^GRAINGER^LUCY^X^^^MD^0010^UAMC^L|MED|||||A0||13579^POTTER^SHERMAN^T^^^MD^0010^UAMC^L|||||||||||||||||||||||||||200605290900
OBX|1|NM|^Body Height||1.80|m^Meter^ISO+|||||F
OBX|2|NM|^Body Weight||79|kg^Kilogram^ISO+|||||F
AL1|1||^ASPIRIN
DG1|1||786.50^CHEST PAIN, UNSPECIFIED^I9|||A

Each line in the message is called a segment and each segment can be divided into fields based on the pipes. For instance the third line is the PID segment which has patient information such as the MRN (PID 3), patient name (PID 5), birth date (PID 7), etc. The PV1 segment has information that relates to the patient visit. It is a fairly concise format without much overhead and as such is perfect for medical institutions where these kinds of messages are flowing constantly throughout the day.

The Problem

In a typical medical environment there will be a system called the HL7 routing engine that serves as an intermediary between all the various medical systems in the clinic or hospital. The HL7 engine can route messages to one or various systems and transforms them en-route based on rules. Most HL7 engines have the ability to log the messages sent through them in some format.

Often times there may be a need to lookup what messages were sent to various system to troubleshoot problems. In many cases there is no great means of searching through the thousands or even hundreds of thousands of messages sent each day to troubleshoot these issues.

The Solution

About a year ago I was approached by some folks from Splunk about creating a Technical Add-on (TA) for Splunk for parsing HL7. After many months of working with one of their engineers named Joe Welsh we were able to release the free ‘HL7 Add-On for Splunk“. We tested the add-on by throwing millions of our HL7 messages at it to make sure it parsed the messages correctly.

With this TA we can have Splunk monitor our HL7 logs and in real-time are able to quickly search those logs to troubleshoot issues, report on failed messages, and view dashboards to monitor the health of our HL7 environment. I have been super pleased with the results.

If you are a medical institution that uses Splunk check out the add-on … it’s free. See what awesome things you can do with Splunk and HL7. Let me know in the comments if you have found it useful.

Splunkbase Link
https://splunkbase.splunk.com/app/3283/

Doing a select on a stored procedure

I came across a scenario today where I just wished that I could do a SQL select on a stored procedure. Akin to something like this …

select * from (exec myStoredProcedure) where column = 'value'

Alas, this is not possible … urg! However, I did manage to find a workaround that gives me basically what I wanted. It involves turning on some insecure settings in SQL sever. So we want to make sure we turn them back off after the fact.

The gist of the below SQL is as follows. We  first enable the insecure settings. Next we execute the stored procedure using openrowset and store the results into a temp table. Once we have the data in a temp table we can work with it just like any other table, yeay!! Finally, we want to make sure to turn off the openrowset feature again.

/**********************************/
/* Allow openrowset               */
/**********************************/
sp_configure 'Show Advanced Options', 1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go
/***********************************/

/***********************************/
/* This is the query               */
/***********************************/
select * into #tempTable from openrowset('SQLNCLI', 'Server=(local);Trusted_Connection=yes;','exec myStoredProcedure')
select * from #tempTable -- <-- Do your stuff here
/***********************************/


/**********************************/
/* Set the insecure settings back */
/**********************************/
sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go
sp_configure 'Show Advanced Options', 0
go
reconfigure
go
/**********************************/

Powered by WordPress, running on raspberries

The Issue

My Raspbery Pi running WordPressMeet my new web server! Isn’t it cute? Up till recently I have been running my website on my home machine. This has worked fine as this site doesn’t get a ton of traffic and my home machine is fairly beefy. However, I’ve always been slightly leery about running my web server on my main computer just for security reasons. If it were to get hacked I could be potentially opening up everything to the hackers. Not only that, in order for the site to be up I had to leave my home machine running 24/7. This is not great on the electricity bill. Also, during the summer months in our not so air-conditioned house it actually adds significantly to the heat. Which is not too fun for my wife who has to been home all day in the heat while I sit at work in the air-conditioning.

The Solution

So, I started looking around for a not too expensive solution to this. As I researched I started seeing more and more people running WordPress on a Raspberry Pi. Now, I can muck my way around Linux, but it’s certainly not my fortĂ©. Fortunately, I found a great tutorial for installing WordPress on a Raspberry Pi that even a Linux noob like me can follow.

It worked great! I was able to get WordPress set up, move my content over and flip the port forwarding over to the Pi within a few hours. I also set up a dynamic DNS client on the PI to keep my address current with my domain registrar. I now have a separate server, that runs on pennies, puts out little to no heat, is completely quiet, and cost very little to purchase. I could not be happier.

Confluence Macro Usage Report

When it comes to upgrading Confluence it is handy to know which macros are being used throughout the site so that you can test the upgrade and check that the used macros still work correctly. The below user macro will generate a Confluence macro usage report that lists all of the macros and user macros that are used in your Confluence installation along with a link for each macro so that you can spot check the pages that use that macro … or you could check all the pages if you are that thorough. This requires at least Confluence 5.5 as the MacroBrowserManager.getMacroSummaries() method was added in 5.5.

Report Example:

Macro Usage Report

Macro Body Processing:
No macro body

Template:

## Developed by: Davin Studer
## Date created: 12/17/2014
## @param Space:title=Space|type=spacekey|desc=This will restrict the macro usage report to a specific space.

#set($containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager'))
#set($getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null))
#set($containerManager = $getInstanceMethod.invoke(null,null))
#set($containerContext = $containerManager.containerContext)
#set($macroBrowserManager = $containerContext.getComponent('macroBrowserManager'))
#set($allMacros = $macroBrowserManager.getMacroSummaries())

#########################################################################################
## This is used for getting around velocity issues when writing jQuery.                ##
#########################################################################################
#set( $d = '$' )

#########################################################################################
## Populate the macro information into a string that we will use as a JS object array. ##
#########################################################################################
#set ($i = 0)
#set($macroObjects = "")
#foreach($macro in $allMacros)
  #if($i != 0)
    #set($macroObjects = $macroObjects + ",")
  #end
  #set($macroObjects = $macroObjects + "{title:'" + $macro.getTitle().getKey().replace("'","\'") + "', name:'" + $macro.getMacroName().replace("'","\'") + "'}")
#set ($i = $i + 1)
#end

#########################################################################################
## Decide which space to search.                                                       ##
#########################################################################################
#if ($!paramSpace && $!paramSpace != "")
    #set ($searchSpace = $!paramSpace)
#else
    #set ($searchSpace = "conf_all")
#end

<script type="text/javascript">
var queue = {
    macros: [$macroObjects],
    current: 0,
    start: function () {
        this.macros.sort(compare);
        this.current = 0;
        this.next();
    },
    next: function () {
    	if (this.current >= this.macros.length) {
            AJS.$('#queryStatus span').removeClass('aui-lozenge-current');
            AJS.$('#queryStatus span').addClass('aui-lozenge-success');
            AJS.$('#queryStatus span').text('Report Complete');
            AJS.tablessortable.setTableSortable(AJS.$('#macroUsageReport table'));
            return null;
		}
		else {
			this.current += 1;
			lookupMacro(this.macros[this.current - 1]);
		}
    }
};

function lookupMacro(macro) {
    var html = '';
    var searchURL = '';
    
    AJS.$('#queryStatus span').text('Getting counts for macro: ' + macro.title);
    
    searchURL = AJS.params.baseUrl + '/rest/searchv3/1.0/search?where=${searchSpace}&spaceSearch=true&queryString=macroName:' + encodeURIComponent(macro.name);
    AJS.${d}.ajax(
        {
            type: 'GET',
            url: searchURL,
            dataType: "json",
            timeout:60000,
            success: function(data) {
                if(data.total > 0) {
                    var spaces = '';
                    for(var i = 0; i < data.results.length; i++)
                    {
                        if(spaces.indexOf(data.results[i].searchResultContainer.name) == -1)
                        {
                            spaces = spaces == '' ? '' : spaces + ', ';
                            spaces = spaces + data.results[i].searchResultContainer.name;
                        }
                    }
                    html = '<tr>';
                    html += '   <td class="confluenceTd">' + macro.title + '</td>';
                    html += '   <td class="confluenceTd">' + macro.name + '</td>';
                    html += '   <td class="confluenceTd">' + data.total + '</td>';
                    html += '   <td class="confluenceTd">' + spaces + '</td>';
                    html += '   <td class="confluenceTd"><a href="' + AJS.params.baseUrl + '/dosearchsite.action?where=conf_all&spaceSearch=true&queryString=macroName:' + encodeURIComponent(macro.name) + '" target="_blank">macro usage</a></td>';
                    html += '</tr>';
                }
                AJS.$('#macroUsageReport table tbody').append(html);
                
                queue.next();
            },
            error: function (x, y, z) {
                AJS.$('#queryStatus span').removeClass('aui-lozenge-current');
                AJS.$('#queryStatus span').addClass('aui-lozenge-error');
                AJS.$('#queryStatus span').text('Error getting counts for macro:' + macro.title);
            }
        }
    );
}

function compare(a, b) {
    if (a.title < b.title) {
        return -1;
    }
    if (a.title > b.title) {
        return 1;
    }
    return 0;
}

AJS.toInit(function(){
    queue.start();    
});
</script>
<div id="queryStatus">
    <span class="status-macro aui-lozenge aui-lozenge-current"></span>
</div>
<div id="macroUsageReport">
    <table class="confluenceTable">
        <thead>
            <tr>
                <th>Macro Title</th>
                <th>Macro Name</th>
                <th>Times Used</th>
                <th>Spaces Used In</th>
                <th>Pages Used On</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
</div>

Referenced on answers.atlassian.com:
https://answers.atlassian.com/questions/29262695/answers/29263237
https://answers.atlassian.com/questions/155280/answers/11465880
https://answers.atlassian.com/questions/13411280/answers/13411414
https://answers.atlassian.com/questions/11463431/answers/11464460
https://answers.atlassian.com/questions/11460827/answers/11464595
https://answers.atlassian.com/questions/11465513/answers/11465876
https://answers.atlassian.com/questions/11967411/answers/11967421

Bookmarklet for Creating a CSV From an HTML Table

We have a wiki at work that we use for documentation called Confluence. It has the ability to export pages to Word or PDF, but a lacking feature is the ability to take a table and export it for Excel. So, I decided to create a bookmarklet that will allow you to select an HTML table on any web page and create a CSV file from that table that can be downloaded to your machine. Below is the bookmarklet. Just drag it to your bookmarks toolbar. Then give it a shot by clicking the bookmarklet and the below example table should get a link right before it that says “Export to CSV”. Click that link and you will be prompted to download the CSV version of that table. Let me know if it is useful for you.

On thing of note, this will not work correctly in Internet Explorer 9 and below as IE will not allow data uri’s for anything other than images.

Bookmarklet:
Export to CSV <- drag this to your bookmarks toolbar

Update:
05/08/2019 – Enhanced to not kill line breaks.
09/16/2016 – Now works with IE 10+.
04/19/2016 – Fixed issue with tables that have header cells not on top.

Example Table:

Heading 1Heading 2Heading 3Heading 4Heading 5Heading 6
Data 1, 1Data 1, 2Data 1, 3Data 1, 4Data 1, 5Data 1, 6
Data 2, 1Data 2, 2Data 2, 3Data 2, 4Data 2, 5Data 2, 6
Data 3, 1Data 3, 2Data 3, 3Data 3, 4Data 3, 5Data 3, 6
Data 4, 1Data 4, 2Data 4, 3Data 4, 4Data 4, 5Data 4, 6
Data 5, 1Data 5, 2Data 5, 3Data 5, 4Data 5, 5Data 5, 6
Data 6, 1Data 6, 2Data 6, 3Data 6, 4Data 6, 5Data 6, 6

Bookmarklet Source: 

javascript:(function(){
    function getJavaScript(url, success) {
        var script = document.createElement('script');
            script.src = url;
        var head = document.getElementsByTagName('head')[0],
            done = false;
        script.onload = script.onreadystatechange = function(){
            if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) {
                done = true;
                success();
                script.onload = script.onreadystatechange = null;
                head.removeChild(script);
            }
        };
        head.appendChild(script);
    }
    function addCSVLinks() {
        jQuery('.csvLink').remove();
        
        jQuery('table').each(function(index){
            jQuery(this).attr('data-csvtable', index).before('<a href="#" class="csvLink" data-forcsvtable="' + index + '">Export to CSV</a>');
        });
        
        jQuery('.csvLink').click(function(){
            var text = '';
            var csvTableIndex = jQuery(this).attr('data-forcsvtable');
            jQuery('table[data-csvtable="' + csvTableIndex + '"] tr').each(function(){
                jQuery('td, th', this).each(function(index){
                    if(index != 0) {
                        text += ',';
                    }
                    text += '"' + formatedText(jQuery(this).html()) + '"';
                });
                text += '\r\n';
            });
            jQuery('.csvLink').remove();
            downloadCSVFile('TableExport.csv', 'text/csv', text);
        });
    }
	function formatedText(html) {
		var ret = html;
		
		//replace line breaks
		ret = ret.replace(/\n/g, ' ');
		
		//replace tabs
		ret = ret.replace(/\t/g, ' ');
		
		//replace multiple spaces
		ret = ret.replace(/\s+/g, ' ');
		
		//Fix html encoded characters
		ret = decodeHtml(ret);
		
		//Deal with lines breaks and paragraphs
		ret = ret.replace(/<br>/ig, '\n<br>');
		ret = ret.replace(/<br/ig, '\n<br ');
		ret = ret.replace(/<p/ig, '\n<p ');
		
		//Deal with quotes
		ret = ret.replace(/"/ig, '""');
		
		//Deal first character being line break
		ret = ret.replace(/^\n/, '');
		
		//Remove HTML tags
		ret = ret.replace(/(<([^>]+)>)/ig,"");

		return ret;
	}
	function decodeHtml(html) {
		var txt = document.createElement('textarea');
		txt.innerHTML = html;
		return txt.value;
	}
    function downloadCSVFile(filename, mime, text) {
        if (window.navigator.msSaveOrOpenBlob){
            // IE 10+
            var blob = new Blob([decodeURIComponent(encodeURI(text))], {
                type: 'text/csv;charset=utf-8'
            });
            window.navigator.msSaveBlob(blob, filename);
        } else {
            var pom = document.createElement('a');
            pom.setAttribute('href', 'data:' + mime + ';charset=utf-8,' + encodeURIComponent(text));
            pom.setAttribute('download', filename);
            document.body.appendChild(pom);
            pom.click();
            document.body.removeChild(pom);
        }
    }
    if(typeof jQuery == 'undefined') {
        getJavaScript(
            '//code.jquery.com/jquery-latest.min.js',
            function(){
                addCSVLinks();
            }
        )
    } else {
        addCSVLinks();
    }
})();

Microsoft, the 90’s wants its security policy back!

Today, I went to change the password on my Microsoft account from a shortish hard to remember and type password to a nice and secure long and easy to type passphrase. With all of the hubbub these days around hacked accounts I want to make sure that my accounts are nice and secure. I just wish Microsoft was on-board with that.

Phrase1I went to my account and put in a pass phrase that seems pretty decent to me. Here is the first message I got. It seems that my nineteen character passphrase is too long. Why is this an issue I ask? Why does Microsoft care if I want to have a long password? If I want to take on the burden of typing those extra characters what is it to them? This is not a limitation with any other Microsoft system I have ever used (personal machine and work machines). I’m sure their own systems are based on their own stack, so this is an artificial limit that they have put in that has no real value except to make my account easier to hack.

Phrase2Ok, since I can only have up to sixteen characters in my password I guess I’ll have to comply. Sixteen characters is still pretty long right? So, I swapped out some words and made a few tweaks only to be presented with this. REALLY!? By the way, the character that they do not like is a space. So, I thought I would see what characters they do allow. They allow A-Z, a-z, 0-9, and every special character printed on your keyboard … just not space. Ugh!! It seems they are going out of their way to discourage pass phrases when it has been shown time, and again, and again, and again that pass phrases are more secure than passwords.

So, in the end, I am forced to bow to their ridiculous security policy that excludes one character and forces me to 16 characters. What do you think?