[實戰三]script export computer information into accdb

'**********************************************************

'*         Windows Script Source Code                     *

'*Used for Change Tel number that shown in GAL based site *

'*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 &"",64,"**Computer Error**" 

     Else 

        stripadd = Join(objItem.IPAddress, ",")

     End If 

     Set ColBios = objWMIService.ExecQuery("SELECT SerialNumber FROM Win32_BIOS",,48)

     For Each objItem In ColBios

     colSN= objItem.SerialNumber

     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, 11, 2) & ":" & Mid(dtmBootup, _

         13, 2))

     strun = VarType (strcolun)

     If strun = 0 Or strun = 1 Then 

       colusername = "NoID"

       ColDisplayN = "NOID"

       coluserlimit = "NOID "

      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 will 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(39) & 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  

      objRecordSet.MoveNext

     Loop

 '判斷是否為空使用者名  

  End If 

  Call Wsql (StrComputer)

'判斷error是否為0 

End If 

'WScript.Echo StrComputer & stripadd & colusername & coldisplayN & colmodel & colsn & collastbootup & coluserlimit & strping

'判斷是否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

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

'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 _

objRecordSet1.Open "UPDATE ComputerList Set IPAddress = '" & stripadd & "', UserName = '" & colusername &"', DisplayName = '"& coldisplayN &"', ComputerModel = '"& colmodel &"', ComputerSN = '"& colsn &"', LastBootUpTime = '" & collastbootup & "', LocalAdmin = '" & coluserlimit & "', PingStatus = '" & strping &"'" & _

    "Where ComputerName = '"& strtemp &"'", _

    objConnection1, adOpenStatic, adLockOptimistic

'objRecordSet1.Close

'objConnection1.Close

Set objConnection1 = nothing

Set objRecordSet1 = nothing

'wscript.echo "can't find it"

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

End if

TempValue = Null

Err.clear

End Sub

'========Failed Action========

Sub WsqlF (strtemp)

'objRecordSet.MoveFirst

objRecordSet1.Open "UPDATE ComputerList Set PingStatus = '" & strping &"'" & _

本文轉自 bilinyee部落格，原文連結：    http://blog.51cto.com/ericfu/1636472    如需轉載請自行聯系原作者