August 17th, 2012
SQL trigger to dump an entire modified/deleted row as XML into a second audit table. It’s not pretty, but it was a quick way to track changes in certain tables.
The trigger:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
| SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE Name = 'trgAudit_tableName') > 0 BEGIN
DROP TRIGGER [dbo].[trgAudit_tableName]
END
GO
CREATE TRIGGER [dbo].[trgAudit_tableName]
ON [dbo].[tableName]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Xml XML
--UPDATE TRIGGERED
IF (SELECT COUNT(*) FROM Inserted) > 0 BEGIN
--RECORDS ARE IN THE INSERTED TABLE
SET @Xml = (
SELECT * FROM Inserted AS tableName
FOR XML AUTO
)
--COMBINE XML AND DATA COLUMNS INTO A SINGLE STREAM FOR INSERTION INTO THE AUDIT TABLE
INSERT INTO tableNameAudit (DMLStatement, AuditedData, primaryKeyColumnName, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy)
SELECT 'UPDATE', @Xml.query('tableName[@primaryKeyColumnName=sql:column("primaryKeyColumnName")]'), primaryKeyColumnName, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy
FROM Inserted
END
--DELETE TRIGGERED
ELSE IF (SELECT COUNT(*) FROM Deleted) > 0 BEGIN
--RECORDS ARE IN THE DELETED TABLE
SET @Xml = (
SELECT * FROM Deleted AS tableName
FOR XML AUTO
)
--COMBINE XML AND DATA COLUMNS INTO A SINGLE STREAM FOR INSERTION INTO THE AUDIT TABLE
INSERT INTO tableNameAudit (DMLStatement, AuditedData, primaryKeyColumnName, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy)
SELECT 'DELETE', @Xml.query('tableName[@primaryKeyColumnName=sql:column("primaryKeyColumnName")]'), primaryKeyColumnName, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy
FROM Deleted
END
SET NOCOUNT OFF;
END
GO |
The audit table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tableNameAudit] (
[AuditID] [dbo].[DataPrimaryKey] ROWGUIDCOL NOT NULL CONSTRAINT [DF_tableNameAudit_AuditID] DEFAULT (newid()),
[DateAudited] [datetime] NOT NULL CONSTRAINT [DF_tableNameAudit_DateAudited] DEFAULT (getdate()),
[DMLStatement] [VARCHAR](50) NOT NULL,
[AuditedData] [xml] NOT NULL,
[primaryKeyColumnName] [dbo].[DataPrimaryKey] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[CreatedBy] [dbo].[DataPrimaryKey] NOT NULL,
[UpdatedOn] [datetime] NULL,
[UpdatedBy] [dbo].[DataPrimaryKey] NULL,
CONSTRAINT [PK_tableNameAudit] PRIMARY KEY CLUSTERED
(
[AuditID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF |
You can search for specific data within the stored XML using standard xpath/xquery syntax:
1 2
| SELECT * FROM tableNameAudit
WHERE AuditedData.exist('tableName[@columnName="foo"]') = 1 |
Posted in Coding, SQL Server | No Comments »
May 2nd, 2012
The Risk of Disruption or Destruction of Critical U.S. Infrastructure by an Offensive Cyber Attack (PDF)
A look at the national security risk posed by a cyber-attack. The research topic was chosen in response to the recent hyperbole on cyber-attacks causing a catastrophic power outage in New York City. This assignment was for INTL634 – Threat Analysis.
Posted in AMU, INTL634 | No Comments »
April 24th, 2012
I read a lot and I like keeping my reading notes in OPML; it’s great for the structured content of a book. Authors have Books, Books have Chapters, etc. I also like to highlight/markup my books.
After resisting for a long time, I broke down and bought some Kindle books that I read on two different computers (desktop & laptop) and an Android tablet. My highlighting addiction has been interrupted by reading Kindle books because it’s a pain to keep up my OPML reading notes while also highlighting across multiple devices. Amazon does a good job of syncing reading positions and highlights created on multiple devices, so that my personal notes (highlights) are eventually combined and kept in the cloud but there’s a headache also — I can’t export them out of Amazon and I want them in OPML.
What I ended up having to do to get my highlights (reading notes) into OPML was to copy the text out of the Kindle app on the PC and paste into the OPML Editor and then I went back and selected the same text and added a highlight — it was obnoxious. It also failed on Android where there isn’t an OPML Editor.
Enter Greasemonkey. I cooked up a script that adds two images to the top left of the Kindle page that shows my highlights (per book) one to capture the highlights and format as OPML (true markup that I could save to file and open in the OPML Editor) and one that formatted as plain text (indented that I could copy and paste directly into the OPML Editor).
Now I just highlight as much as I want across multiple devices and then eventually reformat using the Greasemonkey script in order to save my highlights as OPML from the Amazon Kindle website.
The Amazon page with the Greasemonkey inserted images:

The Amazon page formatted as OPML:

The Amazon page formatted as text:

The content in the OPML Editor:

Here’s the Greasemonkey Script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
| // ==UserScript==
// @name Amazon: Kindle Highlights to OPML
// @namespace havagan.amazon.kindle
// @include https://kindle.amazon.com/work/*
// @include http://kindle.amazon.com/work/*
// ==/UserScript==
var $;
// Load jQuery library
(function(){
if (typeof unsafeWindow.jQuery == 'undefined') {
var GM_Head = document.getElementsByTagName('head')[0] || document.documentElement,
GM_JQ = document.createElement('script');
GM_JQ.src = 'http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js';
GM_JQ.type = 'text/javascript';
GM_JQ.async = true;
GM_Head.insertBefore(GM_JQ, GM_Head.firstChild);
}
GM_wait();
})();
// Check if jQuery is loaded
function GM_wait() {
if (typeof unsafeWindow.jQuery == 'undefined') {
window.setTimeout(GM_wait, 100);
} else {
$ = unsafeWindow.jQuery.noConflict(true);
doMain();
}
}
// All your GM code must be inside this function
function doMain() {
addOpmlButton();
addTextButton();
}
function addOpmlButton()
{
$('body').append("<div id='createOpml' style='position:absolute; top:0px; left:0px; margin:25px;'><img id='opmlLogo' src='http://static.opml.org/images/opml.gif' /></div>");
$("img#opmlLogo").click(function () {
createOpml(getBookAuthor(), getBookTitle(), getBookHighlights());
});
}
function addTextButton()
{
$('body').append("<div id='createOpml' style='position:absolute; top:50px; left:0px; margin:25px;'><img id='textLogo' src='http://www.havagan.com/images/text.gif' /></div>");
$("img#textLogo").click(function () {
createText(getBookAuthor(), getBookTitle(), getBookHighlights());
});
}
function createOpml(bookAuthor, bookTitle, bookHighlights)
{
// wipe the page
wipeContent();
// recreate the page
$('head').append('<title>' + bookTitle + ' (' + bookAuthor + ')</title>');
$('body').append('<pre>');
$('body').append('&lt;?xml version="1.0" encoding="ISO-8859-1"?&gt;</br>');
$('body').append(' &lt;?xml-stylesheet type="text/xsl" href="http://havaganserver/opml/opml.xslt" version="1.0"?&gt;</br>');
$('body').append(' &lt;opml version="2.0"&gt;</br>');
$('body').append(' &lt;head&gt;</br>');
$('body').append(' &lt;title&gt;' + bookTitle + ' (' + bookAuthor + ')&lt;/title&gt;</br>');
$('body').append(' &lt;dateCreated&gt;&lt;/dateCreated&gt;</br>');
$('body').append(' &lt;dateModified&gt;&lt;/dateModified&gt;</br>');
$('body').append(' &lt;ownerName&gt;&lt;/ownerName&gt;</br>');
$('body').append(' &lt;ownerEmail&gt;&lt;/ownerEmail&gt;</br>');
$('body').append(' &lt;expansionState&gt;&lt;/expansionState&gt;</br>');
$('body').append(' &lt;vertScrollState&gt;&lt;/vertScrollState&gt;</br>');
$('body').append(' &lt;windowTop&gt;&lt;/windowTop&gt;</br>');
$('body').append(' &lt;windowLeft&gt;&lt;/windowLeft&gt;</br>');
$('body').append(' &lt;windowBottom&gt;&lt;/windowBottom&gt;</br>');
$('body').append(' &lt;windowRight&gt;&lt;/windowRight&gt;</br>');
$('body').append(' &lt;/head&gt;</br>');
$('body').append(' &lt;body&gt;</br>');
// create author/title elements
$('body').append(' &lt;outline text="' + bookAuthor + '"&gt;</br>');
$('body').append(' &lt;outline text="' + bookTitle + '"&gt;</br>');
// append highlights.
$.each(bookHighlights, function(index, value) {
$('body').append(' &lt;outline text="' + (index + 1) + ". " + bookHighlights[index] + '" /&gt;</br>');
});
// end highlights.
$('body').append(' &lt;/outline&gt;</br>');
$('body').append(' &lt;/outline&gt;</br>');
$('body').append(' &lt;/body&gt;</br>');
$('body').append(' &lt;/opml&gt;</br>');
$('body').append('</pre>');
}
function createText(bookAuthor, bookTitle, bookHighlights)
{
// wipe the page
wipeContent();
// recreate the page
// create author/title elements
$('head').append('<title>' + bookTitle + ' (' + bookAuthor + ')</title>');
$('body').append('<div style="text-indent:0em;">' + bookAuthor + '</div>');
$('body').append('<div style="text-indent:0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' + bookTitle + '</div>');
// append highlights here.
$.each(bookHighlights, function(index, value) {
$('body').append('<div style="text-indent:0em;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' + (index + 1) + ". " + bookHighlights[index] + '</div>');
});
}
function getBookTitle()
{
return $('div.bookInfo div.title').text().trim();
}
function getBookAuthor()
{
return $('div.bookInfo div.author').text().trim().replace("by ", "");
}
function getBookHighlights()
{
var bookHighlights = new Array();
$('span.highlight').each(function(idx, item) {
bookHighlights.push(item.innerHTML.replace(/(\r\n|\n|\r)/gm,"").replace(/(\"|\")/gm, "\'"));
});
return bookHighlights;
}
function wipeContent()
{
// start with a blank canvas; rebuild on a clean foundation.
$('head').empty();
$('body').empty();
} |
Posted in Coding, Greasemonkey | No Comments »
April 24th, 2012
I like to archive news articles to PDF so that I can add highlights, comments and markup to the document. Unfortunately, web pages rarely archive well and that led me to start using Readability. After they redesigned their service and added clutter to the top, bottom, and left margin it became less Readable. I cobbled together a Greasemoney script to remove this clutter and leave a simple black and white page for easy archiving. The script also tags the URL and date accessed to the bottom of the page — needed elements for citing the document in Turabian style.
Here’s an original web page:

And the same page after Readability + Greasemonkey:

Here’s the script (it uses jQuery):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113
| // ==UserScript==
// @name Readability
// @namespace havagan.readability
// @description Tweaks Readability output to better print to PDF for archiving.
// @include https://www.readability.com/articles/*
// @include http://www.readability.com/articles/*
// ==/UserScript==
var $;
// Load jQuery library
(function(){
if (typeof unsafeWindow.jQuery == 'undefined') {
var GM_Head = document.getElementsByTagName('head')[0] || document.documentElement,
GM_JQ = document.createElement('script');
GM_JQ.src = 'http://localhost/scripts/jquery/jquery-151.js';
GM_JQ.type = 'text/javascript';
GM_JQ.async = true;
GM_Head.insertBefore(GM_JQ, GM_Head.firstChild);
}
GM_wait();
})();
// Check if jQuery is loaded
function GM_wait() {
if (typeof unsafeWindow.jQuery == 'undefined') {
window.setTimeout(GM_wait, 100);
} else {
$ = unsafeWindow.jQuery.noConflict(true);
doMain();
}
}
// All your GM code must be inside this function
function doMain() {
recreatePage();
}
function recreatePage()
{
// capture the important page content.
title = $.trim($("h1.entry-title").html());
if (title == null) { title = ""; }
author = $.trim($("ul.entry-meta span.fn").html());
if (author == null) { author = ""; }
postDate = $.trim($("ul.entry-meta time.updated").html());
if (postDate == null) { postDate = ""; }
meta = author;
if (postDate.length > 0) { meta += "&nbsp;&#151;&nbsp;" + postDate; }
article = $("section.entry-content").html();
originalUrl = $.trim($("a#article-url").attr("href"));
// start with a blank canvas
$("html").removeAttr("class");
$("head").empty();
$("body").empty().removeAttr("id").removeAttr("class");
// add information to the head element.
$("head").append("<title>" + title + "</title>");
$("head").append("<style>body { font-family: 'Adobe Garamond Pro', Calibri, Helvetica, Verdana, Arial, sans-serif; color: black; margin: 50px; } img { display: block; margin-top: 10px; margin-bottom: 10px; } .title { font-family: 'Adobe Garamond Pro'; Garamond; font-size: 24pt; font-weight:normal; margin-bottom: 25px; } .meta { font-size:10pt; font-style: italic; display: block; margin-bottom: 10px; } .links { margin-top: 25px; } div#article a { text-decoration: none; color: black; font-weight: bold; } div#links a { font-style: italic; color: black; } div#links li { margin-top: 10px; } </style>");
// keep only the important page content.
$("body").append("<div id='title' class='title'>" + title + "</div>");
$("body").append("<div id='meta' class='meta'>" + meta + "</div>");
$("body").append("<div id='article' class='article'>" + article + "</div>");
$("body").append("<div id='links' class='links'></div>");
// append all article hyperlinks as footnotes.
createFootnotes($("div#article"), $("div#links"), title, originalUrl);
}
function createFootnotes(articleContainer, footnoteContainer, articleTitle, originalUrl)
{
// Create the container to store the footnote data.
footnoteContainer.append("<div style='font-weight:bold;'>Article Hyperlinks</div>");
footnoteContainer.append("<ul id='havagan_links'></ul>");
var linksList = footnoteContainer.find("ul#havagan_links");
// The URL of the original article should be saved first.
linksList.append("<li><div class='reference_title'>" + articleTitle + " (Original Article, Accessed On " + getDateAccessed() + ")</div><div class='reference_url'><a href='" + originalUrl + "'>" + originalUrl + "</a></div></li>");
// footnotes are created by looping through the jQuery object.
articleContainer.find("a").each(function(idx, item) {
if (isValidHyperlink(item) == true) {
linksList.append("<li><div class='reference_title'>" + item.text + "</div><div class='reference_url'><a href='" + item.href + "'>" + item.href + "</a></div></li>");
}
});
}
function isValidHyperlink(item)
{
// Ignore javascript/ajax empty anchor (#) links.
if ((item.text == null) || (item.text.length <= 0)) { return false; }
// Ignore NYTimes "topics" keyword search hyperlinks. indexOf returns -1 if the value is not found.
if (item.href.toLowerCase().indexOf("topics.nytimes.com") >= 0) { return false; }
return true;
}
// Return the current date (formatted).
function getDateAccessed()
{
var d = new Date;
return d.toLocaleString();
} |
Posted in Coding, Greasemonkey | No Comments »
March 9th, 2012
Posted in AMU, INTL650 | No Comments »
November 17th, 2011
The Validity of Analysis on Democratic Islam in Turkey (PDF)
A review of early analysis that focused on the Freedom and Justice Party (AKP) in Turkey and its effect on democracy. This assignment was for INTL506 – Analytics II.
Posted in AMU, INTL506 | No Comments »
November 17th, 2011
The Ongoing Failure of Intelligence – A study of the East Turkistan Islamic Party (PDF)
A literature review focusing on open source intelligence (OSINT) collection on the East Turkistan Islamic Party (ETIP). This literature review assignment was for INTL502 – Collection.
Posted in AMU, INTL502 | No Comments »
November 17th, 2011
Interrogation Effectiveness – Military Interrogations or Law Enforcement Interviews (PDF)
A literature review focusing on whether interrogations by intelligence agencies or Mirandized law enforcement interviews are more effective in extracting information from terrorism suspects. This literature review assignment was for INTL502 – Collection.
Posted in AMU, INTL502 | No Comments »
November 16th, 2011
Is the Director of National Intelligence Ineffective? (PDF)
A literature review focusing on whether the position of Director of National Intelligence is ineffective. This literature review assignment was for INTL501 – Strategic Intelligence.
Posted in AMU, INTL501 | No Comments »
November 16th, 2011
Guantanamo, A less-than-tremendous recruiting tool for Al Qaeda (PDF)
A literature review focusing on whether Al Qaeda uses Guantanamo as a major recruiting tool. This literature review assignment was for INTL500 – Research Methods in Security and Intelligence Studies.
Posted in AMU, INTL500 | No Comments »