Becksi
asked on
Convert Vbscrit to powershell
Public LogOutput
LogOutPut = True
Dim ParentDIR, TemplateFileName, ResultFileName, EVFilenName
Dim strDigipass, strFirstpart, ldapValuefortoken
Dim Period, M, strPeriod, d
Dim objExcel, objTemplateWorkbook, objResultWorkbook, objFSO, objToken, objOtherWorkbook
Dim ExportDataDefinitionSheet, DomainTableSheet, UserDataSheet, PublicFolderDataSheet, WorkplaceDataSheet, PerionDefinitionSheet, ExportDataSheet, CitrixDataSheet, OtherDataSheet
Public ResultRow, OtherRow, ACLRow
ResultRow = 2
OtherRow = 2
ACLRow = 0
' Define Public Dynamic Excel Arrays (2 dimensions -> 10 x N)
Public aExportDataDefinitionSheet (), aDomainTableSheet(), aUserDataSheet(), aPublicFolderDataSheet(), aWorkplaceDataSheet(), aCitrixDataSheet()
Redim aExportDataDefinitionSheet (10,1), aDomainTableSheet(10,1), aUserDataSheet(10,1), aPublicFolderDataSheet(10, 1), aWorkplaceDataSheet(10,1), aCitrixDataSheet(10,1)
' Define Public Dynamic Exchange and EVault Arrays (2 dimensions -> X x N)
Public aExchange(), aPublic(), aAD(), aEVault(), aBlackBerry(), aACL()
Redim aExchange(6,1), aPublic(10,1), aAD(8,1), aEVault(4,1), aBlackBerry (10,1), aACL(10,1)
' Constants
Const ADS_SCOPE_SUBTREE = 2
Const ADS_SCOPE_ONELEVEL = 1
Const ADS_CHASE_REFERRALS_ALWAYS = &H60
Const gcForReading = 1
Const gcForWriting = 2
Const gcForAppending = 8
Const TristateUseDefault = -2
Const OverwriteExisting = TRUE
CONST ASSOCIATED_EXTERNAL = &h4
' General Objects
Set objFSO = CreateObject("Scripting.Fi leSystemOb ject")
Set objExcel = WScript.CreateObject ("Excel.Application")
' Other Variables
ParentDIR = objFSO.GetParentFolderName (wscript.S criptFullN ame) ' This is the directory where script input & outfile is residing.
TemplateFileName = ParentDIR & "\Template.xls" ' Name of the reference template excel file.
Period = UCase (MonthName (Left (Date ,(InStr (Date , "/") -1 )), True )) & " " & Right (Date , 4)
M = Month(Date)
ResultFileName = ParentDIR & "\Result-" & Period & ".xls"' Output Excel file Name appended with the current English Month and Year
OtherFileName = ParentDIR & "\Other-" & Period & ".xls"' Output Excel file Name appended with the current English Month and Year
CopyFile ParentDIR , "EV_VN_MailboxVS01", "EnterpriseVault.tab", True
CopyFile ParentDIR , "FatClient_BlackBerry_Expo rt", "BlackBerry.tab", True
LogOutPut = True
Dim ParentDIR, TemplateFileName, ResultFileName, EVFilenName
Dim strDigipass, strFirstpart, ldapValuefortoken
Dim Period, M, strPeriod, d
Dim objExcel, objTemplateWorkbook, objResultWorkbook, objFSO, objToken, objOtherWorkbook
Dim ExportDataDefinitionSheet,
Public ResultRow, OtherRow, ACLRow
ResultRow = 2
OtherRow = 2
ACLRow = 0
' Define Public Dynamic Excel Arrays (2 dimensions -> 10 x N)
Public aExportDataDefinitionSheet
Redim aExportDataDefinitionSheet
' Define Public Dynamic Exchange and EVault Arrays (2 dimensions -> X x N)
Public aExchange(), aPublic(), aAD(), aEVault(), aBlackBerry(), aACL()
Redim aExchange(6,1), aPublic(10,1), aAD(8,1), aEVault(4,1), aBlackBerry (10,1), aACL(10,1)
' Constants
Const ADS_SCOPE_SUBTREE = 2
Const ADS_SCOPE_ONELEVEL = 1
Const ADS_CHASE_REFERRALS_ALWAYS
Const gcForReading = 1
Const gcForWriting = 2
Const gcForAppending = 8
Const TristateUseDefault = -2
Const OverwriteExisting = TRUE
CONST ASSOCIATED_EXTERNAL = &h4
' General Objects
Set objFSO = CreateObject("Scripting.Fi
Set objExcel = WScript.CreateObject ("Excel.Application")
' Other Variables
ParentDIR = objFSO.GetParentFolderName
TemplateFileName = ParentDIR & "\Template.xls" ' Name of the reference template excel file.
Period = UCase (MonthName (Left (Date ,(InStr (Date , "/") -1 )), True )) & " " & Right (Date , 4)
M = Month(Date)
ResultFileName = ParentDIR & "\Result-" & Period & ".xls"' Output Excel file Name appended with the current English Month and Year
OtherFileName = ParentDIR & "\Other-" & Period & ".xls"' Output Excel file Name appended with the current English Month and Year
CopyFile ParentDIR , "EV_VN_MailboxVS01", "EnterpriseVault.tab", True
CopyFile ParentDIR , "FatClient_BlackBerry_Expo
ASKER
Chris,
It is a part of a script what I created in Vbscript and i would like to convert in Powershell but I am not so good in powershell:
Hereby the script:
' ************************** ********** ** Declaration of Variables,Constants and Objects ************************** ********** **
'Option Explicit
'Generate Console Output = False or True
Public LogOutput
LogOutPut = True
Dim ParentDIR, TemplateFileName, ResultFileName, EVFilenName
Dim strDigipass, strFirstpart, ldapValuefortoken
Dim Period, M, strPeriod, d
Dim objExcel, objTemplateWorkbook, objResultWorkbook, objFSO, objToken, objOtherWorkbook
Dim ExportDataDefinitionSheet, DomainTableSheet, UserDataSheet, PublicFolderDataSheet, WorkplaceDataSheet, PerionDefinitionSheet, ExportDataSheet, CitrixDataSheet, OtherDataSheet
Public ResultRow, OtherRow, ACLRow
ResultRow = 2
OtherRow = 2
ACLRow = 0
' Define Public Dynamic Excel Arrays (2 dimensions -> 10 x N)
Public aExportDataDefinitionSheet (), aDomainTableSheet(), aUserDataSheet(), aPublicFolderDataSheet(), aWorkplaceDataSheet(), aCitrixDataSheet()
Redim aExportDataDefinitionSheet (10,1), aDomainTableSheet(10,1), aUserDataSheet(10,1), aPublicFolderDataSheet(10, 1), aWorkplaceDataSheet(10,1), aCitrixDataSheet(10,1)
' Define Public Dynamic Exchange and EVault Arrays (2 dimensions -> X x N)
Public aExchange(), aPublic(), aAD(), aEVault(), aBlackBerry(), aACL()
Redim aExchange(6,1), aPublic(10,1), aAD(8,1), aEVault(4,1), aBlackBerry (10,1), aACL(10,1)
' Constants
Const ADS_SCOPE_SUBTREE = 2
Const ADS_SCOPE_ONELEVEL = 1
Const ADS_CHASE_REFERRALS_ALWAYS = &H60
Const gcForReading = 1
Const gcForWriting = 2
Const gcForAppending = 8
Const TristateUseDefault = -2
Const OverwriteExisting = TRUE
CONST ASSOCIATED_EXTERNAL = &h4
' General Objects
Set objFSO = CreateObject("Scripting.Fi leSystemOb ject")
Set objExcel = WScript.CreateObject ("Excel.Application")
' Other Variables
ParentDIR = objFSO.GetParentFolderName (wscript.S criptFullN ame) ' This is the directory where script input & outfile is residing.
TemplateFileName = ParentDIR & "\Template.xls" ' Name of the reference template excel file.
Period = UCase (MonthName (Left (Date ,(InStr (Date , "/") -1 )), True )) & " " & Right (Date , 4)
M = Month(Date)
ResultFileName = ParentDIR & "\Result-" & Period & ".xls"' Output Excel file Name appended with the current English Month and Year
OtherFileName = ParentDIR & "\Other-" & Period & ".xls"' Output Excel file Name appended with the current English Month and Year
CopyFile ParentDIR , "EV_VN_MailboxVS01", "EnterpriseVault.tab", True
CopyFile ParentDIR , "FatClient_BlackBerry_Expo rt", "BlackBerry.tab", True
' ************************** ********** ** Main Script ************************** ********** **
' Open Excel Template file
Set objTemplateWorkbook = objExcel.Workbooks.Open(Te mplateFile Name)
objExcel.Visible = False
objExcel.DisplayAlerts=Fal se
' Define Template Worksheets into variables
Set ExportDataDefinitionSheet = objTemplateWorkbook.WorkSh eets("Expo rt-data") ' This pointing to the "Export-data" sheet of the Template file.
Set DomainTableSheet = objTemplateWorkbook.WorkSh eets("Dome in-tabel") ' This pointing to the "Domein-tabel" sheet of the Excel file. This sheet contains Domains to be used for user and workplace matching.
Set UserDataSheet = objTemplateWorkbook.WorkSh eets("Gebr uikers-tab el") ' This pointing to the "Gebruikers-tabel" sheet of the Excel file. This sheet contains User OU patterns to be matched with.
Set PublicFolderDataSheet = objTemplateWorkbook.WorkSh eets("Publ icFolder-t abel") ' This pointing to the "PublicFolder-tabel" sheet of the Excel file. This sheet contains Exchange Public Folder path patterns to be matched with.
Set WorkplaceDataSheet = objTemplateWorkbook.WorkSh eets("Werk plek-tabel ") ' This pointing to the "Werkplek-tabel" sheet of the Excel file. This sheet contains Workplace OU patterns to be matched with.
Set PerionDefinitionSheet = objTemplateWorkbook.WorkSh eets("Peri ode-defini tie") ' This pointing to the "Periode-definitie" sheet of the Excel file. This sheet contains Date/Month Period definition to be matched with.
Set CitrixDataSheet = objTemplateWorkbook.WorkSh eets("Citr ix-tabel") ' This pointing to the "Citrix-tabel" sheet of the Excel file. This sheet contains Citrix User Groups to be matched with.
' Open and save Excel Template file
Set objResultWorkbook = objExcel.Workbooks.Add()
objResultWorkbook.SaveAs(R esultFileN ame)
' Rename and define Result Worksheets into variables
objResultWorkbook.WorkShee ts(1).Name ="Export-d ata"
Set ExportDataSheet = objResultWorkbook.WorkShee ts("Export -data")
Set objOtherWorkbook = objExcel.Workbooks.Add()
objOtherWorkbook.SaveAs(Ot herFileNam e)
' Rename and define Result Worksheets into variables
objOtherWorkbook.WorkSheet s(1).Name= "Export-da ta"
Set OtherDataSheet = objOtherWorkbook.WorkSheet s("Export- data")
' Create the current Month & Year as noted in period definition sheet in Template.xls as Dutch output
strPeriod = "'" & UCase(Left(PerionDefinitio nSheet.cel ls(M+1,6), 3) & "-" & Right (Date , 4))
' Read Excel data into array for matching patterns (Arrays are build from 0 -> 9, where excel rows are build from 1 -> 10)
Logdata "Read Domain Table", "", ""
CreateArrayFromExcel DomainTableSheet, aDomainTableSheet
Logdata "Read User Data Table", "", ""
CreateArrayFromExcel UserDataSheet, aUserDataSheet
Logdata "Read Public Folder Table", "", ""
CreateArrayFromExcel PublicFolderDataSheet, aPublicFolderDataSheet
Logdata "Read Workplace data Table", "", ""
CreateArrayFromExcel WorkplaceDataSheet, aWorkplaceDataSheet
Logdata "Read Citrix Definition Table", "", ""
CreateArrayFromExcel CitrixDataSheet, aCitrixDataSheet
' Select and copy first template sheets row to output OtherDataSheet
ExportDataDefinitionSheet. Activate()
ExportDataDefinitionSheet. Rows.Item( 1).Select( )
objExcel.Selection.Copy()
' Paste first template sheets row to output OtherDataSheet
OtherDataSheet.Activate()
OtherDataSheet.Rows.Item(1 ).Select()
OtherDataSheet.Paste()
OtherDataSheet.Range("A1") .AutoFilte r
' Select and copy first template sheets row to output ExportDataSheet
ExportDataDefinitionSheet. Activate()
ExportDataDefinitionSheet. Rows.Item( 1).Select( )
objExcel.Selection.Copy()
' Paste first template sheets row to output ExportDataSheet
ExportDataSheet.Activate()
ExportDataSheet.Rows.Item( 1).Select( )
ExportDataSheet.Paste()
ExportDataSheet.Range("A1" ).AutoFilt er
' Closing Template workbook as precaution
objTemplateWorkbook.Close
' Reading Exchange and Enterprise Vault data into arrays for matching patterns (Arrays are build from 0 -> N-1, where N is the size)
LogData "Reading Exchange User Data", "", ""
GetExchangeUserData
LogData "Reading Enterprise Vault Data", "", ""
GetEVaultUserData ParentDIR, "EnterpriseVault.tab"
LogData "BlackBerry Data", "", ""
GetBBUserData ParentDIR, "BlackBerry.tab"
LogData "Reading Exchange Public Data", "", ""
GetExchangePublicData
' Get domaindata from domains in the template.xls "domein-tabel", only match domains with include=1
For d=1 to ubound(aDomainTableSheet,2 )
Select Case aDomainTableSheet(0,d)
Case 1
MatchUserData aDomainTableSheet(2,d), aDomainTableSheet(1,d), 1 ' Generate User data per domain
MatchWorkplaceData aDomainTableSheet(2,d), aDomainTableSheet(1,d), 1 ' Generate Workplace data per domain
Case 2
MatchUserData aDomainTableSheet(2,d), aDomainTableSheet(1,d), 2 ' Generate User data per domain
MatchWorkplaceData aDomainTableSheet(2,d), aDomainTableSheet(1,d), 2 ' Generate Workplace data per domain
End Select
Next
' Generata Public Exchange data
MatchPublicFolderData
' Closing script
objExcel.Save
objResultWorkbook.Close
objOtherWorkbook.Close
objExcel.Quit
Set objFSO = Nothing
Set objExcel = Nothing
' ************************** ********** ** AD Sub routines and Funtions ************************** ********** **
Sub MatchUserData (strDNSDomain,strDomain,bD omain)
'On Error Resume Next
Dim objConnection, objCommand, objRecordSet, strBase, strFilter, strAttributes, strQuery
Dim AccountEnabled, Customer, CustomerSub, ExcludeItem, ServiceItem, ExchangeMailboxSize, EVaultSize, BlackBerryState, StrVPNToken, StrVPNTokenPath, intOMA, binOMA, distinguisedID, distinguisedPath
Dim strTrustee, RefdistinguishedName, RefsAMAccountName, RefmsintOMA, RefHomeMDB, ReflegacyExchangeDN, RefdisplayName
' Make Connection with Active directory
Set objConnection = CreateObject("ADODB.Connec tion")
Set objCommand = CreateObject("ADODB.Comman d")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnectio n = objConnection
objCommand.Properties("Cha se referrals") = ADS_CHASE_REFERRALS_ALWAYS
objCommand.Properties("Pag e Size") = 10000
objCommand.Properties("Sea rchscope") = ADS_SCOPE_SUBTREE
'Define LDAP dns connection
strBase = "<LDAP://" & strDNSDomain & ">"
'Define the filter elements
strFilter = "(&(objectCategory=person) (objectCla ss=user))"
'List all attributes you will require
' At the user to the trustee array if it has an trustee
If bDomain = 1 Then
strAttributes = "Name,distinguishedName,sA MAccountNa me,givenNa me,display Name,sn,us erPrincipa lName,lega cyExchange DN,userAcc ountContro l,vasco-Li nkUserToDP Token,msEx chOmaAdmin WirelessEn able,HomeM DB"
Else
strAttributes = "Name,distinguishedName,sA MAccountNa me,givenNa me,display Name,sn,us erPrincipa lName,lega cyExchange DN,userAcc ountContro l,vasco-Li nkUserToDP Token"
End If
'compose query
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
Set objRecordSet = objCommand.Execute
If not objRecordSet.EOF Then
objRecordSet.MoveFirst
End If
Do Until objRecordSet.EOF
'Create variables of current user
LogData "User to be matched", "", "<" & objRecordSet.Fields("disti nguishedNa me").Value & ">"
'Check if account is enabled
AccountEnabled = CheckAccountEnabled(objRec ordSet.Fie lds("userA ccountCont rol").Valu e)
' Get Customer values
aCustomer = Split(GetCustomerValues(aU serDataShe et,objReco rdSet.Fiel ds("distin guishedNam e").Value) ,";")
Customer = aCustomer(0)
CustomerSub = aCustomer(1)
ExcludeItem = aCustomer(2)
ServiceItem = aCustomer(3)
'Strip down the current account distinguishedName to path without account
If InStr (objRecordSet.Fields("dist inguishedN ame").Valu e, ",CN") > 0 Then
distinguisedID = Left (objRecordSet.Fields("dist inguishedN ame").Valu e, InStr (objRecordSet.Fields("dist inguishedN ame").Valu e, ",CN") -1)
Else
distinguisedID = Left (objRecordSet.Fields("dist inguishedN ame").Valu e, InStr (objRecordSet.Fields("dist inguishedN ame").Valu e, ",OU") -1)
End If
distinguisedPath = Replace (objRecordSet.Fields("dist inguishedN ame").Valu e ,distinguisedID & ",", "" )
' Get the account info to be referenced for Exchange
If bDomain = 1 Then
RefdistinguishedName = objRecordSet.Fields("disti nguishedNa me").Value
RefsAMAccountName = objRecordSet.Fields("sAMAc countName" ).Value
RefmsintOMA = objRecordSet.Fields("msExc hOmaAdminW irelessEna ble").Valu e
RefHomeMDB = objRecordSet.Fields("HomeM DB").Value
ReflegacyExchangeDN = objRecordSet.Fields("legac yExchangeD N").Value
RefdisplayName = objRecordSet.Fields("displ ayName").V alue
' At the user to the trustee array, if it has an trustee intTrustee will be 1
strTrustee = GetACL(strDNSDomain, RefdistinguishedName, RefsAMAccountName, RefmsintOMA, RefHomeMDB, ReflegacyExchangeDN, RefdisplayName)
If strTrustee <> "" AND AccountEnabled = "1" Then
WriteOtherData OtherRow, strPeriod, AccountEnabled, "2B.3", Customer, CustomerSub, objRecordSet.Fields("sAMAc countName" ).Value, objRecordSet.Fields("displ ayName").V alue, "User", distinguisedPath, strTrustee, ""
OtherRow = OtherRow + 1
End If
Else
aTrustee = Split(GetExchangeTrustee(s AMAccountN ame),";")
If aTrustee(1) <> "" Then
RefdistinguishedName = aTrustee(0)
RefsAMAccountName = aTrustee(1)
RefmsintOMA = aTrustee(2)
RefHomeMDB = aTrustee(3)
ReflegacyExchangeDN = aTrustee(4)
RefdisplayName = aTrustee(5)
WriteOtherData OtherRow, strPeriod, AccountEnabled, "2B.3", Customer, CustomerSub, objRecordSet.Fields("sAMAc countName" ).Value, objRecordSet.Fields("displ ayName").V alue, "User", distinguisedPath, aTrustee(1), ""
OtherRow = OtherRow + 1
End If
End If
' Get values with reference accounts info
ExchangeMailboxSize = GetExchangeMailboxSize(Ref legacyExch angeDN)
EVaultSize = GetEVaultSize(RefsAMAccoun tName, RefdisplayName)
BlackBerryState = GetBlackBerryState(Reflega cyExchange DN)
' Check if item is not "Skip"
If ExcludeItem <> "2" Then
' Check if there is customer info found and the item is not "Exlcuded"
If Customer <> "0" AND ExcludeItem <> "1" Then
wscript.echo "ExchangeMailboxsize: " & ExchangeMailboxSize
wscript.echo "EvaultSize: " & EvaultSize
' Service 2B.3.1 for Users
If (ExchangeMailboxSize <= 500) Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.3.1", Customer, CustomerSub, objRecordSet.Fields("displ ayName").V alue, ExchangeMailboxSize, EVaultSize, "Mailbox", distinguisedPath, ""
ResultRow = ResultRow + 1
End If
' End Service 2B.3.1 for Users
' Service 2B.3.2 for Users
If (ExchangeMailboxSize <= 2000) Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.3.2", Customer, CustomerSub, objRecordSet.Fields("displ ayName").V alue, ExchangeMailboxSize, EVaultSize, "Mailbox", distinguisedPath, ""
ResultRow = ResultRow + 1
End If
' End Service 2B.3.2 for Users
If TypeName(objRecordSet.Fiel ds("vasco- LinkUserTo DPToken"). Value) <> "Null" Then
' Service 2B.5
For Each VPNToken in objRecordSet.Fields("vasco -LinkUserT oDPToken") .Value
LogData "VPN Token", "", VPNToken
StrVPNToken = Left (VPNToken , InStr (VPNToken , ",") -1)
strVPNTokenPath = Replace (VPNToken ,StrVPNToken&"," , "" )
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.5", Customer, CustomerSub, objRecordSet.Fields("sAMAc countName" ).Value, objRecordSet.Fields("displ ayName").V alue, StrVPNToken, strVPNTokenPath, distinguisedPath, ""
ResultRow = ResultRow + 1
StrVPNToken = Null
strVPNTokenPath = Null
Next
' End Service 2B.5
' Service 2B.1.4
If CheckCitrixEnabled(strDNSD omain,objR ecordSet.F ields("dis tinguished Name").Val ue) = True Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.4", Customer, CustomerSub, objRecordSet.Fields("sAMAc countName" ).Value, objRecordSet.Fields("displ ayName").V alue, "", distinguisedPath, "", ""
ResultRow = ResultRow + 1
End If
' End Service 2B.1.4
VPNToken = Null
Else
' Service 2B.1.4
If CheckCitrixEnabled(strDNSD omain,objR ecordSet.F ields("dis tinguished Name").Val ue) = True Then
WriteOtherData OtherRow, strPeriod, AccountEnabled, "2B.1.4", Customer, CustomerSub, objRecordSet.Fields("sAMAc countName" ).Value, objRecordSet.Fields("displ ayName").V alue, "", distinguisedPath, "", ""
OtherRow = OtherRow + 1
End If
' End Service 2B.1.4
End If
If RefHomeMDB <> "" Then
' Service 2B.1.5
wscript.echo RefdistinguishedName
binOMA = toBin(RefmsintOMA)
Logdata "OMA", "", "int:<" & RefmsintOMA & "> bin:<" & binOMA & ">"
If RefmsintOMA > 1 Then
If inStr(Len(binOMA)-1,binOMA ,"1",1) = Len(binOMA)-1 Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.5", Customer, CustomerSub, objRecordSet.Fields("sAMAc countName" ).Value, objRecordSet.Fields("displ ayName").V alue, "Outlook Mobile Access", distinguisedPath, RefmsintOMA, ""
ResultRow = ResultRow + 1
End If
Else
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.5", Customer, CustomerSub, objRecordSet.Fields("sAMAc countName" ).Value, objRecordSet.Fields("displ ayName").V alue, "Outlook Mobile Access", distinguisedPath, RefmsintOMA, ""
ResultRow = ResultRow + 1
End If
' End Service 2B.1.5
End If
' Service 2B.1.6
If BlackBerryState <> "0" Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.6", Customer, CustomerSub, objRecordSet.Fields("sAMAc countName" ).Value, objRecordSet.Fields("displ ayName").V alue, BlackBerryState, distinguisedPath, "", ""
ResultRow = ResultRow + 1
End If
' End Service 2B.1.6
Else
WriteOtherData OtherRow, strPeriod, AccountEnabled, "No Service", Customer, CustomerSub, objRecordSet.Fields("sAMAc countName" ).Value, objRecordSet.Fields("displ ayName").V alue, "User", distinguisedPath, "", ExcludeItem
OtherRow = OtherRow + 1
End If
End If
BlackBerryState = Null
ExchangeMailboxSize = Null
EVaultSize = Null
CustomerSub = Null
Customer = Null
aCustomer = Null
objRecordSet.MoveNext
Loop
objConnection.Close
Set objConnection = Nothing
Set objCommand = Nothing
End Sub
Sub MatchWorkplaceData (strDNSDomain,strDomain,bD omain)
'On Error Resume Next
Dim objConnection, objCommand, objRecordSet, strBase, strFilter, strAttributes, strQuery
Dim AccountEnabled, Customer, CustomerSub, ExcludeItem, ServiceItem, distinguisedID, distinguisedPath
' Make Connection with Active directory
Set objConnection = CreateObject("ADODB.Connec tion")
Set objCommand = CreateObject("ADODB.Comman d")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnectio n = objConnection
objCommand.Properties("Cha se referrals") = ADS_CHASE_REFERRALS_ALWAYS
objCommand.Properties("Pag e Size") = 10000
objCommand.Properties("Sea rchscope") = ADS_SCOPE_SUBTREE
'Define LDAP dns connection
strBase = "<LDAP://" & strDNSDomain & ">"
'Define the filter elements
strFilter = "(&(objectCategory=compute r)(objectC lass=compu ter))"
'List all attributes you will require
strAttributes = "Name, displayName, distinguishedName,userAcco untControl "
'compose query
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
Set objRecordSet = objCommand.Execute
If not objRecordSet.EOF Then
objRecordSet.MoveFirst
End If
Do Until objRecordSet.EOF
LogData "Workplace to be matched", "", "<" & objRecordSet.Fields("disti nguishedNa me").Value & ">"
AccountEnabled = CheckAccountEnabled(objRec ordSet.Fie lds("userA ccountCont rol").Valu e)
aCustomer = Split(GetCustomerValues(aW orkplaceDa taSheet,ob jRecordSet .Fields("d istinguish edName").V alue),";")
Customer = aCustomer(0)
CustomerSub = aCustomer(1)
ExcludeItem = aCustomer(2)
ServiceItem = aCustomer(3)
If InStr (objRecordSet.Fields("dist inguishedN ame").Valu e, ",CN") > 0 Then
distinguisedID = Left (objRecordSet.Fields("dist inguishedN ame").Valu e, InStr (objRecordSet.Fields("dist inguishedN ame").Valu e, ",CN") -1)
Else
distinguisedID = Left (objRecordSet.Fields("dist inguishedN ame").Valu e, InStr (objRecordSet.Fields("dist inguishedN ame").Valu e, ",OU") -1)
End If
distinguisedPath = Replace (objRecordSet.Fields("dist inguishedN ame").Valu e ,distinguisedID & ",", "" )
If ExcludeItem <> "2" Then
If Customer <> "0" AND ExcludeItem <> "1" Then
If ServiceItem <> "" Then
If ExcludeItem <> "3" Then
WriteResultData ResultRow, strPeriod, AccountEnabled, ServiceItem, Customer, CustomerSub, objRecordSet.Fields("Name" ).Value, strDomain, distinguisedPath, "", "", ""
ResultRow = ResultRow + 1
Else
WriteResultData ResultRow, strPeriod, "0", ServiceItem, Customer, CustomerSub, objRecordSet.Fields("Name" ).Value, strDomain, distinguisedPath, "", "", ""
ResultRow = ResultRow + 1
End If
Else
If ExcludeItem <> "3" Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.1", Customer, CustomerSub, objRecordSet.Fields("Name" ).Value, strDomain, distinguisedPath, "", "", ""
ResultRow = ResultRow + 1
Else
WriteResultData ResultRow, strPeriod, "0", "2B.1.1", Customer, CustomerSub, objRecordSet.Fields("Name" ).Value, strDomain, distinguisedPath, "", "", ""
ResultRow = ResultRow + 1
End If
End If
Else
WriteOtherData OtherRow, strPeriod, AccountEnabled, "No Service", Customer, CustomerSub, objRecordSet.Fields("Name" ).Value, strDomain, "Computer", distinguisedPath, "", ExcludeItem
OtherRow = OtherRow + 1
End If
End If
objRecordSet.MoveNext
Loop
objConnection.Close
Set objConnection = Nothing
Set objCommand = Nothing
End Sub
Sub MatchPublicFolderData
Dim aPFResult(), i, j, r, aLevel, aCustomer
Redim aPFResult(5,1)
r = 0
For i=0 to ubound(aPublic,2)
If aPublic(2,i) <> "" Then
aLevel = Split(aPublic(2,i), "/")
Logdata "Level" & ubound(aLevel)-1, i+1, aPublic(2,i)
aCustomer = Split(GetCustomerValues(aP ublicFolde rDataSheet ,aLevel(1) ), ";")
Select Case ubound(aLevel)
Case 2
' Write all level 1 Data
WriteResultData ResultRow, strPeriod, "1", "2B.3", aCustomer(0), aCustomer(1), aPublic(0,i), Round(aPublic(3,i),2), "0", "Public Folders", aPublic(2,i), ""
ResultRow = ResultRow + 1
Case 3
' Put all level 2 into array for counting
aPFResult(0,r) = aCustomer(0)
aPFResult(1,r) = aCustomer(1)
aPFResult(2,r) = aPublic(0,i)
aPFResult(3,r) = aPublic(2,i)
aPFResult(4,r) = aPublic(3,i)
ListArrayData aPFResult, 5, r
r = r + 1
Redim Preserve aPFResult(5,r)
Case Else
'Matching the rest (higher then level2) with level 2 items and sum the size in the array for counting total sizes
For j=0 to ubound(aPFResult,2)
If Instr(1,aPublic(2,i),aPFRe sult(3,j), 1) > 0 AND aPFResult(3,j) <> "" Then
Logdata "Before", i+1, "<" & aPFResult(4,j) & "><" & aPublic(2,i) & "><" & aPFResult(3,j) & "><" & aPublic(3,i) & ">"
aPFResult(4,j) = aPFResult(4,j) + aPublic(3,i)
Logdata "After", i+1, "<" & aPFResult(4,j) & "><" & aPublic(2,i) & "><" & aPFResult(3,j) & "><" & aPublic(3,i) & ">"
Exit For
End If
Next
End Select
End If
Next
For i=0 to ubound(aPFResult,2)
If aPFResult(2,i) <> "" Then
WriteResultData ResultRow, strPeriod, "1", "2B.3", aPFResult(0,i), aPFResult(1,i), aPFResult(2,i), Round(aPFResult(4,i),2), "0", "Public Folders", aPFResult(3,i), ""
ResultRow = ResultRow + 1
End If
Next
End Sub
' ************************** ********** ** Writing Output Sub routines and Funtions ************************** ********** **
Sub WriteResultData(wRow, wPeriod, wNumber, wService, wCustomer, wCustomerSub, wDetail1, wDetail2, wDetail3, wDetail4, wDetail5, wDetail6)
ExportDataSheet.cells(wRow ,1).value= wPeriod
ExportDataSheet.cells(wRow ,2).value= wNumber
ExportDataSheet.cells(wRow ,3).value= wService
ExportDataSheet.cells(wRow ,4).value= wCustomer
ExportDataSheet.cells(wRow ,5).value= wCustomerSub
ExportDataSheet.cells(wRow ,6).value= wDetail1
ExportDataSheet.cells(wRow ,7).value= wDetail2
ExportDataSheet.cells(wRow ,8).value= wDetail3
ExportDataSheet.cells(wRow ,9).value= wDetail4
ExportDataSheet.cells(wRow ,10).value = wDetail5
ExportDataSheet.cells(wRow ,11).value = wDetail6
Logdata "Written Result", wRow, wPeriod & ";" & wNumber & ";" & wService & ";" & wCustomer & ";" & wCustomerSub & ";" & wDetail1 & ";" & wDetail2 & ";" & wDetail3 & ";" & wDetail4 & ";" & wDetail5 & ";" & wDetail6
End Sub
Sub WriteOtherData(wRow, wPeriod, wNumber, wService, wCustomer, wCustomerSub, wDetail1, wDetail2, wDetail3, wDetail4, wDetail5, wDetail6)
OtherDataSheet.cells(wRow, 1).value= wPeriod
OtherDataSheet.cells(wRow, 2).value= wNumber
OtherDataSheet.cells(wRow, 3).value= wService
OtherDataSheet.cells(wRow, 4).value= wCustomer
OtherDataSheet.cells(wRow, 5).value= wCustomerSub
OtherDataSheet.cells(wRow, 6).value= wDetail1
OtherDataSheet.cells(wRow, 7).value= wDetail2
OtherDataSheet.cells(wRow, 8).value= wDetail3
OtherDataSheet.cells(wRow, 9).value= wDetail4
OtherDataSheet.cells(wRow, 10).value= wDetail5
OtherDataSheet.cells(wRow, 11).value= wDetail6
Logdata "Written Other", wRow, wPeriod & ";" & wNumber & ";" & wService & ";" & wCustomer & ";" & wCustomerSub & ";" & wDetail1 & ";" & wDetail2 & ";" & wDetail3 & ";" & wDetail4 & ";" & wDetail5 & ";" & wDetail6
End Sub
' ************************** ********** ** Array Sub routines and Funtions ************************** ********** **
Sub CreateArrayFromExcel(Sheet ,Array)
Dim z,R
z=0
R=1
Do Until Sheet.Cells(R,1).Value = ""
' Fill in Excel cells into array until first cell is empty
Array(0,z) = Sheet.cells(R,1).value
Array(1,z) = Sheet.cells(R,2).value
Array(2,z) = Sheet.cells(R,3).value
Array(3,z) = Sheet.cells(R,4).value
Array(4,z) = Sheet.cells(R,5).value
Array(5,z) = Sheet.cells(R,6).value
Array(6,z) = Sheet.cells(R,7).value
Array(7,z) = Sheet.cells(R,8).value
Array(8,z) = Sheet.cells(R,9).value
Array(9,z) = Sheet.cells(R,10).value
ListArrayData Array, 10, z
R = R + 1 ' Next Row
z=z+1
Redim Preserve Array(10,z+1) ' Rezize Array 10 x z+1, where z+1 will become size of Excel sheet.
Loop
End Sub
Sub GetExchangeUserData
Dim z, Count
z=0
For I=1 to 8
strComputer = "ITS300SRV"& CStr(401+i)
wscript.echo strComputer
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=imper sonate}!\\ " & strComputer & "\ROOT\MicrosoftExchangeV2 ")
Set colItems = objWMIService.ExecQuery ("Select * from Exchange_Mailbox")
Logdata "Exchange Server", I, strComputer
Count = ColItems.Count
LogData "Mailbox Count", "", z & " + " & Count & " = " & z + Count
For Each objItem in colItems
aExchange(0,z) = objItem.LegacyDN
aExchange(1,z) = objItem.MailboxGUID
aExchange(2,z) = objItem.MailboxDisplayName
aExchange(3,z) = Round(objItem.Size/1024,2)
aExchange(4,z) = objItem.TotalItems
aExchange(5,z) = objItem.LastLoggedOnUserAc count
ListArrayData aExchange, 6, z
z=z+1
Redim Preserve aExchange(6,z+1)
Next
Set colItems=Nothing
Set objWMIService=Nothing
Next
End Sub
Sub GetExchangePublicData
Dim z, objWMIService, colItems
z=0
' Public folders are on one server only, rest is replica
For I=1 to 1
strComputer = "ITS300SRV"& CStr(401+i)
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=imper sonate}!\\ " & strComputer & "\ROOT\MicrosoftExchangeV2 ")
Set colItems = objWMIService.ExecQuery ("Select * from Exchange_PublicFolder")
Logdata "Exchange Server", I, strComputer
For Each objItem in colItems
aPublic(0,z) = objItem.Name
aPublic(1,z) = objItem.Description
aPublic(2,z) = objItem.Path
aPublic(3,z) = objItem.TotalMessageSize/1 024/1024
aPublic(4,z) = objItem.MessageCount
aPublic(5,z) = objItem.HasChildren
aPublic(6,z) = objItem.FolderTree
aPublic(7,z) = ""
aPublic(8,z) = ""
aPublic(9,z) = ""
ListArrayData aPublic, 10, z
z=z+1
Redim Preserve aPublic(10,z+1)
Next
Set colItems=Nothing
Set objWMIService=Nothing
Next
End Sub
Sub GetEVaultUserData (EVFilePath, EVFileName)
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Dim z, objConnection, objRecordset, strPathtoTextFile
z=0
Set objConnection = CreateObject("ADODB.Connec tion")
Set objRecordSet = CreateObject("ADODB.Record set")
strPathtoTextFile = EVFilePath & "\"
objConnection.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & "Data Source=" & strPathtoTextFile & ";" & "Extended Properties=""text;HDR=NO;F MT=Delimit ed;Charact erSet=6500 1"""
objRecordset.Open "SELECT * FROM " & EVFileName, objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
aEVault(0,z) = objRecordset.Fields.Item(" ArchiveNam e")
aEVault(1,z) = objRecordset.Fields.Item(" BillingAcc ount")
aEVault(2,z) = objRecordset.Fields.Item(" TotalItems ")
If objRecordset.Fields.Item(" TotalSize" ) <> Null Then
aEVault(3,z) = Round(objRecordset.Fields. Item("Tota lSize"),2)
Else
aEVault(3,z) = objRecordset.Fields.Item(" TotalSize" )
End If
ListArrayData aEVault, 4, z
z=z+1
Redim Preserve aEVault(4,z+1)
objRecordset.MoveNext
Loop
objConnection.Close
Set objConnection = Nothing
Set objRecordset = Nothing
End Sub
Sub GetBBUserData (BBFilePath, BBFileName)
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Dim z, objConnection, objRecordset, strPathtoTextFile
z=0
Set objConnection = CreateObject("ADODB.Connec tion")
Set objRecordSet = CreateObject("ADODB.Record set")
strPathtoTextFile = BBFilePath & "\"
objConnection.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & "Data Source=" & strPathtoTextFile & ";" & "Extended Properties=""text;HDR=NO;F MT=Delimit ed"""
objRecordset.Open "SELECT * FROM " & BBFileName, objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
aBlackBerry(0,z) = objRecordset.Fields.Item(" DisplayNam e")
aBlackBerry(1,z) = objRecordset.Fields.Item(" PIN")
aBlackBerry(2,z) = objRecordset.Fields.Item(" EnabledSta te")
aBlackBerry(3,z) = objRecordset.Fields.Item(" Forwarded" )
aBlackBerry(4,z) = objRecordset.Fields.Item(" SentFromDe vice")
aBlackBerry(5,z) = objRecordset.Fields.Item(" PendingToD evice")
aBlackBerry(6,z) = objRecordset.Fields.Item(" Expired")
aBlackBerry(7,z) = objRecordset.Fields.Item(" Filtered")
aBlackBerry(8,z) = objRecordset.Fields.Item(" MailboxDN" )
aBlackBerry(9,z) = ""
ListArrayData aBlackBerry, 10, z
z=z+1
Redim Preserve aBlackBerry(10,z+1)
objRecordset.MoveNext
Loop
objConnection.Close
Set objConnection = Nothing
Set objRecordset = Nothing
End Sub
Function GetACL(strDNSDomain, distinguishedName, sAMAccountName, msExchOmaAdminWirelessEnab le, HomeMDB, legacyExchangeDN, displayName)
Dim objUser, oSecurityDescriptor, dacl, ace, ACLResult
ACLResult = ""
On Error Resume Next
' Bind to each User.
Set objUser = GetObject("LDAP://" & strDNSDomain & "/" & distinguishedName)
' Read the Mailbox Security Descriptor
Set oSecurityDescriptor = objuser.Get("msExchMailbox SecurityDe scriptor")
If Err.Number = 0 Then
' Pull out the DACL for reading.
Set dacl = oSecurityDescriptor.Discre tionaryAcl
Set ace = CreateObject("AccessContro lEntry")
For Each ace In dacl
'Display properties of the ACEs which identify the "Associated External Account"
If ace.AccessMask And ASSOCIATED_EXTERNAL then
' Line for Debugging. Reports all properties for troubleshooting
' wscript.echo ace.Trustee & ", " & ace.AccessMask & ", " & ace.AceType & ", " & ace.AceFlags & ", " & ace.Flags & ", " & ace.ObjectType & ", " & ace.InheritedObjectType & " END" & vbcrlf
aACL(0,ACLRow) = distinguishedName
aACL(1,ACLRow) = sAMAccountName
aACL(2,ACLRow) = ace.Trustee
aACL(3,ACLRow) = msExchOmaAdminWirelessEnab le
aACL(4,ACLRow) = HomeMDB
aACL(5,ACLRow) = legacyExchangeDN
aACL(6,ACLRow) = displayName
aACL(7,ACLRow) = ""
aACL(8,ACLRow) = ""
aACL(9,ACLRow) = ""
If ACLResult = "" Then
ACLResult = ace.Trustee
Else
ACLResult = ACLResult & "|" & ace.Trustee
End If
ListArrayData aACL, 10, ACLRow
ACLRow = ACLRow + 1
Redim Preserve aACL(10,ACLRow+1)
End If
Next
End If
On Error Goto 0
GetACL = ACLResult
' Cleanup Variables
Set objUser = Nothing
End Function
' ************************** ********** ** Search and Match Sub routines and Funtions ************************** ********** **
Function GetExchangeTrustee(sAMAcco untName)
Dim Match, x
Match = -1
For x=0 to ubound(aACL,2)
If Instr(1,aACL(2,x),sAMAccou ntName,1) > 0 Then
Match = x
Logdata "Exchange Trustee", x+1, "<" & aACL(1,x) & ">:<" & sAMAccountName & ">"
Exit For
End If
Next
If Match = -1 Then
GetExchangeTrustee = ";;;;;"
Else
GetExchangeTrustee = aACL(0,Match) & ";" & aACL(1,Match) & ";" & aACL(3,Match) & ";" & aACL(4,Match) & ";" & aACL(5,Match) & ";" & aACL(6,Match)
End If
End Function
Function GetExchangeMailboxSize(leg acyExchang eDN)
Dim Match, x
Match = -1
For x=0 to ubound(aExchange,2)
If Instr(1,aExchange(0,x),leg acyExchang eDN,1) > 0 Then
Match = x
Logdata "Exchange Mailbox", x+1, "<" & aExchange(0,x) & ">:<" & legacyExchangeDN & ">"
Exit For
End If
Next
If Match = -1 Then
GetExchangeMailboxSize = 0
Else
GetExchangeMailboxSize = aExchange(3,Match)
End If
End Function
Function GetEVaultSize(sAMAccountNa me,display Name)
Dim Match, x
Match = -1
' Begin at 3th row, above that is header and can give mismatch
For x=3 to ubound(aEVault,2)
If Instr(1,aEVault(1,x),sAMAc countName, 1) > 0 Then
Match = x
Logdata "EVault Archive", x-2, "<" & aEVault(1,x) & ">:<" & sAMAccountName & ">:<" & aEVault(0,x) & ">:Level 1 Match"
Exit For
Else
If Instr(1,aEVault(0,x),displ ayName,1) > 0 AND Instr(1,aEVault(1,x),"\",1 ) = 0 AND aEVault(1,x) <> "" Then
Match = x
Logdata "EVault Archive", x-2, "<" & aEVault(0,x) & ">:<" & displayName & ">:<" & aEVault(1,x) & ">:Level 2 Match"
Exit For
End If
End If
Next
If Match = -1 Then
GetEVaultSize = 0
Else
GetEVaultSize = aEVault(3,Match)
End If
End Function
Function GetBlackBerryState(legacyE xchangeDN)
Dim Match, x
Match = -1
For x=2 to ubound(aBlackBerry,2)
If Instr(1,aBlackBerry(8,x),l egacyExcha ngeDN,1) > 0 Then
Match = x
Logdata "Blackberry", x-1, "<" & aBlackBerry(8,x) & ">:<" & legacyExchangeDN & ">"
Exit For
End If
Next
If Match = -1 Then
GetBlackBerryState = "0"
Else
GetBlackBerryState = aBlackBerry(2,Match)
End If
End Function
Function GetCustomerValues(Array,di stinguishe dName)
Dim Match, x, Exclude
Match = -1
Exclude = 0
For x=0 to ubound(Array,2)
'Logdata "Customer to be matched", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">:<" & Array(4,x) & ">"
If Instr(1, distinguishedName, Array(5,x), 1) > 0 AND Array(5,x) <> "" Then
Select Case Array(4,x)
Case "Exclude"
Match = x
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">;Exclude Match"
Exclude = 1
Exit For
Case "Skip"
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">;Skip Match"
Exclude = 2
Exit For
Case "Reset"
Match = x
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">;Reset Match"
Exclude = 3
Exit For
Case ""
Match = x
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">;Level 1 Match"
Exit For
Case Else
If Instr(1, distinguishedName, Array(4,x), 1) > 0 AND Array(5,x) <> "" Then
Match = x
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & "><" & Array(4,x) & ">;level 2 Match"
Exit For
End If
End Select
End If
Next
' Customer ; Customer Sub Code ; Exclude based on table match=Exclude ; Service Code based on table service
If Match = -1 Then
GetCustomerValues = 0 & ";" & 0 & ";" & Exclude & ";" & 0
Else
GetCustomerValues = Array(0,Match) & ";" & Array(2,Match) & ";" & Exclude & ";" & Array(6,Match)
End If
End Function
Function CheckAccountEnabled(userAc countContr ol)
' Check if account is enabled, this is the second bit
intUAC = userAccountControl
If intUAC > 1 Then
binUAC = toBin(intUAC)
If inStr(Len(binUAC)-1,binUAC ,"1",1) = Len(binUAC)-1 Then
' Account is disabled
CheckAccountEnabled = "0"
Else
' Account is enabled
CheckAccountEnabled = "1"
End If
Else
' Account is enabled
CheckAccountEnabled = "1"
End If
End Function
Function CheckCitrixEnabled(strDNSD omain, distinguishedName)
On Error Resume Next
Dim Match, x, objUser, aGroups
Match = -1
Set objUser = GetObject("LDAP://" & strDNSDomain & "/" & distinguishedName)
aGroups = objuser.GetEx("memberOf")
If (Err.Number <> 0) Then
On Error GoTo 0
Logdata "No Groups", "", ""
Else
On Error GoTo 0
For Each group in aGroups
For x=1 to ubound(aCitrixDataSheet,2)
If Instr(1,group,aCitrixDataS heet(5,x), 1) > 0 AND aCitrixDataSheet(5,x) <> "" Then
Match = x
Logdata "Citrix Match", x, "<" & group & ">:<" & aCitrixDataSheet(5,x) & ">"
Exit For
End If
Next
Next
End If
If Match = -1 Then
CheckCitrixEnabled = False
Else
CheckCitrixEnabled = True
End If
Set objUser = Nothing
End Function
' ************************** ********** ** List, Log and other Sub routines and Funtions ************************** ********** **
Sub ListArrayData(Array, ArraySize, Index)
Dim x
OutputLine = Array(0,Index)
For x=1 to ArraySize
OutputLine = OutputLine & ";" & Array(x,Index)
Next
Logdata "List Row", Index+1, OutputLine
End Sub
Sub LogData(Name, Index, Data)
If LogOutPut = True Then
Wscript.echo Name & " ->" & Index & ";" & Time() & ";" & Data
End If
End Sub
Sub CopyFile(FileNamePath, FileNameSearch, FileNameResult, DeleteOriginal)
On Error Resume Next
Dim objFolder, objFile
Set objFolder = objFSO.GetFolder(FileNameP ath)
For Each objFile In objFolder.files
If Instr(objFile, FileNameSearch) > 0 Then
objFSO.CopyFile objFile, FileNamePath & "\" & FileNameResult, OverwriteExisting
If DeleteOriginal = True Then
objFSO.DeleteFile objFile, True
Logdata "File Moved", "1", objFile
Else
Logdata "File Copied", "1", objFile
End If
End If
Next
Set objFolder = Nothing
Set objFile = Nothing
End Sub
Function toBin(aInt)
If aInt > 0 Then toBin = toBin(aInt\2) & (aInt Mod 2)
End function
It is a part of a script what I created in Vbscript and i would like to convert in Powershell but I am not so good in powershell:
Hereby the script:
' **************************
'Option Explicit
'Generate Console Output = False or True
Public LogOutput
LogOutPut = True
Dim ParentDIR, TemplateFileName, ResultFileName, EVFilenName
Dim strDigipass, strFirstpart, ldapValuefortoken
Dim Period, M, strPeriod, d
Dim objExcel, objTemplateWorkbook, objResultWorkbook, objFSO, objToken, objOtherWorkbook
Dim ExportDataDefinitionSheet,
Public ResultRow, OtherRow, ACLRow
ResultRow = 2
OtherRow = 2
ACLRow = 0
' Define Public Dynamic Excel Arrays (2 dimensions -> 10 x N)
Public aExportDataDefinitionSheet
Redim aExportDataDefinitionSheet
' Define Public Dynamic Exchange and EVault Arrays (2 dimensions -> X x N)
Public aExchange(), aPublic(), aAD(), aEVault(), aBlackBerry(), aACL()
Redim aExchange(6,1), aPublic(10,1), aAD(8,1), aEVault(4,1), aBlackBerry (10,1), aACL(10,1)
' Constants
Const ADS_SCOPE_SUBTREE = 2
Const ADS_SCOPE_ONELEVEL = 1
Const ADS_CHASE_REFERRALS_ALWAYS
Const gcForReading = 1
Const gcForWriting = 2
Const gcForAppending = 8
Const TristateUseDefault = -2
Const OverwriteExisting = TRUE
CONST ASSOCIATED_EXTERNAL = &h4
' General Objects
Set objFSO = CreateObject("Scripting.Fi
Set objExcel = WScript.CreateObject ("Excel.Application")
' Other Variables
ParentDIR = objFSO.GetParentFolderName
TemplateFileName = ParentDIR & "\Template.xls" ' Name of the reference template excel file.
Period = UCase (MonthName (Left (Date ,(InStr (Date , "/") -1 )), True )) & " " & Right (Date , 4)
M = Month(Date)
ResultFileName = ParentDIR & "\Result-" & Period & ".xls"' Output Excel file Name appended with the current English Month and Year
OtherFileName = ParentDIR & "\Other-" & Period & ".xls"' Output Excel file Name appended with the current English Month and Year
CopyFile ParentDIR , "EV_VN_MailboxVS01", "EnterpriseVault.tab", True
CopyFile ParentDIR , "FatClient_BlackBerry_Expo
' **************************
' Open Excel Template file
Set objTemplateWorkbook = objExcel.Workbooks.Open(Te
objExcel.Visible = False
objExcel.DisplayAlerts=Fal
' Define Template Worksheets into variables
Set ExportDataDefinitionSheet = objTemplateWorkbook.WorkSh
Set DomainTableSheet = objTemplateWorkbook.WorkSh
Set UserDataSheet = objTemplateWorkbook.WorkSh
Set PublicFolderDataSheet = objTemplateWorkbook.WorkSh
Set WorkplaceDataSheet = objTemplateWorkbook.WorkSh
Set PerionDefinitionSheet = objTemplateWorkbook.WorkSh
Set CitrixDataSheet = objTemplateWorkbook.WorkSh
' Open and save Excel Template file
Set objResultWorkbook = objExcel.Workbooks.Add()
objResultWorkbook.SaveAs(R
' Rename and define Result Worksheets into variables
objResultWorkbook.WorkShee
Set ExportDataSheet = objResultWorkbook.WorkShee
Set objOtherWorkbook = objExcel.Workbooks.Add()
objOtherWorkbook.SaveAs(Ot
' Rename and define Result Worksheets into variables
objOtherWorkbook.WorkSheet
Set OtherDataSheet = objOtherWorkbook.WorkSheet
' Create the current Month & Year as noted in period definition sheet in Template.xls as Dutch output
strPeriod = "'" & UCase(Left(PerionDefinitio
' Read Excel data into array for matching patterns (Arrays are build from 0 -> 9, where excel rows are build from 1 -> 10)
Logdata "Read Domain Table", "", ""
CreateArrayFromExcel DomainTableSheet, aDomainTableSheet
Logdata "Read User Data Table", "", ""
CreateArrayFromExcel UserDataSheet, aUserDataSheet
Logdata "Read Public Folder Table", "", ""
CreateArrayFromExcel PublicFolderDataSheet, aPublicFolderDataSheet
Logdata "Read Workplace data Table", "", ""
CreateArrayFromExcel WorkplaceDataSheet, aWorkplaceDataSheet
Logdata "Read Citrix Definition Table", "", ""
CreateArrayFromExcel CitrixDataSheet, aCitrixDataSheet
' Select and copy first template sheets row to output OtherDataSheet
ExportDataDefinitionSheet.
ExportDataDefinitionSheet.
objExcel.Selection.Copy()
' Paste first template sheets row to output OtherDataSheet
OtherDataSheet.Activate()
OtherDataSheet.Rows.Item(1
OtherDataSheet.Paste()
OtherDataSheet.Range("A1")
' Select and copy first template sheets row to output ExportDataSheet
ExportDataDefinitionSheet.
ExportDataDefinitionSheet.
objExcel.Selection.Copy()
' Paste first template sheets row to output ExportDataSheet
ExportDataSheet.Activate()
ExportDataSheet.Rows.Item(
ExportDataSheet.Paste()
ExportDataSheet.Range("A1"
' Closing Template workbook as precaution
objTemplateWorkbook.Close
' Reading Exchange and Enterprise Vault data into arrays for matching patterns (Arrays are build from 0 -> N-1, where N is the size)
LogData "Reading Exchange User Data", "", ""
GetExchangeUserData
LogData "Reading Enterprise Vault Data", "", ""
GetEVaultUserData ParentDIR, "EnterpriseVault.tab"
LogData "BlackBerry Data", "", ""
GetBBUserData ParentDIR, "BlackBerry.tab"
LogData "Reading Exchange Public Data", "", ""
GetExchangePublicData
' Get domaindata from domains in the template.xls "domein-tabel", only match domains with include=1
For d=1 to ubound(aDomainTableSheet,2
Select Case aDomainTableSheet(0,d)
Case 1
MatchUserData aDomainTableSheet(2,d), aDomainTableSheet(1,d), 1 ' Generate User data per domain
MatchWorkplaceData aDomainTableSheet(2,d), aDomainTableSheet(1,d), 1 ' Generate Workplace data per domain
Case 2
MatchUserData aDomainTableSheet(2,d), aDomainTableSheet(1,d), 2 ' Generate User data per domain
MatchWorkplaceData aDomainTableSheet(2,d), aDomainTableSheet(1,d), 2 ' Generate Workplace data per domain
End Select
Next
' Generata Public Exchange data
MatchPublicFolderData
' Closing script
objExcel.Save
objResultWorkbook.Close
objOtherWorkbook.Close
objExcel.Quit
Set objFSO = Nothing
Set objExcel = Nothing
' **************************
Sub MatchUserData (strDNSDomain,strDomain,bD
'On Error Resume Next
Dim objConnection, objCommand, objRecordSet, strBase, strFilter, strAttributes, strQuery
Dim AccountEnabled, Customer, CustomerSub, ExcludeItem, ServiceItem, ExchangeMailboxSize, EVaultSize, BlackBerryState, StrVPNToken, StrVPNTokenPath, intOMA, binOMA, distinguisedID, distinguisedPath
Dim strTrustee, RefdistinguishedName, RefsAMAccountName, RefmsintOMA, RefHomeMDB, ReflegacyExchangeDN, RefdisplayName
' Make Connection with Active directory
Set objConnection = CreateObject("ADODB.Connec
Set objCommand = CreateObject("ADODB.Comman
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnectio
objCommand.Properties("Cha
objCommand.Properties("Pag
objCommand.Properties("Sea
'Define LDAP dns connection
strBase = "<LDAP://" & strDNSDomain & ">"
'Define the filter elements
strFilter = "(&(objectCategory=person)
'List all attributes you will require
' At the user to the trustee array if it has an trustee
If bDomain = 1 Then
strAttributes = "Name,distinguishedName,sA
Else
strAttributes = "Name,distinguishedName,sA
End If
'compose query
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
Set objRecordSet = objCommand.Execute
If not objRecordSet.EOF Then
objRecordSet.MoveFirst
End If
Do Until objRecordSet.EOF
'Create variables of current user
LogData "User to be matched", "", "<" & objRecordSet.Fields("disti
'Check if account is enabled
AccountEnabled = CheckAccountEnabled(objRec
' Get Customer values
aCustomer = Split(GetCustomerValues(aU
Customer = aCustomer(0)
CustomerSub = aCustomer(1)
ExcludeItem = aCustomer(2)
ServiceItem = aCustomer(3)
'Strip down the current account distinguishedName to path without account
If InStr (objRecordSet.Fields("dist
distinguisedID = Left (objRecordSet.Fields("dist
Else
distinguisedID = Left (objRecordSet.Fields("dist
End If
distinguisedPath = Replace (objRecordSet.Fields("dist
' Get the account info to be referenced for Exchange
If bDomain = 1 Then
RefdistinguishedName = objRecordSet.Fields("disti
RefsAMAccountName = objRecordSet.Fields("sAMAc
RefmsintOMA = objRecordSet.Fields("msExc
RefHomeMDB = objRecordSet.Fields("HomeM
ReflegacyExchangeDN = objRecordSet.Fields("legac
RefdisplayName = objRecordSet.Fields("displ
' At the user to the trustee array, if it has an trustee intTrustee will be 1
strTrustee = GetACL(strDNSDomain, RefdistinguishedName, RefsAMAccountName, RefmsintOMA, RefHomeMDB, ReflegacyExchangeDN, RefdisplayName)
If strTrustee <> "" AND AccountEnabled = "1" Then
WriteOtherData OtherRow, strPeriod, AccountEnabled, "2B.3", Customer, CustomerSub, objRecordSet.Fields("sAMAc
OtherRow = OtherRow + 1
End If
Else
aTrustee = Split(GetExchangeTrustee(s
If aTrustee(1) <> "" Then
RefdistinguishedName = aTrustee(0)
RefsAMAccountName = aTrustee(1)
RefmsintOMA = aTrustee(2)
RefHomeMDB = aTrustee(3)
ReflegacyExchangeDN = aTrustee(4)
RefdisplayName = aTrustee(5)
WriteOtherData OtherRow, strPeriod, AccountEnabled, "2B.3", Customer, CustomerSub, objRecordSet.Fields("sAMAc
OtherRow = OtherRow + 1
End If
End If
' Get values with reference accounts info
ExchangeMailboxSize = GetExchangeMailboxSize(Ref
EVaultSize = GetEVaultSize(RefsAMAccoun
BlackBerryState = GetBlackBerryState(Reflega
' Check if item is not "Skip"
If ExcludeItem <> "2" Then
' Check if there is customer info found and the item is not "Exlcuded"
If Customer <> "0" AND ExcludeItem <> "1" Then
wscript.echo "ExchangeMailboxsize: " & ExchangeMailboxSize
wscript.echo "EvaultSize: " & EvaultSize
' Service 2B.3.1 for Users
If (ExchangeMailboxSize <= 500) Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.3.1", Customer, CustomerSub, objRecordSet.Fields("displ
ResultRow = ResultRow + 1
End If
' End Service 2B.3.1 for Users
' Service 2B.3.2 for Users
If (ExchangeMailboxSize <= 2000) Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.3.2", Customer, CustomerSub, objRecordSet.Fields("displ
ResultRow = ResultRow + 1
End If
' End Service 2B.3.2 for Users
If TypeName(objRecordSet.Fiel
' Service 2B.5
For Each VPNToken in objRecordSet.Fields("vasco
LogData "VPN Token", "", VPNToken
StrVPNToken = Left (VPNToken , InStr (VPNToken , ",") -1)
strVPNTokenPath = Replace (VPNToken ,StrVPNToken&"," , "" )
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.5", Customer, CustomerSub, objRecordSet.Fields("sAMAc
ResultRow = ResultRow + 1
StrVPNToken = Null
strVPNTokenPath = Null
Next
' End Service 2B.5
' Service 2B.1.4
If CheckCitrixEnabled(strDNSD
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.4", Customer, CustomerSub, objRecordSet.Fields("sAMAc
ResultRow = ResultRow + 1
End If
' End Service 2B.1.4
VPNToken = Null
Else
' Service 2B.1.4
If CheckCitrixEnabled(strDNSD
WriteOtherData OtherRow, strPeriod, AccountEnabled, "2B.1.4", Customer, CustomerSub, objRecordSet.Fields("sAMAc
OtherRow = OtherRow + 1
End If
' End Service 2B.1.4
End If
If RefHomeMDB <> "" Then
' Service 2B.1.5
wscript.echo RefdistinguishedName
binOMA = toBin(RefmsintOMA)
Logdata "OMA", "", "int:<" & RefmsintOMA & "> bin:<" & binOMA & ">"
If RefmsintOMA > 1 Then
If inStr(Len(binOMA)-1,binOMA
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.5", Customer, CustomerSub, objRecordSet.Fields("sAMAc
ResultRow = ResultRow + 1
End If
Else
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.5", Customer, CustomerSub, objRecordSet.Fields("sAMAc
ResultRow = ResultRow + 1
End If
' End Service 2B.1.5
End If
' Service 2B.1.6
If BlackBerryState <> "0" Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.6", Customer, CustomerSub, objRecordSet.Fields("sAMAc
ResultRow = ResultRow + 1
End If
' End Service 2B.1.6
Else
WriteOtherData OtherRow, strPeriod, AccountEnabled, "No Service", Customer, CustomerSub, objRecordSet.Fields("sAMAc
OtherRow = OtherRow + 1
End If
End If
BlackBerryState = Null
ExchangeMailboxSize = Null
EVaultSize = Null
CustomerSub = Null
Customer = Null
aCustomer = Null
objRecordSet.MoveNext
Loop
objConnection.Close
Set objConnection = Nothing
Set objCommand = Nothing
End Sub
Sub MatchWorkplaceData (strDNSDomain,strDomain,bD
'On Error Resume Next
Dim objConnection, objCommand, objRecordSet, strBase, strFilter, strAttributes, strQuery
Dim AccountEnabled, Customer, CustomerSub, ExcludeItem, ServiceItem, distinguisedID, distinguisedPath
' Make Connection with Active directory
Set objConnection = CreateObject("ADODB.Connec
Set objCommand = CreateObject("ADODB.Comman
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnectio
objCommand.Properties("Cha
objCommand.Properties("Pag
objCommand.Properties("Sea
'Define LDAP dns connection
strBase = "<LDAP://" & strDNSDomain & ">"
'Define the filter elements
strFilter = "(&(objectCategory=compute
'List all attributes you will require
strAttributes = "Name, displayName, distinguishedName,userAcco
'compose query
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
Set objRecordSet = objCommand.Execute
If not objRecordSet.EOF Then
objRecordSet.MoveFirst
End If
Do Until objRecordSet.EOF
LogData "Workplace to be matched", "", "<" & objRecordSet.Fields("disti
AccountEnabled = CheckAccountEnabled(objRec
aCustomer = Split(GetCustomerValues(aW
Customer = aCustomer(0)
CustomerSub = aCustomer(1)
ExcludeItem = aCustomer(2)
ServiceItem = aCustomer(3)
If InStr (objRecordSet.Fields("dist
distinguisedID = Left (objRecordSet.Fields("dist
Else
distinguisedID = Left (objRecordSet.Fields("dist
End If
distinguisedPath = Replace (objRecordSet.Fields("dist
If ExcludeItem <> "2" Then
If Customer <> "0" AND ExcludeItem <> "1" Then
If ServiceItem <> "" Then
If ExcludeItem <> "3" Then
WriteResultData ResultRow, strPeriod, AccountEnabled, ServiceItem, Customer, CustomerSub, objRecordSet.Fields("Name"
ResultRow = ResultRow + 1
Else
WriteResultData ResultRow, strPeriod, "0", ServiceItem, Customer, CustomerSub, objRecordSet.Fields("Name"
ResultRow = ResultRow + 1
End If
Else
If ExcludeItem <> "3" Then
WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.1", Customer, CustomerSub, objRecordSet.Fields("Name"
ResultRow = ResultRow + 1
Else
WriteResultData ResultRow, strPeriod, "0", "2B.1.1", Customer, CustomerSub, objRecordSet.Fields("Name"
ResultRow = ResultRow + 1
End If
End If
Else
WriteOtherData OtherRow, strPeriod, AccountEnabled, "No Service", Customer, CustomerSub, objRecordSet.Fields("Name"
OtherRow = OtherRow + 1
End If
End If
objRecordSet.MoveNext
Loop
objConnection.Close
Set objConnection = Nothing
Set objCommand = Nothing
End Sub
Sub MatchPublicFolderData
Dim aPFResult(), i, j, r, aLevel, aCustomer
Redim aPFResult(5,1)
r = 0
For i=0 to ubound(aPublic,2)
If aPublic(2,i) <> "" Then
aLevel = Split(aPublic(2,i), "/")
Logdata "Level" & ubound(aLevel)-1, i+1, aPublic(2,i)
aCustomer = Split(GetCustomerValues(aP
Select Case ubound(aLevel)
Case 2
' Write all level 1 Data
WriteResultData ResultRow, strPeriod, "1", "2B.3", aCustomer(0), aCustomer(1), aPublic(0,i), Round(aPublic(3,i),2), "0", "Public Folders", aPublic(2,i), ""
ResultRow = ResultRow + 1
Case 3
' Put all level 2 into array for counting
aPFResult(0,r) = aCustomer(0)
aPFResult(1,r) = aCustomer(1)
aPFResult(2,r) = aPublic(0,i)
aPFResult(3,r) = aPublic(2,i)
aPFResult(4,r) = aPublic(3,i)
ListArrayData aPFResult, 5, r
r = r + 1
Redim Preserve aPFResult(5,r)
Case Else
'Matching the rest (higher then level2) with level 2 items and sum the size in the array for counting total sizes
For j=0 to ubound(aPFResult,2)
If Instr(1,aPublic(2,i),aPFRe
Logdata "Before", i+1, "<" & aPFResult(4,j) & "><" & aPublic(2,i) & "><" & aPFResult(3,j) & "><" & aPublic(3,i) & ">"
aPFResult(4,j) = aPFResult(4,j) + aPublic(3,i)
Logdata "After", i+1, "<" & aPFResult(4,j) & "><" & aPublic(2,i) & "><" & aPFResult(3,j) & "><" & aPublic(3,i) & ">"
Exit For
End If
Next
End Select
End If
Next
For i=0 to ubound(aPFResult,2)
If aPFResult(2,i) <> "" Then
WriteResultData ResultRow, strPeriod, "1", "2B.3", aPFResult(0,i), aPFResult(1,i), aPFResult(2,i), Round(aPFResult(4,i),2), "0", "Public Folders", aPFResult(3,i), ""
ResultRow = ResultRow + 1
End If
Next
End Sub
' **************************
Sub WriteResultData(wRow, wPeriod, wNumber, wService, wCustomer, wCustomerSub, wDetail1, wDetail2, wDetail3, wDetail4, wDetail5, wDetail6)
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
ExportDataSheet.cells(wRow
Logdata "Written Result", wRow, wPeriod & ";" & wNumber & ";" & wService & ";" & wCustomer & ";" & wCustomerSub & ";" & wDetail1 & ";" & wDetail2 & ";" & wDetail3 & ";" & wDetail4 & ";" & wDetail5 & ";" & wDetail6
End Sub
Sub WriteOtherData(wRow, wPeriod, wNumber, wService, wCustomer, wCustomerSub, wDetail1, wDetail2, wDetail3, wDetail4, wDetail5, wDetail6)
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
OtherDataSheet.cells(wRow,
Logdata "Written Other", wRow, wPeriod & ";" & wNumber & ";" & wService & ";" & wCustomer & ";" & wCustomerSub & ";" & wDetail1 & ";" & wDetail2 & ";" & wDetail3 & ";" & wDetail4 & ";" & wDetail5 & ";" & wDetail6
End Sub
' **************************
Sub CreateArrayFromExcel(Sheet
Dim z,R
z=0
R=1
Do Until Sheet.Cells(R,1).Value = ""
' Fill in Excel cells into array until first cell is empty
Array(0,z) = Sheet.cells(R,1).value
Array(1,z) = Sheet.cells(R,2).value
Array(2,z) = Sheet.cells(R,3).value
Array(3,z) = Sheet.cells(R,4).value
Array(4,z) = Sheet.cells(R,5).value
Array(5,z) = Sheet.cells(R,6).value
Array(6,z) = Sheet.cells(R,7).value
Array(7,z) = Sheet.cells(R,8).value
Array(8,z) = Sheet.cells(R,9).value
Array(9,z) = Sheet.cells(R,10).value
ListArrayData Array, 10, z
R = R + 1 ' Next Row
z=z+1
Redim Preserve Array(10,z+1) ' Rezize Array 10 x z+1, where z+1 will become size of Excel sheet.
Loop
End Sub
Sub GetExchangeUserData
Dim z, Count
z=0
For I=1 to 8
strComputer = "ITS300SRV"& CStr(401+i)
wscript.echo strComputer
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=imper
Set colItems = objWMIService.ExecQuery ("Select * from Exchange_Mailbox")
Logdata "Exchange Server", I, strComputer
Count = ColItems.Count
LogData "Mailbox Count", "", z & " + " & Count & " = " & z + Count
For Each objItem in colItems
aExchange(0,z) = objItem.LegacyDN
aExchange(1,z) = objItem.MailboxGUID
aExchange(2,z) = objItem.MailboxDisplayName
aExchange(3,z) = Round(objItem.Size/1024,2)
aExchange(4,z) = objItem.TotalItems
aExchange(5,z) = objItem.LastLoggedOnUserAc
ListArrayData aExchange, 6, z
z=z+1
Redim Preserve aExchange(6,z+1)
Next
Set colItems=Nothing
Set objWMIService=Nothing
Next
End Sub
Sub GetExchangePublicData
Dim z, objWMIService, colItems
z=0
' Public folders are on one server only, rest is replica
For I=1 to 1
strComputer = "ITS300SRV"& CStr(401+i)
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=imper
Set colItems = objWMIService.ExecQuery ("Select * from Exchange_PublicFolder")
Logdata "Exchange Server", I, strComputer
For Each objItem in colItems
aPublic(0,z) = objItem.Name
aPublic(1,z) = objItem.Description
aPublic(2,z) = objItem.Path
aPublic(3,z) = objItem.TotalMessageSize/1
aPublic(4,z) = objItem.MessageCount
aPublic(5,z) = objItem.HasChildren
aPublic(6,z) = objItem.FolderTree
aPublic(7,z) = ""
aPublic(8,z) = ""
aPublic(9,z) = ""
ListArrayData aPublic, 10, z
z=z+1
Redim Preserve aPublic(10,z+1)
Next
Set colItems=Nothing
Set objWMIService=Nothing
Next
End Sub
Sub GetEVaultUserData (EVFilePath, EVFileName)
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Dim z, objConnection, objRecordset, strPathtoTextFile
z=0
Set objConnection = CreateObject("ADODB.Connec
Set objRecordSet = CreateObject("ADODB.Record
strPathtoTextFile = EVFilePath & "\"
objConnection.Open "Provider=Microsoft.Jet.OL
objRecordset.Open "SELECT * FROM " & EVFileName, objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
aEVault(0,z) = objRecordset.Fields.Item("
aEVault(1,z) = objRecordset.Fields.Item("
aEVault(2,z) = objRecordset.Fields.Item("
If objRecordset.Fields.Item("
aEVault(3,z) = Round(objRecordset.Fields.
Else
aEVault(3,z) = objRecordset.Fields.Item("
End If
ListArrayData aEVault, 4, z
z=z+1
Redim Preserve aEVault(4,z+1)
objRecordset.MoveNext
Loop
objConnection.Close
Set objConnection = Nothing
Set objRecordset = Nothing
End Sub
Sub GetBBUserData (BBFilePath, BBFileName)
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Dim z, objConnection, objRecordset, strPathtoTextFile
z=0
Set objConnection = CreateObject("ADODB.Connec
Set objRecordSet = CreateObject("ADODB.Record
strPathtoTextFile = BBFilePath & "\"
objConnection.Open "Provider=Microsoft.Jet.OL
objRecordset.Open "SELECT * FROM " & BBFileName, objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
aBlackBerry(0,z) = objRecordset.Fields.Item("
aBlackBerry(1,z) = objRecordset.Fields.Item("
aBlackBerry(2,z) = objRecordset.Fields.Item("
aBlackBerry(3,z) = objRecordset.Fields.Item("
aBlackBerry(4,z) = objRecordset.Fields.Item("
aBlackBerry(5,z) = objRecordset.Fields.Item("
aBlackBerry(6,z) = objRecordset.Fields.Item("
aBlackBerry(7,z) = objRecordset.Fields.Item("
aBlackBerry(8,z) = objRecordset.Fields.Item("
aBlackBerry(9,z) = ""
ListArrayData aBlackBerry, 10, z
z=z+1
Redim Preserve aBlackBerry(10,z+1)
objRecordset.MoveNext
Loop
objConnection.Close
Set objConnection = Nothing
Set objRecordset = Nothing
End Sub
Function GetACL(strDNSDomain, distinguishedName, sAMAccountName, msExchOmaAdminWirelessEnab
Dim objUser, oSecurityDescriptor, dacl, ace, ACLResult
ACLResult = ""
On Error Resume Next
' Bind to each User.
Set objUser = GetObject("LDAP://" & strDNSDomain & "/" & distinguishedName)
' Read the Mailbox Security Descriptor
Set oSecurityDescriptor = objuser.Get("msExchMailbox
If Err.Number = 0 Then
' Pull out the DACL for reading.
Set dacl = oSecurityDescriptor.Discre
Set ace = CreateObject("AccessContro
For Each ace In dacl
'Display properties of the ACEs which identify the "Associated External Account"
If ace.AccessMask And ASSOCIATED_EXTERNAL then
' Line for Debugging. Reports all properties for troubleshooting
' wscript.echo ace.Trustee & ", " & ace.AccessMask & ", " & ace.AceType & ", " & ace.AceFlags & ", " & ace.Flags & ", " & ace.ObjectType & ", " & ace.InheritedObjectType & " END" & vbcrlf
aACL(0,ACLRow) = distinguishedName
aACL(1,ACLRow) = sAMAccountName
aACL(2,ACLRow) = ace.Trustee
aACL(3,ACLRow) = msExchOmaAdminWirelessEnab
aACL(4,ACLRow) = HomeMDB
aACL(5,ACLRow) = legacyExchangeDN
aACL(6,ACLRow) = displayName
aACL(7,ACLRow) = ""
aACL(8,ACLRow) = ""
aACL(9,ACLRow) = ""
If ACLResult = "" Then
ACLResult = ace.Trustee
Else
ACLResult = ACLResult & "|" & ace.Trustee
End If
ListArrayData aACL, 10, ACLRow
ACLRow = ACLRow + 1
Redim Preserve aACL(10,ACLRow+1)
End If
Next
End If
On Error Goto 0
GetACL = ACLResult
' Cleanup Variables
Set objUser = Nothing
End Function
' **************************
Function GetExchangeTrustee(sAMAcco
Dim Match, x
Match = -1
For x=0 to ubound(aACL,2)
If Instr(1,aACL(2,x),sAMAccou
Match = x
Logdata "Exchange Trustee", x+1, "<" & aACL(1,x) & ">:<" & sAMAccountName & ">"
Exit For
End If
Next
If Match = -1 Then
GetExchangeTrustee = ";;;;;"
Else
GetExchangeTrustee = aACL(0,Match) & ";" & aACL(1,Match) & ";" & aACL(3,Match) & ";" & aACL(4,Match) & ";" & aACL(5,Match) & ";" & aACL(6,Match)
End If
End Function
Function GetExchangeMailboxSize(leg
Dim Match, x
Match = -1
For x=0 to ubound(aExchange,2)
If Instr(1,aExchange(0,x),leg
Match = x
Logdata "Exchange Mailbox", x+1, "<" & aExchange(0,x) & ">:<" & legacyExchangeDN & ">"
Exit For
End If
Next
If Match = -1 Then
GetExchangeMailboxSize = 0
Else
GetExchangeMailboxSize = aExchange(3,Match)
End If
End Function
Function GetEVaultSize(sAMAccountNa
Dim Match, x
Match = -1
' Begin at 3th row, above that is header and can give mismatch
For x=3 to ubound(aEVault,2)
If Instr(1,aEVault(1,x),sAMAc
Match = x
Logdata "EVault Archive", x-2, "<" & aEVault(1,x) & ">:<" & sAMAccountName & ">:<" & aEVault(0,x) & ">:Level 1 Match"
Exit For
Else
If Instr(1,aEVault(0,x),displ
Match = x
Logdata "EVault Archive", x-2, "<" & aEVault(0,x) & ">:<" & displayName & ">:<" & aEVault(1,x) & ">:Level 2 Match"
Exit For
End If
End If
Next
If Match = -1 Then
GetEVaultSize = 0
Else
GetEVaultSize = aEVault(3,Match)
End If
End Function
Function GetBlackBerryState(legacyE
Dim Match, x
Match = -1
For x=2 to ubound(aBlackBerry,2)
If Instr(1,aBlackBerry(8,x),l
Match = x
Logdata "Blackberry", x-1, "<" & aBlackBerry(8,x) & ">:<" & legacyExchangeDN & ">"
Exit For
End If
Next
If Match = -1 Then
GetBlackBerryState = "0"
Else
GetBlackBerryState = aBlackBerry(2,Match)
End If
End Function
Function GetCustomerValues(Array,di
Dim Match, x, Exclude
Match = -1
Exclude = 0
For x=0 to ubound(Array,2)
'Logdata "Customer to be matched", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">:<" & Array(4,x) & ">"
If Instr(1, distinguishedName, Array(5,x), 1) > 0 AND Array(5,x) <> "" Then
Select Case Array(4,x)
Case "Exclude"
Match = x
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">;Exclude Match"
Exclude = 1
Exit For
Case "Skip"
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">;Skip Match"
Exclude = 2
Exit For
Case "Reset"
Match = x
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">;Reset Match"
Exclude = 3
Exit For
Case ""
Match = x
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & ">;Level 1 Match"
Exit For
Case Else
If Instr(1, distinguishedName, Array(4,x), 1) > 0 AND Array(5,x) <> "" Then
Match = x
Logdata "Customer Match", x+1, "<" & distinguishedName & ">:<" & Array(5,x) & "><" & Array(4,x) & ">;level 2 Match"
Exit For
End If
End Select
End If
Next
' Customer ; Customer Sub Code ; Exclude based on table match=Exclude ; Service Code based on table service
If Match = -1 Then
GetCustomerValues = 0 & ";" & 0 & ";" & Exclude & ";" & 0
Else
GetCustomerValues = Array(0,Match) & ";" & Array(2,Match) & ";" & Exclude & ";" & Array(6,Match)
End If
End Function
Function CheckAccountEnabled(userAc
' Check if account is enabled, this is the second bit
intUAC = userAccountControl
If intUAC > 1 Then
binUAC = toBin(intUAC)
If inStr(Len(binUAC)-1,binUAC
' Account is disabled
CheckAccountEnabled = "0"
Else
' Account is enabled
CheckAccountEnabled = "1"
End If
Else
' Account is enabled
CheckAccountEnabled = "1"
End If
End Function
Function CheckCitrixEnabled(strDNSD
On Error Resume Next
Dim Match, x, objUser, aGroups
Match = -1
Set objUser = GetObject("LDAP://" & strDNSDomain & "/" & distinguishedName)
aGroups = objuser.GetEx("memberOf")
If (Err.Number <> 0) Then
On Error GoTo 0
Logdata "No Groups", "", ""
Else
On Error GoTo 0
For Each group in aGroups
For x=1 to ubound(aCitrixDataSheet,2)
If Instr(1,group,aCitrixDataS
Match = x
Logdata "Citrix Match", x, "<" & group & ">:<" & aCitrixDataSheet(5,x) & ">"
Exit For
End If
Next
Next
End If
If Match = -1 Then
CheckCitrixEnabled = False
Else
CheckCitrixEnabled = True
End If
Set objUser = Nothing
End Function
' **************************
Sub ListArrayData(Array, ArraySize, Index)
Dim x
OutputLine = Array(0,Index)
For x=1 to ArraySize
OutputLine = OutputLine & ";" & Array(x,Index)
Next
Logdata "List Row", Index+1, OutputLine
End Sub
Sub LogData(Name, Index, Data)
If LogOutPut = True Then
Wscript.echo Name & " ->" & Index & ";" & Time() & ";" & Data
End If
End Sub
Sub CopyFile(FileNamePath, FileNameSearch, FileNameResult, DeleteOriginal)
On Error Resume Next
Dim objFolder, objFile
Set objFolder = objFSO.GetFolder(FileNameP
For Each objFile In objFolder.files
If Instr(objFile, FileNameSearch) > 0 Then
objFSO.CopyFile objFile, FileNamePath & "\" & FileNameResult, OverwriteExisting
If DeleteOriginal = True Then
objFSO.DeleteFile objFile, True
Logdata "File Moved", "1", objFile
Else
Logdata "File Copied", "1", objFile
End If
End If
Next
Set objFolder = Nothing
Set objFile = Nothing
End Sub
Function toBin(aInt)
If aInt > 0 Then toBin = toBin(aInt\2) & (aInt Mod 2)
End function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thnx Chris for your response, but it possible to collect Exchange 2003 & 2010 mailboxsize including activesync. in one powershell script
Sure, it would take a bit of engineering but it's entirely possible. I wrote another one to deal with ActiveSync for 2003, it's pleasantly simple (at least compared to a VBScript equivalent):
Of course, the snag isn't really generating these little snippets. It's putting it all together in a logical format that you (and anyone else you must answer to) is happy with. If you wrote the original script I'd say you'll be more than capable of pulling off the translation. A bit of a perspective shift, but I'd regard it as a fun challenge if it were mine to do and I had the time to do it :)
Chris
Function Read-IISLog {
[CmdLetBinding()]
Param(
[Parameter(ValueFromPipelineByPropertyName = $True)]
[ValidateScript( { Test-Path $_ } )]
[Alias("FullName")]
[String]$Path
)
Process {
$FileStream = New-Object IO.FileStream($Path, "Open", "Read", "ReadWrite")
$StreamReader = New-Object IO.StreamReader($FileStream)
For ($i = 0; $i -lt 4; $i++) {
$Line = $StreamReader.ReadLine()
If ($Line -Match '#Fields: ') {
$Header = ($Line -Replace '#Fields: ').Split(' ', [StringSplitOptions]::RemoveEmptyEntries)
}
}
Do {
$Line = $StreamReader.ReadLine()
If ($Line -NotLike "#*") {
$Line | ConvertFrom-Csv -Delimiter ' ' -Header $Header
}
} Until ($StreamReader.EndOfStream)
}
}
$Output = @{}
Get-ChildItem *.log | Read-IISLog |
Where-Object { $_."cs-uri-stem" -Match 'ActiveSync' } |
Select-Object `
@{n='FirstSync';e={ [DateTime]::ParseExact("$($_.date) $($_.time)", "yyyy-MM-dd HH:mm:ss", $Null) }},
@{n='LastSync';e={ [DateTime]::ParseExact("$($_.date) $($_.time)", "yyyy-MM-dd HH:mm:ss", $Null) }},
@{n='ClientIP';e={ [Net.IPAddress]($_."c-ip") }},
@{n='ClientDevice';e={ $_."cs(User-Agent)" }},
@{n='AuthenticatedUser';e={ $_."cs-username" }},
@{n='Mailbox';e={ $_."cs-uri-stem".Split("/")[2] }},
@{n='DeviceId';e={ $_."cs-uri-stem".Split("/")[-1] }},
@{n='DeviceType';e={ $_."cs-uri-stem".Split("/")[-2] }},
@{n='SyncCount';e={ 1 }} |
ForEach-Object {
If (!$Output.Contains($_.DeviceId)) {
$Output.Add($_.DeviceId, $_)
} Else {
If ($_.FirstSync -lt $Output[$_.DeviceId].FirstSync) { $Output[$_.DeviceId].FirstSync = $_.FirstSync }
If ($_.LastSync -gt $Output[$_.DeviceId].LastSync) { $Output[$_.DeviceId].LastSync = $_.LastSync }
$Output[$_.DeviceId].SyncCount += 1
}
}
$Output.Values
It uses a .NET stream reader class to read files, the simpler alternative is Get-Content (native PowerShell). In my case I had to deal with rather large log files, I didn't want to kill my computer doing it. To achieve that, the script reads and processes the content, storing meaningful content only and discarding absolutely everything else (without committing it to memory).Of course, the snag isn't really generating these little snippets. It's putting it all together in a logical format that you (and anyone else you must answer to) is happy with. If you wrote the original script I'd say you'll be more than capable of pulling off the translation. A bit of a perspective shift, but I'd regard it as a fun challenge if it were mine to do and I had the time to do it :)
Chris
er sorry, I need to add a bit, I didn't read clearly enough.
2003 and 2010 in the same script: Well, it depends on your view on what constitutes the script :)
You'll need version checking because the process you use for each version of Exchange differs. For 2007 and up (including 2010, of course) you'll find a lot of built in PS CmdLets in the Exchange tool set, this will account for pretty much everything you have above quite happily.
For Exchange 2003 there's nothing native in PowerShell so you're looking at handling raw returns from AD, or using WMI. The thin end of that wedge is attempting to deal with the CDO library Exchange 2003 uses, I've never had the need to write around that one but I looked into it a few years ago wouldn't regard it as pretty.
If I were doing this script, and if I assume the scale of your estate / user base is large, I'd probably harvest the Configuration tree in AD for Exchange version information (per-server name perhaps). Then I'd call appropriate functions based on the returned version for each user. I'd have those functions return a common output format so the person consuming the script could remain happily oblivious to the data source.
If you want to explore rewriting it further I think there's value in dismantling the long script above and converting on a function-by-function basis. Optimising and cleaning the resulting script up will be a bit of a recursive process.
Chris
2003 and 2010 in the same script: Well, it depends on your view on what constitutes the script :)
You'll need version checking because the process you use for each version of Exchange differs. For 2007 and up (including 2010, of course) you'll find a lot of built in PS CmdLets in the Exchange tool set, this will account for pretty much everything you have above quite happily.
For Exchange 2003 there's nothing native in PowerShell so you're looking at handling raw returns from AD, or using WMI. The thin end of that wedge is attempting to deal with the CDO library Exchange 2003 uses, I've never had the need to write around that one but I looked into it a few years ago wouldn't regard it as pretty.
If I were doing this script, and if I assume the scale of your estate / user base is large, I'd probably harvest the Configuration tree in AD for Exchange version information (per-server name perhaps). Then I'd call appropriate functions based on the returned version for each user. I'd have those functions return a common output format so the person consuming the script could remain happily oblivious to the data source.
If you want to explore rewriting it further I think there's value in dismantling the long script above and converting on a function-by-function basis. Optimising and cleaning the resulting script up will be a bit of a recursive process.
Chris
ASKER
Chris,
Again thnx.
and in my case i will add this to your script:
$MBXs = Get-Mailbox -ResultSize 150
#unlimited
$result = foreach($MBX in $MBXs)
{
If($(Get-MailboxStatistics $MBX.identity -WarningAction "SilentlyContinue").LastLo gonTime -eq $Null )
{
Get-Mailbox $MBX.identity -WarningAction "SilentlyContinue" | select DisplayName, CustomAttribute4,CustomAtt ribute5,Cu stomAttrib ute6,Custo mAttribute 2,CustomAt tribute1,C ustomAttri bute3
#$user = Get-User -Identity $MBX.identity
Write-Host $MBX.identity #$user.identity,$user.Disp layName,$u ser.Custom Attribute4 ,$user.CustomAttribute2,$u ser.Custom Attribute3 ,$user.Cus tomAttribu te5,$user. CustomAttr ibute6
}
}
$result | export-csv c:\TESTVSE.xls -Encoding UTF8
Then i think that i am reaching my goal's.
Again thnx
Again thnx.
and in my case i will add this to your script:
$MBXs = Get-Mailbox -ResultSize 150
#unlimited
$result = foreach($MBX in $MBXs)
{
If($(Get-MailboxStatistics
{
Get-Mailbox $MBX.identity -WarningAction "SilentlyContinue" | select DisplayName, CustomAttribute4,CustomAtt
#$user = Get-User -Identity $MBX.identity
Write-Host $MBX.identity #$user.identity,$user.Disp
}
}
$result | export-csv c:\TESTVSE.xls -Encoding UTF8
Then i think that i am reaching my goal's.
Again thnx
Just a quick note on this bit:
export-csv c:\TESTVSE.xls -Encoding UTF8
While it's called xls, and Excel will handle it, the file format is still CSV (comma separated values) and therefore plain text.
It is entirely possible to write to Excel through PowerShell if that's a requirement. I've always found it a bit messy, more so than VbScript, but that doesn't change that it can be done.
I can show the samples I have on this, but you might get better with a bit of google work. I've never been a fan of Excel so I tend to avoid writing things for it, the things I have aren't the best examples in the world.
Chris
export-csv c:\TESTVSE.xls -Encoding UTF8
While it's called xls, and Excel will handle it, the file format is still CSV (comma separated values) and therefore plain text.
It is entirely possible to write to Excel through PowerShell if that's a requirement. I've always found it a bit messy, more so than VbScript, but that doesn't change that it can be done.
I can show the samples I have on this, but you might get better with a bit of google work. I've never been a fan of Excel so I tend to avoid writing things for it, the things I have aren't the best examples in the world.
Chris
Chris