Querying the FIM Metaverse using powershell

Posted on by Carol

It’s actually pretty easy to query SQL 2008 from powershell.

This simple little script reads employeeID numbers from a text file and then queries the metaverse for selected attributes. The text file must have “id” as the first row, with the id numbers then listed one per line.

if(@(get-pssnapin | where-object {$_.Name -eq “sqlserverprovidersnapin100”} ).count -eq 0) {add-pssnapin sqlserverprovidersnapin100}
if(@(get-pssnapin | where-object {$_.Name -eq “sqlservercmdletsnapin100”} ).count -eq 0) {add-pssnapin sqlservercmdletsnapin100}

set-location SQLSERVER:\SQL\localhost\DEFAULT\Databases\FIMSynchronizationService
$List = Import-Csv -Path C:\scripts\employeeids.txt

foreach ($row in $List)
{
$SqlQuery = "select employeeID,displayName,mail from mms_metaverse where employeeID = '$($row.id)'"
Invoke-SQLCmd $SqlQuery -SuppressProviderContextWarning
}

 

I've been doing IT for 30 years, and IdM for 15. I live in Australia and build IdM solutions based on Microsoft Identity Manager. I also play the violin, but that doesn't help much with the IdM solutions.

2 Replies to “Querying the FIM Metaverse using powershell”

  1. Carol,

    I am unable to get this script working on multiple instances of FIM 2010. I receive an error about ‘object reference not set to an instance of an object’. It seems to have problems with Set-Location path you are using. I am unable to get it to get past: SQLSERVER:\SQL\localhost\DEFAULT\

    I am logged in as enterprise admin who set both FIM 2010 and SQL up, SQL is installed locally. Any ideas? Thx for your time in advance.

    Glenn

  2. Are you using a named instance in SQL server? See this msdn page to make sure you’ve got the correct location identifier: http://msdn.microsoft.com/en-us/library/cc281947.aspx

    If the connection string is ok then check that your SQL query is valid. Stick a “write-host $SqlQuery” after the line where $SqlQuery is defined, then try and run the printed query directly in a SQL query window.

Comments are closed.