Author Posts

August 1, 2014 at 3:12 am

Morning Folks!

I'm doing a Get-ADUser search for inactive/dormant users, including the 'lastlogon' attribute for each user (and querying all four domain controllers as I know this attribute doesn't replicate). This is working fine. (Obviously I have up to four entries for each user, but Security are happy with that – they'll deal with it).

However, I can't get the overall output on to a single spreadsheet. I essentially want the output of each DC to 'append' to the csv file, rather than overwrite it (tried -NoClobber to prevent any overwrites but I obviously only end up with the output of a single DC).

On my first attempt, I had to use Out-File, write to a text file using the -Append switch. I then pasted that into Excel and messed with it a little to get what I wanted. OK as a one-off, but it looks like I'm going to be asked for this on a regular basis. Also, more properties are now required per user and it's defaulting to a list-view. I've tried format-table before exporting to my text file (which I know isn't great), but I think it's time I just found a way of natively exporting everything to a single csv file. I've shifted curly braces around and still get errors. I'm sure I'll kick myself when I find out what's wrong.

My input file is simply a single-column csv containing the first name and last name of each user. Column header is 'name'.

Could somebody please look at the attached code and suggest how I might use Export-Csv to a single csv file? (I've left the current Out-Default cmdlet at the end for now).

Cheers-much-in-advance.

TG

August 1, 2014 at 4:29 am

You cannot do a Format-Table (ft) prior to outputting it to a file. The Format-Table or Format-List to do not pass through. That's the end of the line. Remove the | ft try your command.

August 1, 2014 at 4:34 am

Thanks Rob,

Sorry – I should have made it clearer. I only did that so I could get the information into Out-File, which was a very last resort when Export-Csv wasn't working the way I wanted it to. I didn't originally have ft in there. Even without the | ft, it still doesn't output properly using Export-Csv. It just overwrites the csv file it creates.

August 1, 2014 at 4:57 am

So, you can use that logic and append to a CSV, but that very inefficient. Writing a single line as the script is runs is vbScript logic, but it opens a file, writes each line and then closes the file. In Powershell you should leverage a PSObject, which is comparable in that you create a new object, add a object (line) to it and then you have an object that you can sort, filter and export. :

#Generate a blank object
$users = @()
$dcs = Get-ADComputer -f * -SearchBase 'ou=domain controllers,dc=company,dc=pri'
#Assign a variable to your for loop and as Get-ADUser is run, it will append to the @users object
$users = foreach ($dc in $dcs) {
    Import-Csv 'E:\list_of_displaynames.csv' | foreach {
        $f = $_.name;
        Get-ADUser -f {displayname -eq $f} -Server $dc.name -properties DisplayName,whencreated,description,lastlogon} |
        select Name,DisplayName,Description,WhenCreated,Enabled,@{l='LastLoggedIn';e={w32tm /ntte $_.lastlogon}
    }
}

# Now you have an object, you can leverage the power of PowerShell
# Group all of the Enabled\Disabled users and Sort by the count
$users | Group-Object -Property Enabled | | Sort-Object -Property Count -Descending
#Get all users that have marketing in the Description
$users | Where{$_.Description -like "*Marketing*"}
# Export the results to a CSV
$users | Export-CSV C:\users.csv

August 1, 2014 at 6:06 am

Thanks Rob – that has worked perfectly! Just what I needed.

I thought I was a lot closer than I obviously was, but I've learnt plenty in this one example. Many thanks for your help with this. 🙂

August 3, 2014 at 6:47 am

Two things to add here. Rob Simmers nailed the 'format right rule' on this one, once you pipe to a format command, you have effectively converted your very useful object to a less useful Microsoft.PowerShell.Commands.Internal.Format object. The easy way to see this is to take a command like Get-WMIObject and pipe it to Get-Member, do this again with a format command inserted like this:

PS C:\Scripting> Get-WmiObject Win32_BIOS | Get-Member


   TypeName: System.Management.ManagementObject#root\cimv2\Win32_BIOS

Name                  MemberType    Definition

PSComputerName        AliasProperty PSComputerName = __SERVER
BiosCharacteristics   Property      uint16[] BiosCharacteristics {get;set;}

# Stuff Deleted

PS C:\Scripting> Get-WmiObject Win32_BIOS | Format-List | Get-Member

   TypeName: Microsoft.PowerShell.Commands.Internal.Format.FormatStartData

# Stuff Deleted

   TypeName: Microsoft.PowerShell.Commands.Internal.Format.GroupStartData

# Lots of Stuff Deleted

On a separate AD related note, is there any reason why you are trying to use lastLogon as opposed to lastLogonTimestamp. The lastLogonTimeStamp, by default is replicated to all Domain Controllers in the domain.

These attributes are designed to help with inactive users as they are typically about 9-14 days earlier than the last true logon, real time data will need to come from log collection.

August 4, 2014 at 4:06 am

Thanks Robert,

Yes, I did know about the format-right rule. I was just trying anything to get all of the data into one sheet so I could manipulate it in Excel for a one-off report which was needed in a rush. I know it was slapdash (now you know how I got my name), but it worked on this one occasion.

I may have originally misunderstood the way that the lastlogondate,lastlogon and lastlogontimestamp attributes worked. I had read that they were low-priority replication, but I was worried that if someone hadn't logged in for a number of months, and then for example returned from maternity leave yesterday and logged in, it might not have registered/replicated.

We're disabling accounts that haven't been used for 60 days, so I want to be absolutely sure before I send the results on to Management. Apologies if I've got the wrong end of the stick. I'll read up some more on the differences between the various attributes and if the lastlogontimestamp is the one to use, I'll amend the script to query that attribute and just the one DC.

Appreciate your input. Many thanks.

TG