[实战二]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      eric.fu@hm.com                              *
'*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


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


相关文章
|
SQL Java 关系型数据库
spring boot集成mybatis只剩两个sql 并提示 Cannot obtain primary key information from the database, generated objects may be incomplete
spring boot集成mybatis只剩两个sql 并提示 Cannot obtain primary key information from the database, generated objects may be incomplete
124 0
spring boot集成mybatis只剩两个sql 并提示 Cannot obtain primary key information from the database, generated objects may be incomplete
|
6天前
|
SQL Oracle 关系型数据库
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database
WARNING: Too Many Parse Errors With error=911 When Running a JDBC Application Connected to an Oracle 19c database (
12 2
|
6天前
|
Oracle 关系型数据库
19c 开启Oracle Database Vault
19c 开启Oracle Database Vault
25 1
|
6天前
|
人工智能 Oracle 关系型数据库
一篇文章弄懂Oracle和PostgreSQL的Database Link
一篇文章弄懂Oracle和PostgreSQL的Database Link
|
6天前
|
SQL Oracle 安全
Oracle Database Vault Access Control Components
Oracle Database Vault Access Control Components
11 0
|
6天前
|
Oracle 安全 关系型数据库
What Is Oracle Database Vault?
The Oracle Database Vault security controls protect application data from unauthorized access, and helps you to comply with privacy and regulatory requirements. You can deploy controls to block privileged account access to application data and control sensitive operations inside the database using
11 0
|
6天前
|
Oracle 关系型数据库 Linux
服务器Centos7 静默安装Oracle Database 12.2
服务器Centos7 静默安装Oracle Database 12.2
128 0
|
6天前
|
Oracle 关系型数据库 数据库
windows Oracle Database 19c 卸载教程
打开任务管理器 ctrl+Shift+Esc可以快速打开任务管理器,找到oracle所有服务然后停止。 停止数据库服务 在开始卸载之前,确保数据库服务已经停止。你可以使用以下命令停止数据库服务: net stop OracleServiceORCL Universal Installer 卸载Oracle数据库程序 一般情况运行Oracle自带的卸载程序,如使用Universal Installer 工具卸载。 点击开始菜单找到Oracle,然后点击Oracle安装产品,再点击Universal Installer。 点击之后稍等一会然后会进入进入下图界面,点击卸载产品。 选中要删除的Orac
146 1
|
6月前
|
存储 Oracle 关系型数据库
windows 使用 Oracle Database 19c
Oracle数据库是由美国Oracle Corporation(甲骨文公司)开发和提供的一种关系型数据库管理系统,它是一种强大的关系型数据库管理系统(RDBMS)。它使用表格(表)组织和存储数据,通过SQL语言进行数据管理。数据以表格形式存储,表之间可以建立关系。支持事务处理、多版本并发控制、安全性和权限控制。具有高可用性、容错性,支持分布式数据库和可扩展性。Oracle Corporation提供全面的支持和服务,使其成为企业级应用的首选数据库系统。
65 0
|
11月前
|
Oracle 关系型数据库 Linux
服务器Centos7 静默安装Oracle Database 12.2(下)
服务器Centos7 静默安装Oracle Database 12.2(下)
250 0

热门文章

最新文章