Revision #2

You are currently reviewing an older revision of this page.
Go to current version
Summary
This powershell script will query FIM for all "Requests" that have occurred in the past 7 days for any target "Person". This script requires the Quest Powershell Snapin (otherwise you will have to translate the calls)
It is designed to translate the default reference attributes to the objects corresponding displayname and handle multivalue attributes by adding character returns and newlines for displaying in excel. We use this script to maintain a long term history of requests within FIM without having to extend the delete days for a request.

This script will also update a standalone database with any of the newly added requests
The schema of the database is essentially the same column names as what's stored in the $request_attributes array.
If you don't want to store it to a database you can remove the last line and output request_list to a CSV file

Please modify the header variables and comment out the last line / create a database if you want this to persistently store the data.
This script should be used as reference and not a working copy

Script Code
001.###################################################################################
002.#About this script:
003.#This script is used to export out all Requests that are displayed in the portal.
004.#It will translate all References into DisplayName strings.
005.#In order for this to work, an account must be used that has rights to read Requests and Objects and Schema
006.#This specific version of the script was written to use the Quest FIM Powershell Snapin DLL
007. 
008.#Needed Assembly
009.if(@(get-pssnapin | where-object {$_.Name -eq "Quest.FIMPowershellSnapin"} ).count -eq 0) {add-pssnapin Quest.FIMPowershellSnapin}
010.[reflection.assembly]::LoadWithPartialName("System.Data")
011. 
012.#Configuration Parameters and global variables:
013.$server = "fimserver:5725" ####Important this should be changed to point to the FIM Web Services or load balancer
014.$cred  = get-credential ####Set the credential to be used to query the webservice
015.$request_attributes = @("ActionWorkflowInstance","AuthenticationWorkflowInstance","AuthorizationWorkflowInstance","CommittedTime","ComputedActor","CreatedTime","Creator","Description","DisplayName","HasCollateralRequest","ManagementPolicy","Operation","ParentRequest","RequestControl","RequestParameter","RequestStatus","RequestStatusDetail","ObjectID","ServicePartitionName","Target","TargetObjectType")
016.$global:reference_list = @{}
017.###################################################################################
018. 
019. 
020. 
021.##################
022.# Functions
023.##################
024. 
025.function GetReferenceDisplayName
026.{
027.    PARAM ($objectID)
028.    END
029.    {
030.        if ($global:reference_list.ContainsKey($objectID))
031.        {
032.            return $global:reference_list["$objectID"]
033.        }
034.        else {
035.            #Return the display name of the reference
036.            $obj = get-fimresource -session $session -Filter "/*[ObjectID='$objectID']" -Attribute "DisplayName"
037.            if ($obj)
038.            {
039.             
040.                if ($obj.Resource.ContainsKey("DisplayName"))
041.                {
042.                    if ($obj.Resource["DisplayName"].Value -ne $null)
043.                    {
044.                        $global:reference_list.add($objectID,$obj.Resource["DisplayName"].Value.ToString())
045.                        return $obj.Resource["DisplayName"].Value.ToString()
046.                    }
047.                    else {
048.                        $global:reference_list.add($objectID,"(no display name)")
049.                        return "(no display name)"
050.                        }
051.                }
052.                else {
053.                    $global:reference_list.add($objectID,"(no display name)")
054.                    return "(no display name)"
055.                }
056.            }
057.            else {
058.            $global:reference_list.add($objectID,"$objectID [Nonexistent]")
059.            return "$objectID [Nonexistent]"
060.            }
061.        }
062.    }
063.}
064. 
065.function AddPropertyToObject
066.{
067.    PARAM ($object, $attribute, $value)
068.    END
069.    {
070.        Add-Member -inputObject $object -MemberType NoteProperty -Name $attribute -Value $value
071.    }
072.}
073. 
074.function ParseMultiValue
075.{
076.    PARAM ($values,$delimiter,$reference)
077.    END
078.    {
079.        $stringBuilder = ""
080.        switch ($delimiter)
081.        {
082.            "comma" {
083.                foreach ($item in $fim_request.Resource[$attribute].Values) {
084.                    if ($reference -eq $true)
085.                    {
086.                        $stringBuilder += "$(GetReferenceDisplayName -ObjectId $item), "
087.                    }
088.                    else {
089.                        $stringBuilder += "$item, "
090.                    }
091.                }
092.            }
093.             
094.            "newline" {
095.                foreach ($item in $fim_request.Resource[$attribute].Values) {
096.                    if ($reference -eq $true)
097.                    {
098.                        $stringBuilder += "$(GetReferenceDisplayName -ObjectId $item)`r`n" 
099.                    }
100.                    else {
101.                        $stringBuilder += "$item`r`n"
102.                    }
103.                }
104.            }
105.        }
106.        $stringBuilder = $stringBuilder.SubString(0,$stringBuilder.Length-2)
107.        Return $stringBuilder
108.    }
109.}
110. 
111. 
112.function ParseRequestParams
113.{
114.    PARAM ($object,$attribute,$resolveReference)
115.    END
116.    {
117.        $stringBuilder = ""
118.                foreach ($item in $object.Resource[$attribute].Values) {
119.                    $xml = [xml] $item;
120.                    $operation = $xml.RequestParameter.Operation.ToString()
121. 
122.                        switch ($operation)
123.                        {
124.                            "Create" {
125.                                    $property = $xml.RequestParameter.PropertyName.ToString();
126.                                    #check to see if we have a mode "sometimes its set to unknown"
127.                                    if ($xml.RequestParameter.Mode -ne $null)
128.                                    {
129.                                        $stringBuilder += "[$($xml.RequestParameter.Mode.ToString())] ";
130.                                    }
131.                                     
132.                                    #need to check innertext value to see if it has a value
133.                                    if ($xml.RequestParameter.Value.InnerText -ne $null)
134.                                    {
135.                                        #now check to see if the value is one of the reference values
136.                                        switch ($xml.RequestParameter.PropertyName.ToString())
137.                                        {
138.                                            "Requestor" { $stringBuilder += "$($xml.RequestParameter.PropertyName.ToString()) : $(GetReferenceDisplayName -objectId $xml.RequestParameter.Value.InnerText.ToString())`r`n";}
139.                                            "Creator" {$stringBuilder += "$($xml.RequestParameter.PropertyName.ToString()) : $(GetReferenceDisplayName -objectId $xml.RequestParameter.Value.InnerText.ToString())`r`n";}
140.                                            "Manager" {$stringBuilder += "$($xml.RequestParameter.PropertyName.ToString()) : $(GetReferenceDisplayName -objectId $xml.RequestParameter.Value.InnerText.ToString())`r`n";}
141.                                            "Applications" {$stringBuilder += "$($xml.RequestParameter.PropertyName.ToString()) : $(GetReferenceDisplayName -objectId $xml.RequestParameter.Value.InnerText.ToString())`r`n";}
142.                                             
143.                                            default {$stringBuilder += "$($xml.RequestParameter.PropertyName.ToString()) : $($xml.RequestParameter.Value.InnerText.ToString())`r`n";}
144.                                        }
145.                                    }
146.                                    else {
147.                                        #inner value is unknown
148.                                        $stringBuilder += "$($xml.RequestParameter.PropertyName.ToString()) : Unknown`r`n";
149.                                    }
150.                                }
151.                            "Delete" {$stringBuilder += "[All Attributes Removed]`r`n"}
152.                        }
153. 
154.                        #$stringBuilder += "$(GetReferenceDisplayName -ObjectId $item)`r`n"
155.                        $xml = $null;
156.                        $property = $null;
157.                }
158.        $stringBuilder = $stringBuilder.SubString(0,$stringBuilder.Length-2)
159.        Return $stringBuilder
160.    }
161.}
162. 
163. 
164.function UpdateDatabase([Parameter(Mandatory=$true)] $allRequests)
165.{
166.    $objOleDbConnection = New-Object "System.Data.OleDb.OleDbConnection"
167.    $objOleDbCommand = New-Object "System.Data.OleDb.OleDbCommand"
168.    $objOleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
169.    $objDataTable = New-Object "System.Data.DataTable"
170. 
171.    #$objOleDbConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\FIM_Request_Log.accdb;Persist Security Info=False;"
172.    $objOleDbConnection.ConnectionString = "Provider=SQLNCLI10;Server=DEVSERVER;Database=SecurityDev;Trusted_Connection=yes;"
173. 
174.    $objOleDbConnection.Open()
175. 
176.    $objOleDbCommand.Connection = $objOleDbConnection
177.    $objOleDbCommand.CommandText = "SELECT * FROM dbo.FIMRequestLog"
178. 
179.    ##set the Adapter object and command builder
180.    $objOleDbAdapter.SelectCommand = $objOleDbCommand
181.    $objOleDbCommandBuilder = New-Object "System.Data.OleDb.OleDbCommandBuilder"
182.    $objOleDbCommandBuilder.DataAdapter = $objOleDbAdapter
183. 
184. 
185.    ##fill the objDataTable object with the results
186.    [void] $objOleDbAdapter.Fill($objDataTable)
187.    [void] $objOleDbAdapter.FillSchema($objDataTable,[System.Data.SchemaType]::Source)
188.     
189.    #store all the primary keys in a list for kicking out dups
190.    $sql_id = @()
191.    $objDataTable.Rows | foreach { $sql_id += $_.ObjectID }
192. 
193.    #####
194. 
195.    #loop through all the requests
196.    trap {
197.    "Error: $($i)"
198.    }
199.    $i = 0
200.    $total = $allRequests.count
201.    foreach ($request in $allRequests)
202.    {      
203.        $i++
204.        write-progress -activity "Filling DataTable" -status "% Complete: $($i/$total*100)" -PercentComplete ($i/$total*100)
205.        #check to see if entry already exists in our table (by primary key)
206.        if (!($sql_id -contains $request.ObjectID.Value))
207.        {
208.            #shouldn't have to do this but i noticed sometimes requests are duplicate in the list? (probably restarted the script and caught some old requests
209.            $sql_id += $request.ObjectID.Value
210.             
211.            #write-host "Adding: $($request.ObjectID.Value) to our DataTable"
212.            $row = $objDataTable.Rows.Add($request.ObjectID.Value)
213.            #go through all the attributes from the request and add them to the table
214.            $list = get-member -in $request | Where-Object { $_.MemberType -eq "NoteProperty" }
215.            foreach ($attr in $list)
216.            {
217.                if ($request.($attr.name) -ne $null)
218.                {
219.                    $row.($attr.name) = $request.($attr.name)
220.                }
221.            }
222.             
223.        } else {
224.            #write-host "$($request.ObjectID.Value) already in DB"
225.        }
226.         
227.    }
228. 
229.    #update the database with our new records
230.    $objOleDbAdapter.Update($objDataTable)
231. 
232.    ## close the connection
233.    $objOleDbConnection.Close()
234. 
235.}
236.################
237.# Main
238.################
239.function GetRequestsFromFIM([Parameter(Mandatory=$true)] $server, [Parameter(Mandatory=$true)] $cred, [Parameter(Mandatory=$true)] $request_attributes)
240.{
241.    $session = new-fimsession -server $server -credential $cred
242.    $request_list = @()
243. 
244.    #Grab all Requests
245.    #$all_requests = get-fimresource -session $session -filter "/Request[TargetObjectType='Person' or TargetObjectType='STSAccount']" -Attribute $request_attributes
246.    $all_requests = get-fimresource -session $session -filter "/Request[CreatedTime >= op:add-dayTimeDuration-to-dateTime(fn:current-dateTime(), xs:dayTimeDuration('-P7D')) and (TargetObjectType='Person')]" -Attribute $request_attributes
247.     
248.    $total = $all_requests.count
249.    $counter = 0
250.    foreach($fim_request in $all_requests)
251.    {
252.        #create our own MPR object with static string data for copy paste into excel
253.        $request = new-object object
254.        write-progress -Activity "Processing FIM Request" -status "Transforming: $($fim_request.Resource.DisplayName) [$($counter / $total * 100)]" -percentcomplete ($counter / $total * 100)
255.        $counter+= 1
256.        #go thru each attribute of an MPR to describe our object
257.        foreach($attribute in $request_attributes)
258.        {
259.            if ($fim_request.resource["$attribute"] -ne $null -and $fim_request.Resource["$attribute"].Value -ne $null)
260.            {
261.                switch ($attribute)
262.                {
263.                    "ObjectID" {AddPropertyToObject -object $request -attribute "ObjectID" -Value $fim_request.Resource[$attribute].Value}
264.                    "Target" {AddPropertyToObject -object $request -attribute $attribute -Value $(GetReferenceDisplayName -objectID $fim_request.Resource[$attribute].Value.ToString()) }
265.                    "Creator" {AddPropertyToObject -object $request -attribute $attribute -Value $(GetReferenceDisplayName -objectID $fim_request.Resource[$attribute].Value.ToString()) }
266.                     
267.                    "RequestParameter" {AddPropertyToObject -object $request -attribute $attribute -Value $(ParseRequestParams -object $fim_request -attribute $attribute -resolveReference $true) }
268.                     
269.                    "ManagementPolicy" {AddPropertyToObject -object $request -attribute $attribute -value $(ParseMultiValue -values $fim_request.Resource[$attribute].Values -delimiter "newline" -reference $true)}
270.                    "ActionWorkflowInstance" {AddPropertyToObject -object $request -attribute $attribute -value $(ParseMultiValue -values $fim_request.Resource[$attribute].Values -delimiter "newline" -reference $true)}
271.                    "AuthenticationWorkflowInstance" {AddPropertyToObject -object $request -attribute $attribute -value $(ParseMultiValue -values $fim_request.Resource[$attribute].Values -delimiter "newline" -reference $true)}
272.                    "AuthorizationWorkflowInstance" {AddPropertyToObject -object $request -attribute $attribute -value $(ParseMultiValue -values $fim_request.Resource[$attribute].Values -delimiter "newline" -reference $true)}
273. 
274.                    default {AddPropertyToObject -object $request -attribute $attribute -Value $fim_request.resource[$attribute].Value.ToString()}
275.                }
276.            }
277.            else {
278.                AddPropertyToObject -object $request -attribute $attribute -value $null
279.            }
280.        }
281.        $request_list += $request
282.        $request = $null
283.    }
284.    return $request_list
285.}
286. 
287.####BEGIN PROGRAM
288. 
289.#Get the requests
290.$request_list = GetRequestsFromFIM -server $server -cred $cred -request_attributes $request_attributes
291. 
292.#Update the Database
293.UpdateDataBase($request_list)

See Also

Getting Started with the Quest FIM PowerShell Cmdlets
FIM 2010 PowerShell Cmdlets

http://fimpscmdlets.codeplex.com/releases/view/44432
Revert to this revision