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
kuzumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
How do you know if your security is working?

Protecting your business doesn’t have to mean sifting through endless alerts and notifications. With WatchGuard Total Security Suite, you can feel confident that your business is secure, meaning you can get back to the things that have been sitting on your to-do list.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.