Cannout grab the comouter name from this code to output

[hannable80]

Can't get the computer name to output from this script. I will only bring the domain controler can anyone see my issue.
OPEN EXCEL

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"


' Format the cell A1 and add the text: Service

objExcel.Cells(1, 1).Value = "Service"

objExcel.Cells(1, 1).Font.Bold = TRUE

objExcel.Cells(1, 1).Interior.ColorIndex = 43

objExcel.Cells(1, 1).Font.ColorIndex = 2



' Format the cell A1 and add the text: Status

objExcel.Cells(1, 2).Value = "Status"

objExcel.Cells(1, 2).Font.Bold = TRUE

objExcel.Cells(1, 2).Interior.ColorIndex = 50

objExcel.Cells(1, 2).Font.ColorIndex = 2



' Format the cell A1 and add the text: Status

objExcel.Cells(1, 3).Value = "Status"

objExcel.Cells(1, 3).Font.Bold = TRUE

objExcel.Cells(1, 3).Interior.ColorIndex = 49

objExcel.Cells(1, 3).Font.ColorIndex = 2

Set objCOmmand.ActiveConnection = objConnection
objCommand.CommandText = _
"Select Name, Location from 'LDAP://DC=XX, dc=XXX, DC=XXX' " _
& "Where objectClass='computer'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
Set objRecordSet = objCommand.Execute

' ` services on this computer

strComputer = objRecordSet.Fields("Name").Value

Set objWMIService = GetObject _
("winmgmts:\\" & strComputer & "\root\cimv2")

Set colServices = objWMIService.ExecQuery _
("Select * From Win32_Service")



' Write each service to Excel, starting in A2
Do Until objRecordSet.EOF
x = 1
For Each objService in colServices

x = x + 1

objExcel.Cells(x, 1) = objService.Name

objExcel.Cells(x, 2) = objService.State

objExcel.Cells(x, 3) = strComputer

if objService.State = "Stopped" then

objExcel.Cells(x, 2).Font.ColorIndex = 3

end if

Next



' Autofit

objExcel.Columns("A:A").EntireColumn.AutoFit
loop

 

[markdmac]

Are you looking to enumerate all the computers in the domain or just trying to get the current computer name?

Please be more specific with your question.

http://support.microsoft.com/kb/555375

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[PHV]

Have a look here:

http://msdn.microsoft.com/en-us/library/wck0hkd7(VS.85).aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hannable80]

Got it working. Problem was with the out put of the computer name and the for loop. Can some one show me how to drill down into AD and select a specific OU. Also i would love to be able to save the excel files to my C drive......and finally i would like to disable any VNC service and disable it on start up... Any Ideas guys



' OPEN EXCEL

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"


' Format the cell A1 and add the text: Service


Set objCOmmand.ActiveConnection = objConnection
objCommand.CommandText = _
"Select Name, Location from 'LDAP:// DC=xx, dc=xx, DC=xxx' " _
& "Where objectClass='computer'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
Set objRecordSet = objCommand.Execute

' ` services on this computer

strComputer = objRecordSet.Fields("Name").Value

Set objWMIService = GetObject _
("winmgmts:\\" & strComputer & "\root\cimv2")

Set colServices = objWMIService.ExecQuery _
("Select * From Win32_Service")

objExcel.Workbooks.Add


objExcel.Cells(1, 1).Value = "Service"

objExcel.Cells(1, 1).Font.Bold = TRUE

objExcel.Cells(1, 1).Interior.ColorIndex = 43

objExcel.Cells(1, 1).Font.ColorIndex = 2



' Format the cell A1 and add the text: Status

objExcel.Cells(1, 2).Value = "Status"

objExcel.Cells(1, 2).Font.Bold = TRUE

objExcel.Cells(1, 2).Interior.ColorIndex = 50

objExcel.Cells(1, 2).Font.ColorIndex = 2



' Format the cell A1 and add the text: Status

objExcel.Cells(1, 3).Value = "Computer"

objExcel.Cells(1, 3).Font.Bold = TRUE

objExcel.Cells(1, 3).Interior.ColorIndex = 49

objExcel.Cells(1, 3).Font.ColorIndex = 2



' Write each service to Excel, starting in A2
Do Until objRecordSet.EOF
x = 1
For Each objService in colServices

x = x + 1


objExcel.Cells(x, 1) = objService.Name

objExcel.Cells(x, 2) = objService.State

objExcel.Cells(x, 3) = objRecordSet.Fields("Name").Value


if objService.State = "Stopped" then

objExcel.Cells(x, 2).Font.ColorIndex = 3



end if

Next


objRecordSet.MoveNext
objExcel.Workbooks.Add


' Autofit

objExcel.Columns("A:A").EntireColumn.AutoFit

loop

 

[hannable80]

never mind the last post got it working....Would now like to know is if there is any redundancy if a PC is offline?

 

[markdmac]

YOu can bind to an OU like this:

objCommand.CommandText = _
    "Select Name, Location from 'LDAP:// OU=myOU, DC=xx, dc=xx, DC=xxx' " _
        & "Where objectClass='computer'"

If a PC is offline you are best off recording it in a text document and then using that document to retry the connection at a later time.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.