SQL Trigger: Audit Changes to XML

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 »

The Risk of Disruption or Destruction of Critical U.S. Infrastructure by an Offensive Cyber Attack

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 »

Greasemonkey: Kindle Highlights Into OPML

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('<?xml version="1.0" encoding="ISO-8859-1"?></br>');
$('body').append('    <?xml-stylesheet type="text/xsl" href="http://havaganserver/opml/opml.xslt" version="1.0"?></br>');
$('body').append('    <opml version="2.0"></br>');
$('body').append('    <head></br>');
$('body').append('        <title>' + bookTitle + ' (' + bookAuthor + ')</title></br>');
$('body').append('        <dateCreated></dateCreated></br>');
$('body').append('        <dateModified></dateModified></br>');
$('body').append('        <ownerName></ownerName></br>');
$('body').append('        <ownerEmail></ownerEmail></br>');
$('body').append('        <expansionState></expansionState></br>');
$('body').append('        <vertScrollState></vertScrollState></br>');
$('body').append('        <windowTop></windowTop></br>');
$('body').append('        <windowLeft></windowLeft></br>');
$('body').append('        <windowBottom></windowBottom></br>');
$('body').append('        <windowRight></windowRight></br>');
$('body').append('    </head></br>');
$('body').append('    <body></br>');
// create author/title elements
$('body').append('        <outline text="' + bookAuthor + '"></br>');
$('body').append('            <outline text="' + bookTitle + '"></br>');
// append highlights.
$.each(bookHighlights, function(index, value) {
$('body').append('                <outline text="' + (index + 1) + ". " + bookHighlights[index] + '" /></br>');
});
// end highlights.
$('body').append('            </outline></br>');
$('body').append('        </outline></br>');
$('body').append('    </body></br>');
$('body').append('    </opml></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;">     ' + bookTitle + '</div>');
// append highlights here.
$.each(bookHighlights, function(index, value) {
$('body').append('<div style="text-indent:0em;">          ' + (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 »

Greasemonkey: Readability

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 += " — " + 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 »

Four Essays Related to Terrorism or Counter-Terrorism

March 9th, 2012

One Man’s Terrorist Is Another Man’s Freedom Fighter (PDF)

Key Drivers of Terrorist Behavior (PDF)

Analyze the NYPD Counter-Terrorism Program (PDF)

The Threat of Failed and Failing States (PDF)

Four individual essays that were assigned as part of INTL650 – Counterterrorism.

Posted in AMU, INTL650 | No Comments »

The Validity of Analysis on Democratic Islam in Turkey

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 »

The Ongoing Failure of Intelligence – A study of the East Turkistan Islamic Party

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 »

Interrogation Effectiveness – Military Interrogations or Law Enforcement Interviews

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 »

Is the Director of National Intelligence Ineffective?

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 »

Guantanamo, A less-than-tremendous recruiting tool for Al Qaeda

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 »

« Previous Entries