DStools and powershell

Hi Guys,

We have server 2003 r2 and therefore do no have the powershell module for AD.

On powershell i typed in

$comp=dsquery computer -name *

it give me the full OU path e.g "CN=PC249083166313,OU=WA Computers,OU=Wales (WA),OU=Professional Services (PS),OU=Departmental Accounts,OU=CI,DC=ci,

however I only need the computer name in the variable $comp,

I then need to run an query and find out who is logged onto these machines and put it in an excel spreadsheet. How can I do this?

Thank in advance for the help.

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.

So I recently wrote and tested something that does this:

Give below a test, if you need tweaking there are some other really good guys here who can collaborate to get you where you need to be.

I am exporting to csv you should have no problem simply opening the csv file in excel.

$output = ""
$output +=
Server, UserName, LogonTime
Import-Module activedirectory
$servers = (Get-ADComputer  -filter {name -like "*"} | select -expand name )
            ForEach($server in $servers) {
                $LogonSessions = Get-WmiObject Win32_LogonSession -ComputerName $server     
                ForEach($LogonSession in $LogonSessions) {
                    $LoggedOnUser = Get-WmiObject -Query "Associators of {Win32_LogonSession.LogonId=$($LogonSession.LogonId)} Where AssocClass=Win32_LoggedOnUser Role=Dependent" -ComputerName $server | select-Object Domain,Name,SID,StartTime,LogonID,LogonType,LogonTypeName,ComputerName
foreach ($user in $LoggedOnUser)
if ($LoggedOnUser.Name -ne "" -and $LoggedOnUser.Name -ne $null)
                    $LoggedOnUser.StartTime = [Management.ManagementDateTimeConverter]::ToDateTime($LogonSession.starttime)
                    $LoggedOnUser.LogonID = $LogonSession.LogonID
                    $LoggedOnUser.LogonType = $LogonSession.logontype
                    $LoggedOnUser.ComputerName = $server
                    $since = $LoggedOnUser.StartTime
					$user = $LoggedOnUser.Name
$output +=
$server, $user, $since
				 } #End foreach user
                } # end  ForEach $LogonSession               
            }  # end ForEach $Computer
  $output | Export-Csv -NoType file.csv

Open in new window

chglAuthor Commented:
^ Hi we don't have powershell for AD, and I don't want it right now.

I need to know how to get the computer name from "dsquery computer -name *" into a variable.
If you are looking for Current Logged On User, then try..
$ds = New-Object System.DirectoryServices.DirectorySearcher
$ds.searchRoot = New-Object ADSI("GC://dc=Domain,dc=com")
$ds.Filter = "(objectCategory=Computer)"
$de = $ds.Findall()
$de | % {
$server = $_.Properties.name
 $User = (Get-WmiObject Win32_ComputerSystem -ComputerName $server -EA Stop).UserName
 "" | Select @{N="Computer";E={$server}},@{N="CurrentUser";E={$User}}
 $_.Exception.Message | Select @{N="Computer";E={$server}},@{N="CurrentUser";E={$_}}
} | Select Computer,CurrentUser | Export-Csv C:\report.csv -NTI

Open in new window

Replace dc=Domain,dc=com with your domain information..
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If you have your result as you need it, directly exporting to Excel (vs. having to use a CSV file) might get handy:
function export-xls ([String] $xlsFile)
begin {
	<# Excel initialization stuff #>
	$excel = New-Object -ComObject excel.application
	$wb = $excel.Workbooks.Add()            # empty, unnamed workbook

	#   Delete all but one work sheet
	$excel.DisplayAlerts = $false
	for ($i = $wb.Worksheets.Count; $i -ge 2; --$i) {$wb.Worksheets.Item($i).Delete()}
	$excel.DisplayAlerts = $true
	$ws = $wb.Worksheets.Item(1)
	$props = $null
	$row = 1
process {
	if (!$props) {
	  $col = 1
		$props = $_ | gm -MemberType NoteProperty
		$props | % {
			$ws.Cells.Item($row, $col).Value2 = $_.Name
			$ws.Cells.Item($row, $col++).Font.Bold = $true
	$col = 1
	foreach ($prop in $props) {
		$ws.Cells.Item($row, $col++).Value2 = $_.($prop.Name)
end {
	$ws.usedRange.EntireColumn.AutoFit() | Out-Null

<# generate objects to export to XLS here #> | export-xls "C:\temp\ee\tst.xls"

Open in new window

chglAuthor Commented:
Thank you Subsun and Qlemo - very useful.

However I am trying to learn how to split the data in an array, this is also my intent for asking.
However I am trying to learn how to split the data in an array
Sorry, I am not clear about the question. Which data you want to split?  Can you please explain?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That's something different, because parsing string results in PowerShell is still cumbersome, and if you have alternatives (that is, can use .NET methods or cmdlets) that should be preferred.

Whatsoever, there are several ways to get the computer name only:
(dsquery computer) -replace '"CN=([^,]+),.*', '$1'
dsquery computer | % { $_.split('"=,')[2] }

Open in new window

and certainly some more.
chglAuthor Commented:
Hi Qlemo,

Thank you very much, very useful and it works!

However please can you send me a link that explains what youve written because I dont understand the [^ .... [2]

Many thanks again.
chglAuthor Commented:
what does the '$1' and the [2] mean?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You need to look at Regular Expressions for the first example.

The second is easier - split the string at any double quote, equal sign or comma, and only take the third (!) part. So
will be split into (each split position represented by a pipe symbol - and yes, the separator is removed):
with [0] being empty, [1] = cn, [2] = pc1, etc.

Back to regex:
"CN=    search for "CN= (not keeping the result)
([^,]+)   any character with exception of a comma (and keep that string, since in parens)
,.*        comma and any characters (zero to unrestricted amount).

The parens tell to keep the match. The match is available as $1, $2 aso., as many parens there are.
Details are available at e.g. http://msdn.microsoft.com/en-us/library/hs600312.aspx, but be aware regex can get very complex to read.

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
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
Microsoft Office

From novice to tech pro — start learning today.