• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

powerhsell for AD users

Hi,
I have this below code which seems ok but I would like to get few things added.

Import-Module Activedirectory
Search-ADAccount -AccountInactive -TimeSpan 30.00:00:00 | where {$_.ObjectClass -eq 'user'} | Select Name,samaccountname,lastlogondate | Export-csv C:\temp\test13.csv -nti

this pulls system mailbolboxes ( I attached  screenshot)  how can I avoid them being detected by the script? Can I exclude them.
Also is there better way of exporting data into excel with a better fortmat?  result in Excel seems very nested and I always need to expand the cells so that data can fit it ok and also information is not in-line in the column
ad.PNG
0
kuzum
Asked:
kuzum
  • 12
  • 10
1 Solution
 
SubsunCommented:
You can try this code to exclude the system mailboxes...
Search-ADAccount -AccountInactive -TimeSpan 30.00:00:00 | where {$_.ObjectClass -eq 'user' -and $_.Name -notlike "SystemMailbox*"} | Select Name,samaccountname,lastlogondate | Export-csv C:\temp\test13.csv -nti

Open in new window


Exporting to xlsx is not that easy, but if required you can use the custom functions like Export-XLSX (refer the following article for details). What you mean by information is not in-line in the column?

http://gallery.technet.microsoft.com/office/Export-XLSX-PowerShell-f2f0c035
0
 
kuzumAuthor Commented:
can you also please give a code that only pulls ALL Active AD user  Accounts?
0
 
SubsunCommented:
You mean to pull the users who are not disabled? If yes.. following code should do it..
Search-ADAccount -AccountInactive -TimeSpan 30.00:00:00 -UsersOnly | where {$_.Enabled -eq $true -and $_.Name -notlike "SystemMailbox*"} | Select Name,samaccountname,lastlogondate,Enabled | Export-csv C:\temp\test13.csv -nti

Open in new window

0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
kuzumAuthor Commented:
I mean active accounts technically yes enabled accounts. Is there any other way of knowing if the account is in use or not?
I believe only way to know is by running the first code which tells inactive accounts?
0
 
SubsunCommented:
When we use -AccountInactive switch Search-ADAccount will looks for accounts that have not logged in within a given time period (In our case -TimeSpan 30.00:00:00 means 30 days )or since a specified time. So you know that the account was not in use for last 30 days..

To know more about Search-ADAccount, refer following article..
http://technet.microsoft.com/en-us/library/ee617247.aspx

Ideally in a production environment, all inactive accounts (may be 30 or 60 days) will be moved to a specific OU and kept disabled for another 30 or 45 days (Buffer time). After the buffer time account will be deleted. If the user come back with in the buffer time then we can re-enable the account.
0
 
kuzumAuthor Commented:
thanks Subsun, very helpful as always..

I attached 2 screen shot.
1- some accounts have no usernames and instead filled in with 82AFFADA236A4847952 etc.
2. what I meant about output was is also attached, I guess this is why you suggested xls ? I would like to have excel report to look more clean if possible?
samaccountname.PNG
scv-export.PNG
0
 
kuzumAuthor Commented:
oops- forgot to ask the main question:) can We please have the names in the report only for those have long string like 82AFFADA236A4847952  etc. ? I need to have user's names, surnames  and usernames with last logon date and time only on the report.
0
 
SubsunCommented:
For excel report you can use the function which I posted in my previous comment ID: 40279541..

'82AFFADA236A4847952' is that a sAMAccountName? If yes is it always be a combination of numbers and alphabets?
0
 
kuzumAuthor Commented:
Regards to
'82AFFADA236A4847952' is that a sAMAccountName? If yes is it always be a combination of numbers and alphabets"

Yes it is the Samaccount name, What I would like to have is user's username and their full names basically. am I doing it wrong?
0
 
SubsunCommented:
Hmm.. Are you saying the actual sAMAccountName of user In AD is not reflecting in report?
0
 
kuzumAuthor Commented:
Correct, not sure why it is happening
0
 
kuzumAuthor Commented:
I foundout why, those are disabled accounts and in AD they have no information in "use logon name"  field and I report is pulling that information from "user logon name (pre-Windows 2000) " field
0
 
SubsunCommented:
Ok.. Surname is not included in the output of Search-ADAccount. You can try the following code to get GivenName,Surname etc..
Search-ADAccount -AccountInactive -TimeSpan 30.00:00:00 -UsersOnly | where {$_.Enabled -eq $true -and $_.Name -notlike "SystemMailbox*"} | Get-ADuser | Select Name,GivenName,Surname,samaccountname,lastlogondate,Enabled | Export-csv C:\temp\test13.csv -nti

Open in new window

0
 
kuzumAuthor Commented:
thanks Subsun,  I am not seing the last logon date on excel? Can you please sort it by  columns ?

thanks
0
 
kuzumAuthor Commented:
sorry for confusing you, I mean no information in the last logon field ?
0
 
SubsunCommented:
I didn't select that property in Get-ADuser command. Also it would be better to export LastLogonTimestamp attribute to get the last logon..
Search-ADAccount -AccountInactive -TimeSpan 30.00:00:00 -UsersOnly | where {$_.Enabled -eq $true -and $_.Name -notlike "SystemMailbox*"} | Get-ADuser -pr LastLogonTimestamp | Select Name,GivenName,Surname,samaccountname,@{N='LastLogon'; E={[DateTime]::FromFileTime($_.LastLogonTimestamp)}},Enabled | Export-csv C:\temp\test13.csv -nti

Open in new window

0
 
kuzumAuthor Commented:
Hi Subsun, I have now last logon date field in with attached data for some accounts. What is that mean? Can we avoid them in the script?
thanks
0
 
kuzumAuthor Commented:
Also, is there any way we can skips any service accounts? most of the service accounts starts with "svc_ "
We can may be say not to include any names or last names or dislay names starts with svc_  in the code?
thanks
0
 
SubsunCommented:
basically there are two attributes lastLogon & lastLogonTimestamp to specify the last log on time of user, lastLogon is more accurate than lastLogonTimestamp but lastLogon is not replicated across the domains. Ideally you need to query all DC's and find the latest time stamp of lastLogon attribute to find the actual last log on time of user.

lastLogonTimestamp is replicable but, we can say that accurate between 9-14 days. You can find many articles to find the difference between these two attributes. Search with keyword 'lastlogontimestamp vs lastlogon'

Search-ADAccount does a very good job in finding the inactive accounts (Above Windows Server 2003 Domain Functional Level), so I would not worry much about the lastlogon attribute..

Ref : http://technet.microsoft.com/en-us/library/ee617247.aspx
0
 
SubsunCommented:
To exclude service account..
Search-ADAccount -AccountInactive -TimeSpan 30.00:00:00 -UsersOnly | where {$_.Enabled -eq $true -and $_.Name -notlike "SystemMailbox*" -and $_.Name -notlike "SVC_*"} | Get-ADuser -pr LastLogonTimestamp | Select Name,GivenName,Surname,samaccountname,@{N='LastLogon'; E={[DateTime]::FromFileTime($_.LastLogonTimestamp)}},Enabled | Export-csv C:\temp\test13.csv -nti

Open in new window

0
 
kuzumAuthor Commented:
I will post another question now please, please assign that one to yourself too- thanks
0
 
SubsunCommented:
Please close this question if you don't have any further queries on this one..
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now