Combining CSV into one Excel File

CSVMerge is a small command line application that will take one or more CSV files and merge them all together into a single XLSX file. One Worksheet per CSV is created

CSVMerge Screenshot
Combining three files with automatic column sizing

The application supports wildcards, and any CSV column separator (such as semicolon and tabs). It can also automatically size columns to fit the contents.

ADeLe 1.01 Update

I released a little update for my App-V loading tool that adds the following things:

  • Fixed a bug that prevented launching shortcuts from a package that was sequenced in a PVAD
  • Fixed a bug that sometimes didn’t update the application window when a package got (un-)published
  • Added the ability to copy package info to the clipboard with right click or Ctrl+C

You can download the new version from here: http://exar.ch/adele-app-v-debugger/

ADeLe 1.0 Release

I just released version 1.0 of my App-V package debugging tool. Changes in this version are:

– Launching multiple commands in quick succession now adds them to a queue instead of displaying an error
– There is now an option to automatically unpublish packages before removing them
– There is now an option to start virtual applications as non-admin even if ADeLe is running as admin

You can download the new version from here: http://exar.ch/adele-app-v-debugger/

App-V Debugger release

For App-V 4 there used to be quite a nice tool called AC/DC from Login Consultants to load and debug packages. They did release a new version for App-V 5 too, but it suffers from general slowness which always annoyed me when having to test my sequenced packages.

So I simply made my own app and properly used multi-threading to make sure the UI always stays responsive even while the application is doing stuff. The end result was the “App-V Debugger and Loader” or short “ADeLe”.

You can watch a quick overview about what this tool can do here:

And download the app here: ADeLe 0.90

Automating SCCM global conditions with Powershell

Microsoft has added a lot of new Powershell cmdlets to Configuration Manager recently, but one thing is still notably absent from the official API, and that’s handling of global conditions, or Requirements as they are called in the GUI.

paint-requirements

A few months ago rfl_raphael posted a script on Technet that allows adding global conditions. I updated that script to support Windows 10, changed a few oddities such as allowing to run it directly from a ConfigMgr drive and fixed a few error messages. I also added two other scripts that allow reading and removing global conditions.

You can download the combined script collection here: GlobalConditionCmdlets

With these scripts you can for instance list all applications that only deploy to a primary user like this:

$applications = Get-CMApplication

$applications | % {
 $deploymenttypes = $_ | Get-CMDeploymentType

 $appname = $_.LocalizedDisplayName
 $deploymenttypes | % {
  [array]$requirements = Get-CMDeploymentTypeGlobalCondition.ps1 -ApplicationName $appname -DeploymentTypeName $_.LocalizedDisplayName

  if ($requirements.Name -like "*Primary*") {
   "Application $appname, DeploymentType $($_.LocalizedDisplayName) has"
   Write-Host ($requirements.Name) -ForegroundColor yellow
  }
 }
}

Collecting custom WMI classes with SCCM

In earlier versions of SCCM, customizing your inventory involved hand-coding MOF files and then replacing critical files in your SCCM installation directory with these. Even though SCCM was quite smart about parsing these and ignoring invalid files, this still scared most people off even attempting such things and thus custom inventory classes were sadly almost never used in production environments.

Luckily Microsoft streamlined the procedure in SCCM 2012 and it’s now possible to do this purely in the GUI. To extend the inventory, browse to “Administration” -> “Client Setting” then open your default client settings. Go to “Hardware Inventory” then click on “Set Classes…”

hardware-inventory

In the following window, click on “Add…” then on “Connect…” and enter the name of a PC that you have already run the script from part 1 on. You will then receive a list of the WMI classes available for collection on that PC, one of which will be our newly created one.

add-class

Select the MonitorDetails class for collection and press OK three times.

You can now either wait for the settings to take effect on your systems – which may take days depending on your hardware inventory cycle – or you can speed this up by first doing a Machine Policy Retrieval & Evaluation Cycle, waiting a few minutes for that to take effect, then follow up with a Hardware Inventory Cycle.

Our collected data showing up in resource explorer.
Our collected data showing up in resource explorer.

You will also get a new View in your SQL database called v_GS_MonitorDetails that you can query for reports.

In part 3 of this guide I will show you how to create a report to display this collected data.

Collecting monitor serial numbers with SCCM

Configuration Manager already collects some information about the monitors by default, but it is being extremely stingy about it.

Only the resolution is really of any use to us, everything else is quite pointless
This is what ConfigMgr collects out of the box. Only the resolution is really of any worth to us, everything else is useless.

Monitors actually advertise quite a bit more about themselves to the computer in the form of the EDID record. They do that so the operating system can display valid resolutions to the user to choose from, but this record also includes some extremely useful data for our inventory, mostly the manufacturer name, the serial number and the manufacturing date.

Windows collects this data and stores it in the registry in a sub-key under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\DISPLAY

Regedit Screenshot

However as you can see above, it is stored directly in the raw binary format sent by the monitor. We can’t collect that from SCCM – it first needs to be converted into nice tabular data. For this, we will use a Powershell script you can get here: Download

We can run this script with administrator rights on any PC that has physical monitors connected to it. VMs and display-less computers obviously don’t work.

Running the script

It will parse the binary data in the correct registry key, create a new WMI class if necessary, then fill it with the most useful EDID fields of all currently attached monitors.

After the script has been run, you can query the new WMI class like usual.
After the script has been run once, you can query the new WMI class like usual.

If you create a package in SCCM with this script and push it out to your PCs you will get a nicely formatted WMI class on all your systems. To make sure that later changes in hardware are correctly recorded you will need to set a repeated deployment schedule – I recommend setting it to the same interval that your hardware inventory runs at.

Alternatively you can of course also use Scheduled Tasks to regularly run it.

Now that we have our data available in WMI, read part 2 of this guide where I will show you how to collect a custom WMI class in SCCM 2012.