Thursday, December 1, 2011

PowerShell - count folders in folders

Counting the number of files in a directory is easy
Here's a more complex example that I will break down, like a tutorial. Unlike the previous example, this is taking advantage of the object oriented nature of PowerShell. You should understand about_pipelines before you continue reading

The problem
An invoice scanning system uploads files to a file server. The directory structure is the following.

Here's an German invoice folder scanned on December 1st 2011

The question is : "How many invoices have been created every day by country ?" . The output has to be a table to be analyzed with Excel.

The proposed solution

Get all the country folders
dir \\server\files\*-invoices
For each (% is the operator) country folder ($_ is the pipeline object), display only its name
dir \\server\files\*-invoices | %{$_.Name}
Display the date folders for each country
dir \\server\files\*-invoices | %{dir $_}
We'll store the country name in $country, to use it later as we bring it up the pipeline.
dir \\server\files\*-invoices | %{$country=$_.Name}

Now it's getting a bit tricky.We'll put a pipeline inside a pipeline!
Because we need to process each date folder in each country folder.

  • For Each country folder display its name
  • For Each date folder in a country folder display its name
  • For Each date folder, count the number of folders it contains

Display the country name and the date folder

dir \\server\files\*-invoices | %{$country=$_.Name;dir $_ |%{Write-Host $country $_.Name}}
DE-invoices 2011-12-01
DE-invoices 2011-11-30

Display the folder count
dir \\server\files\*-invoices | %{$country=$_.Name;dir $_ |%{Write-Host $country $_.Name (dir $_).count}}


DE-invoices 2011-12-01 5
DE-invoices 2011-11-30 18

The result can now be imported as CSV file ,using the space character as the separator.

Wednesday, November 16, 2011

Windows PowerShell : consolidate log files

I'm using Windows PowerShell more and more every day, here's a simple example.

The task is to consolidate several csv files.
With command prompt

copy/b SoftDistribution*.csv Consolidated_logs.csv

With Powershell, use Get-Content and Add-Content

Get-Content SoftDistribution*.csv | Add-Content Consolidated_logs.csv

Now we have used a "text-based" approach in both cases.
In case you have headers in the .csv file, and you just want to filter out some fields of the CSV file, it will get very complicated with the command prompt.
That's when you have to take a more "object-oriented" approach with PowerShell : check out this article from Microsoft's Scripting Guy, which addresses this particular issue.

Since you can use COM and .Net objects in PowerShell, the possibilities are endless! So instead of developing a VBScript for a task we'll run one time only (not a batch), I use PowerShell interactively.

Monday, July 4, 2011

Microsoft Windows - delete a local user profile whose account is missing from AD

Here's a post on how I helped my helpdesk colleagues to solve a strange problem.

John Doe's user account was deleted from Active Directory (he left the company over a year ago), but we could not delete his local profile on a Windows Server 2003:
  • through the user profiles control panel, it is not present
  • deleting the folder in "c:\documents and settings" said ntuser.dat is in use
  • The "User Profile Deletion Utility" (delprof) from Windows Server 2003 Resource Kit Tools doesn't find this profile either.
I checked with "Process Explorer" and saw his ntuser.dat was loaded.

NTUSER.DAT is a file containing the user's registry hive. it is loaded in the machine-wide registry under HKEY_USERS.

So we need to unload this user's registry hive, in order to delete his profile in "c:\document and settings"

You need to find out which SID (the S-1-5-21 ..etc) correspond to this user
So check the loaded hives in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\hivelist

Then go back to HKEY_USERS , select the SID and go to file > unload hive.
You should now be able to delete his profile in "c:\documents and settings"

Thursday, May 26, 2011

SCCM Metering query

UPDATE 04/10/2011: the main query ("query_metering_data") has been changed, so that it even displays computers with no metering data

In a multinational company,you have to be ready for a software audit.

The people in charge of license control go mad when they realise you have 500 computers with Microsoft Visio Professional, when you are licensed for only 450 copies !
Then you have to find 50 users which have it installed but don't need it. If you ask them , they will all say they use it every day :-)

Software Metering in Configuration Manager is extensively covered on the web, so I will spare you the introduction.

Using SCCM, I wanted to uninstall unused software on PCs  automatically, based on Software Metering data.
This can be done and is covered in forums, but my customer wanted to first check who was scheduled for removal. Imagine if you uninstalled Microsoft Project Standard from the laptop of the vice president of IT !

I developped a VB.NET application which is centered around a DataGridView, the data source being the SCCM SQL database server. Here's an overview.

First, list all the metering rules

Dim query_rules As String = "select productname from v_MeterRuleInstallBase" & _
  " GROUP BY productname ORDER BY productname"

 Double clicking a metering rule displays the metering data for this metering rule, for the collection specified.
Now here's the core of this : the SQL query to make sense of the metering data.

Dim query_metering_data = "select sys.Name0 AS Name,sys.User_Name0,mru.MeteredFileID,mru.ResourceId,MAX(TimeKey) As TimeKey, MAX(LastUsage) AS 'LastUsage' , MAX(lastseen.LastHWScan) AS 'Last hardware scan',sf.FilePath" & _
            " from v_MeterRuleInstallBase mru" & _
            " LEFT JOIN v_MonthlyUsageSummary mus ON (mru.MeteredFileID = mus.FileID AND mru.ResourceID = mus.ResourceID)" & _
            " JOIN v_r_system sys ON mru.ResourceID = sys.ResourceID" & _
            " JOIN v_GS_WORKSTATION_STATUS lastseen ON mru.ResourceID = lastseen.ResourceID" & _
            " JOIN v_fullcollectionmembership m ON mru.ResourceID = m.ResourceID" & _
            " JOIN v_GS_Softwarefile sf ON m.ResourceID = sf.ResourceID AND mru.meteredFileID = sf.FileID" & _
            " WHERE mru.ProductName = '" & Metering_rule & "'" & _
            " AND m.CollectionID = '" & TextBox_CollectionID.Text & "'" & _
            " GROUP BY sys.Name0,sys.User_Name0,mru.MeteredFileID,mru.ResourceID,sf.FilePath" & _
            " ORDER BY sys.Name0"

I've added a right click menu to remove false positives (like the $PatchCache$ path ). The list can be sorted by any column desired.
  • Ensuring the last hardware scan is recent tells you the client looks OK
  • The FilePath is helpful, we found people using portable applications run from USB stick were metered!
  • Sort by "Last Usage" to find who has not used it the metered software for a long time.
  • The desired cells can be copied to excel, or directly as Comma Separated Values with the "Copy to clipboard as CSV"

My customer uses a list of computers as a CSV file, to assign an uninstall program. This required another tool (which is not covered in this post).

Tuesday, January 4, 2011

SCCM Package replication status gadget for Windows 7 Sidebar

Making sure your packages are replicating correctly in your SMS hierarchy is important.
Sometimes, you even want to know as soon as possible when a package has been replicated!
This has been useful when a new office is being build far away, and I want to inform the fields technicians on site when they'll be allowed to push out software.

Therefore, I present the "PackageStatusDetailSummarizer" gadget for Windows 7 Sidebar!


It's like  the "Package Status" view in the "Configuration Manager Console", just neater in a gadget :-)

Inspiration from

To install in Windows 7:
  1. drop the folder "PackageStatusDetailSummarizer.Gadget" in %userprofile%\appdata\Local\Microsoft\Windows Sidebar
  2. right click on your desktop, choose "Gadgets", you should see it in the list
  3. right click, "install"
  4. configure by setting the options, just like a normal gadget
  5. Be careful with the refresh interval in the options, if you set it to low you can hurt your site's performance.

If you want to customize it,just close the gadget and edit the files in the folder you have copied (see the "Windows Sidebar" link)
The possibilites are endless : check the deployment of packages,advertisements, software updates, site health etc..