Learn


Sometimes I walk alone outside late at night, when the streets are empty of everything but the whisper of lives lived and lost, and my eye is drawn up to the billions of stars over my head, and I have to wonder: is a SUM/COUNT/MAX/MIN over a VALUES statement the most effective way to calculate an aggregate over columns instead of rows?

The answer, by the way, is “Yes”.

For example:

SELECT keycol,
(SELECT MAX(colval) FROM (VALUES(col1val),(col2val),(col3val),(col4val),(col5val), (col6val)) AS D(val)) AS MaxTable
FROM dbo.TableName

That’s pretty beautiful, considering the mess of UNION and CASE statements this would otherwise require.

We had a need to put in place a vulnerability management system for our servers, and it needed to contain a ton of different data from multiple systems, bringing it all together in a way that was relatable in order to provide a “scorecard” for each server that could be rolled up by business unit.

So we built it.

I want to document a bit of this, partially so I can remember how we did it, but also so that others can hopefully learn from our mistakes.

When the process first started, I was approached with a request to build a “health check” report for our servers.  It was practically impossible for us to understand the overall security status of a particular server, considering all of the variables and different systems that held part of the data.  In order to understand the “health” of a server, we need to be able to know:

  • What high-level business applications run on it?
  • What software is installed on the server to support that business application?
  • Does the application fall in scope of any of our security and regulatory compliance programs (e.g. S-Ox, PCI, PII, GLBA)?  And if so, what are the algorithms that determine whether this server falls into scope?
  • What basic tools does the server need installed for day-to-day management and monitoring?
  • What additional tools does the server need installed for compliance and regulatory compliance (e.g. HIDS for PCI)?
  • Are those tools reporting correctly, and are they configured in the right way?
  • Are all the tools reporting conflicting information?  For example, is the software asset management tool reporting an installation of a monitoring tool, but the console for that tool has not received any communication from that agent?  That can imply misconfiguration (or simple disabling) of a particular tool.
  • What vulnerabilities exist on the server?  And are they:
    • missing patches
    • configuration file issues
    • missing tools
    • incorrect group memberships

At the end of the day, there are two outputs from collecting and understanding this pile of data

  1. The “health check” report, which can algorithmically be converted into a “risk score” for each server
  2. The “activity list” report, which is the list of things that need to be done to this server to reduce the “risk score”.

To build this, we leveraged:

  • MS  SQL (database to store all the collected data)
  • SQL Reporting Services (to produce the two reports listed above, as well as a metric buttload of other reports)
  • SQL Integration Services (to import and aggregate all the data from the multiple sources)
  • Iron Speed Designer (for the interface)

All of this to bring in data from (currently)

  • Our Application Portfolio Manager (to understand the relationship between servers and business apps, and the scopes for those applications)
  • Service Center (the quasi-CMDB and server asset management tool, to get basic data on the servers themselves)
  • Our event logging tool
  • Our HIDS tool
  • Multiple A/V tools (including different versions of McAfee and Symantec agents)
  • The database monitoring and encryption tool)
  • Multiple vulnerability management and patch deployment systems
  • Our internal vulnerability assessment tools, which assign categories and overall security severities and importance to the discovered vulnerabilities
  • The software asset management tools
  • The reporting tools from the supplier/vendor supporting the server hardware itself
  • Several other smaller utilities and consoles to provide additional required data: financial, business unit ownership, responsibility and ownership hierarchies

More details in coming posts.

Something I learned when using Attensa, but that works very well in our current (test) implementation of NGES, and should work in any RSS/Atom feedreader that dumps blog entries into subfolders in Outlook. I've been using what I've found to be a very efficient way to read Atom/RSS feeds within the folders in Outlook, but it requires Outlook 2003: use a very simple search folder.

NGES, by default, puts each feed into a separate folder under a top-level "Feeds" folder. Normally, you'd have to open each folder individually to read it, which doesn't lend itself well to the type of "skimming" reading that many feeds require (I'm looking at you, deli.cio.us/popular).

Here are the steps:

  • Right-click on "Search Folders" in Outlook 2003, select "New Search Folder"
  • Select Custom – Create a custom Search Folder Click "Choose" to specify search criteria
  • Call the new folder whatever you want (e.g. "All Feeds") Select, for the folders that will be included in the search folder, the NGES "Feeds" folder only, and leave "Search in Subfolders" turned on
  • Click "OK" on the warning that you have not specified any criteria
  • When the search folder populates, make sure that the view is arranged by folder (top of the view)

Voila! A single folder with all of your unread RSS/Atom feed items. You can select a "feed"/"folder" by clicking on the sorting group title (which has the feed name), and actions performed against that title are performed against all the feed entries: you can catch up on a feed and delete all items, for example, by selecting the folder and hitting "Del". You can go from item to item by using the space bar. Since the search folder is just a view, whatever you do to the entries in that folder is done to the original items.

Advanced capabilities:

  • hitting the space bar will go through the items and to the next unread entry, but depending on how you have Outlook configured, the item may or may not be marked as "read" automatically (mine is configured to not "mark as read").
  • Because this is a search folder capability, you don't need to limit yourself to just one view: the filters can be customized even further, and you can have separate, independent views of your feeds. You can aggregate from all your feeds only entries with specific keywords (I have an "enterprise architecture" and an "XML" view) into a single view, or categorize your feeds into groups by using search folders that only view specific subfolders under "Feeds".
  • You can categorize and search by date, subject, author, anything you want, and the view is populated automatically by Outlook's quite powerful search folder capabilities.
  • You can see how many unread items are in your entire set of feeds (the NGES "Feeds" folder only allows you to see how many unread items there are in each feed)
  • If you're really geeky and are using the GTD Outlook add-in, you can even create tasks and events off blog entries ("Read this later", "Comment on this blog")

Important note: remember as you're investigating possibilities here, that each entry in an NGES feed is a "Post" item, not an email item. I found this out the hard way after trying to troubleshoot a search folder that relied on an email-specific property, when the fact that the icon for the entries is a "post" icon. In my defense, I had the icons turned off at the time.

Edumification wants to be free!

Mountain Motion: The Adventure of Physics

Seems to be a pretty complete text: can’t wait to get to the chapter where they explain the whole unification theory! Ah, here it is, chapter XII… not yet available?

NOOOOOOOOOOOOOOO!!!!!

Here’s my favorite quote out of context: “The limit speed for Olympic walking is thus only one third of the speed of light.” Nice mix of serious, complex subject with some easy to understand examples.