Powershell script for CSV user import

Paul Harrington

11. helmik. 2016, 12.58

The CSV process is very fussy. It would be good to have a provided powershell script to export users' details into a format that Knowbe4 would accept.

1

• 

  Todd Staben 15. kesäk. 2016, 21.36

  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

  Kommenttitoiminnot Pysyvä linkki

Kirjaudu sisään jättääksesi kommentin.