AD Secretary and seeAlso attribute - CSV

[mctech]

Hi Guys.

I am having some issues with a script. I am trying to pull some info out of AD in to a CSV file. Now i am able to pull the SamAccountName and the firstname and lastname of the Secretary and i am trying to do the same with the seeAlso attribute. Secretary and seeAlso are both DN attributes.

I am just getting nothing in the CSV file for seeAlso

Thanks in advance for any help

Connect-QADService -Service "AR-SERVER-NAME" -Proxy
$OUName = "OU=DN-HEREt"
$CSVName = "C:\Export.csv"

$Report = @()

foreach ($user in (Get-QADUser -SearchRoot $OUName -SizeLimit 0 -IncludedProperties Secretary , seeAlso )) {

    try {
        $Secretary = Get-QADUser $user.Secretary -ErrorAction Stop
        $sName = "$($Secretary.GivenName) $($Secretary.SN)"
    
    }
    catch {
        $sName = ''
    }


     try {
        $ADSeeAlso = Get-QADUser $user.seeAlso -ErrorAction Stop
        $sADSeeAlso = "$($ADSeeAlso.GivenName) $($ADSeeAlso.SN)"
    
    }
    catch {
        $sADSeeAlso = ''
    }

    $Report += New-Object PSObject -Property @{
        'samaccountname' = $user.SamAccountName 
        'Secretary' = $sName
        'seeAlso' = $sADSeeAlso
    }
}

$Report | Select-Object samaccountname , Secretary , seeAlso | Export-Csv $CSVName -NoTypeInformation -Force

[Olaf]

Secretary and seeAlso seem to be special properties generated by the Quest AD cmdlets. Since most of us will not have access to these cmdlets it might be hard to find someone able to help you further.

You are setting the output to '' (empty string) if there are errors. How about setting them to something helpful in case of an error to be able to troubleshoot your code?

What do you get when you query one particular users seeAlso attribute?

And please … do not format code as quotes. Instead format it as code.

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org 1 <---- Click

( !! Sometimes the preformatted text button hides behind the settings gear symbol. )

[mctech]

Hello. Thanks for the reply.

Secretary and seeAlso are not specific to the Quest cmdlets. The attributes are standard AD attributes.

Running the Quest code below

Get-QADUser UserName -includedproperties seeAlso | Select-object SeeAlso

Or the AD equivalent

Import-Module ActiveDirectory


$username = "UserName"
$user = Get-ADUser -Identity $username -Properties seeAlso

$seeAlso = $user.seeAlso
Write-Output "seeAlso: $seeAlso"

Both return the same results for a user account. In that i see the full DN, I just need to be returning the first and last name from them DN

[Olaf]

@mctech

The attributes are standard AD attributes.

Only until Windows Server 2012.

@mctech

Both return the same results for a user account. In that i see the full DN, I just need to be returning the first and last name from them DN

And what are the property names of the first and last name from them DN?

What actually is “DN”? Are you talking about the “CN” (Common Name)?

[mctech]

Apologies. The AD attribute Secretary and seeAlso are a DN type but yes the attribute value is a CN=LastName, FirstName

As said i can return the first name and last name of the secretary attribute but not the seeAlso. its possible that it contains more than one entry

[mctech]

In fact. I have just noticed that accounts where there is only a single entry in the seeAlso field are returning a first name and last name back to me in a CSV. Where there are two or more i am seeing nothing in the CSV. I am a little unsure what would need to be done to my original code to over come that?

[Olaf]

@mctech

Where there are two or more i am seeing nothing in the CSV. I am a little unsure what would need to be done to my original code to over come that?

Didn’t we treat this issue in the other thread you had here a few days ago?

[mctech]

I thought that post had been removed. I have had to refresh my browser a few times to see it again.

using -join ', ' didnt change anything. In the code above i get blanks in the CSV not System.Object[] which if i was at least getting that it would be something.

[mctech]

Ok, so this code gets me closer. So now when i run this i am getting two or more CN= in the Secretary field. But what i need here is just the LastName , FirstName of the accounts listed.

Connect-QADService -Service "AR-Service" -Proxy
$OUName = "OU=HERE"
$CSVName = "C:\Export.csv"

Get-QADUser -SearchRoot $OUName -SizeLimit 0 -IncludedProperties Secretary  |

Select-Object @{Name='Secretary';Expression={$_.Secretary}} | Export-Csv $csvName -NoTypeInformation

[Olaf]

@mctech

In the code above i get blanks in the CSV

I’d expect this from your try catch block if there is an error …

@mctech

try {
        $Secretary = Get-QADUser $user.Secretary -ErrorAction Stop
        $sName = "$($Secretary.GivenName) $($Secretary.SN)"
    }
    catch {
        $sName = ''
    }

That’s why I asked what you get back when you query one of these user account where you get these blanks.

@mctech

So now when i run this i am getting two or more CN= in the Secretary field. But what i need here is just the LastName , FirstName of the accounts listed.

So you either query the AD for each of the accounts and extract FirstName and LastName or you use string acrobatics to slice the output into the pieces you want.

BTW: Is there a particular readson why you’re using the Quest AD cmdlets? AFAIK ended their support already and they are actually not necessary anmore since the MSFT AD cmdlets provide sufficient functionality.

And you will probably find easier / better / more help on the default MSFT AD cmdlets.

[mctech]

Thanks for the reply. I have to use the Quest cmdlets as we use Active Roles server and part of my extra code needs to obtain data from there.

How could i query for the firstname and last name of each item returned in the $_.Secretary?

[Olaf]

@mctech

How could i query for the firstname and last name

You do not query for the first and last name you query for the account and there included you have the properties for first and last name.

Do you really get only the CN returned? … or is it the DistinguishedName? I’d recommend to try to get one of the unique names from the AD like DistinguishedName, sAMAccountName or UserPrincipalName. With this you could create a hashtable in advance and use this as lookup table inside your loop. That would reduce the stress you put on your AD.

[mctech]

The value returned is CN=blah blah its from this i need to get the first and last name.

[Olaf]

Hmmm your answers are actually quite ambigious.

@mctech

CN=blah blah

Does “blah blah” mean “FirstName LastName” only or does it mean “*FirstName LastName,OU=Users,OU=City,DC=Contoso,DC=com” or something like this? The latter one is the DistinguishedName and should be prefered.

[mctech]

Hi. Thanks for the reply, Yes. I said at the start Secretary and seeAlso are DN attributes and both contain as an example

“*LastName\, FirstName,OU=Users,OU=City,DC=Contoso,DC=com”

Its from this where is has a single value or multiple value that i need to grab the first and last name of each entry.

“*FirstName LastName,OU=Users,OU=City,DC=Contoso,DC=com”

@Olaf

“*FirstName LastName,OU=Users,OU=City,DC=Contoso,DC=com”

[Olaf]

@mctech

Yes. I said at the start Secretary and seeAlso are DN attributes and both contain as an example

So you have everything you need. Again - as I said earlier - I’d query all potentially needed accounts in advance, save them to a hashtable and use this as lookuptable in a nested loop inside your loop.

[mctech]

Hi.

So i am really close here.

So this is what i have knocked up. Its not mega pretty but for now i can live with that as i just need something working.

So i am grabbing all the DN from the Secretary field and from there i am grabbing the SamAccountName. From that i am able to grab the value from an attribute called firstname-lastname-display

Now if i write-host $ADsecretaryName then as an example i am returned Steve and Jane

Now the CSV seems to be only adding to the report ‘section Secretary’ = $ADsecretaryName the very last name returned.

Any ideas? I need to have everything in the same CSV column field? Its not -Append to the CSV file itself, Just need all data in the Secretary CSV Column

Connect-QADService -Service "AR-SERVER-NAME-HEREt" -Proxy
$OUName = "OU=Here"
$CSVName = "C:\Export.csv"


$Report = @()

foreach ($user in (Get-QADUser -SearchRoot $OUName -SizeLimit 0 -IncludedProperties Secretary , firstname-lastname-display )) {
$secretaryName = $User.secretary

$SamAccountName = $User.SamAccountName


foreach ($user in $secretaryName ) {


$ADsecretarySamAccountName = Get-QADUser -Identity $User | Select-Object -ExpandProperty SamAccountName

$ADsecretaryName = Get-QADUser $ADsecretarySamAccountName -IncludedProperties firstname-lastname-display | select-object -ExpandProperty firstname-lastname-display

}

}
  
    $Report += New-Object PSObject -Property @{
        'Employee Initals' = $SamAccountName 
        'Secretary' = $ADsecretaryName
    }
#}


$Report | Select-Object "Employee Initals" , Secretary | Export-Csv $CSVName -NoTypeInformation -Force -Encoding UTF8

[Olaf]

If you’d format your code properly you might see your failures yourself. What do you use as your IDE? I’d recommend using VSCode since it gives you a lot of help and hints.

$OUName = "OU=Here"
$CSVName = "C:\Export.csv"

$UserList = 
    Get-QADUser -SearchRoot $OUName -SizeLimit 0 -IncludedProperties Secretary , firstname-lastname-display
    
$Report = 
foreach ($user in $UserList ) {
    foreach ($secretary in $User.secretary ) {
        $ADsecretary = Get-QADUser -Identity $User.SamAccountName
        $ADsecretaryName = Get-QADUser $ADsecretary.sAMAccountName -IncludedProperties firstname-lastname-display 
        [PSCustomObject]@{
            'Employee Initals' = $User.SamAccountName
            'Secretary'        = $ADsecretaryName.'firstname-lastname-display'
        }
    }
}

$Report | 
    Export-Csv -Path $CSVName -NoTypeInformation -Force -Encoding UTF8

But again: This way you put a lot of stress to your AD since you query it for each individual user and again for each individual secretary.
I’d query it only once in advance reading all relevant users and attributes and work with this data.

You may read up this:

GitHub - PoshCode/PowerShellPracticeAndStyle: The Unofficial PowerShell Best...

[mctech]

Thanks , I will take a look at VSCode.

Thank you for giving me some code to try. This does not return anything in the Secretary field now. Im still looking at it

[Olaf]

I’ll go for a walk with the dog and take a look at it later …