Monday, July 28, 2008

Enumerating File Server Resources

This post provides several methods of enumerating sessions, connections and open files on a local or remote machine. Apart from the Powershell script that calls NetFileEnum there's nothing particularly new about this post, but I thought it was worth putting several different methods in one place.

This information is useful for gauging the use of file servers in particular, including the detail of which users are connected and what files they have locked.

Querying information against a single computer:

  • Run 'compmgmt.msc /computer=%server%' and look in 'Shared Folders' for Shares, Sessions and 'Open Files'
  • Run 'procexp.exe' (Process Explorer - SysInternals) on the machine and view the handles of the server service
  • psexec \\%server% net file
  • psloggedon \\server
  • psexec \\%server% -c handle.exe

Querying against one or more computers:

  • for %i in (server1 server2 server3 server4) do wmic /node:"%i" path win32_serverconnection get * /format:csv >> ServerConnections.txt
  • for %i in (server1 server2 server3 server4) do wmic /node:"%i" path win32_serversession get * /format:csv >> ServerSessions.txt
  • for %i in (server1 server2 server3 server4) do cscript //nologo listOpenFiles.vbs %i >> OpenFiles_%dateflat%.txt
  • for %i in (server1 server2 server3 server4) do psexec \\%i net file
    $servers = ("server1", "server2") foreach {. .\EnumOpenFiles.ps1 -s $_} write-host

Closing handles:

  • The compmgmt.msc GUI
  • The .remove method in the VBS below
  • net session \\%client% [/delete]
  • net file %id% [/close]

' VBScript
On Error Resume Next

If wscript.arguments.count = 1 Then
 strserver = wscript.arguments.unnamed(0)
 wscript.echo "Error, no server name provided"
End If

Set objConnection = GetObject("WinNT://" & strServer & "/LanmanServer")
Set colResources = objConnection.Resources

For Each objResource in colResources
    Wscript.Echo objResource.User & "," & objResource.LockCount & "," & objResource.Path & "," & objResource.Name

  ' Remove the session: colResources.Remove(objResource.Name)


# PowerShell
# EnumOpenFiles #
# 28/07/2008, martinwx, Initial version
# Description:
#   Call NetFileEnum() to enumerate open files on a file server
#   the computer that a user is connecting from.
# Assumptions, this script works on the assumption that:
#   The caller has administrator or server operator rights to the specified machines
# Usage
#   Enumerate open files from the specified server:
#   . .\EnumOpenFiles.ps1 -s server
# References

$server = $null

if (($args.count -eq 2) -or ($args.count -eq 4)) {
  for ($i = 0; $i -le $args.count-1; $i+=2) {
    if ($args[$i].ToLower().Contains("-s")) {
      $server = $args[$i+1]
    } elseif ($args[$i].ToLower().Contains("-v")) {
      $verbosePreference = $args[$i+1]

$info = ""
if ($server -eq $null) {
    write-output "No server specified, listing local files"

$provider = new-object Microsoft.VisualBasic.VBCodeProvider
$params = new-object System.CodeDom.Compiler.CompilerParameters
$params.GenerateInMemory = $True
$refs = "System.dll","Microsoft.VisualBasic.dll"

$txtCode = @'
Imports System
Imports System.Runtime.InteropServices
Imports System.Net

Class EnumFiles

Const ERROR_SUCCESS As Long = 0
Private Const MAX_PREFERRED_LENGTH As Long = -1

Private Const PERM_FILE_READ = &h1
Private Const PERM_FILE_WRITE = &h2
Private Const PERM_FILE_CREATE = &h4
Private Const ACCESS_EXEC = &h08  '  Execute Permission (X)
Private Const ACCESS_DELETE = &h10  '  Delete Permission (D)
Private Const ACCESS_ATRIB = &h20  '  Change Attribute Permission (A)
Private Const ACCESS_PERM = &h40  '  Change ACL Permission (P)

Private Structure FILE_INFO_3
    public fi3_id As Integer
    public fi3_permissions As Integer
    public fi3_num_locks As Integer
    public fi3_pathname As String
    public fi3_username As String

    'fi3_id  Specifies a DWORD value that contains the identification number assigned to the resource when it is opened.
    'fi3_permissions Specifies a DWORD value that contains the access permissions associated with the opening application. This member can be one or more of the following values.
    'fi3_num_locks  Specifies a DWORD value that contains the number of file locks on the file, device, or pipe.
    'fi3_pathname  Pointer to a string that specifies the path of the opened resource.
    'fi3_username Pointer to a string that specifies which user (on servers that have user-level security) or which computer (on servers that have share-level security) opened the resource. Note that Windows does not support share-level security.

End Structure

Declare Auto Function NetApiBufferFree Lib "netapi32" (ByVal Buffer As Long) As Long

Declare Function NetFileEnum Lib "netapi32.dll" ( _
    ByVal servername As String, _
    ByVal basepath As String, _
    ByVal username As String, _
    ByVal level As Integer, _
    ByRef bufptr As Integer, _
    ByVal prefmaxlen As Integer, _
    ByRef entriesread As Integer, _
    ByRef totalentries As Integer, _
    ByVal resume_handle As Integer) As Integer

Function Main(Optional ByVal servername As String = Nothing) As String()
   Dim result  As Long
   Dim ptr  As IntPtr
   Dim bufptr  As Long  'out buffer
   Dim i  As Integer
   Dim fi3  As FILE_INFO_3
   Dim finfo  As Object
   Dim dwEntriesread As Long  'out
   Dim dwTotalentries As Long  'out
   Dim dwResumehandle As Long  'out
   Dim iPermissions  As Integer
   Dim sPermissions As String = ""

    If servername = "" Then servername = Nothing

    result = NetFileEnum(servername, _
   Nothing, _
   Nothing, _
   3, _
   bufptr, _
   dwEntriesRead, _
   dwTotalEntries, _

    'Console.WriteLine("Entries: " + dwTotalentries.ToString() + ", " + dwEntriesRead.ToString())

    Dim output(dwEntriesRead-1) As String        ' Re-dim the array for the number of results
    if result = 0 then

        ptr = BufPtr          ' Convert long to pointer
        For i = 0 To dwEntriesread -1
     sPermissions = ""
            ptr = BufPtr          ' Convert long to pointer
            fi3 = CType(Marshal.PtrToStructure(ptr, GetType(FILE_INFO_3)),FILE_INFO_3)   ' Marshal this record of the output buffer to the structure
            BufPtr = BufPtr + Marshal.SizeOf(fi3)       ' Increment for the next record

     iPermissions = fi3.fi3_permissions
     If (iPermissions AND PERM_FILE_READ) Then sPermissions = sPermissions + "+Read"
     If (iPermissions AND PERM_FILE_WRITE) Then sPermissions = sPermissions + "+Write"
     If (iPermissions AND PERM_FILE_CREATE) Then sPermissions = sPermissions + "+Create"
     If (iPermissions AND ACCESS_EXEC) Then sPermissions = sPermissions + "+Execute"
     If (iPermissions AND ACCESS_DELETE) Then sPermissions = sPermissions + "+Delete"
     If (iPermissions AND ACCESS_ATRIB) Then sPermissions = sPermissions + "+Attr"
     If (iPermissions AND ACCESS_PERM) Then sPermissions = sPermissions + "+Security"

     If sPermissions.Length > 1 Then sPermissions = sPermissions.Remove(0,1)

            output(i) = serverName + "," + fi3.fi3_id.ToString() + "," + fi3.fi3_pathname + "," + _
                        fi3.fi3_username + "," + fi3.fi3_num_locks.ToString() + "," + sPermissions
        Output = Nothing
    End If

    Call NetApiBufferFree(ptr)         ' Free the memory

    return output
End Function

end class


$cr = $provider.CompileAssemblyFromSource($params, $txtCode)
if ($cr.Errors.Count) {
    $codeLines = $txtCode.Split("`n");
    foreach ($ce in $cr.Errors)
        write-output -i "Error: $($codeLines[$($ce.Line - 1)])"
        write-output -i $ce
        #$ce write-output
    Throw "INVALID DATA: Errors encountered while compiling code"
$mAssembly = $cr.CompiledAssembly
$instance = $mAssembly.CreateInstance("EnumFiles")

#write-output "Enumerating open files from $server"
$result = $instance.main($server)    # Call the VB.Net entry point
write-output -i $result


IADsResource Property Methods

IADsResource Interface

List Open Sessions and Open Files

Wayne's World of IT (WWoIT)

Read more!

Thursday, July 24, 2008

WMIC Custom Alias and Format

This post describes my first attempt at creating a WMIC alias, which provides an easy way of allowing people to run complex queries with a single alias.

Note that while creating new WMIC aliases provides a very flexible and transportable solution, another very useful part of WMIC discussed here is the use of the 'format' command to format the output in one of many formats - CSV, XML, HTML tables - or passed through any custom XSL.

The examples here provide an alias and output of printer jobs from a cluster node, using the perfdata information, which combined with HTML table output, provides a repeatable method of displaying print spooler information on a 2003 cluster node.

As a summary, this post describes using WMIC to:

  • Use the format command to modify output, using either an alias or a path/get command
  • Create, compile and run a custom alias using WMIC
  • Modify one of the builtin XSL files to allow sorting by ascending/descending

The Format option

The following commands provide different examples of formatting output as CSV from a path/get command, as well as various combinations of using the custom alias created below.

Use WMIC to get instances of a class and format the output as CSV
wmic path win32_process get name,commandline /format:csv

Use WMIC aliases to format the output in CSV or XML
wmic process list /format:xml
wmic process list /format:csv

Use WMIC aliases to format the output in HTML TABLE, MOFCSV or XML
wmic process list /format:htable
wmic process list /format:HMOF

Use WMIC aliases to sort the output in HTML
wmic process list /format:htable:"sortby=Name" > test.html

Use WMIC aliases to filter and sort the output in CSV
wmic process get name /format:csv:"datatype=text":"sortby=Name"

Use WMIC remotely aliases to retrieve command-line process arugments
wmic /node:"%server%" process get name,CommandLine /format:csv:"sortby=Name"

Use WMIC wmic aliases to sort the result set by number
wmic Logon get /Format:htable:"datatype=number":"sortby=LogonType"

Use a custom WMIC alias to report printer info from a cluster node in CSV
wmic /node:"%server%" spoolerjobs list /format:table

Use a custom WMIC alias to report sorted HTML printer info from a cluster node
wmic /node:"%server%" spoolerjobs list /format:htable:"datatype=number":"sortby=TotalJobsPrinted"

Use a custom WMIC alias to report a brief summary of printer statistics
wmic /node:"%server%" spoolerjobs list brief /format:htable:"datatype=number":"sortby=TotalJobsPrinted"

Use a custom WMIC alias and xsl to sort print jobs output descending HTML table
wmic /node:"%server%" spoolerjobs list brief /format:"htabledesc-sortby.xsl":"datatype=number":"orderby=descending":"sortby=TotalJobsPrinted" > test.html

Query a user from AD using WMI
wmic /node:"%DC%" /namespace:\\root\directory\LDAP path ds_user where "ds_cn='%username%'" GET ds_displayName,DS_UserPrincipalName,ds_cn,ds_name,ds_whenCreated

Create, compile and run a custom alias using WMIC

The following steps were taken to created and compile the MOF file:

  1. Use the CIM Studio, root\cli namespace
  2. Select the MSFT_CliAlias Class
  3. Double-click the 'MOF Generator' button (top-right, next to 'MOF Compiler' which is next to the help icons).
  4. Select at least one instance of the class to export as well. The 'Startup' alias is relatively simple and was used in this example
  5. Choose a filename and path
  6. Remove the class definition from the MOF
  7. Modify the instance definition -
    1. Create/modify MSFT_CliProperty properties to set the derivation, description and name as appropriate for the data you are retrieving
    2. Add qualifiers to the objects as appropriate, providing greater integrity of the dataset
    3. Change the FriendlyName to be the new alias name, and the target WMI query
    4. Use the PWhere attribute to specify an optional where query clause with the value specified at the command prompt
  8. Use mofcomp -check to validate the MOF
  9. Use mofcomp to compile into the repository

To customise the XSL to add the ability to sort by ascending or descending in the htable output:

  1. copy c:\WINDOWS\system32\wbem\htable-sortby.xsl c:\WINDOWS\system32\wbem\htabledesc-sortby.xsl
  2. Add parameter: <xsl:param name="orderby" select="'ascending'"/>
  3. In the XSL:Sort element, add: order="{$orderby}"

The MOF file:

//* File: ClusterPrintJobs.mof

// References:
// Win32_PerfFormattedData_Spooler_PrintQueue Class
// Creating and editing formats in WMIC
// Creating and editing aliases

// Author:  Wayne Martin
// Date:    22/07/2008
// Example uses:
// Use a custom WMIC alias to report printer info from a cluster node in CSV:
//   wmic /node:"b%server%" spoolerjobs list /format:table
// Use a custom WMIC alias to report sorted HTML printer info from a cluster node:
//   wmic /node:"%server%" spoolerjobs list /format:htable:"datatype=number":"sortby=TotalJobsPrinted"
// Use a custom WMIC alias to report a brief summary of printer statistics
//   wmic /node:"%server%" spoolerjobs list brief /format:htable:"datatype=number":"sortby=TotalJobsPrinted"
// Use a custom WMIC alias and xsl to sort print jobs output descending HTML table
//   wmic /node:"%server%" spoolerjobs list brief /format:"htabledesc-sortby.xsl":"datatype=number":"orderby=descending":"sortby=TotalJobsPrinted" > test.html 

//* This MOF was generated from the "\\.\ROOT\cli"
//* namespace on machine "-".
//* To compile this MOF on another machine you should edit this pragma.
#pragma namespace("\\\\.\\ROOT\\cli")

//* Instances of: MSFT_CliAlias
instance of MSFT_CliAlias
 Connection = 
 instance of MSFT_CliConnection
  Locale = "ms_409";
  NameSpace = "ROOT\\CIMV2";
  Server = ".";
 Description = "List print jobs for each printer on the specified node, and spooler totals.";
 Formats = {
  instance of MSFT_CliFormat
   Name = "SYSTEM";
   Properties = {
    instance of MSFT_CliProperty
     Derivation = "__CLASS";
     Name = "__CLASS";
    instance of MSFT_CliProperty
     Derivation = "__DERIVATION";
     Name = "__DERIVATION";
    instance of MSFT_CliProperty
     Derivation = "__DYNASTY";
     Name = "__DYNASTY";
    instance of MSFT_CliProperty
     Derivation = "__GENUS";
     Name = "__GENUS";
    instance of MSFT_CliProperty
     Derivation = "__NAMESPACE";
     Name = "__NAMESPACE";
    instance of MSFT_CliProperty
     Derivation = "__PATH";
     Name = "__PATH";
    instance of MSFT_CliProperty
     Derivation = "__PROPERTY_COUNT";
     Name = "__PROPERTY_COUNT";
    instance of MSFT_CliProperty
     Derivation = "__RELPATH";
     Name = "__RELPATH";
    instance of MSFT_CliProperty
     Derivation = "__SERVER";
     Name = "__SERVER";
    instance of MSFT_CliProperty
     Derivation = "__SUPERCLASS";
     Name = "__SUPERCLASS";
  instance of MSFT_CliFormat
   Name = "INSTANCE";
   Properties = {
    instance of MSFT_CliProperty
     Derivation = "Name";
     Description = "the print queue. ";
     Name = "Name";
     Qualifiers = {
      instance of MSFT_CliQualifier
       Name = "MaxLen";
       QualifierValue = {"64"};
  instance of MSFT_CliFormat
   Format = "LIST";
   Name = "FULL";
   Properties = {
    instance of MSFT_CliProperty
     Derivation = "Name";
     Description = "Name of the print queue.";
     Name = "Name";
    instance of MSFT_CliProperty
     Derivation = "Jobs";
     Description = "Current number of jobs in a print queue.";
     Name = "Jobs";
    instance of MSFT_CliProperty
     Derivation = "TotalJobsPrinted";
      Description = "Total number of jobs printed on a print queue after the last restart.";
      Name = "TotalJobsPrinted";
    instance of MSFT_CliProperty
     Derivation = "TotalPagesPrinted";
     Description = "Total number of pages printed through GDI on a print queue after the last restart.";
     Name = "TotalPagesPrinted";
    instance of MSFT_CliProperty
     Derivation = "MaxJobsSpooling";
     Description = "Maximum number of spooling jobs in a print queue after the last restart.";
     Name = "MaxJobsSpooling";
    instance of MSFT_CliProperty
     Derivation = "JobErrors";
     Description = "Total number of job errors in a print queue after the last restart.";
     Name = "JobErrors";
    instance of MSFT_CliProperty
     Derivation = "OutOfPaperErrors";
     Description = "Total number of out-of-paper errors in a print queue after the last restart.";
     Name = "OutOfPaperErrors";
     Qualifiers = {
      instance of MSFT_CliQualifier
       Name = "CookingType";
       QualifierValue = {"PERF_COUNTER_RAWCOUNT"};
      instance of MSFT_CliQualifier
       Name = "Counter";
       QualifierValue = {"OutofPaperErrors"};
      instance of MSFT_CliQualifier
       Name = "PerfTimeStamp";
       QualifierValue = {"Timestamp_PerfTime"};
      instance of MSFT_CliQualifier
       Name = "PerfTimeFreq";
       QualifierValue = {"Frequency_PerfTime"};
  instance of MSFT_CliFormat
   Format = "TABLE";
   Name = "BRIEF";
   Properties = {
    instance of MSFT_CliProperty
     Derivation = "Name";
     Description = "Name of the print queue.";
     Name = "Name";
    instance of MSFT_CliProperty
     Derivation = "TotalJobsPrinted";
     Description = "Total number of jobs printed on a print queue after the last restart.";
     Name = "TotalJobsPrinted";
    instance of MSFT_CliProperty
     Derivation = "TotalPagesPrinted";
     Description = "Total number of pages printed through GDI on a print queue after the last restart.";
     Name = "TotalPagesPrinted";
 FriendlyName = "SpoolerJobs";
 //PWhere = "where Caption='#'";
 Target = "Select Name,Jobs,TotalJobsPrinted,TotalPagesPrinted,MaxJobsSpooling,JobErrors,OutOfPaperErrors from Win32_PerfFormattedData_Spooler_PrintQueue";

//* EOF ClusterPrintJobs.mof


WMI Adminsitrative Tools (contains CIM Studio):

Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.

Read more!

Sunday, July 20, 2008

AD site discovery

This post discusses the scenario where in an Active Directory with one site, that site is always returned as the client site, even if the subnet isn't recorded in AD. This is not necessarily the intuitive result, and I couldn't easily see this documented anywhere by Microsoft, hence the testing below.

The theory is that because there is only a single site in AD, all clients will belong to that site by default regardless of IP subnet. The converse is also true, in that if there is more than one site in the directory, then without correct IP subnets a client will be unable to determine its site.

To prove this theory, I created a site in the a 2003 AD lab and ran some tests:

  1. As soon as more than one site exists in the directory, if a workstation’s IP address is not part of a known subnet linked to a site, ‘null’ will be returned when querying the site a client belongs to.
  2. If only a single site exists, with or without the subnets in AD the client will return the first and only site as the current workstation site


  1. In the first test where the site could not be determined, the netlogon locator was still able to find and use a DC, but in a distributed environment it would more than likely not be the closest DC.
  2. This would almost certainly cause issues with SMS using AD site boundaries. As soon as a second site was added, any workstations in subnets that were not in AD would stop working as SMS clients.

Test process:

  1. Created a second site in the lab, two sites now exist, with no subnets
  2. Waited a minute or so, from an XP workstation: 'nltest /dsgetdc:domain' still returns ‘our site’ of default-first-name-site
  3. Restart netlogon on an XP workstation, same result ]
  4. Restarted the workstation
  5. nltest /dbflag:0x2080ffff
  6. net stop netlogon & net start netlogon
  7. Check netlogon and nltest, site name of null and nltest omits ‘our site’ from the output:
    find /i "site" c:\windows\debug\netlogon.log
    07/14 09:56:35 [SITE] Setting site name to '(null)'
    07/14 09:56:38 [INIT] SiteName (0) = (null)
    07/14 09:56:38 [INIT] CloseSiteTimeout = 900 (0x384)
    07/14 09:56:38 [INIT] SiteNameTimeout = 300 (0x12c)
    07/14 09:56:38 [INIT] AutoSiteCoverage = TRUE
    07/14 09:56:38 [SITE] Setting site name to '(null)'
    C:\Program Files\Support Tools>nltest /dsgetdc:domain
    DC: \\TESTAD1
    Address: \\
    Dom Guid: 3aa9ef9a-f1b4-4ce4-a85b-823b259c4919
    Dom Name: domain
    Forest Name: domain.local
    Dc Site Name: Default-First-Site-Name
  8. Deleted the second site
  9. reboot an XP workstation, check netlogon and nltest:
    find /i "site" c:\windows\debug\netlogon.log
    07/14 10:01:43 [SITE] Setting site name to '(null)'
    07/14 10:01:43 [SITE] Setting site name to 'Default-First-Site-Name'
    07/14 10:01:43 [INIT] SiteName (0) = Default-First-Site-Name
    C:\>nltest /dsgetdc:domain
    DC: \\TESTAD1
    Address: \\
    Dom Guid: 3aa9ef9a-f1b4-4ce4-a85b-823b259c4919
    Dom Name: domain
    Forest Name: domain.local
    Dc Site Name: Default-First-Site-Name
    Our Site Name: Default-First-Site-Name
    The command completed successfully 

Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.

Read more!

Friday, July 18, 2008

Passing Parameters between OpsMgr and SSRS

This post provides information on passing parameters between Operations Manager 2007 and SQL Server Reporting Services (SSRS) when creating customised reports. It discusses Operations Manager Smart Controls, using dataset queries and populating parameters from a string.

This is the third in a series of posts on creating customised Operations Manager reports; see the posts 'OpsMgr 2007 Customized Reporting - SQL Queries' and 'OpsMgr 2007 SSRS Reports using SQL 2005 XML'.

Report parameters define input parameters to the report, which are then typically passed to dataset parameters, used to determine the data displayed in the report. The dataset parameters can be used in-line with text-based SQL queries, or passed to a SQL Stored Procedure.

Report Smart Controls

Operations Manager provides several controls tailored to OpsMgr specific reporting. For example:

  • Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.MonitoringObjectXmlPicker
  • Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.PerformanceRulePicker
  • Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.TextBox

The controls are documented in the reporting guide and can be seen in use by unsealing the standard Microsoft Management Packs. Generally the controls relate to data stored in Operations Manager, such as the Monitoring Object or Performance Rule pickers, providing methods of selecting and passing existing OpsMgr data types as parameters.

The example below shows the definition of the control for a multi-value textbox, using the multiline property element to enable multiline support for the control. The idea of this control is to provide a text-string filter that would be passed as a parameter to SSRS to filter or determine the final output of a report.

<Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.TextBox" rowSpan="3" columnSpan="1">
    <ReportParameter name="InstanceFilter">
    <Property name="Multiline">

Populating parameters from a string

Single and multi-valued strings can be used to provide default parameters to the report. Unfortunately, the multi-line support of this control does not map to a multi-value string input expected by SSRS, and only the first instance works. The correct method would be to use another control to select instances based on the object/rule and then pass this to the SQL query for filtering.

Populating parameters from a dataset query

Populating parameters from a dataset query is a flexible method of providing parameter defaults that can be designed to minimise changes in the future. For example, the default group target for a report can remain constant, with only the members of that group changing as reporting needs change. Rather than hard-coding a partulcar GUID, a lookup based on a well-known name can also save re-work.

For example, the following direct text SQL query returns the Rule GUID for the 'Logical Disk Free Megabytes' rule, which could be used to populate a default report parameter, providing a default when the report is opened:

SELECT vRule.RuleGuid from vRule
inner join vPerformanceRule ON vPerformanceRule.RuleRowID = vRule.RuleRowID
WHERE vRule.RuleDefaultName = 'Logical Disk Free Megabytes'

Another example is constructing XML to find the ManadedEntityRowID of a particular group, again useful for populating report parameter defaults:

SELECT '<Data><Objects><Object Use=''Containment''>' + Cast(ManagedEntityRowID as varchar) + '</Object></Objects></Data>' as XMLManagedEntity
FROM vManagedEntity WHERE ManagedEntityDefaultName = 'Custom Group Name'

Note the use of the double single-quotes, required to allow this text to be stored in an XML management pack definition while still resulting in well-formed XML.


OpsMgr 2007 Customized Reporting - SQL Queries

OpsMgr 2007 SSRS Reports using SQL 2005 XML

Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.

Read more!

Tuesday, July 15, 2008

Analyzing Windows Kernel Dumps

This post contains information on steps I took to investigate a kernel mode dump with a repeatable 0x7F bugcheck caused on a workstation when printing a specific document to a generic LaserJet 4000 on a remote print server.

After analysing the kernel dump, the problem appeared to be occurring in the kernel-mode GDI win32k.sys driver, and using this information I was able to find a hotfix that did resolve the issue.

I don't pretend to understand a large portion of what was visible in the kanalyze results, but the information that I did glean was enough to point to the hotfix very quickly.

The following steps were taken to analyse the kernel dump created by the stop 0x7F error:

  1. Install debugging tools, The Kanalyze guide recommends version 32-bit (the most recent is dbg_x86_6.9.3.113.msi)
  2. Install Kanalyze 8.1. The default directory is c:\kktools\kanalyze8.1
  3. Copy c:\kktools\kanalyze8.1\x86\*.* to the ‘\Program Files\Debugging Tools for Windows’ directory (including the plug-ins subdirectories)
  4. Run ‘\Program Files\Debugging Tools for Windows\kanalyze.exe
    1. Perform a new memory dump
    2. Type the location of the dump file (usually c:\windows\memory.dmp if a local dump)
    3. Leave the symbol and binaries path to the HTTP locations (requires Internet access)
    4. Select ‘Show Detail’ and then Next

    5. The crashdump analysis will begin, which can be relatively time consuming (~30 minutes for the small 87MB crashdump of my workstation).

  5. When the analysis is complete, select ‘Browse’ to view the results, including
    1. Analysis Summary – The automatically generated summary, providing a best-guess of the potential cause of the crash. Note that in this crash, the AnalysisSummary.txt file shows that the best guess was rdbss.sys – the redirected drive buffering subsystem, used by the SMB mini-redirector.
    2. Analysis TriageInfo CrashStackInfo – Information on the kernel stack at the time of the crash. For this crash, this information seems more useful, as it shows the GDI bitblt and dithercolor functions being called, followed by a divide by zero trap in the kernel and the bugcheck. This gives quite a bit more information to go on when searching for cause and resolution
    3. Analysis TriageInfo CrashProcess – The process that caused the crash, in this case, winword.exe, providing information on the memory and processed (useful for other areas in the debug namespace)
    4. Analysis TriageInfo CrashThread – The thread that caused the crash, in this case, the kernel thread ID and start address space, useful for other areas in the debug namespace
    5. Analysis Deadlock – Current deadlocks – conflicts between threads trying to exclusively access code. Locks occur constantly, but a deadlock can occur when two or more threads have code locked and are waiting for the other(s) to continue. The deadlock in this scenario is presumed to be a result of the divide by zero kernel error, and not causative
    6. Root – Provides an interesting overview of the memory allocation of the system, including boot, HAL, non-paged/paged, kernel and usermode address spaces.

The following stack information from the crash dump was what pointed me in the hotfix direction, the bitblt and drvdither calls which resulted in a divide by zero error in the kernel:

          |            |   StackLimit   = aa1f5000
          |      :     |
          |            |   KernelStack  = aa1f85d8
   /|     |            |
    |     |      :     |
    |     |            |
  stack   +------------+
  growth  |            |   InitialStack = aa1f9000

ChildEBP Ret-Addr Called Procedure
aa1f8984 80596813 nt!KeBugCheck + 14
aa1f89dc 8053d60f nt!Ki386CheckDivideByZeroTrap + 41
aa1f89dc bf903230 nt!KiTrap00 + 83
aa1f8a94 bf81af4d win32k!UMPDDrvDitherColor + 7d
aa1f8b24 bf826e70 win32k!bGetRealizedBrush + 2bd
aa1f8b3c bf82f869 win32k!pvGetEngRbrush + 1f
aa1f8b98 bf805363 win32k!EngBitBlt + 283
aa1f8be8 bf80bd41 win32k!GrePatBltLockedDC + 1ea
aa1f8d54 8053c9fa win32k!NtGdiFlushUserBatch + 689
aa1f8d70 aaea754a nt!KiFastCallEntry + ca
aa1f8ddc 805411c2 rdbss!RxpWorkerThreadDispatcher + 18a
aa1f8de8 00000000 nt!KiThreadStartup + 16


  1. The analysis requires more than a minidump. Use sysdm.cpl Advanced Startup and Recovery Settings to write at least a ‘Kernel memory dump’, rather than a small / minidump.
  2. The console can be used to execute plug-in commands, or standard kernel debug commands (eg VIEWCMD or kd !ntsdexts.locks). Either click on the console button, or use the File Command Window menu option on the browser


Debugging Tools – v6.5.3.8:

Kernel Memory Space Analyzer Version 8.1

First Step Guide to Kernel Memory Space Analyzer V8.1 (Kanalyze)



Debugging Deadlocks (No Ready Threads)

Error message when you try to print after you install the GDI security update 925902: "Stop 0x0000007F"

Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.

Read more!

Sunday, July 13, 2008

Process list with command-line arguments

This post provides information on using WMI to provide a list of processes with additional information, including the full path to the executable and any parameters passed to the command-line. While this may not sound that useful, this can greatly assist when troubleshooting or just understanding how applications work.

You can run this command against one server, or specify multiple nodes with a control file:
wmic /node:"server01" path win32_process get ExecutablePath,Caption,CommandLine,CreationDate,WorkingSetSize,ProcessId

To filter the list, you could also add a where clause:
wmic /node:"server01" path win32_process Where "Caption Like '%cscript%'" get ExecutablePath,Caption,CommandLine,CreationDate,WorkingSetSize,ProcessId

This information can be useful for diagnosing processes:

For example, Operations Manager 2007 uses cscript quite heavily, and at times I've seen many cscript processes running, but had no clear idea what they were doing.

cscript.exe "C:\WINDOWS\system32\cscript.exe" /nologo "CPUUtilization.vbs" 95 15 opsmgr01.test.local 100. 20080713130117.079481+600 9876 5820416
cscript.exe "C:\WINDOWS\system32\cscript.exe" /nologo "MemoryUtilization.vbs" 2.5 opsmgr01.test.local 114.66666666666667 20080713130301.876356+600 6832 2473984
cscript.exe "C:\WINDOWS\system32\cscript.exe" /nologo "DiscoverHealthServiceCommunicationRelationships.js" 20080713130337.876356+600 8484 2457600

Processes that normally show up as just 'cmd.exe' with tools like pslist.exe can easily be further identified:

cmd.exe CMD /D /S /Q /C""C:\Program Files (x86)\VERITAS\VxPBX\bin\pbxservice.cmd" "C:\Program Files (x86)\VERITAS\VxPBX\bin\pbx_exchange.exe""

It's easy to see command-line parameters used to launch some applications, eg, the 'manage your server' wizard is started with:
C:\WINDOWS\system32\oobechk.exe /LaunchMYS

And screensavers are started with a /s parameter:
logon.scr logon.scr /s

It is easy to see which host groups are being run by which instance of svchost (tasklist /svc also shows this information):

svchost.exe C:\WINDOWS\system32\svchost.exe -k DcomLaunch 20080323192353.500000+600 C:\WINDOWS\system32\svchost.exe 676 5496832
svchost.exe C:\WINDOWS\system32\svchost.exe -k rpcss 20080323192354.187500+600 C:\WINDOWS\system32\svchost.exe 780 9392128
svchost.exe C:\WINDOWS\system32\svchost.exe -k NetworkService 20080323192402.828125+600 C:\WINDOWS\system32\svchost.exe 1016 7897088
svchost.exe C:\WINDOWS\system32\svchost.exe -k LocalService 20080323192402.828125+600 C:\WINDOWS\system32\svchost.exe 1036 5959680
svchost.exe C:\WINDOWS\System32\svchost.exe -k netsvcs

Inconsistencies show up, such as instances of a cluster resource monitor on an x64 server, some running native, some WOW64:
resrcmon.exe "C:\WINDOWS\SysWOW64\resrcmon.exe" -e 1464 -m 1468 -p 2744 20080323192505.936883+600 C:\WINDOWS\SysWOW64\resrcmon.exe 3652 5472256
ResrcMon.exe "C:\WINDOWS\cluster\resrcmon.exe" -e 1592 -m 1596 -p 2744 20080323192506.686004+600 C:\WINDOWS\cluster\resrcmon.exe 3716 8388608

Instances of rundll32 and similar launch methods, often showing up interesting things, eg a notification baloon launched through rundll32:
rundll32.exe RunDll32.exe wlnotify.dll,ShowNotificationBalloon Global\00000000f0357177_WlballoonKerberosNotificationEventName

Executing this command

Add as a doskey macro

Put the following line into a text file called macros.txt:
PSL=if "$1" EQU "" (wmic path win32_process get ExecutablePath,Caption,CommandLine,CreationDate,WorkingSetSize,ProcessId) else (wmic /node:"$1" path win32_process get ExecutablePath,Caption,CommandLine,CreationDate,WorkingSetSize,ProcessId)

Then run the following command, which will execute the doskey command to install the macro as a command prompt is started:
reg add "hklm\software\microsoft\command processor" /v AutoRun /t reg_sz /d "doskey /macrofile=%path%\macros.txt"

The command works either locally or with a parameter, so you can either run:
psl server01

Call through Scripting

Instead of using wmic, you could also use scripting - either powershell or vbscript - to query the WMI instances.

eg, in PowerShell:

Get-WmiObject win32_process  Format-Table ExecutablePath,Caption,CommandLine,CreationDate,WorkingSetSize,ProcessId

Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.

Read more!

Wednesday, July 9, 2008

OpsMgr 2007 Customized Reporting - SQL Queries

This post provides information on SQL queries relating to Microsoft Operations Manager 2007. I do not have much knowledge of OpsMgr 2007 or SQL, but the following includes information on becoming familiar with the database, using OpsMgr groups and querying XML with SQL - all integral parts of authoring Operations Manager 2007 reports.

Familiarity with the OpsMgr Data warehouse database

The default reports provide many specific and generic report types, but apart from modifying the layout of an existing result set, the only way to report on extra information is to run additional queries against the data warehouse.

There are several different ways to become familiar with the Operations Manager 2007 Data Warehouse database, including:

  1. The Management Pack authoring guide, the report authoring guide and the design guide. See the References section.
  2. There are dozens of example queries in the sealed management packs provided with Operations Manager 2007. See below for information on unsealing a management pack
  3. There are many web pages providing example SQL queries and custom management packs.

Unsealing a Management Pack

Management packs usually exist in:
c:\Program Files\System Center Operations Manager 2007\

To unseal a management pack:

  1. Copy the management pack from the server to your workstation
  2. Change directory to the folder containing the script, Start PowerShell, and then run
  3. . .\UnsealMP.ps1 -f %path%\

The powershell script can be found at the end of this post.

This provides the XML for the management pack, typically containing any SQL queries used in the operation of that management pack.

Parsing Managed Entity IDs

One of the most useful smart parameter controls available in Operations Manager 2007 is the ParameterPrompt.ObjectList control, providing the ability to select one or more groups and/or objects to use as the managed entity filter for the report.

Creating Groups

Depending on the data being queried, it seems that the direct objects returned in the search are not always relevant. For example, when choosing instances of performance-based rules, selecting specific objects - such as a specific ‘Windows Computer’ instance - does not return any results. This is because the performance rules are created against a different ManangedEntityRowID - the logical disk free counter for each instance of a logical disk against that windows computer instance.

It is more practical and less management overhead to create a structured series of dynamic groups, used throughout all functions of Operations Manager. For example, groups containing types of servers, such as clusters, IIS servers, SMS servers etc usually makes sense when reporting.

This OpsMgr smart control creates XML and passes it as a string parameter to SSRS (SQL Server Reporting Services), which is in turn passed to the stored procedure used for the main dataset.

The following SQL query creates an example XML string containing the object ID of a group which essentialy contain a group of servers. This would return multiple managed entities relating to each server in the group, including the servers themselves, cluster servers, logical drives, network connections, group policy and license objects.

DECLARE @ExecError int
DECLARE @StartDate datetime
DECLARE @EndDate datetime
Set @EndDate = GetDate()
Set @StartDate = DateAdd(day, -1, @EndDate)

DECLARE @ManagedEntity table (ManagedEntityID int)
DECLARE @TESTGROUP nvarchar(256)

Select @TESTGROUP = '<data><objects><object use="Containment">
' + Cast(ManagedEntityRowID as varchar) + '</object></objects></data>'
from vmanagedentity where managedentitydefaultname = 'Test Group'

INSERT INTO @ManagedEntity
EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]
@StartDate = @StartDate,
@EndDate = @EndDate,
@ObjectList = @TESTGROUP

select * from vmanagedentity
inner join @ManagedEntity MET on vmanagedentity.ManagedEntityRowID = MET.ManagedEntityID

Querying XML using TSQL

Passing multi-valued parameters from the Operations Manager Smart Controls through the SSRS report to SQL can be difficult, and the method employed most by the default Microsoft reports seems to be XML.

SQL 2005 includes the XML data type and associated methods, including XQuery, a subset of the XPath query standard. In addition, SQL supports OPENXML – a rowset provider to construct a relational rowset view of an XML document.

The second method below is used in the ‘Managed Entity Current Instance’ report to pass a multi-valued string parameter as XML to the SQL query. After the XML has been transformed to a rowset, it is then used in the where clause to filter the instance names of the performance counters being returned.

Included below are two examples of processing an XML string, and querying an element value from the XML elements inside a SQL insert/select clause.

declare @execerror int
declare @xmldoc xml
declare @ixmldoc int
set @xmldoc = '<data><objects><object use="Containment">
123</object><object use="Containment">

DECLARE @tblTest table (test int)

/* Parse the XML document and insert the converted int object element value into temporary table */
element value into temporary table */
EXEC @ExecError = sp_xml_preparedocument @ixmldoc OUTPUT, @xmldoc
OPENXML (@ixmldoc, '/Data/Objects/Object')
WITH (InstanceFilter int '.')

select * from @tbltest

/* Translate the value of object nodes to an int from XML document into the test field of the table */ nodes to an int from XML document into the test field of the table */
INSERT INTO @tblTest (test)
select tblTest.test.value('.', 'int')
from @xmldoc.nodes('/Data/Objects/Object') AS tblTest(test)

select * from @tbltest

Example SQL

An example I was providing executes a generic version of the following SQL, selecting the most recent performance rule instance for each the specified rule and managed entity, in this case, servers with a name matching ‘%testserver%’ and the 'Logical Disk Free Megabytes’ performance rule.

This query generates a temporary named result set, partitioning that result set using the row-number() ranking windowing function, over managed entities by time and then selecting the first record of each partition.

WITH CurrentDiskFree AS
(SELECT PRI.Instancename, DateAdd(Hour, 10, PPR.DateTime) as DateTime, ME.Path, ME.ManagedEntityRowID, PPR.SampleValue,
ROW_NUMBER() OVER (partition by ME.ManagedEntityRowID order by PPR.DateTime DESC)as RowNumber
from vperformanceruleinstance PRI
inner join vPerformanceRule PR on PRI.RuleRowID = PR.RuleRowID
inner join perf.vPerfRaw PPR on PRI.PerformanceRuleInstanceRowID = PPR.PerformanceRuleInstanceRowID
inner join vManagedEntity ME on ME.ManagedEntityRowID = PPR.ManagedEntityRowID
inner join vRule RU ON RU.RuleRowID = PR.RuleRowID
WHERE RU.RuleDefaultName = 'Logical Disk Free Megabytes'
AND ME.Path like '%testserver%')
FROM CurrentDiskFree
where RowNumber = 1

# -- UnsealMP.ps1 -- #

  $FileInput = "",
  $outputDirectory = $pwd)

# Usage:
#  . .\UnsealMP.ps1 -f c:\temp\
#  . .\UnsealMP.ps1 -f c:\temp\ -o c:\ManagementPacks

$OMManagement = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement.OperationsManager")

if ($FileInput -ne "") {
    $mp = new-object Microsoft.EnterpriseManagement.Configuration.ManagementPack($FileInput)
    $mpWriter = new-object Microsoft.EnterpriseManagement.Configuration.IO.ManagementPackXmlWriter($outputDirectory)
} else {
  write-host "No management pack specified"


Operations Manager Report Authoring Guide

Microsoft Operations Manager 2007 Management Pack Authoring Guide

Introduction to the Operations Manager 2007 Design Guide

Operator Element (RDL)

FilterExpression Element (RDL)

Reports in Management Packs.

ManagementPackDataWarehouseScript.UpgradeUnsupported Property

Enabling the EnterpriseManagementChartControl

Example XML:

Defining Report Datasets for a SQL Server Relational Database

Select the Data Source (Report Wizard)

SSRS examples

Expression Examples in Reporting Services

Wayne's World of IT (WWoIT), Copyright 2008 Wayne Martin.

Read more!

All Posts

printQueue AD objects for 2003 ClusterVirtualCenter Physical to VirtualVirtual 2003 MSCS Cluster in ESX VI3
Finding duplicate DNS recordsCommand-line automation – Echo and macrosCommand-line automation – set
Command-line automation - errorlevels and ifCommand-line automation - find and findstrBuilding blocks of command-line automation - FOR
Useful PowerShell command-line operationsMSCS 2003 Cluster Virtual Server ComponentsServer-side process for simple file access
OpsMgr 2007 performance script - VMware datastores...Enumerating URLs in Internet ExplorerNTLM Trusts between 2003 and NT4
2003 Servers with Hibernation enabledReading Shortcuts with PowerShell and VBSModifying DLL Resources
Automatically mapping printersSimple string encryption with PowerShellUseful NTFS and security command-line operations
Useful Windows Printer command-line operationsUseful Windows MSCS Cluster command-line operation...Useful VMware ESX and VC command-line operations
Useful general command-line operationsUseful DNS, DHCP and WINS command-line operationsUseful Active Directory command-line operations
Useful command-linesCreating secedit templates with PowerShellFixing Permissions with NTFS intra-volume moves
Converting filetime with vbs and PowerShellDifference between bat and cmdReplica Domain for Authentication
Troubleshooting Windows PrintingRenaming a user account in ADOpsMgr 2007 Reports - Sorting, Filtering, Charting...
WMIC XSL CSV output formattingEnumerating File Server ResourcesWMIC Custom Alias and Format
AD site discoveryPassing Parameters between OpsMgr and SSRSAnalyzing Windows Kernel Dumps
Process list with command-line argumentsOpsMgr 2007 Customized Reporting - SQL QueriesPreventing accidental NTFS data moves
FSRM and NTFS Quotas in 2003 R2PowerShell Deleting NTFS Alternate Data StreamsNTFS links - reparse, symbolic, hard, junction
IE Warnings when files are executedPowerShell Low-level keyboard hookCross-forest authentication and GP processing
Deleting Invalid SMS 2003 Distribution PointsCross-forest authentication and site synchronizati...Determining AD attribute replication
AD Security vs Distribution GroupsTroubleshooting cross-forest trust secure channels...RIS cross-domain access
Large SMS Web Reports return Error 500Troubleshooting SMS 2003 MP and SLPRemotely determine physical memory
VMware SDK with PowershellSpinning Excel Pie ChartPoke-Info PowerShell script
Reading web content with PowerShellAutomated Cluster File Security and PurgingManaging printers at the command-line
File System Filters and minifiltersOpsMgr 2007 SSRS Reports using SQL 2005 XMLAccess Based Enumeration in 2003 and MSCS
Find VM snapshots in ESX/VCComparing MSCS/VMware/DFS File & PrintModifying Exchange mailbox permissions
Nested 'for /f' catch-allPowerShell FindFirstFileW bypassing MAX_PATHRunning PowerSell Scripts from ASP.Net
Binary <-> Hex String files with PowershellOpsMgr 2007 Current Performance InstancesImpersonating a user without passwords
Running a process in the secure winlogon desktopShadow an XP Terminal Services sessionFind where a user is logged on from
Active Directory _msdcs DNS zonesUnlocking XP/2003 without passwords2003 Cluster-enabled scheduled tasks
Purging aged files from the filesystemFinding customised ADM templates in ADDomain local security groups for cross-forest secu...
Account Management eventlog auditingVMware cluster/Virtual Center StatisticsRunning scheduled tasks as a non-administrator
Audit Windows 2003 print server usageActive Directory DiagnosticsViewing NTFS information with nfi and diskedit
Performance Tuning for 2003 File ServersChecking ESX/VC VMs for snapshotsShowing non-persistent devices in device manager
Implementing an MSCS 2003 server clusterFinding users on a subnetWMI filter for subnet filtered Group Policy
Testing DNS records for scavengingRefreshing Computer Account AD Group MembershipTesting Network Ports from Windows
Using Recovery Console with RISPAE Boot.ini Switch for DEP or 4GB+ memoryUsing 32-bit COM objects on x64 platforms
Active Directory Organizational Unit (OU) DesignTroubleshooting computer accounts in an Active Dir...260+ character MAX_PATH limitations in filenames
Create or modify a security template for NTFS perm...Find where a user is connecting from through WMISDDL syntax in secedit security templates

About Me

I’ve worked in IT for over 20 years, and I know just about enough to realise that I don’t know very much.