Public/Excel.ps1
|
# TM Excel File Function Read-TMAssetFile { param( [Parameter(Mandatory = $true)][String]$filePath ) if (Get-FileTypeFromPath($filePath) -eq "xlsx") { $tmAssets = @{ } $data = Import-Excel $filePath -WorksheetName "Applications" Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Applications" -Value $data -Force $data = Import-Excel $filePath -WorksheetName "Devices" Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Devices" -Value $data -Force $data = Import-Excel $filePath -WorksheetName "Databases" Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Databases" -Value $data -Force $data = Import-Excel $filePath -WorksheetName "Storage" Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Storage" -Value $data -Force $data = Import-Excel $filePath -WorksheetName "Dependencies" Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Dependencies" -Value $data -Force $data = Import-Excel $filePath -WorksheetName "Room" Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Room" -Value $data -Force $data = Import-Excel $filePath -WorksheetName "Rack" Add-Member -InputObject $tmAssets -MemberType "NoteProperty" -Name "Rack" -Value $data -Force $tmAssets } } function Export-TMExcel { <# .SYNOPSIS Exports TM Asset data to an Excel Spreadsheet .DESCRIPTION This function exports TM Asset data to an Excel Spreadsheet in the TMExcel format .EXAMPLE Export-TMExcel ## Saves to local directory .EXAMPLE Export-TMExcel -Path 'C:\Temp\' .EXAMPLE Export-TMExcel -Path 'C:\Temp\' .OUTPUTS Saves an Excel Export in the Path Specified #> [CmdletBinding()] param( [Parameter(Mandatory = $false)][PSObject]$TMSession = 'Default', [Parameter(Mandatory = $true)][String]$Path ) begin { ## Get Session Configuration $TMSession = Get-TMSession $TMSession #Honor SSL Settings $TMCertSettings = @{SkipCertificateCheck = $TMSession.AllowInsecureSSL } $Instance = $TMSession.TMServer.Replace('/tdstm', '') $instance = $instance.Replace('https://', '') $instance = $instance.Replace('http://', '') $uri = 'https://' $uri += $instance + '/tdstm/ws/asset/exportAssets' } process { $RequestBody = [PSCustomObject]@{ "projectIdExport" = $TMSession.UserContext.project.id "dataTransferSet" = 1 "application" = "application" "asset" = "asset" "exportFormat" = "xlsx" "ImportApplication" = $true "ImportServer" = $true "ImportDatabase" = $true "ImportStorage" = $true "ImportRoom" = $true "ImportRack" = $true "ImportDependency" = $true "ImportCabling" = $true "ImportComment" = $true "bundle" = "All" } | ConvertTo-Json -Compress ## Write the new Event that will be created Write-Verbose "Requesting Download: $RequestBody" ## Update the Content Type to JSON Set-TMHeaderContentType -ContentType 'JSON' -TMSession $TMSession ## Create a Request Splat $WebRequestSplat = @{ Method = 'POST' Uri = $uri WebSession = $TMSession.TMWebSession Body = $RequestBody } try { $Response = Invoke-WebRequest @WebRequestSplat @TMCertSettings if ($Response.StatusCode -eq 200 ) { $ResponseContent = $Response.content | ConvertFrom-Json if ($ResponseContent.status -ne 'success') { Write-Error -Message $ResponseContent.errors[0] return } } else { throw 'Unable to begin download' } } catch { throw $_ } ## An Export job has started, get the Export Job ID $ExportJobID = $ResponseContent.data.key $Uri = "https://$instance/tdstm/ws/progress/$ExportJobId" ## Create a new WebRequest Splat $WebRequestSplat = @{ Method = 'Get' Uri = $Uri WebSession = $TMSession.TMWebSession } try { $DownloadComplete = $False ## Poll for Status on the export job while (-Not $DownloadComplete) { ## Sleep an interval Start-Sleep -Seconds 1 ## Check the Status from the server $Response = Invoke-WebRequest @WebRequestSplat @TMCertSettings $ResponseContent = $Response.content | ConvertFrom-Json <#{ "status": "success", "data": { "percentComp": 1, "status": "In progress", "detail": "", "remainingTime": "Unknown", "lastUpdated": 1638762169774, "data": {} } }#> ## Throw any Errors returned if ($ResponseContent.status -ne 'success') { throw $ResponseContent.errors } ## Write progress $ProgressSplat = @{ Activity = 'Downloading Asset Data' PercentComplete = $ResponseContent.data.percentComp Status = $ResponseContent.data.status } Write-Progress @ProgressSplat if ($ResponseContent.data.status -eq 'Completed') { $DownloadComplete = $True $FileName = $ResponseContent.data.data.header -replace 'attachment; filename=', '' -replace '"', '' } } ## ## Download the File ## $Uri = "https://$instance/tdstm/assetEntity/downloadExport?key=$ExportJobId" ## Create a new WebRequest Splat $WebRequestSplat = @{ Method = 'Get' Uri = $Uri WebSession = $TMSession.TMWebSession } $OutFilePath = Join-Path $Path $FileName $Response = Invoke-WebRequest @WebRequestSplat -OutFile $OutFilePath -PassThru if ($Response.StatusCode -ne 200 ) { throw 'Unable to Download Export file' } ## Write progress $ProgressSplat = @{ Activity = 'Downloaded Asset Data' PercentComplete = 100 Status = 'File Saved' Completed = $true } Write-Progress @ProgressSplat } catch { throw $_ } } end { } } |