Revision #1

How to Use PowerShell to Export FIM Requests to a Database/File

You are currently reviewing an older revision of this page.

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)

Revert to this revision

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

Revision #1