Powershell search query invoke

Identity Security Cloud (ISC) ISC Discussion and Questions
, ,
1

Hi, Can any one please help me on search query invoke example from Powershell sdk. For example, I have these two different search queries which I am using from Sailpoint admin console search page:

Example 1 - @accounts(source.name:ApplicationName) AND attributes.employeeStatus: "T"

Example 2 - attributes.sourceName:("SourceName1","SourceName2") AND created:[now-7d TO now] AND operation:Aggregate AND status:Failed

This same query I want to invoke from powershell and save the output in csv file.

2

Also, one more thing, when we use search query from Sailpoint console, we have an option to select required columns and sort it. Can we do similar kind of column filter and sort in powershell api call.

3

You can only make a json output, not csv.

4

Thanks Kamil for your response. Could you please share any similar example as per my use case.

5

Hello @hranjan3,

The script below includes an example on how to use your two queries in the PowerShell SDK. To get the json for the query, I just used the chrome browser to inspect the call in the UI and pulled out the search payload.

$Example1 = @"
{
    "query": {
        "query": "@accounts(source.name:ApplicationName) AND attributes.employeeStatus: \"T\""
    },
    "indices": [
        "identities"
    ],
    "includeNested": false,
    "sort": [
        "displayName"
    ]
}
"@

$Example2 = @"
{
    "query": {
        "query": "attributes.sourceName:(\"SourceName1\",\"SourceName2\") AND created:[now-7d TO now] AND operation:Aggregate AND status:Failed"
    },
    "indices": [
        "events"
    ],
    "includeNested": false,
    "sort": [
        "-created"
    ]
}
"@



$Search = ConvertFrom-JsonToSearch -Json $Example1

Search-Post -Search $Search -WithHttpInfo -Verbose
6

Thank you so much Tyler!

7

Hi Tyler,
I need one help. I am using one powershell script which is as follows:

$Example1 = @"
{
    "query": {
        "query": "@accounts(source.name:Source_Name) AND attributes.customAttribute2: \"A\""
    },
    "indices": [
        "identities"
    ],
    "includeNested": false,
    "sort": [
        "displayName"
    ]
}
"@
try {
    $Search = ConvertFrom-JsonToSearch -Json $Example1
    Search-Post -Search $Search | ConvertTo-Csv -UseCulture -NoTypeInformation | Out-File "C:\Users\Documents\WindowsPowerShell\Modules\PSSailpoint\1.2.1\beta\src\PSSailpointBeta\Api\SPAPITest\TEST3.csv"

} catch {
    Write-Host ("Exception occurred when calling Search-Post: {0}" -f $_.ErrorDetails)
    Write-Host ("Response headers: {0}" -f $_.Exception.Response.Headers)
}

On executing this script I am getting output but there are few columns whose output is showing up as :

"lastName" - "Bond"
"access" - "System.Object[]"
"roleCount" - "0"
"ownsCount" - "0"
"displayName" - "James Bond"
"accessProfileCount" - "0"
"source" - "System.Management.Automation.OrderedHashtable"
"visibleSegmentCount" - "0"
"employeeNumber" - "0824190"
"inactive" - "False"
"protected" - "False"
"identityProfile" - "System.Management.Automation.OrderedHashtable"
"modified" - "1/7/2024 8:19:55 PM"
"isManager, - "False"
"id" - "997d1f3e8fe7400000000eb619adfceab"
"email" - "xxxxxxxxxxxxxxxxxxx"
"apps" - "System.Object[]"
"appCount" - "1"
"accessCount" - "65"
"entitlementCount" - "65"
"tagsCount" - "0"
"accountCount" - "9"
"synced" - "1/7/2024 8:19:57 PM"
"manager" - "System.Management.Automation.OrderedHashtable"
"created" - "5/11/2023 7:51:39 PM"
"tags" - "System.Object[]"
"firstName", - "James"
"name" - "P10001456"
"attributes" - "System.Management.Automation.OrderedHashtable"
"accounts" - "System.Object[]"
"status" - "UNREGISTERED"
"_type" - "identity"
"type" - "identity"
"_version" - "v7"

most of these column values in csv appear ok but for few its showing up as - System.Management.Automation.OrderedHashtable could you please suggest me how to handle this and how the proper value.

8

@kjakubiak , @darrenjrobinson FYI and help

9

Hello @hranjan3,

The issue here is that the ConvertTo-Csv cmdlet does not support nested objects. The CSV format is typically meant to be a flat file, and not for nested object values.

Can you use the json format returned by default?

10

@tyler_mairose is correct and the Convert to CSV command can’t handle nested objects, you’ll get the Type of the object instead of the contents. Depending on your needs you can use JSON like he suggested, just be sure to use the Depth parameter, 10 is usually deep enough, but no risk of going higher that I’ve found.

@hranjan3, another options is run a Foreach loop through your Search results and create a PSCustomObject:

$Data =     Search-Post -Search $Search | ConvertTo-Csv -UseCulture -NoTypeInformation | Out-File "C:\Users\Documents\WindowsPowerShell\Modules\PSSailpoint\1.2.1\beta\src\PSSailpointBeta\Api\SPAPITest\TEST3.csv"

$Export = foreach ($User in $Data) {

[pscustomobject]@{
        
        Name = $Data.name
        id  = $Data.id
        MgrName = $Data.manager.displayName

}

$Export | Export-Csv $Path

Accounts and Access are Arrays, so if you also want data from those lists it will be more involved. But for Attributes and Manager you can just create a line for the details you want in the Custom Object. Does that help?

1 Like
11

Another option is to try using | Out-String before your | Out-CSV

You might need to do that in two steps.
So get your results into an object

$results = Search-Post -Search $Search | out-string
$results | ConvertTo-Csv -UseCulture -NoTypeInformation | Out-File "C:\ ....
12

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.