How do I: Create An Advanced SQL Database Backup Monitor In Visual Studio?

My favorite part of my job is getting to work with customers and understand exactly how they use, and also need a product to work. Some of the time this just means listening closely, answering questions, and relaying information back to the product group. But often there are those tiny changes and use cases that fall outside the realm of things the product group is likely to address.

A change that might be hugely valuable for Customer A, won’t happen if it breaks backwards compatibility for Customers B-Z.

One of my customers has a SCOM environment for SQL that monitors over 20,000 databases. Due to their size there are often cases where the out-of-box monitoring provided by the SQL Product Groups management packs isn’t able to completely meet their needs. While the pack provides fantastic monitoring for SQL, there are times where they need a more granular view of the health of their environment.

An instance of this from the past year was monitoring SQL Database backups. The SQL Product Group’s pack gives you the ability to alert if a database hasn’t been backed up in a certain number of days. By default it is set to 7 days, but you can override that to any integer value of days.

For my customer this wasn’t really good enough. They wanted to be able to drill down and alert on hours since last backup. They also wanted multiple severities so if it had been 20 hours since a backup, an e-mail could go out, but at 30 hours we would generate a page. The 20 & 30 hours would be customizable at the individual database level, and they also wanted some added logic that would check database backups for databases that had a status of “ONLINE”. We have other monitors that look at DB Status in general so in this case if a database was OFFLINE they either knew about it from the other monitors and were fixing it, or it was intentional in which case they didn’t want a backup alert.

The basic logic behind the SQL PG’s MP is a simple T-SQL query wrapped in a fairly complex vbscript. The unwrapped T-SQL is below:

The T-SQL modifications we need to make are relatively simple swap DAY to HOUR, and add in a line to only return database backup info for databases with a status of ONLINE.

To get this into a working Management Pack is a little bit more complex and requires isolating and cloning the Product Groups Database Backup Monitor in Visual Studio, and then making a few changes to the XML for our custom iteration.  To prevent screenshot overload I did a quick step-by-step walkthrough of the process. For this video I opted to leave out three-state severity request, and will show how to add that functionality in a follow up video.

If you have any questions or need any help, just leave a comment.

Sidenote/Tangent: C Programming & Key Loggers

One of my side projects over the past few months has been to teach myself the C programming language. It has been going pretty well, but it’s easy to get bored with the typical practice problems.To combat this I gave myself a slightly more real-world practice problem. I decided to write a Key Logger in native C using only the Win32 API. There are a few good examples online of the mechanics of how to do this, but what I quickly found after getting a working prototype up and running was that Windows Defender is actually pretty good in some cases at detecting/tagging key loggers as Trojans. I tried different example code from online, and in each case Windows Defender instantly caught it upon execution.

So the next challenge was how to write a Key logger in native C with the Win32 API which Windows Defender could not detect.

After a few modifications I thought I had it figured out, but what I came to realize is that Defender is built such that it can tell if you are repeatedly trying to vet code past it and it will temporarily stop intercepting it so you can’t tell if your modifications are working.

It is actually a fairly interesting problem in that Defender has to be able to recognize certain patterns common to Key Loggers, but at the same time it has to be able ignore programs that function like Notepad/Word/Games which have a completely legitimate reason for listening and responding instantly to keystrokes. This balance is where the room for vulnerability lies. It would be easy to alert anytime a new program was listening for keystrokes via the Win32 API’s, but you would have so many non-actionable events it would be pointless. So it becomes a game of whack-a-mole where the patterns for detecting key loggers need to be  permissive enough not to piss off users trying to run non-malicious code, but restrictive enough to hopefully catch/deter a less than determined adversary.

I have no insider knowledge into how Defender works (nor would I share any if I did), but after figuring out how to force Defender to try to quarantine my program every time it ran, I could slowly comment out aspects of my Key Logger code until I zeroed in on the characteristics it was using to make its diagnosis. Once I had that, the problem was solved.

It took a few hours, but I now have a basic functional Key Logger that doesn’t need to run as administrator and is completely invisible to Windows Defender. I also learned more about C and the Win32 API in the past few hours than from three weeks worth of reading.  As much as possible I am going to try to add in programming assignments like this one that have a bit more real-world application as they are able to maintain my interest better than writing Celsius to Fahrenheit temperature converters and the other example problems that are universal to almost every learn programming language X book.

I had debated not publishing the code, but a routine Bing/Google search will reveal plenty of similar alternate examples, and with C source code for programs like mimikatz readily available this would fall into the tamer categories of code available for aspiring white hats who want to learn more about programming and security.

The code is provided without warranty of any kind and is for educational purposes only, and in this case I am only including screenshots without the details of the work to vet the code against an antivirus application. If you want to create a working copy you have to type it out yourself which is in the end the best way to learn.

To start off launch Visual Studio.  (For my example I am using Visual Studio 2013)

File New Project (Ctrl — Shift — N)

Select Visual C++ (Win32 Console Application) -Even though the code we are writing is almost entirely C the C++ compiler is backwards compatible with any of our C code.

Select Empty project

Right-Click Source File — Add — New Item (Ctrl-Shift-A)

Give you .cpp files a name click Add

The code itself is pretty simple. The ShowWindow() function controls the visibility of the command prompt window.

While loop continues indefinitely until the End Key is pressed or the Console Window/associated process is closed/stopped. For loop cycles between keys 8-255.

GetAsyncKeyState() function let’s us know if the key we are cycling through in the for loop is down. http://www.cplusplus.com/forum/general/141404/ (Nice explanation of why to use & 0x0001)

Use if statements to determine what keys get written directly to file. Shift letters A-Z by 32 making all letters lowercase via ASCII character set. http://www.asciitable.com/

To map other keys you need to use win32 API Virtual-Key Codes:

https://msdn.microsoft.com/en-us/library/windows/desktop/dd375731(v=vs.85).aspx

If you have any suggestions on good C Programming resources let me know. I have exhausted K&R and K.N.King’s C Programming a Modern approach and am currently working my way through Robert Sedgewick’s Algorithms in C, but I am always happy to get new reading suggestions.

How do I: Create a task that will allow me to bulk adjust a regkey via the SCOM Console

There are lots of ways to adjust reg keys in bulk. SCCM, Group Policy, Remote PowerShell to name a few.

Occasionally I find that SCOM customers like to have the ability to modify a registry setting via a Task in the SCOM console. This gives them the ability to modify the regkey for a single server, a group of servers, all servers, whatever they want in a matter of seconds without having to rely on outside tools.

Recently I have had a few customers need to adjust the MaxQueueSize reg key for their agents:

This is actually a fairly good simple MP Authoring exercise so I will quick walkthrough the process.

The end design in Visual Studio will look like this:

regkeymp

Easy enough, two Tasks, and two Scripts, standard out-of-box references – which then generate two tasks in the console:

task

Usually for something like this I like to start with the PowerShell before I break open Visual Studio. It is easier for me to get the script working in the PowerShell ISE and then start a new MP once I know I have the PowerShell working.

For the most part the PowerShell is pretty straight forward. The only complication I ran into in testing was that  since some of my customer’s agents are multi-homed and some aren’t I needed a way to handle either scenario without erroring out. Handling multiple management groups adds three lines of code to my original script, but still not too bad:

$GetParentKey = Get-Item -Path ‘HKLM:\SYSTEM\CurrentControlSet\services\HealthService\Parameters\Management Groups’
$MGName = $GetParentKey.getsubkeynames()
Foreach ($Name in $MGName)
{
Set-ItemProperty -Path “HKLM:\SYSTEM\CurrentControlSet\services\HealthService\Parameters\Management Groups\$Name” -Name ‘maximumQueueSizeKb’ -Value 76800 -Force
}


To make things as simple as possible in this example I am using hardcoded QueueSize Values. One Task to increase the queue size to 75 MB, and one to set it back to the default of 15 MB.

$GetParentKey = Get-Item -Path ‘HKLM:\SYSTEM\CurrentControlSet\services\HealthService\Parameters\Management Groups’
$MGName = $GetParentKey.getsubkeynames()
Foreach($Name in $MGName){
Set-ItemProperty-Path “HKLM:\SYSTEM\CurrentControlSet\services\HealthService\Parameters\Management Groups\$Name” -Name ‘maximumQueueSizeKb’ -Value 15360 -Force
}

Now that we have the scripts we can open up our copy of Visual Studio with the Visual Studio Authoring Extensions:

File – New Project

newproj

Management Pack – Operations Manager 2012 R2

opsproj

create

We are going to create two folders. These aren’t required, I just like adding a little bit of organization rather than dealing with one large .mpx file. Ultimately how you divide things up is somewhat arbitrary and more a matter of personal preference rather than any specifc rules.

To create a folder. Right-click MaxQueueSize – Add – New Folder

new-folder

Do this two times. We will create one folder called Scripts and one called Tasks:

scripts

Now we need to populate our Scripts folders with the two PowerShell scripts we wrote in the ISE earlier.

Right-Click the Scripts folder – Add – New Item

addnewitem

PowerShell script file – Name file – Add

powershell-script-file

Now you can paste in the code we wrote in the PowerShell ISE

increasemaxsize

This takes care of the Increase Max Queue Size PowerShell. Now repeat the steps above for the reset max queue size script:

powershell-scripts

Now we need to populate our Tasks folder

Right-Click Tasks Folder – Add – New Item

add-new-task

Empty Management Pack Fragment – IncreaseMaxQueueSize.mpx – Add

increasetask

The code for a task that kicks off a PowerShell script is pretty easy:


<ManagementPackFragment><SchemaVersion>
=”2.0xmlns:xsd=”http://www.w3.org/2001/XMLSchema“>
<Monitoring>
<Tasks>
<Task ID=”Sample.RegKey.IncreaseMaxQueueSize.AgentTaskAccessibility=”InternalTarget=”SC!Microsoft.SystemCenter.ManagedComputerEnabled=”trueTimeout=”300Remotable=”true“>
<Category>Custom</Category>
<ProbeAction ID=”ProbeTypeID=”Windows!Microsoft.Windows.PowerShellProbe“>
<ScriptName>IncreaseMaxQueueSize.ps1</ScriptName>
<ScriptBody>$IncludeFileContent/Scripts/IncreaseMaxQueueSize.ps1$ </ScriptBody>
<SnapIns />
<Parameters />
<TimeoutSeconds>300</TimeoutSeconds>
<StrictErrorHandling>true</StrictErrorHandling>
</ProbeAction>
</Task>
</Tasks>
</Monitoring>
<LanguagePacks>
<LanguagePack ID=”ENUIsDefault=”true“>
<DisplayStrings>
<DisplayString ElementID=”Sample.RegKey.IncreaseMaxQueueSize.AgentTask“>
<Name>Max Queue Size Increase</Name>
<Description>Increase Max Queue Size Regkey to 75 MB</Description>
</DisplayString>
</DisplayStrings>
</LanguagePack>
</LanguagePacks>
</ManagementPackFragment>


taskxml

You do this for both tasks and associate each with the appropriate PowerShell file.

So Visual Studio will look like this:

regkeymp

And once you build and import the pack you will have two tasks that will show up as options when you are in the Windows Computer State view:

task

If anyone wants these instructions in video form, just post a comment below and I will record a step-by-step video walkthrough.

If the source files or finished MP are helpful again don’t hesitate to ask. Just post a comment and I will zip up the files and upload to TechNet or GitHub.

How do I: Generate a single report of all healthy agents + grey agents +timestamp of last recorded heartbeat?

This week is a training week, which means I have tiny windows of time to catch up on some blogging.

I have had this question a few times over the years. It seems like it should have a straightforward answer, but if there is one, I have not been able to find it.

When customers have asked this in the past I usually refer them to the following three posts:

https://blogs.msdn.microsoft.com/mariussutara/2008/07/24/last-contacted/

http://www.systemcentercentral.com/quicktricks-last-agent-heartbeat/

http://blog.scomskills.com/grey-agents-with-reason-gray-agents/

These do an excellent job in different ways of getting at the question of what agents are greyed out and when did heartbeats stop coming in.

Unfortunately, these do nothing to address the first part of the question, they want all agents, those that have stopped heart beating and also those that haven’t.

This is a little bit more tricky. It is easy enough to get a list of all agents, a list of grey agents, and to query for when health service heartbeat failures occur. But there is nothing easily accessible via the SDK or via the DW that (at least that I am aware of) allows us to capture a timestamp for when a non-grey agents last heartbeat came in.

So my natural question to my customer is why do you need the healthy agents heartbeat timestamp? The answer was basically that they want to feed that data into other systems in their org and they don’t want to deal with two different lists/files. They want one file, but at the end of the day they don’t actually need an exact timestamp for last heartbeat of a healthy agent.

This makes things a lot easier and lends itself to a relatively simple potential solution:

Import-Module OperationsManager

$Agent = get-scomclass -name “Microsoft.SystemCenter.Agent”
$MonitoringObjects = Get-SCOMMonitoringObject $Agent
$Date= Get-Date | Where-Object {$_.ToShortDateString()}
$DateSString= $Date.ToShortDateString()
$TimeLString= $Date.ToLongTimeString()
$DateTimeCombine = $DateSString + ” “ + $TimeLString
$UserDesktop = [Environment]::GetFolderPath(“Desktop”)
 
function GenerateAgentReport

{
    foreach ($object in $MonitoringObjects)
        {
    $result = New-Object –TypeName PSObject
    $result | Add-Member -MemberType NoteProperty -Name DisplayName -Value $object.DisplayName 
    $result | Add-Member -MemberType NoteProperty -Name Agent_Healthy -Value $object.IsAvailable
        if ($object.IsAvailable -contains “True”)
            {
             $result | Add-Member -MemberType NoteProperty -Name LastHeartbeat -Value $DateTimeCombine -PassThru
            }
        else
            {
            $result | Add-Member -MemberType NoteProperty -Name LastHeartbeat -Value $object.AvailabilityLastModified -PassThru
            }
        }
}

GenerateAgentReport | out-gridview

heartbeat

Basically this returns each agent in your management group. If the Agent is greyed out we use the AvailabilityLastModified property to pull an approximate timestamp. If the agent is still heartbeating as determined by the IsAvailable property then the AvailabilityLastModified property isn’t going to contain useful information, so in this case we substitute the current date/time for that field indicating that we have had a successful heartbeat within the past 5 minutes.

I said “approximate timestamp” when referring to agents with an IsAvailable value of false (greyed out agent) in that while in many cases AvailabilityLastModified should correspond to a when a heartbeat failure occurs flipping the agent from healthy to critical. If for some reason the agent was already in a critical state, but was still heartbeating the AvailabilityLastModified property would only be capturing when the agent went into the critical state, not the moment of last heartbeat. If you need a more or less exact moment of last heartbeat report I suggest using one of the links above. But if you need a quick PowerShell report to feed into other systems to help prioritize agent remediation the above script or some modified form of it might be mildly useful.

The contents of this site are provided “AS IS” with no warranties, or rights conferred. Example code could harm your environment, and is not intended for production use. Content represents point in time snapshots of information and may no longer be accurate. (I work @ MSFT. Thoughts and opinions are my own.)