lib/Excel.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237

# 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)][String]$TMSession = "Default",
                
        [Parameter(Mandatory = $true)][String]$Path,

        [Parameter(Mandatory = $false)][String]$Server = $global:TMSessions[$TMSession].TMServer,
        [Parameter(Mandatory = $false)]$AllowInsecureSSL = $global:TMSessions[$TMSession].AllowInsecureSSL
        
    )

    begin {

        ## Get Session Configuration
        $TMSessionConfig = $global:TMSessions[$TMSession]
        if (-not $TMSessionConfig) {
            Write-Host 'TMSession: [' -NoNewline
            Write-Host $TMSession -ForegroundColor Cyan
            Write-Host '] was not Found. Please use the New-TMSession command.'
            Throw 'TM Session Not Found. Use New-TMSession command before using features.'
        }

        #Honor SSL Settings
        if ($TMSessionConfig.AllowInsecureSSL) {
            $TMCertSettings = @{SkipCertificateCheck = $true }
        }
        else { 
            $TMCertSettings = @{SkipCertificateCheck = $false }
        }

    
        $instance = $Server.Replace('/tdstm', '')
        $instance = $instance.Replace('https://', '')
        $instance = $instance.Replace('http://', '')
    
        $uri = 'https://'
        $uri += $instance + '/tdstm/ws/asset/exportAssets'

    }
    
    process {

        $RequestBody = [PSCustomObject]@{
            "projectIdExport"   = $TMSessionConfig.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 = $TMSessionConfig.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 = $TMSessionConfig.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 = $TMSessionConfig.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 {
    }
}