Saturday, August 2, 2008

WMIC XSL CSV output formatting

While using the wmic command with the /format:csv option, it occurred to me that it would be useful to reformat numbers if appropriate and have the CSV fields enclosed in quotes to allow outputting fields containing commas.

Take a copy of the %windir%\system32\wbem\csv.xsl (I've called mine csv2.xsl in this example), and modify the template match for 'VALUE' to the line below:
<xsl:template match="VALUE" xml:space="preserve">"<xsl:choose><xsl:when test="string(number(.))='NaN'"><xsl:value-of select="."/></xsl:when><xsl:when test=". > 1000000000"><xsl:value-of select="string(format-number(.,'###,###,###'))"/></xsl:when><xsl:otherwise><xsl:value-of select="."/></xsl:otherwise></xsl:choose>"</xsl:template>

This will:

  • Check if the value is a number
  • If not, output as normal.
  • If it is a number, and if the number is greater than 1000000000, reformat with commas as thousand separators.
  • If if is a number, and less than 1000000000, output as normal.
    Output the results with quotes surrounding the data, useful when the data may contain comma's (as in this case)

I find this useful when I'm querying remote machines for their free/total disk space, when the number comes back as a daunting 227770765312 bytes, which is much easier to interpret when reformatted as 227,770,765,312 (~227GB or ~212 depending on whether you're a 1000 or 1024 kind of person)

A query using this modified xsl transform:

wmic /node:"server-01","server-02","server-03" path Win32_LogicalDisk WHERE "FileSystem='NTFS' AND Name != 'C:' AND Name != 'D:'" GET Name,Size,FreeSpace,VolumeName /format:csv2

Note the double-quotes surrounding the node-names, which is required when a server name contains a hyphen. When specifying more than one node by commas, each node is surrounded by quotes.

You could also actually divide the number by (/1024/1024/1024) to give a GB figure, or any other number of output modifications to the original data.

Note that when redirecting the wmic command to file it will result in a Unicode file, and by default when loading a .csv in Excel it won't split the columns automatically.

To output as UTF-8, you can either:

  • Modify the xsl output element to use a different encoding to the default utf-16, such as utf-8 or us-ascii
  • Use the 'type' command - eg. type output.csv output8.csv will take a Unicode file and provide as ASCII output

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


Greg said...

Wayne, can you verify the syntax of the first line of this. Seems suspect since there appears an extra quote at the end of line.

(but, I am only an xml apprentice!)

xsl:template match="VALUE" xml:space="preserve">"

\\Greg Martin

Wayne Martin said...

Hi Greg,

The syntax is correct, that extra quota is actually part of the output outside the XSL element - quotes wrapped around each value, eg:


tbb said...

Hello Wayne,

ive got a problem with csv output:
first the command

WMIC NTEVENT WHERE "EventType<3 AND LogFile !='security' and TimeGenerated > '20080826000000.000000-240'" GET Message, TimeGenerated, LogFile, SourceName, EventType /format:csv

if the message contains a "," like "anytext, is there" then the order is broken. how can i fix this?

Do you know if there is an option to set up the seperator like "§" instead of ",".

Greetings Torsten

Wayne Martin said...

Hi Torsten,

That’s the main reason I have added the quotes above in the updated transform – in CSV when a field contains an embedded comma, you have to surround each field with double-quotes, eg:
"test","anytext, is there" <-- only two fields, despite there being three commas

So you could either use my modified transform to double-quote fields, or modify the xsl yourself and change the delimiter (any comma's in the xsl) to whatever other character you're after.

Michael L Perry said...

Hi, Wayne.

Have you tried the new WMI extensions in .NET 3.5 SP1? I'm having trouble creating an instance of a custom management class. wmic gives me the following error:

wmic:root\cli>path Activity create Name="fee"
Create instance of 'Activity' class (Y/N)?y
Code = 0x80041002
Description = Not found
Facility = WMI

I'm not sure exactly what is "Not found". I know the class is defined, because I can query it and other instances that my application creates.

Thanks for the great resource.

Anonymous said...

In W7 csv.XSL file not found in the folder C: \ Windows \ System32 \ wbem. This file is located within a corresponding language folder (in this case C: \ Windows \ System32 \ wbem \ en-GB).
The solution is to move the file (csv.XLS) to the folder C: \ Windows \ System32 \ wbem



Mike said...

Wayne, I found this post to be very interesting. The /output and /format flags do not seem to be assisting me. I have started using WMIC to remove and install things like Java on my systems to make my admin life easier. I need to generate reports after doing so. Is there anyway, withing wmic to out put the system name a comma, then completion status so that I can write to a CSV?

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 13 years, and I know just about enough to realise that I don’t know very much.