lib/Tables.ps1
|
function Get-SNTable { param( # Table Name [Parameter(Mandatory = $True, Position = 0)][String]$Name, [Parameter(Mandatory = $False)][String]$SNSession = 'Default', [Parameter(Mandatory = $False)][Int32]$Limit = -1, [Parameter(Mandatory = $False)][String]$UpdatedSince, [Parameter(Mandatory = $False)][string]$SortByAsc, [Parameter(Mandatory = $False)][string]$SortByDesc ) ## Get Session Configuration $SNSessionConfig = $global:SNSessions[$SNSession] if (-not $SNSessionConfig) { Write-Host 'SNSession: [' -NoNewline Write-Host $SNSession -ForegroundColor Cyan Write-Host '] was not Found. Please use the New-SNSession command.' Throw 'SN Session Not Found. Use New-SNSession command before using features.' } ## ## Create Request Properties ## $QueryFilters = @() $Uri = "https://$($SNSessionConfig.SNServer)/api/now/table/$($Name)?" ## If there is a limit to the size we want for the data if ($Limit -gt -1) { $ResultSize = $Limit $FollowRelLink = $False } else { ## Set the per-batch limit to 10000 and allow following the reflink to get the next batch $ResultSize = 10000 $FollowRelLink = $True } ## Add the Result Limit to the query $Uri += "sysparm_limit=$($ResultSize)" ## Create a Date Filter on 'UpdatedSince' if ($UpdatedSince) { ## 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= $UpdatedSinceParam = "sys_updated_on>=javascript:gs.dateGenerate('" ## Attempt converting the UpdatedSince parameter $StartDate = Get-Date -Date $UpdatedSince -UFormat '%Y-%m-%d' -ErrorAction 'SilentlyContinue' if($StartDate) { ## Conversion successful, Convert the rest of the needed date strings $StartTime = Get-Date -Date $UpdatedSince -UFormat '%H:%M:%S' $UpdatedSinceParam += $StartDate.ToString() $UpdatedSinceParam += "', '" $UpdatedSinceParam += $StartTime.ToString() } ## Date Conversion failed, Use the string directly else { $UpdatedSinceParam += $UpdatedSince } $UpdatedSinceParam += "')" ## Add this Query to the QueryFilters list to include in the request $QueryFilters += $UpdatedSinceParam } ## Handle the Sorty By property if ($SortByAsc) { ## Example from SN API Docs: sysparm_query=active=true^ORDERBYnumber^ORDERBYDESCcategory ## Add the SortBy properties $QueryFilters += "^ORDERBY$($SortByAsc)" } ## Handle the Sorty By property if ($SortByDesc) { ## Example from SN API Docs: sysparm_query=active=true^ORDERBYnumber^ORDERBYDESCcategory ## Add the SortBy properties $QueryFilters += "^ORDERBYDESC$($SortByDesc)" } ## Add the Search and orderby parameters if ($QueryFilters.Count -gt 0) { $Uri += "&sysparm_query=" $Uri += [System.Web.HttpUtility]::UrlEncode(($QueryFilters -join '')) } ## ## Make the Request ## ## Create a RestMethodSplat $RestMethodSplat = @{ WebSession = $SNSessionConfig.SNRestSession SkipCertificateCheck = $SNSessionConfig.AllowInsecureSSL Method = 'GET' FollowRelLink = $FollowRelLink Uri = $Uri } try { Write-Verbose "Web Request Parameters:" Write-Verbose ($WebRequestSplat | ConvertTo-Json -Depth 10) Write-Verbose "Invoking web request" $Response = Invoke-RestMethod @RestMethodSplat } catch { throw $_ } ## Return the Result $Response.result } function Get-SNRelTypes { param( # Table Name [Parameter(Mandatory = $False, Position = 0)][String]$Name, [Parameter(Mandatory = $False)][String]$SNSession = 'Default', [Parameter(Mandatory = $False)][Int32]$Limit = -1 ) ## Get Session Configuration $SNSessionConfig = $global:SNSessions[$SNSession] if (-not $SNSessionConfig) { Write-Host 'SNSession: [' -NoNewline Write-Host $SNSession -ForegroundColor Cyan Write-Host '] was not Found. Please use the New-SNSession command.' Throw 'SN Session Not Found. Use New-SNSession command before using features.' } ## ## Create Query Parameters ## ## If there is a limit to the size we want for the data if ($Limit -gt -1) { $ResultSize = $Limit $FollowRelLink = $False } else { ## Set the per-batch limit to 10000 and allow following the reflink to get the next batch $ResultSize = 10000 $FollowRelLink = $True } $Uri = "https://$($SNSessionConfig.SNServer)/api/now/table/cmdb_rel_type" $Uri += "?sysparm_limit=$($ResultSize)" ## ## Make the Request ## ## Create a RestMethodSplat $RestMethodSplat = @{ WebSession = $SNSessionConfig.SNRestSession SkipCertificateCheck = $SNSessionConfig.AllowInsecureSSL Method = 'GET' FollowRelLink = $FollowRelLink Uri = $Uri } try { Write-Verbose "Web Request Parameters:" Write-Verbose ($WebRequestSplat | ConvertTo-Json -Depth 10) Write-Verbose "Invoking web request" $Response = Invoke-RestMethod @RestMethodSplat } catch { throw $_ } ## Return the Result $Response.result } # 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.SNAssetClass # 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 {} # } # } # } |