Is there a faster way?

[DrMarkX]

I have a script that finds all tasks associated with a specific queue (let's call it XGroup) and a string in the short description. The task search/retrieval takes about 6 minutes since there are almost 300 tasks that meet the criteria. I then need to loop those task IDs through the requested items to get data from the custom variable. Doing this causes the script to run for so long that I end up killing it and only getting about 70 results. Is there a faster way to perform this process?

Example:

$filter = @('active', '-eq', 'true'),
	'-and', @('u_resolved_date', '-eq', '')
	'-and', @('assignment_group', '-like', 'XGroup'),
	'-and', @('short_description', '-like', 'Access Request')

$tasks = Get-ServiceNowRecord -Table 'Catalog Task' -Filter $filter -IncludeTotalCount

$taskList = @()
foreach ($task in $tasks) {
	$ritm = Get-ServiceNowRecord -Table 'sc_req_item' -ID $($task.request_item).display_value -IncludeCustomVariable | Select-Object -ExpandProperty CustomVariable
	$requestFor = Get-ServiceNowRecord -Table 'sys_user' -ID $($ritm.Where({$_.name -eq 'reqfor_id'})).value | Select-Object user_name
	$modelAfter = Get-ServiceNowRecord -Table 'sys_user' -ID $($ritm.Where({$_.name -eq 'basic_copy_id'})).value | Select-Object user_name
	$taskParts = [PSCustomObject]@{
		Number		= $task.number
		Description	= $task.short_description
		Created		= $task.sys_created_on
		Updated		= $task.sys_updated_on
		StartDate	= [DateTime]$($ritm.Where({$_.name -eq 'basic_start_date'})).value
		RequestFor	= $requestFor.user_name
		Model		= $modelAfter.user_name
		RITM		= $($task.request_item).display_value
	}
	$taskList += $taskParts
}

[gdbarron]

@DrMarkX, apologize for the delay, here are a few thoughts:

• Only bring back the properties you actually need as this will reduce the amount of data coming back and definitely speed things up
• Minimize the use of -like in your filter as that will always take longer
• You can get all the ritm data in one call once you have your list of tasks
• When getting the user name, just get the 1 property and use -AsValue
• Instead of adding to an array with +=, use arraylist or a generic list. For a few hundred items it won't make a huge impact, but += will always be slower.
• You could also execute the loop in -Parallel, but would need to use a thread safe collection or simply set the foreach to a variable directly, eg. $taskList = foreach ($task in $tasks){}

Give this a try...

$filter = @('active', '-eq', 'true'),
'-and', @('u_resolved_date', '-eq', '')
'-and', @('assignment_group.name', '-eq', 'XGroup'),
'-and', @('short_description', '-like', 'Access Request')

$tasks = gsnr -table 'catalog task' -Property number, short_description, sys_created_on, sys_updated_on, request_item.number -IncludeTotalCount
$ritms = gsnr -table sc_req_item -filter @('number', '-in', ($tasks.'request_item.number' -join ',')) -IncludeCustomVariable -Property number -IncludeTotalCount

$taskList = [System.Collections.Generic.List[object]]::new()

foreach ($task in $tasks) {
    $ritm = $ritms.where{ $_.number -eq $task.'request_item.number' } | Select-Object -ExpandProperty CustomVariable
    $requestFor = Get-ServiceNowRecord -Table 'sys_user' -ID $($ritm.Where({ $_.name -eq 'reqfor_id' })).value -Property user_name -AsValue
    $modelAfter = Get-ServiceNowRecord -Table 'sys_user' -ID $($ritm.Where({ $_.name -eq 'basic_copy_id' })).value -Property user_name -AsValue
    $taskList.Add(
        [PSCustomObject]@{
            Number      = $task.number
            Description	= $task.short_description
            Created     = $task.sys_created_on
            Updated     = $task.sys_updated_on
            StartDate   = [DateTime]$($ritm.Where({ $_.name -eq 'basic_start_date' })).value
            RequestFor  = $requestFor
            Model       = $modelAfter
            RITM        = $ritm.number
        }
    )
}

[DrMarkX]

Thanks. This has sped up the process but I'm getting a 400 Bad Request error with
$ritms = Get-ServiceNowRecord -table sc_req_item -Filter @('number', '-in', ($tasks.'request_item.number' -join ',')) -IncludeCustomVariable -Property number

[DrMarkX]

I tested setting the task list to only five records and this worked. Is there a maximum number of records you can use with "IN"? Currently, I'm trying to gather 270+ records.

[gdbarron]

I'm not aware of any max with -in, but perhaps the URL is too long. I'd try 2 calls, each with half.

[DrMarkX]

Excellent call out - completely forgot about passing these data to a URL.
I was able to fix this by splitting it into batches of 50.

$allRitms = @()
$ritms = @()

if($tasks.count -lt 50) { $seg = $tasks.count }
else { $seg = 49 }

$taskMaths = [Math]::Ceiling($tasks.Length/50) - 1
$i = 1
$tcs, $tce = 0
while($i -le $taskMaths) {
	$tce = $tcs + $seg
	$ritms = Get-ServiceNowRecord -Table sc_req_item -filter @('number', '-in', ($tasks.'request_item.number'[$tcs..$tce] -join ',')) -IncludeCustomVariable -Property number -IncludeTotalCount
	$allRitms += $ritms
	$tcs = $tce + 1
	$i++
}
$tce = $tasks.Length - 1
$ritms = Get-ServiceNowRecord -Table sc_req_item -filter @('number', '-in', ($tasks.'request_item.number'[$tcs..$tce] -join ',')) -IncludeCustomVariable -Property number -IncludeTotalCount
$allRitms += $ritms