Problem with DGSET GROUP report

I made a simple batch file to get a list of machines belonging to
certain groups, save it to a file then open it in Excel.

See below:

dsget group "cn=AdobeAcrobat9,ou=*,ou=Groups,ou=*,DC=*,DC=*" -members -
expand | sort > "c:\documents and settings\%username%\desktop
\spreadsheetname.xls
start excel.exe "c:\documents and settings\%username%\desktop
\spreadsheetname.xls


It works but the output is ugly with it displaying a bunch of info
about the path that to the OU that I don't need to look at, and, at
the same time, there is a bit more info I want that I can't get it to
display.

It has the following problems.

1. Excel fails to seperate the OU info into seperate cells despite
the info being seperated by commas. (I'd like to solve this comma
seperation or just not show this info).

2. I need to get the "description" field of the computers, but when I
try to add "desc" to dsget it gives the description field of the
AdobeAcrobat9 group instead the description field of each of the
members of the group.

3. I really don't need it to display all the OU info. All I'd like
it do is save a list of the machine names that belong to the
AdobeAcrobat9 group in a format Excel can display elegantly and in
alphabetical order. The description field of each of the member
computers should be displayed in the same row as the computer name.

How can this be done?
Can it be done with a simple bat file or does it have to be a vbs
script?

Kuma
Wed Jul 16 23:26:41 CDT 2008

This can be done extremely easily in powershell with Quest's Active
Directory cmdlets.
#Get the group, access the member collection
(Get-QADGroup -SearchRoot "ou=*,ou=Groups,ou=*,DC=*,DC=*" -Name
"AdobeAcrobat9").Member `
#Foreach member get the corresponding ad entry
| Foreach-object {Get-QADComputer $_} `
#select only name and description
| Select name,description `
export results to the csv file
| Export-CSV -Path "c:\documents and settings\$env:username\desktop
\spreadsheetname.csv" -NoTypeInformation

#Open the file in excel
Invoke-Item "c:\documents and settings\$env:username\desktop
\spreadsheetname.csv"

Thats by far the easiest way i can think of to do it. Hope its helpful.

rosevilleca
Thu Jul 17 00:53:10 CDT 2008

On Jul 16, 9:26=A0pm, Kuma <kumasa...@hotmail.com> wrote:
> This can be done extremely easily in powershell with Quest's Active
> Directory cmdlets.
> #Get thegroup, access the member collection
> (Get-QADGroup -SearchRoot "ou=3D*,ou=3DGroups,ou=3D*,DC=3D*,DC=3D*" -Name
> "AdobeAcrobat9").Member `
> #Foreach member get the corresponding ad entry
> | Foreach-object {Get-QADComputer $_} `
> #select only name and description
> | Select name,description `
> export results to the csv file
> | Export-CSV -Path "c:\documents and settings\$env:username\desktop
> \spreadsheetname.csv" -NoTypeInformation
>
> #Open the file in excel
> Invoke-Item "c:\documents and settings\$env:username\desktop
> \spreadsheetname.csv"
>
> Thats by far the easiest way i can think of to do it. Hope its helpful.

I've never seen Quest cmdlets before and all the domain controllers
are Server 2003. There is no Server 2008 or powershell available.
So, this new Quest software would need to be installed on on
everyone's machine that needs to use that method to create the report?

Richard
Thu Jul 17 10:14:08 CDT 2008


<rosevilleca@gmail.com> wrote in message
news:8bf239dc-d474-4fb2-885c-f06f589602b8@i76g2000hsf.googlegroups.com...
>I made a simple batch file to get a list of machines belonging to
> certain groups, save it to a file then open it in Excel.
>
> See below:
>
> dsget group "cn=AdobeAcrobat9,ou=*,ou=Groups,ou=*,DC=*,DC=*" -members -
> expand | sort > "c:\documents and settings\%username%\desktop
> \spreadsheetname.xls
> start excel.exe "c:\documents and settings\%username%\desktop
> \spreadsheetname.xls
>
>
> It works but the output is ugly with it displaying a bunch of info
> about the path that to the OU that I don't need to look at, and, at
> the same time, there is a bit more info I want that I can't get it to
> display.
>
> It has the following problems.
>
> 1. Excel fails to seperate the OU info into seperate cells despite
> the info being seperated by commas. (I'd like to solve this comma
> seperation or just not show this info).
>
> 2. I need to get the "description" field of the computers, but when I
> try to add "desc" to dsget it gives the description field of the
> AdobeAcrobat9 group instead the description field of each of the
> members of the group.
>
> 3. I really don't need it to display all the OU info. All I'd like
> it do is save a list of the machine names that belong to the
> AdobeAcrobat9 group in a format Excel can display elegantly and in
> alphabetical order. The description field of each of the member
> computers should be displayed in the same row as the computer name.
>
> How can this be done?
> Can it be done with a simple bat file or does it have to be a vbs
> script?

It may be possible to do this with dsquery and dsget, but I tried for some
time and failed. Some of my attempts failed if any members of the group were
users (objects of class other than computer). A bigger problem might be that
these commands are only available on W2k3 DC's (and above). I would use a
VBScript program similar to:
==========
Option Explicit
Dim objGroup, objMember, strName
Dim strExcelPath, objExcel, objSheet, intRow

' Spreadsheet file to be created.
strExcelPath = "c:\scripts\Computers.xls"

' Bind to Excel object.
Set objExcel = CreateObject("Excel.Application")

' Create a new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Bind to the group object.
Set objGroup =
GetObject("LDAP://cn=AdobeAcrobat9,ou=*,ou=Groups,ou=*,DC=*,DC=*")

' Enumerate direct members.
intRow = 1
For Each objMember In objGroup.Members
If (objMember.Class = "computer") Then
' Retrieve NetBIOS name of computer.
strName = objMember.sAMAccountName
' Strip off trailing "$"
strName = Left(strName, Len(strName) - 1)
objSheet.Cells(intRow, 1).Value = strName
objSheet.Cells(intRow, 2).Value = objMember.description
intRow = intRow + 1
End If
Next

objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close
========

Richard
Thu Jul 17 10:42:25 CDT 2008

First, you may be able to use Joe Richards' free adfind utility for this:

http://www.joeware.net/freetools/tools/adfind/index.htm

This still has the drawback that people that use it need the tool, but at
least it's one exe. PowerShell requires .NET framework, PowerShell, and the
cmdlets.

Next, I just noticed you used -expand to reveal nested group members. In
VBScript we can do the same thing with a recursive subroutine:
=======
Option Explicit
Dim objGroup
Dim strExcelPath, objExcel, objSheet, intRow

' Spreadsheet file to be created.
strExcelPath = "c:\scripts\Computers.xls"

' Bind to Excel object.
Set objExcel = CreateObject("Excel.Application")

' Create a new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Bind to the group object.
Set objGroup =
GetObject("LDAP://cn=AdobeAcrobat9,ou=*,ou=Groups,ou=*,DC=*,DC=*")

' Enumerate members.
intRow = 1
Call GetMembers(objGroup)

objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

Sub GetMembers(objParent)
' Recursive subroutine to enumerate nested group membership.
' objSheet and intRow must have global scope.
Dim objParent, strName

For Each objMember In objParent.Members
If (objMember.Class = "computer") Then
' Retrieve NetBIOS name of computer.
strName = objMember.sAMAccountName
' Strip off trailing "$"
strName = Left(strName, Len(strName) - 1)
objSheet.Cells(intRow, 1).Value = strName
objSheet.Cells(intRow, 2).Value = objMember.description
intRow = intRow + 1
End If
If (objMember.Class = "group") Then
Call GetMembers(objMember)
End If
Next
End Sub

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

rosevilleca
Thu Jul 17 12:23:19 CDT 2008

On Jul 17, 8:14=A0am, "Richard Mueller [MVP]" <rlmueller-
nos...@ameritech.nospam.net> wrote:
> <rosevill...@gmail.com> wrote in message
>
> news:8bf239dc-d474-4fb2-885c-f06f589602b8@i76g2000hsf.googlegroups.com...
>
>
>
>
>
> >I made a simple batch file to get a list of machines belonging to
> > certain groups, save it to a file then open it in Excel.
>
> > See below:
>
> >dsgetgroup"cn=3DAdobeAcrobat9,ou=3D*,ou=3DGroups,ou=3D*,DC=3D*,DC=3D*" -=
members -
> > expand | sort > "c:\documents and settings\%username%\desktop
> > \spreadsheetname.xls
> > start excel.exe "c:\documents and settings\%username%\desktop
> > \spreadsheetname.xls
>
> > It works but the output is ugly with it displaying a bunch of info
> > about the path that to the OU that I don't need to look at, and, at
> > the same time, there is a bit more info I want that I can't get it to
> > display.
>
> > It has the followingproblems.
>
> > 1. =A0Excel fails to seperate the OU info into seperate cells despite
> > the info being seperated by commas. (I'd like to solve this comma
> > seperation or just not show this info).
>
> > 2. =A0I need to get the "description" field of the computers, but when =
I
> > try to add "desc" todsgetit gives the description field of the
> > AdobeAcrobat9groupinstead the description field of each of the
> > members of thegroup.
>
> > 3. =A0I really don't need it to display all the OU info. =A0All I'd lik=
e
> > it do is save a list of the machine names that belong to the
> > AdobeAcrobat9groupin a format Excel can display elegantly and in
> > alphabetical order. =A0The description field of each of the member
> > computers should be displayed in the same row as the computer name.
>
> > How can this be done?
> > Can it be done with a simple bat file or does it have to be a vbs
> > script?
>
> It may be possible to do this with dsquery anddsget, but I tried for some
> time and failed. Some of my attempts failed if any members of thegroupwer=
e
> users (objects of class other than computer). A bigger problem might be t=
hat
> these commands are only available on W2k3 DC's (and above). I would use a
> VBScript program similar to:
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Option Explicit
> Dim objGroup, objMember, strName
> Dim strExcelPath, objExcel, objSheet, intRow
>
> ' Spreadsheet file to be created.
> strExcelPath =3D "c:\scripts\Computers.xls"
>
> ' Bind to Excel object.
> Set objExcel =3D CreateObject("Excel.Application")
>
> ' Create a new workbook.
> objExcel.Workbooks.Add
>
> ' Bind to worksheet.
> Set objSheet =3D objExcel.ActiveWorkbook.Worksheets(1)
>
> ' Bind to thegroupobject.
> Set objGroup =3D
> GetObject("LDAP://cn=3DAdobeAcrobat9,ou=3D*,ou=3DGroups,ou=3D*,DC=3D*,DC=
=3D*")
>
> ' Enumerate direct members.
> intRow =3D 1
> For Each objMember In objGroup.Members
> =A0 =A0 If (objMember.Class =3D "computer") Then
> =A0 =A0 =A0 =A0 ' Retrieve NetBIOS name of computer.
> =A0 =A0 =A0 =A0 strName =3D objMember.sAMAccountName
> =A0 =A0 =A0 =A0 ' Strip off trailing "$"
> =A0 =A0 =A0 =A0 strName =3D Left(strName, Len(strName) - 1)
> =A0 =A0 =A0 =A0 objSheet.Cells(intRow, 1).Value =3D strName
> =A0 =A0 =A0 =A0 objSheet.Cells(intRow, 2).Value =3D objMember.description
> =A0 =A0 =A0 =A0 intRow =3D intRow + 1
> =A0 =A0 End If
> Next
>
> objExcel.ActiveWorkbook.SaveAs strExcelPath
> objExcel.ActiveWorkbook.Close
> =3D=3D=3D=3D=3D=3D=3D=3D- Hide quoted text -
>
> - Show quoted text -

I saved it as a vbs file, tried it and it fails with a message:
"Variable is undefined: 'objMember'

rosevilleca
Thu Jul 17 12:58:03 CDT 2008

On Jul 17, 10:23=A0am, rosevill...@gmail.com wrote:
> On Jul 17, 8:14=A0am, "Richard Mueller [MVP]" <rlmueller-
>
>
>
>
>
> nos...@ameritech.nospam.net> wrote:
> > <rosevill...@gmail.com> wrote in message
>
> >news:8bf239dc-d474-4fb2-885c-f06f589602b8@i76g2000hsf.googlegroups.com..=
.
>
> > >I made a simple batch file to get a list of machines belonging to
> > > certain groups, save it to a file then open it in Excel.
>
> > > See below:
>
> > >dsgetgroup"cn=3DAdobeAcrobat9,ou=3D*,ou=3DGroups,ou=3D*,DC=3D*,DC=3D*"=
-members -
> > > expand | sort > "c:\documents and settings\%username%\desktop
> > > \spreadsheetname.xls
> > > start excel.exe "c:\documents and settings\%username%\desktop
> > > \spreadsheetname.xls
>
> > > It works but the output is ugly with it displaying a bunch of info
> > > about the path that to the OU that I don't need to look at, and, at
> > > the same time, there is a bit more info I want that I can't get it to
> > > display.
>
> > > It has the followingproblems.
>
> > > 1. =A0Excel fails to seperate the OU info into seperate cells despite
> > > the info being seperated by commas. (I'd like to solve this comma
> > > seperation or just not show this info).
>
> > > 2. =A0I need to get the "description" field of the computers, but whe=
n I
> > > try to add "desc" todsgetit gives the description field of the
> > > AdobeAcrobat9groupinstead the description field of each of the
> > > members of thegroup.
>
> > > 3. =A0I really don't need it to display all the OU info. =A0All I'd l=
ike
> > > it do is save a list of the machine names that belong to the
> > > AdobeAcrobat9groupin a format Excel can display elegantly and in
> > > alphabetical order. =A0The description field of each of the member
> > > computers should be displayed in the same row as the computer name.
>
> > > How can this be done?
> > > Can it be done with a simple bat file or does it have to be a vbs
> > > script?
>
> > It may be possible to do this with dsquery anddsget, but I tried for so=
me
> > time and failed. Some of my attempts failed if any members of thegroupw=
ere
> > users (objects of class other than computer). A bigger problem might be=
that
> > these commands are only available on W2k3 DC's (and above). I would use=
a
> > VBScript program similar to:
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > Option Explicit
> > Dim objGroup, objMember, strName
> > Dim strExcelPath, objExcel, objSheet, intRow
>
> > ' Spreadsheet file to be created.
> > strExcelPath =3D "c:\scripts\Computers.xls"
>
> > ' Bind to Excel object.
> > Set objExcel =3D CreateObject("Excel.Application")
>
> > ' Create a new workbook.
> > objExcel.Workbooks.Add
>
> > ' Bind to worksheet.
> > Set objSheet =3D objExcel.ActiveWorkbook.Worksheets(1)
>
> > ' Bind to thegroupobject.
> > Set objGroup =3D
> > GetObject("LDAP://cn=3DAdobeAcrobat9,ou=3D*,ou=3DGroups,ou=3D*,DC=3D*,D=
C=3D*")
>
> > ' Enumerate direct members.
> > intRow =3D 1
> > For Each objMember In objGroup.Members
> > =A0 =A0 If (objMember.Class =3D "computer") Then
> > =A0 =A0 =A0 =A0 ' Retrieve NetBIOS name of computer.
> > =A0 =A0 =A0 =A0 strName =3D objMember.sAMAccountName
> > =A0 =A0 =A0 =A0 ' Strip off trailing "$"
> > =A0 =A0 =A0 =A0 strName =3D Left(strName, Len(strName) - 1)
> > =A0 =A0 =A0 =A0 objSheet.Cells(intRow, 1).Value =3D strName
> > =A0 =A0 =A0 =A0 objSheet.Cells(intRow, 2).Value =3D objMember.descripti=
on
> > =A0 =A0 =A0 =A0 intRow =3D intRow + 1
> > =A0 =A0 End If
> > Next
>
> > objExcel.ActiveWorkbook.SaveAs strExcelPath
> > objExcel.ActiveWorkbook.Close
> > =3D=3D=3D=3D=3D=3D=3D=3D- Hide quoted text -
>
> > - Show quoted text -
>
> I saved it as a vbs file, tried it and it fails with a message:
> "Variable is undefined: 'objMember'- Hide quoted text -
>
> - Show quoted text -

Nevermind, I figured out how to declare the variables and it works.
It runs successfully and saves the info to a file.
However, it doesn't work using the %username% path to the desktop or
automatically launch the file my original batch file though.
How can I have launch automatically so the user doesn't have to go
hunt for the file and click on it?

Kuma
Sat Jul 19 07:36:03 CDT 2008

On Jul 18, 2:58=A0am, rosevill...@gmail.com wrote:
> On Jul 17, 10:23=A0am, rosevill...@gmail.com wrote:
>
>
>
>
>
> > On Jul 17, 8:14=A0am, "Richard Mueller [MVP]" <rlmueller-
>
> > nos...@ameritech.nospam.net> wrote:
> > > <rosevill...@gmail.com> wrote in message
>
> > >news:8bf239dc-d474-4fb2-885c-f06f589602b8@i76g2000hsf.googlegroups.com=
...
>
> > > >I made a simple batch file to get a list of machines belonging to
> > > > certain groups, save it to a file then open it in Excel.
>
> > > > See below:
>
> > > >dsgetgroup"cn=3DAdobeAcrobat9,ou=3D*,ou=3DGroups,ou=3D*,DC=3D*,DC=3D=
*" -members -
> > > > expand | sort > "c:\documents and settings\%username%\desktop
> > > > \spreadsheetname.xls
> > > > start excel.exe "c:\documents and settings\%username%\desktop
> > > > \spreadsheetname.xls
>
> > > > It works but the output is ugly with it displaying a bunch of info
> > > > about the path that to the OU that I don't need to look at, and, at
> > > > the same time, there is a bit more info I want that I can't get it =
to
> > > > display.
>
> > > > It has the followingproblems.
>
> > > > 1. =A0Excel fails to seperate the OU info into seperate cells despi=
te
> > > > the info being seperated by commas. (I'd like to solve this comma
> > > > seperation or just not show this info).
>
> > > > 2. =A0I need to get the "description" field of the computers, but w=
hen I
> > > > try to add "desc" todsgetit gives the description field of the
> > > > AdobeAcrobat9groupinstead the description field of each of the
> > > > members of thegroup.
>
> > > > 3. =A0I really don't need it to display all the OU info. =A0All I'd=
like
> > > > it do is save a list of the machine names that belong to the
> > > > AdobeAcrobat9groupin a format Excel can display elegantly and in
> > > > alphabetical order. =A0The description field of each of the member
> > > > computers should be displayed in the same row as the computer name.
>
> > > > How can this be done?
> > > > Can it be done with a simple bat file or does it have to be a vbs
> > > > script?
>
> > > It may be possible to do this with dsquery anddsget, but I tried for =
some
> > > time and failed. Some of my attempts failed if any members of thegrou=
pwere
> > > users (objects of class other than computer). A bigger problem might =
be that
> > > these commands are only available on W2k3 DC's (and above). I would u=
se a
> > > VBScript program similar to:
> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > > Option Explicit
> > > Dim objGroup, objMember, strName
> > > Dim strExcelPath, objExcel, objSheet, intRow
>
> > > ' Spreadsheet file to be created.
> > > strExcelPath =3D "c:\scripts\Computers.xls"
>
> > > ' Bind to Excel object.
> > > Set objExcel =3D CreateObject("Excel.Application")
>
> > > ' Create a new workbook.
> > > objExcel.Workbooks.Add
>
> > > ' Bind to worksheet.
> > > Set objSheet =3D objExcel.ActiveWorkbook.Worksheets(1)
>
> > > ' Bind to thegroupobject.
> > > Set objGroup =3D
> > > GetObject("LDAP://cn=3DAdobeAcrobat9,ou=3D*,ou=3DGroups,ou=3D*,DC=3D*=
,DC=3D*")
>
> > > ' Enumerate direct members.
> > > intRow =3D 1
> > > For Each objMember In objGroup.Members
> > > =A0 =A0 If (objMember.Class =3D "computer") Then
> > > =A0 =A0 =A0 =A0 ' Retrieve NetBIOS name of computer.
> > > =A0 =A0 =A0 =A0 strName =3D objMember.sAMAccountName
> > > =A0 =A0 =A0 =A0 ' Strip off trailing "$"
> > > =A0 =A0 =A0 =A0 strName =3D Left(strName, Len(strName) - 1)
> > > =A0 =A0 =A0 =A0 objSheet.Cells(intRow, 1).Value =3D strName
> > > =A0 =A0 =A0 =A0 objSheet.Cells(intRow, 2).Value =3D objMember.descrip=
tion
> > > =A0 =A0 =A0 =A0 intRow =3D intRow + 1
> > > =A0 =A0 End If
> > > Next
>
> > > objExcel.ActiveWorkbook.SaveAs strExcelPath
> > > objExcel.ActiveWorkbook.Close
> > > =3D=3D=3D=3D=3D=3D=3D=3D- Hide quoted text -
>
> > > - Show quoted text -
>
> > I saved it as a vbs file, tried it and it fails with a message:
> > "Variable is undefined: 'objMember'- Hide quoted text -
>
> > - Show quoted text -
>
> Nevermind, I figured out how to declare the variables and it works.
> It runs successfully and saves the info to a file.
> However, it doesn't work using the %username% path to the desktop or
> automatically launch the file my original batch file though.
> How can I have launch automatically so the user doesn't have to go
> hunt for the file and click on it?- Hide quoted text -
>
> - Show quoted text -

Powershell can run on 2k3 XP 2k8 and Vista, it just doesn't come
installed by default. The quest active directory cmdlets are free to
download and can make working with AD much simpler. Each computer that
you would want to make the report on would need to have it installed
though.