lib/Tables.ps1
|
function Get-SNTable { param ( $instance, $table, $method, $headers, $limit = -1, $UpdatedSince) # $Filtered = ($sn_table_list | Where-Object {$_.Name -eq $table}).Filtered # if ($Filtered -eq $true) { # $searchParam = '&sysparm_query=sys_domain=' + $searchDomain # } if ($UpdatedSince) { $StartDate = Get-Date -Date $UpdatedSince -UFormat '%Y-%m-%d' $StartTime = Get-Date -Date $UpdatedSince -UFormat '%H:%M:%S' $EndDate = Get-Date -UFormat '%Y-%m-%d' $EndTime = Get-Date -UFormat '%H:%M:%S' ## Filtering Query must look like this # https://dev96230.service-now.com/cmdb_ci_server_list.do?sysparm_query=sys_updated_on%3E%3Djavascript%3Ags.dateGenerate('2021-07-15'%2C'17%3A44%3A57')&sysparm_view= $searchParam = "&sysparm_query=sys_updated_onBETWEENjavascript%3Ags.dateGenerate('" $SearchParam += $($StartDate) $SearchParam += "'%2C'" $SearchParam += $($StartTime -replace ':', '%3A') $SearchParam += "')%40javascript%3Ags.dateGenerate('" $SearchParam += $($EndDate) $SearchParam += "'%2C'" $SearchParam += $($EndTime -replace ':', '%3A') $SearchParam += "')" } # if (-Not [string]::IsNullOrEmpty($fieldList)) { # $fieldListParam = '&sysparm_fields=' + $fieldList # } # else { # $resultSize = 10000 # $restMethodParms = @{FollowRelLink = $true; } # } # ## Control Dependency collection as an override # if ($table -eq 'cmdb_rel_ci') { # # Debug Settings # if ($debug_createsampleresult -eq $true) { # $resultSize = 10000 # } # } ## Update Core tables so we get the whole thing # if ($table -in @("core_company", "cmn_location","cmdb_model","cmdb_rel_type")){ # } ## Experement with sysparm_display_value - 'true' may replace sys_ids with the display name. ## If there is a limit to the size we want for the data if ($limit -gt -1) { $resultSize = $limit $restMethodParms = @{FollowRelLink = $false; } } else { ## Set the per-batch limit to 10000 and allow following the reflink to get the next batch $resultSize = 10000 $restMethodParms = @{FollowRelLink = $True; } } Write-Host "Getting ServiceNow Table: " -NoNewline Write-Host $table -ForegroundColor Green # $uri = "https://$instance/api/now/table/" + $table + "?sysparm_limit=" + $resultSize + $searchParam + $fieldListParam + '&sysparm_display_value=true' $uri = "https://$instance/api/now/table/" + $table + "?sysparm_limit=" + $resultSize + $searchParam + $fieldListParam $response = Invoke-RestMethod -Headers $headers -Method $method -Uri $uri @restMethodParms foreach ($item in $response) { $resultset += $item.result } $resultset } Function Invoke-SNDataCollection { if ($debug_createsampleresult -eq $true) { Write-Host " Collecting Sample Size of $debug_sampleassetresultsize Assets, and $debug_sampledependencyresultsize Dependencies" -ForegroundColor Yellow } foreach ($table in $sn_table_list) { $data = Get-SNTable -table $table.Name -instance $instance -method $method -headers $headers Add-Member -InputObject $ro -MemberType NoteProperty -Name $table.Name -Value $data -Force } } Function Update-SNReferenceColumns { param ( [Parameter(Mandatory = $true)][Array]$inputObject, [Parameter(Mandatory = $true)][PSObject]$Table ) ## Sort the object $inputObject = $inputObject | Sort-Object -Property sys_id $assetClass = $Table.TMAssetClass foreach ($item in $inputObject) { if ($assetClass -ne $null) { $ro.sys_id_to_asset_type.($item.sys_id) = $assetClass } $item.model_id | Add-Member -MemberType NoteProperty -Name name -Value ($ro.cmdb_model | Where-Object { $_.sys_id -eq $item.model_id.value }).name -Force -ErrorAction SilentlyContinue $item.manufacturer | Add-Member -MemberType NoteProperty -Name name -Value ($ro.core_company | Where-Object { $_.sys_id -eq $item.manufacturer.value }).name -Force -ErrorAction SilentlyContinue $item.location | Add-Member -MemberType NoteProperty -Name name -Value ($ro.cmn_location | Where-Object { $_.sys_id -eq $item.location.value }).name -Force -ErrorAction SilentlyContinue # These reduce the output file size if ($item.model_id) { $item.model_id.PSObject.Properties.Remove("link") } if ($item.manufacturer) { $item.manufacturer.PSObject.Properties.Remove("link") } if ($item.location) { $item.location.PSObject.Properties.Remove("link") } if ($item.running_process) { $item.running_process.PSObject.Properties.Remove("link") } if ($item.running_process_command) { $item.running_process_command.PSObject.Properties.Remove("link") } } } Function Update-SNDependencies { $knownparents = @() $knownparentsandchildren = @() Write-Host "Removing Dependencies with Unknown Parents" $knownparents = foreach ($dep in $global:ro.cmdb_rel_ci) { if ($dep.parent.value -ne $null) { if ($global:ro.sys_id_to_asset_type.Contains($dep.parent.value)) { $dep } } } Write-Host "Remaining Dependencies (with Known Parents): " -NoNewLine Write-Host $knownparents.count -ForegroundColor Green Write-Host "Removing Dependencies with Unknown Children" $knownparentsandchildren = foreach ($dep in $knownparents) { if ($dep.child.value -ne $null) { if ($global:ro.sys_id_to_asset_type.Contains($dep.child.value)) { $dep } } } #Report how many dependencies were kept Write-Host "Total Remaining Dependencies: " -NoNewline Write-Host $knownparentsandchildren.Count -ForegroundColor Green Write-host "Adding Asset Types" foreach ($item in $knownparentsandchildren) { Add-Member -InputObject $item -MemberType NoteProperty -Name "parentclass" -value $ro.sys_id_to_asset_type[$item.parent.value] -Force Add-Member -InputObject $item -MemberType NoteProperty -Name "childclass" -value $ro.sys_id_to_asset_type[$item.child.value] -Force Add-Member -InputObject $item -MemberType NoteProperty -Name "deptype" -value ($ro.cmdb_rel_type | Where-Object { $_.sys_id -eq $item.type.value }).name -Force } #Replace the original data with the applicable relationships $global:ro.cmdb_rel_ci = $knownparentsandchildren } function Update-ObjectToValue { param( [Parameter(Mandatory = $true)][PSObject]$inputObject, [Parameter(Mandatory = $true)][string]$property ) if ($inputObject.$property -ne $null) { $inputObject.$property = $inputObject.$property.value } } function Update-ObjectToName { param( [Parameter(Mandatory = $true)][PSObject]$inputObject, [Parameter(Mandatory = $true)][string]$property ) if ($inputObject.$property -ne $null) { $inputObject.$property = $inputObject.$property.name } } Function Update-SNResults { foreach ($table in $sn_table_list) { switch ($table.ProcessingRequired) { "Base" { Write-Host "Processing Data Updates for: " -NoNewline Write-Host $table.Name -ForegroundColor Green $updateObject = $global:ro.($table.Name) if ($updateObject.Count -gt 0) { Update-SNReferenceColumns -inputObject $updateObject -Table $table } } "Relationship" { Write-Host "Processing Relationships/Dependencies" Update-SNDependencies } Default {} } } } |