[实战二]vbs script export user information to access database

vbs script export user information to access database,

vbs operate accdb

source code is below

=====================================

‘********************************************************
‘*         Windows Script Source Code                   *
‘*Used for Collect PPG China Users PC infromation       *
‘*Author: Fu Eric      [email protected]                              *
‘*HM Senior IT                           *                   
‘*Date: 28 Nov 2013                                  *
‘*Any Site can change infor depend on your request      *
‘********************************************************
On error resume Next

strldap = "LDAP://OU=CN,OU=Users,OU=CN,OU=HM,dc=HM, dc=com"

Const ADS_UF_DONT_EXPIRE_PASSWD = &h10000
Const ADS_UF_ACCOUNTDISABLE = 2
Const ADS_ACETYPE_ACCESS_DENIED_OBJECT = &H6
Const CHANGE_PASSWORD_GUID  = _
   "{ab721a53-1e2f-11d0-9819-00aa0040529b}"
  
Set usr=GetObject(strldap)

For each member in usr

strutype = member.class

If strutype = "user" Then

strid= member.get("samaccountname")

strdisplayname = member.displayname

stroffice = member.physicalDeliveryOfficeName

strtel = member.Get("telephoneNumber")

strothermobile = member.GetEx ("otherMobile")

For Each Item in strothermobile

strkeynum1 = Item

strleftstr = Left (strkeynum1,2)

If strleftstr = "AI" Then

strkeynum = strkeynum1

Exit For

End If
 
Next

Item = Null

strothermobile = Null

strkeynum1 = Null

‘==============================
strbus = member.otherTelephone

intuac = member.Get("userAccountControl")
 If intuac AND ADS_UF_ACCOUNTDISABLE Then
   
 straccountstate = "Disable"
  Else 
  straccountstate= "Enable"
  
  End If
 
strdes = member.description

strTitle = member.get("title")

striphone = member.ipPhone

‘=====get lastlogin time=====

Set objLastLogon = member.Get("lastLogonTimestamp")
     
     
     strlastlogontimestamp = objLastLogon.HighPart * (2^32) + objLastLogon.LowPart
     strlastlogontimestamp = strlastlogontimestamp / (60 * 10000000)
     strlastlogontimestamp = strlastlogontimestamp / 1440
     strlastlogontimestamp = strlastlogontimestamp + #1/1/1601#
 
     Set objlastlogon = Nothing   
    
     Set objlastlogon1 = member.Get("lastlogon")  
     strLastLogonTime1 = objLastLogon1.HighPart * (2^32) + objLastLogon1.LowPart
     strLastLogonTime1 = strLastLogonTime1 / (60 * 10000000)
     strLastLogonTime1 = strLastLogonTime1 / 1440
     strLastLogonTime1 = strLastLogonTime1 + #1/1/1601# 
     Set objlastlogon1 = Nothing
    
     strvalue1 = VarType (strlastlogontimestamp)
     strvalue2 = VarType (strLastLogonTime1)
     strstring =  strvalue1 &"+" & strvalue2
    
     strlocallt = DateValue(strLastLogonTime1)
     strremotelt = DateValue(strlastlogontimestamp)
    
     Select Case strstring
     Case "1+7"  strllt = strLastLogonTime1
     Case "7+1"  strllt = strlastlogontimestamp
     Case "1+1"  strllt = Null
     Case Else   If DateDiff ("d",strlocallt,strremotelt) >=0 Then
                 strllt = strlastlogontimestamp
                 Else
                 strllt = strLastLogonTime1
                End If
     End Select  
    
    
              

 If member.mDBUseDefaults = False Then
    
     strStoQuota = member.mDBStorageQuota
     strOveQuota = member.mDBOverQuotaLimit
     strHarQuotalim= member.mDBOverHardQuotaLimit
    
     strquotamailbox =  strStoQuota  & "MB/" & strOveQuota & "MB/" & strHarQuotalim &"MB"
    
     Else
 
 strquotamailbox = "Default Settings"
    
     End If
 
‘====mailbox store is strmstore1======    
strmstore = member.homemdb

Myarray1 = Split (strmstore,",",-1,1)

strmstore1 = Myarray1(0)

strmstore = Null

‘====check whether have webmail======
strmemberOf = member.GetEx("memberOf")

strcheck = IsObject(strmemberOf)

‘WScript.Echo strcheck

If strcheck = 0 Then

For Each Item1 in strmemberOf

Myarray = Split (Item1,",",-1,1)

Myarray2 = Split (Myarray(0),"=",-1,1)

‘WScript.Echo Myarray2(1)

If Myarray2(1) = "CNSH Webmail Users" Or Myarray2(1) = "Webmail Users" Then

 strwebmail = "Enable"
 
   Exit For
 Else
 strwebmail = "Disable"
End If

Next

Else

strwebmail = "Disable"

End If

Set myarray = Nothing 

Set Myarray2 = Nothing

Set Myarray1 = Nothing

‘=========
‘strid = CStr (strid)
‘strdisplayname = CStr (strdisplayname)
‘stroffice = CStr (stroffice)
‘straccountstate = CStr (straccountstate)
‘strdes = CStr (strdes)
‘strllt = CStr (strllt)
‘strtel = CStr (strtel)
‘strbus = CStr (strbus)
‘striphone = CStr (striphone)
‘strtitle = CStr (strtitle)
‘strmstore1 = CStr (strmstore1)
‘strkeynum = CStr (strkeynum)
‘strwebmail = CStr (strwebmail)
‘strquotamailbox = CStr (strquotamailbox)

‘WScript.Echo (strid &  strdisplayname  & stroffice & straccountstate & strdes _
‘          & strllt & strtel & strbus & striphone & strtitle &  strmstore1 _
‘         & strkeynum & strwebmail & strquotamailbox )

Call Wsql (strid)

WScript.Sleep (1000)

strid = Null 
strdisplayname = Null 
stroffice = Null 
straccountstate = Null 
strdes = Null
strllt = Null
strtel = Null
strbus = Null
striphone = Null
strtitle = Null
strmstore1 = Null
strkeynum = Null
strwebmail = Null 
strquotamailbox = Null

Myarray1 = Null

Myarray = Null

Myarray2 = Null

Set intuac = Nothing

Set strmemberOf = Nothing

strmstore1 = Null

strvalue1 = Null
strvalue2 = Null
strstring = Null

End If

Next

‘======SQL Table List====
‘UserName   strid
‘DisplayName  strdisplayname
‘OfficeName  stroffice
‘AccountStatus straccountstate
‘Description   strdes
‘LastLogonTime  strllt
‘Telephone  strtel
‘Business2  strbus
‘IPphone  striphone
‘Title  strtitle
‘MailStore  strmstore1
‘RASKeyNumber  strkeynum
‘WebmailCheck  strwebmail
‘MailboxQuota strquotamailbox

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\AccountList\Account.accdb"

objRecordSet.Open "SELECT * FROM AccountList where UserName = ‘"& strtemp&"‘", _
  objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF

    TempValue = objRecordset.Fields.Item("UserName")

    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\AccountList\Account.accdb"

objRecordSet1.Open "UPDATE AccountList Set DisplayName = ‘" & strdisplayname & "‘, OfficeName = ‘" & stroffice &"‘, AccountStatus = ‘"& straccountstate &"‘, Description = ‘"& strdes &"‘, LastLogonTime = ‘"& strllt &"‘, Telephone = ‘" & strtel & "‘, Business2 = ‘" & strbus & "‘, IPphone = ‘" & striphone &"‘, Title = ‘" & strtitle &"‘, MailStore = ‘" &  strmstore1  &"‘, RASKeyNumber = ‘" & strkeynum  &"‘,WebmailCheck = ‘"&  strwebmail &"‘, MailboxQuota = ‘"& strquotamailbox &"‘" & _
    "Where UserName = ‘"& 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\AccountList\Account.accdb"

objRecordSet1.Open "INSERT INTO AccountList (UserName, DisplayName, OfficeName, AccountStatus, Description, LastLogonTime, Telephone, Business2, IPphone, Title, MailStore, RASKeyNumber,WebmailCheck, MailboxQuota)" &  _
    "VALUES (‘" & strid & "‘,‘ " & strdisplayname & "‘,‘" & stroffice & "‘,‘" & straccountstate & "‘,‘ " & strdes & "‘,‘ " & strllt & "‘,‘ " & strtel & "‘,‘ " & strbus & "‘,‘ " & striphone & "‘,‘ " & strtitle & "‘,‘ " & strmstore1 & " ‘,‘ " & strkeynum & "‘,‘" & strwebmail &"‘,‘ " & strquotamailbox &"‘)", _
        objConnection1, adOpenStatic, adLockOptimistic
       
objRecordSet1.Close
objConnection1.Close

Set objConnection1 = nothing
Set objRecordSet1 = nothing

End if

TempValue = Null

Err.clear

End Sub

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