Zack Meier
158 lines
7.5 KiB
PowerShell
158 lines
7.5 KiB
PowerShell
# loop through them, create sql record
|
|
|
|
# $cat_item_sys_id = 'c64e27af47244610b7853238436d435d'
|
|
New-ITDServiceNowSession -Environment Production -Credential $Secret:snow_vmcred
|
|
|
|
Write-Verbose -Message "Prep variable and SQL connection" -Verbose
|
|
$ServerInstance = "itdintsql22p1.nd.gov\INTSQL22P1"
|
|
$Database = "ITD-Systems-Automation"
|
|
$Table = "ServiceNow_RitmDump_ServerBuildRequestV1"
|
|
|
|
# get most recent RITM from SQL, get the opened_at value
|
|
$SqlQuery = "SELECT [RitmNum],[RitmSysId],[opened_at] FROM [$Database].[dbo].[$Table]"
|
|
$SqlExistingRecords = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $SqlQuery -Credential $Secret:sql_itdpsu1 -Verbose
|
|
|
|
$NewestRecord = ($SqlExistingRecords | Sort-Object -Descending opened_at) | select -First 1
|
|
$DateYMDFilter = ($NewestRecord.opened_at | Get-Date -UFormat "%Y-%m-%d")
|
|
$DateHMSFilter = ($NewestRecord.opened_at | Get-Date -UFormat "%H:%M:%S")
|
|
|
|
#$Filter = "cat_item=c64e27af47244610b7853238436d435d^opened_at>javascript:gs.dateGenerate('2024-07-30','23:59:59')"
|
|
|
|
#$Filter = "cat_item=c64e27af47244610b7853238436d435d"
|
|
$Filter = "cat_item=c64e27af47244610b7853238436d435d^opened_at>javascript:gs.dateGenerate('$DateYMDFilter','$DateHMSFilter')"
|
|
Write-Verbose -Message ("Filter is " + $Filter) -Verbose
|
|
|
|
# retrieve list of RITMs created since $opened_at (>opened_at)
|
|
Write-Verbose -Message "Retrieve list of RITMs created since last update" -Verbose
|
|
#$AllRitms = Get-ITDServiceNowRecord -ItemType 'Request Item' -Filter $Filter -IncludeTotalCount -IncludeCustomVariable | Sort-Object Number
|
|
$AllRitms = Get-ITDServiceNowRecord -ItemType 'Request Item' -Filter $Filter -IncludeTotalCount -IncludeCustomVariable | Sort-Object Number
|
|
#$AllRitms = Get-ITDServiceNowRecord -ItemType 'Request Item' -Number 'RITM0262097' -IncludeCustomVariable
|
|
Write-Verbose -Message ("RITMs found: " + @($AllRitms).count) -Verbose
|
|
|
|
|
|
ForEach ($Ritm in $AllRitms) {
|
|
Write-Verbose ("Start " + $Ritm.number) -Verbose
|
|
# get all variable set rows
|
|
$VariableSet = (Get-ITDServiceNowRecord -ItemType 'Request Item' -Number $Ritm.number.value -IncludeVariableSet).VariableSet
|
|
ForEach ($VSet in $VariableSet) {
|
|
Write-Verbose -Message ("Start " + $VSet.host_name + ' *** ' + $VSet.host_name_ref)
|
|
# figure out values
|
|
$RitmNum = $Ritm.number.value
|
|
$RitmSysId = $Ritm.sys_id.value
|
|
$opened_at = $Ritm.opened_at.display_value
|
|
$requested_for = $Ritm.requested_for.display_value
|
|
$request_type = $Ritm.CustomVariable.request_type.value
|
|
$environment = $Ritm.CustomVariable.environment.value
|
|
$host_name_ref = $VSet.host_name_ref
|
|
$host_name = If ($VSet.host_name_ref) {
|
|
(Get-ITDServiceNowRecord -Table cmdb_ci -SysId $VSet.host_name_ref).Name.display_value
|
|
}
|
|
Else {
|
|
$VSet.host_name
|
|
}
|
|
$server_type = $VSet.server_type
|
|
$operating_system = $VSet.operating_system
|
|
$target_os_version_linux = $VSet.target_os_version_linux
|
|
$target_os_version_windows = $VSet.target_os_version_windows
|
|
$target_platform = $VSet.target_platform
|
|
$processors = $VSet.processors
|
|
$memory_gb = $VSet.memory_gb
|
|
$cidr_block_sys_id = $VSet.cidr_block
|
|
$cidr_block = (Get-ITDServiceNowRecord -Table 'cmdb_ci_ip_network' -SysId $cidr_block_sys_id).subnet.display_value
|
|
$vlan_id = $VSet.vlan_id
|
|
$data_center = $VSet.data_center
|
|
$licensing_restrictions = $VSet.licensing_restrictions
|
|
$application_info_sys_id = $VSet.application_info
|
|
$application_info = (Get-ITDServiceNowRecord -Table 'cmdb_ci_service' -SysId $application_info_sys_id).name.display_value
|
|
$support_hours = $VSet.support_hours
|
|
$dr_protection = $VSet.dr_protection
|
|
$startup_priority = $VSet.startup_priority
|
|
$disk_1_os = $VSet.disk_1_os
|
|
$disk_2_swap_disk = $VSet.disk_2_swap_disk
|
|
$disk_3 = $VSet.disk_3
|
|
$disk_4 = $VSet.disk_4
|
|
$disk_5 = $VSet.disk_5
|
|
$disk_6 = $VSet.disk_6
|
|
$disk_7 = $VSet.disk_7
|
|
$disk_8 = $VSet.disk_8
|
|
$disk_9 = $VSet.disk_9
|
|
$disk_10 = $VSet.disk_10
|
|
$disk_11 = $VSet.disk_11
|
|
$disk_12 = $VSet.disk_12
|
|
$disk_13 = $VSet.disk_13
|
|
$disk_14 = $VSet.disk_14
|
|
$disk_15 = $VSet.disk_15
|
|
$disk_16 = $VSet.disk_16
|
|
$special_instructions = $Ritm.CustomVariable.special_instructions.value
|
|
$customer_request = $Ritm.CustomVariable.customer_request.value
|
|
$additional_comments = $Ritm.CustomVariable.additional_comments.value
|
|
|
|
Write-Verbose -Message ("Add to SQL " + $VSet.host_name + ' *** ' + $VSet.host_name_ref)
|
|
# add record to SQL
|
|
$SqlQuery = "INSERT INTO [$Table] (
|
|
RitmNum, RitmSysId, opened_at,requested_for,request_type,environment,host_name_ref,host_name,server_type,operating_system,target_os_version_linux,target_os_version_windows,target_platform,processors,memory_gb,cidr_block_sys_id,cidr_block,vlan_id,data_center,licensing_restrictions,application_info_sys_id,application_info,support_hours,dr_protection,startup_priority,disk_1_os,disk_2_swap_disk,disk_3,disk_4,disk_5,disk_6,disk_7,disk_8,disk_9,disk_10,disk_11,disk_12,disk_13,disk_14,disk_15,disk_16,special_instructions,customer_request,additional_comments) Values (
|
|
'$RitmNum','$RitmSysId','$opened_at','$requested_for','$request_type','$environment','$host_name_ref','$host_name','$server_type','$operating_system','$target_os_version_linux','$target_os_version_windows','$target_platform','$processors','$memory_gb','$cidr_block_sys_id','$cidr_block','$vlan_id','$data_center','$licensing_restrictions','$application_info_sys_id','$application_info','$support_hours','$dr_protection','$startup_priority','$disk_1_os','$disk_2_swap_disk','$disk_3','$disk_4','$disk_5','$disk_6','$disk_7','$disk_8','$disk_9','$disk_10','$disk_11','$disk_12','$disk_13','$disk_14','$disk_15','$disk_16','$special_instructions','$customer_request','$additional_comments'
|
|
)
|
|
"
|
|
|
|
Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $SqlQuery -Credential $Secret:sql_itdpsu1 -Verbose
|
|
Write-Verbose -Message ("End " + $VSet.host_name + ' *** ' + $VSet.host_name_ref)
|
|
}
|
|
Write-Verbose ("End " + $Ritm.number) -Verbose
|
|
}
|
|
|
|
<# scratch
|
|
Write-Verbose -Message ("Add record to SQL") -Verbose
|
|
$SqlQuery = "INSERT INTO [$SnapshotTable] (VMName, DateTime, RequestedBy, DurationHours,Status,ExpireDateTime,NotifyEmail,PSUJobIdRequest) Values ('$Name', '$StartDateTimeSql', '$RequestedBy', $DurationHours, 'Requested', '$EndDateTimeSql','$Email','$PSUJobId');SELECT SCOPE_IDENTITY();"
|
|
#Write-Verbose -Message $SqlQuery -Verbose
|
|
$SnapshotId = (Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $SqlQuery -Credential $Secret:itdpsu1 -Verbose).Column1
|
|
|
|
|
|
|
|
|
|
select TOP (1000) [RitmNum]
|
|
, [RitmSysId]
|
|
, [opened_at]
|
|
, [requested_for]
|
|
, [request_type]
|
|
, [environment]
|
|
, [host_name_ref]
|
|
, [host_name]
|
|
, [server_type]
|
|
, [operating_system]
|
|
, [target_os_version_linux]
|
|
, [target_os_version_windows]
|
|
, [target_platform]
|
|
, [processors]
|
|
, [memory_gb]
|
|
, [cidr_block_sys_id]
|
|
, [cidr_block]
|
|
, [vlan_id]
|
|
, [data_center]
|
|
, [licensing_restrictions]
|
|
, [application_info_sys_id]
|
|
, [application_info]
|
|
, [support_hours]
|
|
, [dr_protection]
|
|
, [startup_priority]
|
|
, [disk_1_os]
|
|
, [disk_2_swap_disk]
|
|
, [disk_3]
|
|
, [disk_4]
|
|
, [disk_5]
|
|
, [disk_6]
|
|
, [disk_7]
|
|
, [disk_8]
|
|
, [disk_9]
|
|
, [disk_10]
|
|
, [disk_11]
|
|
, [disk_12]
|
|
, [disk_13]
|
|
, [disk_14]
|
|
, [disk_15]
|
|
, [disk_16]
|
|
, [special_instructions]
|
|
, [customer_request]
|
|
, [additional_comments]
|
|
FROM [ITD-Systems-Automation].[dbo].[ServiceNow_RitmDump_ServerBuildRequestV1]
|
|
#> |