Solved

Convert Vbscrit to powershell

Posted on 2013-01-28
8
508 Views
Last Modified: 2013-01-29
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.FileSystemObject")
Set objExcel = WScript.CreateObject ("Excel.Application")

' Other Variables
ParentDIR = objFSO.GetParentFolderName(wscript.ScriptFullName) ' 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_Export", "BlackBerry.tab", True
0
Comment
Question by:Becksi
  • 5
  • 3
8 Comments
 
LVL 70

Expert Comment

by:Chris Dent
ID: 38830143
There's nothing substantial here to convert, is the script truncated?

Chris
0
 

Author Comment

by:Becksi
ID: 38830193
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.FileSystemObject")
Set objExcel = WScript.CreateObject ("Excel.Application")

' Other Variables
ParentDIR = objFSO.GetParentFolderName(wscript.ScriptFullName) ' 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_Export", "BlackBerry.tab", True

' ************************************** Main Script **************************************
      
' Open Excel Template file
Set objTemplateWorkbook = objExcel.Workbooks.Open(TemplateFileName)
objExcel.Visible = False
objExcel.DisplayAlerts=False  


' Define Template Worksheets into variables
Set ExportDataDefinitionSheet = objTemplateWorkbook.WorkSheets("Export-data") ' This pointing to the "Export-data" sheet of the Template file.
Set DomainTableSheet = objTemplateWorkbook.WorkSheets("Domein-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.WorkSheets("Gebruikers-tabel") ' This pointing to the "Gebruikers-tabel" sheet of the Excel file. This sheet contains User OU patterns to be matched with.
Set PublicFolderDataSheet = objTemplateWorkbook.WorkSheets("PublicFolder-tabel") ' 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.WorkSheets("Werkplek-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.WorkSheets("Periode-definitie") ' 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.WorkSheets("Citrix-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(ResultFileName)

' Rename and define Result Worksheets into variables
objResultWorkbook.WorkSheets(1).Name="Export-data"
Set ExportDataSheet = objResultWorkbook.WorkSheets("Export-data")

Set objOtherWorkbook = objExcel.Workbooks.Add()
objOtherWorkbook.SaveAs(OtherFileName)

' Rename and define Result Worksheets into variables
objOtherWorkbook.WorkSheets(1).Name="Export-data"
Set OtherDataSheet = objOtherWorkbook.WorkSheets("Export-data")

' Create the current Month & Year as noted in period definition sheet in Template.xls as Dutch output
strPeriod = "'" & UCase(Left(PerionDefinitionSheet.cells(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").AutoFilter

' 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").AutoFilter

' 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,bDomain)
'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.Connection")
      Set objCommand =   CreateObject("ADODB.Command")
      objConnection.Provider = "ADsDSOObject"
      objConnection.Open "Active Directory Provider"
      Set objCommand.ActiveConnection = objConnection
      objCommand.Properties("Chase referrals") = ADS_CHASE_REFERRALS_ALWAYS
      objCommand.Properties("Page Size") = 10000
      objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

      
      'Define LDAP dns connection
      strBase = "<LDAP://" & strDNSDomain & ">"
      'Define the filter elements
      strFilter = "(&(objectCategory=person)(objectClass=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,sAMAccountName,givenName,displayName,sn,userPrincipalName,legacyExchangeDN,userAccountControl,vasco-LinkUserToDPToken,msExchOmaAdminWirelessEnable,HomeMDB"
      Else
            strAttributes = "Name,distinguishedName,sAMAccountName,givenName,displayName,sn,userPrincipalName,legacyExchangeDN,userAccountControl,vasco-LinkUserToDPToken"
      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("distinguishedName").Value & ">"
            
            'Check if account is enabled
            AccountEnabled = CheckAccountEnabled(objRecordSet.Fields("userAccountControl").Value)
            
            ' Get Customer values
            aCustomer = Split(GetCustomerValues(aUserDataSheet,objRecordSet.Fields("distinguishedName").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("distinguishedName").Value, ",CN") > 0 Then
                  distinguisedID = Left (objRecordSet.Fields("distinguishedName").Value, InStr (objRecordSet.Fields("distinguishedName").Value, ",CN") -1)
            Else
                  distinguisedID = Left (objRecordSet.Fields("distinguishedName").Value, InStr (objRecordSet.Fields("distinguishedName").Value, ",OU") -1)
            End If      
            distinguisedPath = Replace (objRecordSet.Fields("distinguishedName").Value ,distinguisedID & ",", "" )            

            ' Get the account info to be referenced for Exchange
            If bDomain = 1 Then
                  RefdistinguishedName = objRecordSet.Fields("distinguishedName").Value
                  RefsAMAccountName = objRecordSet.Fields("sAMAccountName").Value
                  RefmsintOMA = objRecordSet.Fields("msExchOmaAdminWirelessEnable").Value
                  RefHomeMDB = objRecordSet.Fields("HomeMDB").Value
                  ReflegacyExchangeDN = objRecordSet.Fields("legacyExchangeDN").Value
                  RefdisplayName = objRecordSet.Fields("displayName").Value
                  
                  ' 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("sAMAccountName").Value, objRecordSet.Fields("displayName").Value, "User", distinguisedPath, strTrustee, ""
                              OtherRow = OtherRow + 1
                  End If
            Else
                  aTrustee = Split(GetExchangeTrustee(sAMAccountName),";")
                  
                  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("sAMAccountName").Value, objRecordSet.Fields("displayName").Value, "User", distinguisedPath, aTrustee(1), ""
                        OtherRow = OtherRow + 1
                  End If
            End If            
            
            ' Get values with reference accounts info
            ExchangeMailboxSize = GetExchangeMailboxSize(ReflegacyExchangeDN)
            EVaultSize = GetEVaultSize(RefsAMAccountName, RefdisplayName)
            BlackBerryState = GetBlackBerryState(ReflegacyExchangeDN)
      
            ' 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("displayName").Value, 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("displayName").Value, ExchangeMailboxSize, EVaultSize, "Mailbox", distinguisedPath, ""
                    ResultRow = ResultRow + 1                      
                  End If
                 ' End Service 2B.3.2 for Users

                        If TypeName(objRecordSet.Fields("vasco-LinkUserToDPToken").Value) <> "Null" Then

                              ' Service 2B.5
                              For Each VPNToken in objRecordSet.Fields("vasco-LinkUserToDPToken").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("sAMAccountName").Value, objRecordSet.Fields("displayName").Value, StrVPNToken, strVPNTokenPath, distinguisedPath, ""
                                    ResultRow = ResultRow + 1
                                    
                                    StrVPNToken = Null
                                    strVPNTokenPath = Null
                              Next
                              ' End Service 2B.5      
                                    
                              ' Service 2B.1.4
                              If CheckCitrixEnabled(strDNSDomain,objRecordSet.Fields("distinguishedName").Value) = True Then
                                    WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.4", Customer, CustomerSub, objRecordSet.Fields("sAMAccountName").Value, objRecordSet.Fields("displayName").Value, "", distinguisedPath, "", ""
                                    ResultRow = ResultRow + 1
                              End If      
                              ' End Service 2B.1.4
                              
                              VPNToken = Null
                  
                        Else
                        
                              ' Service 2B.1.4
                              If CheckCitrixEnabled(strDNSDomain,objRecordSet.Fields("distinguishedName").Value) = True Then
                                    WriteOtherData OtherRow, strPeriod, AccountEnabled, "2B.1.4", Customer, CustomerSub, objRecordSet.Fields("sAMAccountName").Value, objRecordSet.Fields("displayName").Value, "", 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("sAMAccountName").Value, objRecordSet.Fields("displayName").Value, "Outlook Mobile Access", distinguisedPath, RefmsintOMA, ""
                                          ResultRow = ResultRow + 1                              
                                    End If
                              Else
                                    WriteResultData ResultRow, strPeriod, AccountEnabled, "2B.1.5", Customer, CustomerSub, objRecordSet.Fields("sAMAccountName").Value, objRecordSet.Fields("displayName").Value, "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("sAMAccountName").Value, objRecordSet.Fields("displayName").Value, BlackBerryState, distinguisedPath, "", ""
                              ResultRow = ResultRow + 1
                        End If            
                        ' End Service 2B.1.6      
                        
                  Else
                        WriteOtherData OtherRow, strPeriod, AccountEnabled, "No Service", Customer, CustomerSub, objRecordSet.Fields("sAMAccountName").Value, objRecordSet.Fields("displayName").Value, "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,bDomain)
'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.Connection")
      Set objCommand =   CreateObject("ADODB.Command")
      objConnection.Provider = "ADsDSOObject"
      objConnection.Open "Active Directory Provider"
      Set objCommand.ActiveConnection = objConnection
      objCommand.Properties("Chase referrals") = ADS_CHASE_REFERRALS_ALWAYS
      objCommand.Properties("Page Size") = 10000
      objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

      'Define LDAP dns connection
      strBase = "<LDAP://" & strDNSDomain & ">"
      'Define the filter elements
      strFilter = "(&(objectCategory=computer)(objectClass=computer))"
      'List all attributes you will require
      strAttributes = "Name, displayName, distinguishedName,userAccountControl"
      '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("distinguishedName").Value & ">"
            AccountEnabled = CheckAccountEnabled(objRecordSet.Fields("userAccountControl").Value)      
            aCustomer = Split(GetCustomerValues(aWorkplaceDataSheet,objRecordSet.Fields("distinguishedName").Value),";")
            Customer = aCustomer(0)
            CustomerSub = aCustomer(1)
            ExcludeItem = aCustomer(2)
            ServiceItem = aCustomer(3)
            If InStr (objRecordSet.Fields("distinguishedName").Value, ",CN") > 0 Then
                  distinguisedID = Left (objRecordSet.Fields("distinguishedName").Value, InStr (objRecordSet.Fields("distinguishedName").Value, ",CN") -1)
            Else
                  distinguisedID = Left (objRecordSet.Fields("distinguishedName").Value, InStr (objRecordSet.Fields("distinguishedName").Value, ",OU") -1)
            End If      
            distinguisedPath = Replace (objRecordSet.Fields("distinguishedName").Value ,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(aPublicFolderDataSheet,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),aPFResult(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=impersonate}!\\" & 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.LastLoggedOnUserAccount

                        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=impersonate}!\\" & 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/1024/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.Connection")
      Set objRecordSet = CreateObject("ADODB.Recordset")
      strPathtoTextFile = EVFilePath & "\"
      
      objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strPathtoTextFile & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited;CharacterSet=65001"""
      objRecordset.Open "SELECT * FROM " & EVFileName, objConnection, adOpenStatic, adLockOptimistic, adCmdText

      Do Until objRecordset.EOF
            aEVault(0,z) = objRecordset.Fields.Item("ArchiveName")
            aEVault(1,z) = objRecordset.Fields.Item("BillingAccount")
            aEVault(2,z) = objRecordset.Fields.Item("TotalItems")
            If objRecordset.Fields.Item("TotalSize") <> Null Then
                  aEVault(3,z) =  Round(objRecordset.Fields.Item("TotalSize"),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.Connection")
      Set objRecordSet = CreateObject("ADODB.Recordset")
      strPathtoTextFile = BBFilePath & "\"
      
      objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strPathtoTextFile & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""
      objRecordset.Open "SELECT * FROM " & BBFileName, objConnection, adOpenStatic, adLockOptimistic, adCmdText

      Do Until objRecordset.EOF
            aBlackBerry(0,z) = objRecordset.Fields.Item("DisplayName")
            aBlackBerry(1,z) = objRecordset.Fields.Item("PIN")
            aBlackBerry(2,z) = objRecordset.Fields.Item("EnabledState")
            aBlackBerry(3,z) = objRecordset.Fields.Item("Forwarded")
            aBlackBerry(4,z) = objRecordset.Fields.Item("SentFromDevice")
            aBlackBerry(5,z) = objRecordset.Fields.Item("PendingToDevice")
            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, msExchOmaAdminWirelessEnable, 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("msExchMailboxSecurityDescriptor")
      
      If Err.Number = 0 Then
    ' Pull out the DACL for reading.
    Set dacl = oSecurityDescriptor.DiscretionaryAcl
      Set ace = CreateObject("AccessControlEntry")
      
        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) = msExchOmaAdminWirelessEnable
                        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(sAMAccountName)
      Dim Match, x
      
      Match = -1

      For x=0 to ubound(aACL,2)
            If Instr(1,aACL(2,x),sAMAccountName,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(legacyExchangeDN)
      Dim Match, x
      
      Match = -1

      For x=0 to ubound(aExchange,2)
            If Instr(1,aExchange(0,x),legacyExchangeDN,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(sAMAccountName,displayName)
      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),sAMAccountName,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),displayName,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(legacyExchangeDN)
      Dim Match, x
      
      Match = -1

      For x=2 to ubound(aBlackBerry,2)
            If Instr(1,aBlackBerry(8,x),legacyExchangeDN,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,distinguishedName)
      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(userAccountControl)
            ' 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(strDNSDomain, 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,aCitrixDataSheet(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(FileNamePath)
      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
0
 
LVL 70

Accepted Solution

by:
Chris Dent earned 500 total points
ID: 38831551
So some of that is going to be really simple, you could start with Quest's AD CmdLets (http://www.quest.com/powershell/activeroles-server.aspx) or the native MS CmdLets if you're running 2008 R2.

Get-QADUser will let you grab most of the bits and pieces you've got above. Tie in a small amount of text parsing (less than VBScript) and you're most of the way there in a few lines.

Much of what you're doing here is going to boil down to a variation on this:
Get-QADUser | Select-Object Name, DN | Export-Csv "OutputFile.csv" -NoTypeInformation

Open in new window

That comes a bit unstuck when you try and read off things like the mailbox security descriptor. Are you still running 2003? If you were moving to 2007 and above that problem goes away and we only need the Exchange tools.

"Coming unstuck" doesn't apply so much to the bits you're pulling using WMI. I wrote a little script to do that for 2003 in PowerShell a number of years ago (http://www.indented.co.uk/index.php/2009/01/22/exchange-2003-get-mailboxstatistics/).

Chris
0
 

Author Comment

by:Becksi
ID: 38831590
Thnx Chris for your response, but it possible to collect Exchange 2003 & 2010 mailboxsize including activesync. in one powershell script
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 70

Expert Comment

by:Chris Dent
ID: 38831635
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):
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

Open in new window

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
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 38831684
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
0
 

Author Comment

by:Becksi
ID: 38831688
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").LastLogonTime -eq $Null )

 

 {

   Get-Mailbox $MBX.identity -WarningAction "SilentlyContinue" | select DisplayName, CustomAttribute4,CustomAttribute5,CustomAttribute6,CustomAttribute2,CustomAttribute1,CustomAttribute3
   #$user = Get-User -Identity $MBX.identity
   Write-Host $MBX.identity #$user.identity,$user.DisplayName,$user.CustomAttribute4 ,$user.CustomAttribute2,$user.CustomAttribute3,$user.CustomAttribute5,$user.CustomAttribute6

 

 }

}

$result | export-csv c:\TESTVSE.xls -Encoding UTF8

Then i think that i am reaching my goal's.

Again thnx
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 38831841
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Why would I want to create a function for tracking messages? I am glad you asked. As with most monotonous/routine tasks, human error tends to creep in after doing the same task over and over again. By creating a function, you load the function once…
Utilizing an array to gracefully append to a list of EmailAddresses
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now