Powershell code

 Here’s how you can convert the second script (extract_req_details) to PowerShell, which performs the aggregation query based on hostname or any other field and retrieves the required details.

PowerShell Script for extract_req_details

powershell
# Define the API endpoint and headers
$apiUrl = "https://dev-api.cmnow-dev.ubsdev.net/aggregate?collection=sc_req_item"
$headers = @{
    "Content-Type"  = "application/json"
    "x-snow-auth"   = "xxxxx"  # Your token or authentication method
}

# Define the aggregation pipeline
$body = @(
    @{ "$match" = @{ "variables.hostname" = "ZU322K10" } },  # Filter by hostname
    @{ "$lookup" = @{
            "from" = "sc_task"
            "localField" = "_id"
            "foreignField" = "parent.sys_id"
            "as" = "sc_task"
        }
    },
    @{ "$unwind" = "$sc_task" },  # Deconstruct the array of tasks
    @{ "$project" = @{
            "RITM Number" = "$number"
            "REQ Number" = "$request.display_value"
            "Catalog Item" = "$cat_item.display_value"
            "Task Number" = "$sc_task.number"
            "Task Description" = "$sc_task.short_description"
            "Task Assignment Group" = "$sc_task.assignment_group.display_value"
            "Task State" = "$sc_task.state.display_value"
            "Task Assigned To" = "$sc_task.assigned_to.display_value"
        }
    }
) | ConvertTo-Json -Compress  # Convert to JSON

# Make the POST request
$response = Invoke-RestMethod -Uri $apiUrl -Method Post -Body $body -Headers $headers

# Output the result
$response | Format-List

Explanation:

1. API Endpoint and Headers:

   • The $apiUrl holds the endpoint to which you are sending the request.
   • The $headers contains the required headers for authentication and content type (x-snow-auth and Content-Type).

2. Aggregation Pipeline:

   • The $body holds the aggregation query, converted to JSON using ConvertTo-Json. It matches records based on the hostname and looks up related tasks from the sc_task collection.
   • The project stage specifies the fields to be returned, including RITM Number, REQ Number, Task Number, etc.

3. Making the Request:

   • The Invoke-RestMethod is used to send the POST request to the API with the aggregation query in the body.
   • The result is stored in $response.

4. Displaying the Output:

   • The Format-List cmdlet formats the output in a readable list form. You can customize how you want the output to appear or store it in a file for further processing.

Customization:

• Authentication: Ensure the x-snow-auth token is correctly set in the $headers section.
• Filter by Other Fields: If you want to filter based on something other than hostname, replace "variables.hostname" with the appropriate field in the $match stage.
• Output Formatting: You can modify how you display the results using PowerShell's Select-Object, Export-Csv, or other output cmdlets.