Powershell script for CSV user import

Comments

1 comment

  • Avatar
    Todd Staben

    I made the following script yesterday to generate a CSV for our organization. I tried to make it as flexible as possible since every organization will be different. Definitely play around with the Get-ADUser filter and OU searchbase options to trim down the results to what you need. As a note this script does heavily rely on AD fields being up to date and accurate in order to work, but if nothing else this should get you started.

    #Created by Todd Staben of QMI Security Solutions.

    #This script will gather information on the AD User objects for the purpose of exporting the results into a CSV that can be uploaded to KnowBe4

    #In order for this script to work it must be run on a computer that has the Active Directory Powershell Module available which will typically be added
    #by installing the Windows RSAT tools

    #This script will require customization per organization depending on which AD fields they store certain information such as telephone extension.
    #The easiest way to figure out what the AD field names are is to open powershell and run the command: Get-ADUser someusername -Properties *
    #This will give you the object properties this command will return. Then for instance if you notice the pager field is where your organization
    #stores extension numbers, you would go into the fnGetADFields function and change the line "Extension" -Value $User.ipPhone to "Extension" -Value $User.Pager
    #If there are any field values you don't plan on using anyways you can change the end of the line to -Value $Null and it will just leave them blank

    Import-Module ActiveDirectory

    #If you want to change the AD filter from * it can be picky about where quotes are placed. An example of a working filter is $ADFilter = {Department -ne 'NonStandard Accounts'}
    $ADFilter = "*"
    [string]$ADOUSearch = $Null
    [array]$arrKnowBe4Users = @()
    [string]$CSVExportPath = "C:\Users\" + $env:username + "\Desktop\CSV\"
    [string]$CSVExport = $CSVExportPath + "KnowBe4.CSV"
    If ((Test-Path -Path $CSVExportPath) -eq $False) {New-Item $CSVExportPath -ItemType Directory | Out-Null}

    function fnGetADFields ($User) {
    $objUser = New-Object PSObject
    $objUser | Add-Member -MemberType NoteProperty -Name Email -Value $User.EmailAddress
    $objUser | Add-Member -MemberType NoteProperty -Name "First Name" -Value $User.GivenName
    $objUser | Add-Member -MemberType NoteProperty -Name "Last Name" -Value $User.sn
    $objUser | Add-Member -MemberType NoteProperty -Name "Phone Number" -Value $User.OfficePhone
    $objUser | Add-Member -MemberType NoteProperty -Name "Extension" -Value $User.ipPhone
    $objUser | Add-Member -MemberType NoteProperty -Name "Group" -Value $Null
    $objUser | Add-Member -MemberType NoteProperty -Name "Location" -Value $User.Office
    $objUser | Add-Member -MemberType NoteProperty -Name "Division" -Value $User.Department
    $objUser | Add-Member -MemberType NoteProperty -Name "Manager Name" -Value $Null
    $objUser | Add-Member -MemberType NoteProperty -Name "Manager Email" -Value $Null
    #We don't have employee numbers stored in AD fields, if your organization differs this is where you can set it
    $objUser | Add-Member -MemberType NoteProperty -Name "Employee Number" -Value $Null
    $objUser | Add-Member -MemberType NoteProperty -Name "Job Title" -Value $User.Title
    #We don't set the passwords on account creation, if you want to set it to something static this would be where you set it
    $objUser | Add-Member -MemberType NoteProperty -Name "Password" -Value $Null
    [string]$Manager = (Get-ADUser $User.SamAccountName -Properties *).Manager
    If ($Manager) {
    $objUser."Manager Name" = ($Manager -split ",")[0].Substring(3)
    $objUser."Manager Email" = (Get-ADUser -Filter {DistinguishedName -eq $Manager} -Properties *).Mail
    }
    return $objUser

    }

    #If you want to customize the search based on OU, change the value of the $ADOUSearch variable earlier in the script, and in the command below
    #add in "-SearchBase $ADOUSearch". The resulting command before the pipe would look like: Get-ADUser -Filter $ADFilter -SearchBase $ADOUSearch -Properties *
    Get-ADUser -Filter $ADFilter -Properties * | Foreach-Object {$arrKnowBe4Users += fnGetADFields -User $_}
    $arrKnowBe4Users | Export-CSV -Path $CSVExport -Force -NoTypeInformation

    0
    Comment actions Permalink

Please sign in to leave a comment.