[probleme] Script VBS LDAP + WMI + Excel

Bonjour a tous et a toutes

j'ai fais ce script :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
on error resume next
dim location
Const ADS_SCOPE_SUBTREE = 2
const HKEY_CURRENT_USER = &H80000001
const HKEY_LOCAL_MACHINE = &H80000002
Set oConn = CreateObject("ADODB.Connection")
Set oCommand =   CreateObject("ADODB.Command")
oConn.Provider = "ADsDSOObject"
oConn.Open "Active Directory Provider"
 
Set oCommand.ActiveConnection = oConn
oCommand.CommandText = _
    "Select Name, Location from 'LDAP://ou=xxx,ou=xxx,dc=xxx,dc=xxx' " _
        & "Where objectClass='computer'"  
oCommand.Properties("Page Size") = 1000
oCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
Set oRS = oCommand.Execute
oRS.MoveFirst
 
dim computers()
dim ub:ub = 0
Do Until oRS.EOF
            redim preserve computers(ub)
    computers(ub) = oRS.Fields("Name").Value
    oRS.MoveNext
    ub = UBound(computers)+1
Loop
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
x = 2
objExcel.Cells(1, 1).Value = "Machine :" '3,2
objExcel.Cells(1, 2).Value = "Model :" '6.2
objExcel.Cells(1, 3).Value = "Fabricant :" '9.2
objExcel.Cells(1, 4).Value = "Utilisateur :" 
objExcel.Cells(1, 5).Value = "Mémoire :"
objExcel.Cells(1, 6).Value = "IP :"
for each strComputer in computers
    Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}")._
    ExecQuery("select * from Win32_PingStatus where address = '"_
    & strComputer & "'")
		For Each objStatus in objPing
            Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
				strKeyPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon"
				strValueName = "DefaultUserName"
				oReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue
				objExcel.Cells(x, 4).Value = strValue
			Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
			Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
			For Each objItem in colItems
				objWorksheet.Cells(x, 1) = ObjItem.Name
				objWorksheet.Cells(x ,2) = objItem.Model
				objWorksheet.Cells(x ,5).Value = FormatNumber( objItem.TotalPhysicalMemory / 1048576,1,false,true,true) & " Mo"
			Next
			Set ColItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystemProduct")
			For Each objItem in colItems
				objWorksheet.Cells(x ,3).Value = objItem.Vendor
			Next
			Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration", "WQL", _
                                          wbemFlagReturnImmediately + wbemFlagForwardOnly)
			i = 0
			For Each objItem In colItems
				If objItem.IPEnabled Then
					i = i + 1
					If i = 1 Then
						WMIGetIP = Join(objItem.IPAddress, ",")
					Else ' i>1
						WMIGetIP = WMIGetIP & ", " & Join(objItem.IPAddress, ",")
					End If
				End If
			objWorksheet.Cells(x, 6).Value = WMIGetIP
			Next
        Next
		x = x+1        
Next
		msgbox "Traitement Terminer " & i  & " Machines importer"
	Set objRange = objWorksheet.UsedRange
	objRange.EntireColumn.Autofit()

Il fonctionne bien mais je ne comprends pas pourquoi il me mets plusieur fois les meme machines dans le fichiers Excel ...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
UC0398	POWERMATE VL6	NEC Computers International	mboulet	1*015,4 Mo	128.24.6.27
UC0398	POWERMATE VL6	NEC Computers International	mboulet	1*015,4 Mo	128.24.6.27
UC0398	POWERMATE VL6	NEC Computers International	mboulet	1*015,4 Mo	128.24.6.27
UC0398	POWERMATE VL6	NEC Computers International	mboulet	1*015,4 Mo	128.24.6.27
UC0398	POWERMATE VL6	NEC Computers International	mboulet	1*015,4 Mo	128.24.6.27
UC0398	POWERMATE VL6	NEC Computers International	mboulet	1*015,4 Mo	128.24.6.27
UC0398	POWERMATE VL6	NEC Computers International	mboulet	1*015,4 Mo	128.24.6.27
UC0398	POWERMATE VL6	NEC Computers International	mboulet	1*015,4 Mo	128.24.6.27
PO0358	9459WS4	LENOVO	clesueur	2*038,4 Mo	128.24.6.21, 0.0.0.0, 0.0.0.0
PO0358	9459WS4	LENOVO	clesueur	2*038,4 Mo	128.24.6.21, 0.0.0.0, 0.0.0.0
PO0358	9459WS4	LENOVO	clesueur	2*038,4 Mo	128.24.6.21, 0.0.0.0, 0.0.0.0
PO0358	9459WS4	LENOVO	clesueur	2*038,4 Mo	128.24.6.21, 0.0.0.0, 0.0.0.0
PO0358	9459WS4	LENOVO	clesueur	2*038,4 Mo	128.24.6.21, 0.0.0.0, 0.0.0.0
PO0358	9459WS4	LENOVO	clesueur	2*038,4 Mo	128.24.6.21, 0.0.0.0, 0.0.0.0
PO0358	9459WS4	LENOVO	clesueur	2*038,4 Mo	128.24.6.21, 0.0.0.0, 0.0.0.0
PO0358	9459WS4	LENOVO	clesueur	2*038,4 Mo	128.24.6.21, 0.0.0.0, 0.0.0.0
UC0441	POWERMATE VL260	NEC COMPUTERS SAS	ndaens	503,3 Mo	128.24.6.10
UC0441	POWERMATE VL260	NEC COMPUTERS SAS	ndaens	503,3 Mo	128.24.6.10
UC0441	POWERMATE VL260	NEC COMPUTERS SAS	ndaens	503,3 Mo	128.24.6.10
UC0441	POWERMATE VL260	NEC COMPUTERS SAS	ndaens	503,3 Mo	128.24.6.10
UC0441	POWERMATE VL260	NEC COMPUTERS SAS	ndaens	503,3 Mo	128.24.6.10
UC0441	POWERMATE VL260	NEC COMPUTERS SAS	ndaens	503,3 Mo	128.24.6.10
UC0441	POWERMATE VL260	NEC COMPUTERS SAS	ndaens	503,3 Mo	128.24.6.10

Pouvez vous m'aidez a diagnostiqué le probleme?
Je pense que ca vient d'une boucle foireuse mais ...

Le but etant de lister tout les pc de l'ad, de faire les requete WMI pour récupéré les infos dont j'ai besoin puis de les mettre dans Excel, je suis prenneur de toute les améliorations...

Etienne