[Combat three]script export computer information into ACCDB

‘**********************************************************
' * Windows Script Source Code *
' *used for change Tel number this shown in GAL based site *
' *hm Senior it:eric Fu[email protected]*
' *date:29 NOV 2013 *
' *any Site can change infor depend on your request *
‘**********************************************************
On Error Resume Next

Const ADS_NAME_INITTYPE_GC = 3

Const ADS_NAME_TYPE_NT4 = 3

Const ads_name_type_1779 = 1

Const Ads_scope_subtree = 2

Dim StrComputer

Set fso1=createobject ("Scripting.FileSystemObject")

If (fso1.folderexists ("C:\Disk2")) = False Then

Set Foldr=fso1.createfolder ("C:\Disk2")

End if

Const THEFILE2 = "C:\Disk2\computerlist.txt"

Set thefile = Fso1. OpenTextFile (thefile2,1)

Do While Thefile.atendofstream <> True

StrComputer = Thefile.readline

' StrComputer = ' wks06225 '

Set objping = GetObject ("Winmgmts:{impersonationlevel=impersonate}"). ExecQuery _
("SELECT * from win32_pingstatus where address = '" & StrComputer & "'")

For each objretstatus in objping

If IsNull (objretstatus.statuscode) or objretstatus.statuscode<>0 Then

strping = "False"

Stripadd = Null

Colusername = Null

Colmodel = Null

COLSN = Null

Collastbootup = "1901/1/11 12:00:00"

Coldisplayn = Null

Coluserlimit = Null

Call WSQLF (StrComputer)

Else

strping = "True"

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

Set Colcomputersystem = objWMIService.ExecQuery (_
"Select Username,model from Win32_ComputerSystem",, 48)

' WScript.Echo Err.Number

If Err.Number = 0 Then

For each objitem in Colcomputersystem

Strcolun = Objitem.username

Colmodel = Objitem.model

Next

Set Colipadd = objWMIService.ExecQuery (_
"SELECT * from Win32_NetworkAdapterConfiguration",, 48)

For each objitem in Colipadd

If IsNull (objitem. IPAddress) Then

' MsgBox ' "&vbcrlf &" No IP enable on this computer "& VbCrLf & VbCrLf &" ",", "**computer error**"

Else

Stripadd = Join (objitem.ipaddress, ",")

End If

Next

Set Colbios = objWMIService.ExecQuery ("Select SerialNumber from Win32_BIOS",, 48)

For each objitem in Colbios

colsn= Objitem.serialnumber

Next

Set Colopersystem = objWMIService.ExecQuery ("Select LastBootUpTime from Win32_OperatingSystem")

For each objitem in Colopersystem

Dtmbootup = Objitem.lastbootuptime

Next

Collastbootup = CDate (Mid (Dtmbootup, 5, 2) & "/" & _
Mid (Dtmbootup, 7, 2) & "/" & Left (Dtmbootup, 4) _
& "" & Mid (Dtmbootup, 9, 2) & ":" & _
Mid (Dtmbootup, one, 2) & ":" & Mid (Dtmbootup, _
13, 2))

Strun = VarType (Strcolun)

If strun = 0 Or strun = 1 Then

Colusername = "Noid"

Coldisplayn = "Noid"

Coluserlimit = "Noid"

Else

Comarray = Split (Strcolun, "\", -1,1)

Colusername = Comarray (1)

Set Objtrans = CreateObject ("Nametranslate")
' Initialize nametranslate by locating the Global Catalog.
Objtrans.init ads_name_inittype_gc, ""

' Use the Set method to specify the NT format of the object name.
Objtrans.set ads_name_type_nt4, "hm\" & Colusername

' Use the Get method to retrieve the RFC 1779 distinguished Name.
strUserDn = Objtrans.get (ads_name_type_1779)

' Escape any '/' characters with backslash Escape character.
' All other characters, that need to be escaped'll be escaped.
strUserDn = Replace (strUserDn, "/", "\ \")

Set objuser = GetObject ("ldap://" & strUserDn)

Coldisplayn = Objuser.get ("DisplayName")

' =======check local admin========

Coluserlimit = "No"

Strgroupname = StrComputer & "Administrators"

Set objconnection = CreateObject ("ADODB. Connection ")

Set objcommand = CreateObject ("Adodb.command")

Objconnection.provider = "Adsdsoobject"

Objconnection.open "Active Directory Provider"

Set objcommand.activeconnection = objconnection

objCommand.Properties ("page Size") = 1000

objCommand.Properties ("searchscope") = Ads_scope_subtree

Objcommand.commandtext = _
"Select ADsPath from ' ldap://ou=hmgroup,dc=hm,dc=com ' WHERE" _
& "objectcategory= ' group ' and sAMAccountName =" & Chr & Strgroupname & Chr (39)

Set objRecordSet = Objcommand.execute

Objrecordset.movefirst

Do Until objrecordset.eof

strpath = Objrecordset.fields ("ADsPath"). Value ' WScript.Echo strpath

Set Objgroup = GetObject (strpath)

Objgroupmem = Objgroup. GetEx ("member")

For each II in OBJGROUPMEM

' WScript.Echo II

Set objuser = Getobject ("ldap://" & II)

Objusername = objuser. Get ("sAMAccountName")

' WScript.Echo Objusername

If LCase (objusername) = LCase (Colusername) Then

Coluserlimit = "Yes"

Exit for

End If

Next

Objrecordset.movenext

Loop

' Determine if the user name is empty
End If

Call Wsql (StrComputer)

' Determine if the error is 0
End If

' WScript.Echo StrComputer & Stripadd & colusername & Coldisplayn & Colmodel & Colsn & Collastbootu P & Coluserlimit & Strping

' Determines whether to ping ACCESS
End If

Next

StrComputer = Null

Stripadd = Null

Colusername = Null

Coldisplayn = Null

Colmodel = Null

COLSN = Null

Collastbootup = Null

Coluserlimit = Null

strping = Null

Loop

Thefile.close

Err.Clear

' =======sql table======
' Computername:strcomputer
' Ipaddress:stripadd
' Username:colusername
' Displayname:coldisplayn
' Computermodel:colmodel
' Computersn:colsn
' Lastbootuptime:collastbootup
' Localadmin:coluserlimit
' Pingstatus:strping

Sub Wsql (strtemp)

Dim Tempvalue

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objconnection = CreateObject ("ADODB. Connection ")
Set objRecordSet = CreateObject ("ADODB. Recordset ")

Objconnection.open _
"Provider = microsoft.ace.oledb.12.0;" & _
"Data Source = C:\inetpub\wwwroot\ComputerList\Computer.accdb"

Objrecordset.open "SELECT * from computerlist where ComputerName = '" & strtemp & "'", _
Objconnection, adOpenStatic, adLockOptimistic

Objrecordset.movefirst

Do Until objrecordset.eof

Tempvalue = ObjRecordset.Fields.Item ("ComputerName")

Objrecordset.movenext
Loop

' Objrecordset.close
' Objconnection.close

Set objconnection = Nothing
Set objRecordSet = Nothing

If Tempvalue = strtemp Then

' WScript.Echo Find It '

Set ObjConnection1 = CreateObject ("ADODB. Connection ")
Set ObjRecordSet1 = CreateObject ("ADODB. Recordset ")

Objconnection1.open _
"Provider = microsoft.ace.oledb.12.0;" & _
"Data Source = C:\inetpub\wwwroot\ComputerList\Computer.accdb"

Objrecordset1.open "UPDATE computerlist Set IPAddress = '" & Stripadd & "', UserName = '" & Colusername & " ', DisplayName = ' "& Coldisplayn &" ', Computermodel = ' "& Colmodel &" ', COMPUTERSN = ' "& Colsn &" ' , LastBootUpTime = ' "& Collastbootup &" ', Localadmin = ' "& Coluserlimit &" ', Pingstatus = ' "& Str Ping & "'" & _
"Where ComputerName = '" & strtemp & "'", _
ObjConnection1, adOpenStatic, adLockOptimistic

' Objrecordset1.close
' Objconnection1.close

Set ObjConnection1 = Nothing
Set ObjRecordSet1 = Nothing

Else

' WScript.Echo ' can ' t find it

Set ObjConnection1 = CreateObject ("ADODB. Connection ")
Set ObjRecordSet1 = CreateObject ("ADODB. Recordset ")

Objconnection1.open _
"Provider = microsoft.ace.oledb.12.0;" & _
"Data Source = C:\inetpub\wwwroot\ComputerList\Computer.accdb"

Objrecordset1.open "INSERT into Computerlist (ComputerName, IPAddress, UserName, DisplayName, Computermodel, COMPUTERSN , LastBootUpTime, Localadmin, Pingstatus) "& _
"VALUES ('" & StrComputer & "', '" & Stripadd & "', '" & Colusername & "', '" & Coldisplayn & ; "', '" & Colmodel & "', '" & Colsn & "', '" & Collastbootup & "', '" & Coluserlimit & "', ' "& strping &" ') ", _
ObjConnection1, adOpenStatic, adLockOptimistic

' Objrecordset1.close
' Objconnection1.close

Set ObjConnection1 = Nothing
Set ObjRecordSet1 = Nothing

End if

Tempvalue = Null

Err.Clear

End Sub

' ========failed action========

Sub WSQLF (strtemp)

Dim Tempvalue

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objconnection = CreateObject ("ADODB. Connection ")
Set objRecordSet = CreateObject ("ADODB. Recordset ")

Objconnection.open _
"Provider = microsoft.ace.oledb.12.0;" & _
"Data Source = C:\inetpub\wwwroot\ComputerList\Computer.accdb"

Objrecordset.open "SELECT * from computerlist where ComputerName = '" & strtemp & "'", _
Objconnection, adOpenStatic, adLockOptimistic

' Objrecordset.movefirst

Do Until objrecordset.eof

Tempvalue = ObjRecordset.Fields.Item ("ComputerName")

Objrecordset.movenext
Loop

' Objrecordset.close
' Objconnection.close

Set objconnection = Nothing
Set objRecordSet = Nothing

If Tempvalue = strtemp Then

' WScript.Echo Find It '

Set ObjConnection1 = CreateObject ("ADODB. Connection ")
Set ObjRecordSet1 = CreateObject ("ADODB. Recordset ")

Objconnection1.open _
"Provider = microsoft.ace.oledb.12.0;" & _
"Data Source = C:\inetpub\wwwroot\ComputerList\Computer.accdb"

Objrecordset1.open "UPDATE computerlist Set pingstatus = '" & strping & "'" & _
"Where ComputerName = '" & strtemp & "'", _
ObjConnection1, adOpenStatic, adLockOptimistic

' Objrecordset1.close
' Objconnection1.close

Set ObjConnection1 = Nothing
Set ObjRecordSet1 = Nothing

Else
' WScript.Echo ' can ' t find it

Set ObjConnection1 = CreateObject ("ADODB. Connection ")
Set ObjRecordSet1 = CreateObject ("ADODB. Recordset ")

Objconnection1.open _
"Provider = microsoft.ace.oledb.12.0;" & _
"Data Source = C:\inetpub\wwwroot\ComputerList\Computer.accdb"

Objrecordset1.open "INSERT into Computerlist (ComputerName, IPAddress, UserName, DisplayName, Computermodel, COMPUTERSN , LastBootUpTime, Localadmin, Pingstatus) "& _
"VALUES ('" & StrComputer & "', '" & Stripadd & "', '" & Colusername & "', '" & Coldisplayn & ; "', '" & Colmodel & "', '" & Colsn & "', '" & Collastbootup & "', '" & Coluserlimit & "', ' "& strping &" ') ", _
ObjConnection1, adOpenStatic, adLockOptimistic

' Objrecordset1.close
' Objconnection1.close

Set ObjConnection1 = Nothing
Set ObjRecordSet1 = Nothing

End if

Tempvalue = Null

Err.Clear

End Sub

This article is from "Eric's Mind Journey" blog, declined reprint!

[Combat three]script export computer information into ACCDB