[实战三]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 [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 &"",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

本文出自 “Eric的心路旅程” 博客，谢绝转载！