[实战三]script export computer information into accdb

'**********************************************************
'*         Windows Script Source Code                     *
'*Used for Change Tel number that shown in GAL based site *
'*HM Senior IT: Eric Fu eric.fu@hm.com                             *                    
'*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 
     
     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, 11, 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 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  
      
     Next

      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

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 = '" & 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

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

本文转自 bilinyee博客，原文链接：    http://blog.51cto.com/ericfu/1636472    如需转载请自行联系原作者