I find myself constantly in a situation where I have a giant list of data and I need to know which entries in that data occur most frequently. I’ve always used Excel pivot tables for this purpose.  Recently someone saw me solve a problem using this technique and they were very surprised and wished they’d known this years ago. With that in mind, I figure I’d share my technique.

Let’s create a made-up scenario for demonstration purposes. Let’s say you want to know which DLL on your computer loaded in memory most frequently. The scenario doesn’t matter, it’s just an excuse to generate some data.

Ultimately, you’re going to end up with Excel open with a bunch of columns of data similar to this:

As you can see, we have 2,092 rows of data with the fourth column being FileName. We want to produce a list in order of frequency of which DLL is listed most often. To do that complete the following steps:

• Select either the column you’re interested in or the entire sheet depending on your needs
• Select Insert / Pivot Table
• The range will default to what you selected. Since you selected all the data, simply press OK

Continue reading

Today I had a need to compare the local security policies between two domain controllers to verify the audit configuration on each. I figured this could be easily achieved using the Security Configuration and Analysis MMC built into Windows. The tool seems to work well with comparing with pre-defined baselines but for the life of me I could not figure out how to export the current configuration for analysis on another machine. While this proved to ultimately be fruitless, it did lead me down a several hour rabbit hole that thankfully did ultimately result in a solution.

The solution ultimately was looking outside of the core Windows OS to a free download from Microsoft called the Security Compliance Manager. That can be found here:

http://technet.microsoft.com/en-ca/solutionaccelerators/cc835245.aspx

Note: This is a 132MB download and requires a SQL server to install. I installed SQL 2008 R2 Express on the same machine without issue. If you’d like to know how I configured SQL, see this blog post:
https://pleasework.robbievance.net/howto-configure-server-performance-advisor-to-troubleshoot-domain-controller-performance/

Otherwise the install is simply a next, next finish kind of thing.  Once it’s installed, you can launch it from your start menu. The first time you launch it will likely take 5+ minutes while it loads in all of the policy modules for all of the supported products. Once it’s installed, you should see a screen similar to this:

At this point, you might be thinking as I did that somewhere in the UI you would tell it to scan a remote computer to pull its current configuration. I’ll save you some time and tell you that the Security Compliance Manager does not allow you to import live configurations. It only works for comparing baselines. That obviously doesn’t help us much. If you look in the top right, you’ll see an option for Import GPO Backup (folder). Don’t be fooled as I did. This is not just for GPOs but can also import the local security policy from a machine. The question now becomes, how do you generate the configuration? After much more digging, I discovered, almost by accident, the following file that was generated as part of the installation of the SCM:

“C:\Program Files (x86)\Microsoft Security Compliance Manager\LGPO\LocalGPO.msi”

Continue reading

Consider the following scenario. You run a command in PowerShell. It fails. It generates tons of scary red text. You don’t like this. You want to display a more friendly error message. Or better still, you want to take some action based on the error.

With PowerShell, you can do this easily with something called try / catch statements. There is lots of documentation online for how to use these. However, one thing I found difficult to figure out was how to define specific exceptions. Consider this code block:

Try {
# Do something
}
Catch [SpecificExceptionGoesHere]
{ # Do something else if the above fails }

The problem I’ve had is figuring out what exactly you type in the yellow box. I’m pleased to report I now have an easy way of figuring it out.
To do so, you first need to intentionally cause the error you want to capture. Once you’ve done that you can extract the exception text.

Oh, I should probably mention that PowerShell automatically saves all Errors in a custom variable called $Error and that the most recent error is always available from $Error[0]

Let’s dive straight into an example. Below we’ve run a simple cmdlet (in this case Get-ChildItem) against a file we know doesn’t exist. It generates an error.

Continue reading

I make use of custom objects constantly in PowerShell.  Having the ability to easily combine and present data is incredibly powerful.  Since I use them so much, I’ve found myself wanting to make them as fast and easy as possible to create. After much research and experimentation, I think I have created one of the single fastest and easiest ways of making custom objects in PowerShell – and I wanted to share with you.  Ready?

$C=@("Column1 Column2 ColumnXX Column99")
$myObj=@();Function Add-ToObject{$C=($C -split ' ');$i=0;ForEach($R in $C){$P+=@{$R=$Args[$i]};$i++;};$Script:myObj+=[pscustomobject]$P}

Update: I managed to shrink this function down even further by collapsing some functions into others and making some other tweaks.  If you think you can get this shorter, (even by a single character!), please share how in the comments:

$C="User ComputerName Email OS"
Function Add-ToObject{$args|%{$i++;$P+=@{$C.split(" ")[$i-1]=$_}};$Script:myObj+=@([pscustomobject]$P)}

To make the script a little easier to understand, here it is again, but this time in its full expanded form:

I’ve effectively gotten the process down to 2 lines for any number of custom properties! But it’s even better than that.  You can ignore the second line entirely.  Instead just concentrate on the first line where you define the columns you want in your custom object.  If you want to add another column, just add it to the end separated by a space.  If you want to remove a column, delete the name.  That’s it!  Let’s look at a real world example.

Imagine a scenario where you need to identify all of the users running Windows XP in your environment and send them an email saying you need to upgrade.  To do this, you need to produce a list of all of the computers in your network, the operating system they run as well as the user that manages/owns it along with their email address.  This information must come from two different cmdlets (Get-ADComputer and Get-ADUser).  How can we combine these results into a single object for easy output?

Step 1) Copy and paste the two lines above at the top of your script and modify the column names and quantity to match your needs

Step 2) Write the code that generates the data that you need

Step 3) Once you have the data gathered, call the function Add-ToObject and pass in all the data you data you want.  Since these are objects, this can be any kind of data

Step 4) Output the results from the variable $myObj.  (You can change this if you want but I often find for quick work I’m doing, I just need a single object so sticking with a standard name is useful for smaller projects)

Continue reading

I’ve recently started a new job at a large company with thousands of employees and so I have a constant need for looking up who people are, what their user name is, who their manager is, what city they are in, etc? In many cases, I have to look up entire batches of people. Initially I just using Active Directory Users and Computers and searching each user and then going to each tab for the details I need. After a few times of that though, I found the process maddeningly inefficient and decided to automate it.

The script below is pretty simple but it does have a few cool features.  First off, to get user details, all you need to do is copy and paste your list of names red box area and run the script.  There can be one name or many.  They can be SAMaccountnames, display names or canonical names, it doesn’t matter as the script will figure it out and display the results in a table.  Second, normally with Powershell, you have to encapsulate each name in quotations.  Since I’m often copying and pasting the names from other sources, this adds extra busy work.  My script obsoletes that and allows you to paste in the plain text and it’ll automatically convert it into an object PowerShell can use. Lastly, it’s pulling data that otherwise requires access to at least 3 different tabs in ADUC to view.

It’s otherwise nothing fancy.  The idea for me is that since I always have PowerGUI (my PowerShell IDE of choice) open anyway, I can just alt-tab and quickly paste in the names, press F5 to run the script and figure out who does what.  It’s been incredibly useful for me already so I figured I’d share.

 
Continue reading

I am troubleshooting an issue where a domain controller pins its CPU at 100% for extended periods and as a result, LDAP authentication requests fail causing organization wide application failures. I recently posted a HOWTO on setup and configuration of the Server Performance Advisor tool from Microsoft. This is the tool that allows you to determine which specific machines and even specific LDAP queries are being performed on a domain controller. You can find more on that HOWTO here.
The problem now is that the tool generates a tremendous amount of data and so it’s not feasible to leave it running all the time. I wanted to find a way that the SPA could be triggered only during periods of high CPU utilization. While I unfortunately unable to find an off-the-shelf solution to this, thanks to PowerShell I was able to quickly develop a solution for this problem.

The script below is a rough-around-the-edges purpose built script that is designed to be run on an offending domain controller. It requires that the Server Performance Advisor has already been setup and configured. Assuming that’s done, the idea is that you simply run this script on your domain controller for an extended period of time. What the script does is check for CPU utilization (by default every 60 seconds) and if high CPU usage is detected, it automatically triggers the SPA.

Once you let this run over time, you can then use the SPA Report Explorer to determine which specific clients and queries were the most expensive and take action to resolve them. An example of what the script can do for you is shown below. Each report shown was automatically triggered during periods of high CPU usage and each capture 2 minutes worth of detailed logs.

If you find this useful, let me know in the comments below!

# This script is designed to be run on a Domain Controller that is experiencing high CPU utilization
# This script assumes that an SPA Database has already been deployed and configured

$SPAPAth = "D:\SPA" # The location of the extracted files for the Server Performance Advisor
$AdminUser = "domain\username" # A user account that has "Logon as Batch Job" permission on the target server
$SecondsToWait = 60 # How long to wait between checks. Recommended: 60 (seconds)
$CPUThreshold = 95 # When CPU usage reaches this level as a percent, trigger the SPA. Recommended: 95 (%)
$LogDuration = 120 # The duration of time the SPA should run once triggered. Recommended: 120 (seconds)
$SQLServer = "SQLSERVER01" # The hostname of the server that is hosting SQL Server
$SPADatabase = "SPA1" # The name of the SPA Database that will be used. This will be created in advance when you first run the wizard from SPAConsole.exe 

# Import the SPA cmdlets, specifically Start-SPAAnalysis
Import-Module -Name $SPAPAth\SpaCmdlets.dll 

# Function to display countdown progress bar. This is useful as this is designed to run for long periods of time and this helps validate the script is still functioning
Function Wait-CountDown ($WaitTimeInSeconds) {
 $IntervalPercent = $WaitTimeInSeconds / 100
 while($SecondsToWait -gt 0) {
 Write-Progress -Activity "Waiting for next execution..." -status "$SecondsToWait seconds remaining..." -PercentComplete ($SecondsToWait/$IntervalPercent)
 Start-Sleep -Seconds 1
 $SecondsToWait-- }
}

# Create prompt to type in password for the account with log on as batch job rights to server
$Cred = Get-Credential -UserName $AdminUser -Message "Account with 'log on as batch job right' on destination server:"

# Loop indefinitely or until the user stops the script
While(1 -eq 1) {
 # Get the current CPU usage as a percent
 $ProcCounter = Get-Counter -Counter "\Processor(_Total)\% Processor Time" -SampleInterval 2
 $CPUUsage =[math]::Round(($ProcCounter.readings -split ":")[-1])

 # If the threshold is met or exceeded, it means the domain controller is busy. Start the SPA logger to determine exactly why it is busy
 if($CPUUsage -ge $CPUThreshold)
 { Start-SpaAnalysis -ServerName "localhost" -AdvisorPackName Microsoft.ServerPerformanceAdvisor.AD.V1 -Duration $LogDuration -SqlInstanceName $SQLServer -SqlDatabaseName $SPADatabase -Credential $Cred }
 Wait-CountDown $SecondsToWait
}

Continue reading

Have you ever found one of your domain controllers pinned at 100% CPU with lsass.exe using up 99% of it?  Have you been baffled at how to figure out what to do next and how to figure out who and what is causing it?  Thankfully Microsoft has created a tool to help aid us in our troubleshooting.  I’ve found however that there is remarkably little documentation online for this tool so hopefully this document will help those that are trying to get this tool working.

This HOWTO describes how to use a downloadable tool called the Server Performance Advisor (SPA) to troubleshoot situations where a Windows domain controller is experiencing high CPU utilization.

The SPA tool is a free download from Microsoft and can be downloaded here:

http://download.microsoft.com/download/0/3/D/03D07D11-18D4-4160-B4AC-915061B85669/SPAPlus_amd64.cab
(At the time of this writing, the most recent version is v3.1)

Please note that this tool requires a SQL Server to function.  Fortunately, the free Express edition will work.  In my case I used SQL 2008 R2.  You can grab that here:

http://www.microsoft.com/en-ca/download/details.aspx?id=23650

SQL Server Installation

For this use case, I will install SQL server on my laptop.  You could also use an existing SQL server if you have one or build a new VM.

• When you run the installer, select New installation or add features to an existing installation

Continue reading

I had a conversation with a coworker today where he expressed an interest in learning PowerShell.  He knew it was critical to his future in IT and could help him solve many day to day challenges but felt that it was too intimidating and didn’t know where to begin. I decided I would do my best to give him a clear and concrete place to start. That’s where this blog post steps in.  What I will attempt to do over the remainder of this post is introduce the the core concepts of PowerShell in such a way that you can be immediately productive with it to solve real world problems without feeling burdened at learning the entire syntax and structure all at once.

Let’s start by outlining our scenario.  We are working on a windows 8 client machine that a user reports is “acting funny” as random pop ups advertisements are appearing on the screen.  You immediately suspect some kind of malware but you need to investigate to confirm your suspicions.  How would you go about solving this problem?

The first thing you might do is open up Task Manager and select the Details tab to see if you can spot any unusual processes.

Unfortunately there are so many that it’s difficult to isolate and determine which ones are valid and which ones are not.  Now what we might do next if we are desperate is to go through every process one at a time and try to figure out what they do and if they are legitimate or not.  You could do that… or you could use PowerShell.  It might seem scary but let’s see if this PowerShell thing can do anything to help us here.

• To launch Powershell, simply click the start button and type ‘powershell‘ or launch it through the start menu by clicking this icon

• A window will appear where you can enter commands.  The question now is —  What do I type?

Continue reading

This quick HOWTO is a PowerShell script I wrote to monitor concurrent connections to a server. In this case we have a domain controller that is not behaving properly and I suspect it may be due to some kind of port exhaustion. The script is very quick and dirty but since it works I figured I’d share it. Note that the heavy lifting is done by TCPVCON.EXE from Sysinternals (http://technet.microsoft.com/en-ca/sysinternals/bb897437.aspx). I also include the current CPU utilization so I can correlate if during periods of high CPU if we are seeing an unusually high connection count.
Continue reading

There are countless situations where an IT professional needs to parse through a log file. In most instances, notepad/notepad2/notepad++ are enough to get in, find the information required and get out. But what if your log files are large. As in 20GB a day large. None of the typical editing tools will help you in this case as the files are simply too large to open. In this case, you can switch to more specialized text viewers such as LogExpert. But what if you need to actually manipulate the data in these log files to perform some kind of analysis? In other words, what if you have to review every single line in that 20GB a day file and do something to compare it to some other line? But before you can even do that, you have to reformat the data as the original source includes a bunch of cruft and formatting that you simply don’t want. This is where things get interesting and this is what this blog post will help you to solve.

In this specific scenario, I have a Windows DNS server that is very heavily used by tens of thousands of endpoints. The request from management was to identify which two hour block of time over the course of a week where the DNS servers are least utilized. In addition, there there was a want to know what the top 20 most requested DNS records were during a given 24 hour period. At first blush, I thought this would be fairly simple:

1. Enable DNS Debug Logging within the DNS Management Console
2. Capture 24 hours worth of data
3. Parse the resulting file to extract the date stamps and queries for that time period
4. Group the results such that we can find the total number of queries as well as the most popular queries

I initially tried this approach and while it would have worked with smaller files, it failed miserably in this case due to size of the data involved. This DNS server was generating log files in the neighborhood of 240MB per minute. My initial parsing code to extract the query names was taking about 90 minutes to run on each file. As a result, a large number of queries were missed. I eventually realized that if I was going to solve this problem, I was going to have to get clever and optimize.

Continue reading