Shep's IT Solutions

Azure AD Export Named Locations PowerShell

2022-05-04T12:32:00.000-07:00

Connect-AzureAd

$array = @()

$ipranges = Get-AzureADMSNamedLocationPolicy | select displayname,ipranges

foreach($range in $ipranges)

{

write-host "Found" $range.displayname

write-host "IP Range" ($range).ipranges.cidraddress

$item = New-Object PSObject

$item | Add-Member -type NoteProperty -Name 'Display Name' -Value ($range).displayname

$item | Add-Member -type NoteProperty -Name 'IP Range' -Value ($range).ipranges.cidraddress

$array += $item

}

$array | Out-GridView

UltiPro Sync User Attributes to Active Directory (Report-as-a-Service + SQL)

2021-03-10T07:43:00.004-08:00

UltiPro has two methods of REST API access. For the base APIs, you can find my prior post. This post will deal with the custom "Report-as-a-Service" (RaaS) API. This allows HR staff to author custom reports via Cognos Analytics. Most commonly, they'll email these around or do FTP. Many people don't realize that these APIs exist and streamline data exchange with improved security.

For more info on setting up Azure Automation variables and where to find the API documentation, please see my original post. This post will concentrate on the scripting for RaaS as well as exporting the data to SQL rather than CSV.

RaaS

The process is documented but not especially intuitive in the UltiPro API library. The process requires 5 steps to retrieve a report. Some of these steps may seem redundant. Don't try to take shortcuts. I tried, it won't work.

Logon (create a session)
Get the Report List
Execute the Report
Retrieve the Report
Logoff (end the session)

The Script

Most of the parameters are at the start of the script. But be sure to update the SQL server names if you plan to use that section. Those aren't set as variables (don't remember why, maybe laziness).

You can break this into 4 distinct sections:

UltiPro data retrieval
AD data retrieval (fetch distinguished names of managers)
SQL Import for UltiPro data
SQL Import for AD data

*FYI, we just filter out certain employee IDs like "999999" which for us just indicate a contractor. Feel free to remove it.

SQL Setup

I worked with a DBA to create a DB and tables. I just provided a spreadsheet of columns, data types, and expected character limits. Be sure that your data set matches the destination.

Our DBA also created a stored procedure to call during the import that completely overwrites the data set every time. For our purposes, we didn't need to keep the data. We wanted it fresh every X hours.

We use two tables + a view

Contains the UltiPro
Contains the AD manager DN (so we can match UltiPro manager ID to a usable attribute.
The view combines the two (which we use ManageEngine AD Manager to process)

#initialize variables from automation library
$dataserviceUrl = Get-AutomationVariable -Name 'UltiPro Data Service URL'
$streamingserviceUrl = Get-AutomationVariable -Name 'UltiPro Streaming Service URL'
$ClientAccessKey = Get-AutomationVariable -Name 'UltiPro ClientAccessKey'
$UserAccessKey = Get-AutomationVariable -Name 'UltiPro UserAccessKey'
$apiuser = Get-AutomationPSCredential -Name 'UltiPro Web API User'
$reportname = Get-AutomationVariable -Name 'UltiPro Report Name'

#Extract secure password string from account storage to plain text
$username = $apiuser.username
$password = [System.Net.NetworkCredential]::new("", $apiuser.password).Password

#configure reusable http header
$headers =  @{'Content-Type' = 'application/soap+xml; charset=utf-8'}

#reset variables
$serviceID = ""
$token = ""
$InstanceKey = ""

##########################################
#Logon to UltiPro Report-as-a-Service API#
##########################################
write-output "Connecting to Report-as-a-Service API"
$body = @"
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/LogOn</a:Action>
        <a:To s:mustUnderstand="1">$dataserviceUrl</a:To>
    </s:Header>
    <s:Body>
        <LogOn xmlns="http://www.ultipro.com/dataservices/bidata/2">
            <logOnRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <UserName>$username</UserName>
                <Password>$password</Password>
                <ClientAccessKey>$ClientAccessKey</ClientAccessKey>
                <UserAccessKey>$UserAccessKey</UserAccessKey>
            </logOnRequest>
        </LogOn>
    </s:Body>
</s:Envelope>
"@
[xml]$logon = Invoke-RestMethod -Method POST -Uri $dataserviceUrl -headers $headers -Body $body -Credential $cred

##########################################
#         Set Session Specific IDs       #
##########################################
write-output "Fetching session IDs"
$serviceID = $logon.Envelope.body.LogOnResponse.LogOnResult.serviceID
$token = $logon.Envelope.body.LogOnResponse.LogOnResult.Token
$InstanceKey = $logon.Envelope.body.LogOnResponse.LogOnResult.InstanceKey

write-output "Session ID: $serviceID"

##########################################
#             Get Report List            #
##########################################
write-output "Enumerating report list"
$body = @"
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/GetReportList</a:Action>
        <a:To s:mustUnderstand="1">$dataserviceUrl</a:To>
    </s:Header>
    <s:Body>
        <GetReportList xmlns="http://www.ultipro.com/dataservices/bidata/2">
            <context xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <ServiceId>$serviceID</ServiceId>
                <ClientAccessKey>$ClientAccessKey</ClientAccessKey>
                <Token>$token</Token>
                <Status>Ok</Status>
                <StatusMessage i:nil="true"/>
                <InstanceKey>$instanceKey</InstanceKey>
            </context>
        </GetReportList>
    </s:Body>
</s:Envelope>
"@
[xml]$ReportList = Invoke-RestMethod -Method POST -Uri $dataserviceUrl -headers $headers -Body $body -Credential $cred

$reportscount = ($reportlist.Envelope.body.GetReportListResponse.GetReportListResult.reports.Report).count

write-output "Reports Found: $reportscount"

##########################################
#             Execute Report             #
##########################################
write-output "Excecuting AD Sync Report"
#Get report based on name defined in UltiPro
$reportPath = $ReportList.Envelope.body.GetReportListResponse.GetReportListResult.Reports.Report | `
    Where-Object{$_.reportname -eq "$reportname"} | `
    Select-Object -ExpandProperty ReportPath

#Specify delimiter in header (if not specified, defaults to XML)
$executeHeaders =  @{ `
      'Content-Type' = 'application/soap+xml; charset=utf-8'; `
      'US-DELIMITER' = ","; `
  }

#Configure XML Header/Body payload for Execute Invoke REST
$body = @"
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/ExecuteReport</a:Action>
        <a:To s:mustUnderstand="1">$dataserviceUrl</a:To>
    </s:Header>
    <s:Body>
        <ExecuteReport xmlns="http://www.ultipro.com/dataservices/bidata/2">
            <request xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <ReportPath>$ReportPath</ReportPath>
                <ReportParameters/>
            </request>
            <context xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <ServiceId>$serviceID</ServiceId>
                <ClientAccessKey>$ClientAccessKey</ClientAccessKey>
                <Token>$token</Token>
                <Status>Ok</Status>
                <StatusMessage i:nil="true"/>
                <InstanceKey>$instanceKey</InstanceKey>
            </context>
        </ExecuteReport>
    </s:Body>
</s:Envelope>
"@

[xml]$execute = Invoke-RestMethod -Method POST -Uri $dataserviceUrl -headers $executeHeaders -Body $body

#Set the report key retrieved during execution to be used in the report streaming steps next
$reportKey = $execute.Envelope.body.ExecuteReportResponse.ExecuteReportResult.ReportKey

write-output "ReportKey: $reportkey"

##########################################
#             RetrieveReport             #
##########################################
write-output "Fetching report results output"
#Set retrieval header and specify stream output
$retrieveReportURL = $streamingserviceUrl + '?EmploymentStatus=A'
$retrieveHeaders =  @{ `
    'Content-Type' = 'application/soap+xml; charset=utf-8'; `
    'Accept-Encoding' = 'gzip, deflate'; `
}

#Stream output SOAP body
$body = @"
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bistream/2/IBIStreamService/RetrieveReport</a:Action>
        <h:ReportKey xmlns:h="http://www.ultipro.com/dataservices/bistream/2" xmlns="http://www.ultipro.com/dataservices/bistream/2">$reportKey</h:ReportKey>
        <a:To s:mustUnderstand="1">$streamingserviceUrl</a:To>
    </s:Header>
    <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <RetrieveReportRequest xmlns="http://www.ultipro.com/dataservices/bistream/2"/>
    </s:Body>
</s:Envelope>
"@

$response = Invoke-RestMethod -Method POST -Uri $retrieveReportURL -headers $retrieveHeaders -Body $body

##########################################
#                LogOff                  #
##########################################
write-output "Disconnecting session"

#Disconnect from the data service API
$body = @"
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">
    <s:Header>
        <a:Action s:mustUnderstand="1">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/LogOff</a:Action>
        <a:To s:mustUnderstand="1">$dataserviceUrl</a:To>
    </s:Header>
    <s:Body>
        <LogOff xmlns="http://www.ultipro.com/dataservices/bidata/2">
            <context xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <ServiceId>$serviceID</ServiceId>
                <ClientAccessKey>$ClientAccessKey</ClientAccessKey>
                <Token>$token</Token>
                <Status>Ok</Status>
                <StatusMessage i:nil="true"/>
                <InstanceKey>$InstanceKey</InstanceKey>
            </context>
        </LogOff>
    </s:Body>
</s:Envelope>
"@

Invoke-RestMethod -Method POST -Uri $dataserviceUrl -headers $headers -Body $body

##########################################
#        Convert Base64 to Array         #
##########################################
write-output "Converting report stream data from Base64 to array/table format"
#Convert output from Base64 to string data (comes out as XML from )
$bytes = [Convert]::FromBase64String($response.Envelope.body.StreamReportResponse.ReportStream)
$csvdata = [System.Text.Encoding]::ASCII.GetString($bytes)
$arraydata = $csvdata | convertFrom-csv

##########################################
#             Find Managers              #
##########################################

$managers = get-aduser -filter * -Properties employeeID,Manager | where-object {
    $_.employeeID -notlike "666666" -and `
    $_.employeeID -notlike "888888" -and `
    $_.employeeID -notlike "999999" -and `
    $_.employeeID -ne $null
} | Select-Object employeeID, Manager, distinguishedname

##########################################
#          Output UltiPro to SQL         #
##########################################

write-output "Creating empty SQL data table in memory"
#build an empty data table in the expected SQL format
[System.Data.DataTable]$table = New-Object('system.Data.DataTable')
$table.Columns.Add("givenName", "System.String")
$table.Columns.Add("middleName", "System.String")
$table.Columns.Add("lastName", "System.String")
$table.Columns.Add("telephoneNumber", "System.String")
$table.Columns.Add("homePhone", "System.String")
$table.Columns.Add("streetAddress", "System.String")
$table.Columns.Add("city", "System.String")
$table.Columns.Add("state", "System.String")
$table.Columns.Add("postalCode", "System.String")
$table.Columns.Add("physicalDeliveryOfficeName", "System.String")
$table.Columns.Add("department", "System.String")
$table.Columns.Add("Title", "System.String")
$table.Columns.Add("company", "System.String")
$table.Columns.Add("employeeID", "System.String")
$table.Columns.Add("division", "System.String")
$table.Columns.Add("orglevel2code", "System.String")
$table.Columns.Add("orglevel3code", "System.String")
$table.Columns.Add("employeeStatusCode", "System.String")
$table.Columns.Add("ManagerEmployeeID", "System.String")
$table.Columns.Add("preferredName", "System.String")
$table.Columns.Add("mail", "System.String")
$table.Columns.Add("location", "System.String")
$table.Columns.Add("jobCode", "System.String")

write-output "Adding report data to PowerShell SQL data table object"
foreach($aRow in $arraydata)
{
    $row = $table.NewRow()
        $row.givenName = $arow.FirstName

        if($arow.MiddleName.length -ne 0)
        {$row.middleName = $arow.MiddleName.Substring(0,1)}

        $row.lastName = $arow.LastName
        $row.telephoneNumber = $arow.WorkPhone
        $row.homePhone = $arow.HomePhone
        $row.streetAddress = $arow.Address
        $row.city = $arow.City
        $row.state = $arow.State
        $row.postalCode = $arow.Zip
        $row.physicalDeliveryOfficeName = $arow.Address
        $row.department = $arow.OrgLevel2
        $row.Title = $arow.Title
        $row.company = $arow.Company
        $row.employeeID = $arow.EmployeeNumber
        $row.division = $arow.OrgLevel1Code
        $row.orglevel2code = $arow.OrgLevel2Code
        $row.orglevel3code = $arow.OrgLevel3Code
        $row.employeeStatusCode = $arow.EmployeeStatus
        $row.ManagerEmployeeID = $arow.EmployeeNumberSupervisor
        #$row.preferredName = $arow.
        #$row.mail = $arow.
        #$row.location = $arow.Location
        #$row.jobCode = $arow.JobCode

        $table.Rows.Add($row)
}

$tablecount = $table.Rows.count
write-output "Discovered $tablecount employee records"

write-output "Connecting to SQL Instance"
#Create Connection string
$cnString = 'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBMaintenance;Data Source=SERVERNAMEHERE'

#define empty system objects for command and connection strings
[System.Data.SqlClient.SqlCommand]$cmd = New-Object('system.data.sqlclient.sqlcommand')
[System.Data.SqlClient.SqlConnection]$c = New-Object('system.data.sqlclient.sqlconnection')

#set $c to connection string and open connection
$c.connectionstring = $cnString;
$c.open();

write-output "Executing SQL stored procedure"
#configure command type as stored procedure
$cmd.Connection = $c;
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd.commandText = "dbo.ADSync_InsertAll"
$perfResultsParam = New-Object('system.data.sqlclient.sqlparameter')

#sql table type param
$perfResultsParam.ParameterName = "ADSyncT"
$perfResultsParam.SqlDBtype = [System.Data.SqlDbType]::Structured
$perfResultsParam.Direction = [System.Data.ParameterDirection]::Input
$perfResultsParam.value = $table

$cmd.parameters.add($perfResultsParam);       
$cmd.executeNonQuery(); 

write-output "Disconnecting from SQL Instance"
$c.close();

write-output "Data retrieval and import complete"

##########################################
#          Output AD Data to SQL         #
##########################################

write-output "Creating empty SQL data table in memory"
#build an empty data table in the expected SQL format
[System.Data.DataTable]$table2 = New-Object('system.Data.DataTable')
$table2.Columns.Add("employeeID", "System.String")
$table2.Columns.Add("distName", "System.String")

write-output "Adding data to PowerShell SQL data table object"
foreach($manager in $managers)
{
    $row = $table2.NewRow()
        $row.employeeID = $manager.employeeID
        $row.distName = $manager.distinguishedname

        $table2.Rows.Add($row)
}

$table2count = $table2.Rows.count
write-output "Discovered $table2count employee records"

write-output "Connecting to SQL Instance"
#Create Connection string
$cnString = 'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBMaintenance;Data Source=SERVERNAMEHERE'

#define empty system objects for command and connection strings
[System.Data.SqlClient.SqlCommand]$cmd = New-Object('system.data.sqlclient.sqlcommand')
[System.Data.SqlClient.SqlConnection]$c = New-Object('system.data.sqlclient.sqlconnection')

#set $c to connection string and open connection
$c.connectionstring = $cnString;
$c.open();

write-output "Executing SQL stored procedure"
#configure command type as stored procedure
$cmd.Connection = $c;
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd.commandText = "dbo.ADDist_InsertAll"
$perfResultsParam = New-Object('system.data.sqlclient.sqlparameter')

#sql table type param
$perfResultsParam.ParameterName = "ADDistT"
$perfResultsParam.SqlDBtype = [System.Data.SqlDbType]::Structured
$perfResultsParam.Direction = [System.Data.ParameterDirection]::Input
$perfResultsParam.value = $table2

$cmd.parameters.add($perfResultsParam);       
$cmd.executeNonQuery(); 

write-output "Disconnecting from SQL Instance"
$c.close();

write-output "AD Data retrieval and import complete"

UltiPro Sync User Attributes to Active Directory (CSV Method)

2020-10-08T12:15:00.007-07:00

[NOTICE]

This is a completely valid method but not how we do it today. We use the Report-as-a-Service API to get more granular about the data we extract. It's a little more complex because it uses session logic vs. the simple logon approach used below. Both have their merits. But the RaaS option will certainly run much faster on large data sets. Please check out the next blog post for more info to compare your options.

Objective

Sync user attributes between UltiPro and Active Dirctory. Many of the values originate with HR. Items like Title, Department, EmployeeID and so on.

UltiPro has a relatively well documented taxonomy for their webservices / REST APIs.
https://connect.ultipro.com/documentation#/api/

It isn't the most intuitive page but you can find the model for each API mid-page. If the API accepts query parameters they'll be spelled out at the bottom. (see below)

Method

Use existing PowerShell skills to create scripting to pull/parse UltiPro web services data
Use Azure Automation to:

Secure the credentials for the Web Service Account
Control the job schedules
Connect to on-prem resources through Hybrid Worker

Create output that can be digested by sync tools like ManageEngine ADManager

Requirements

Web Service Account with API Access. We used the following APIs with View-Only access.

Employee Changes
Employement Details
Org Levels

Customer API Key
Customer tenant URL (varies slightly based on where you're hosted)

Before we get started...

Other Noteworthy Items

ManageEngine does have a native integration --- HOWEVER, they require permission to APIs that include SSN and such. We opted not to use their API to keep the HR data as secure as possible.
There are pagination limits in the APIs (this is common for REST). This is highlighted in the script.
Any of this can be accomplished with Task Scheduler and cred keys. Azure just makes it a little easier and does a nice job logging.
This does take time to process. Depending on the size of your organization, it could take quite a while. Ours takes about 20-30 minutes. This is due to some of the cross references between the different APIs being processed in memory.

Create a Credential

In your Automation Account > Credentials, add a credential for the Web Service API User

Create an Azure Runbook

I'm not going to go into great detail but this is just a generic PowerShell Runbook. Copy the script above into the runbook.

Schedule the Runbook Job

Set a reasonable schedule to allow time for it to finish but not so frequent that no updates are likely to be there or you get flagged for hammering the API. We set ours to 4 hours.

Add the Script to your Runbook

Replace these lines as appropriate:

Credentials

$webserviceaccount = Get-AutomationPSCredential -Name 'UltiPro Sync User'

$ADManagerRunAsAccount = Get-AutomationPSCredential -Name 'ADManager Runas Account'

Your Customer API Key #####

$headers = @{'us-customer-api-key' = '#####'}

Your export file path

$exportpath = "\\FILEPATHHERE\UltiPro-User-Data"

$webserviceaccount = Get-AutomationPSCredential -Name 'UltiPro Sync User'
$ADManagerRunAsAccount = Get-AutomationPSCredential -Name 'ADManager Runas Account'

$exportpath = "\\FILEPATHHERE\UltiPro-User-Data"
$exportFile = '\Employee-Data.csv'
$headers =  @{'us-customer-api-key' = '#####'}

###########################################
#GET ACTIVE EMPLOYEES FROM ULTIPRO APIS
###########################################
#Collecting all active UltiPro users, 200 at a time. They do not support more than that through the API in one request.
#https://connect.ultipro.com/documentation#/api/817/Employment%20Details/get__personnel_v1_employment-details

write-Output "Searching Employment Details API for active employees"

$a=0
$employees = @()
$AgeObjCount = ""

do {
    $a++
    $tempObj = $null
    $URL = "https://service5.ultipro.com/personnel/v1/employment-details?employeeStatusCode!=T&primaryProjectCode=COMPUTER&per_page=200&page=$a"
    $tempObj = Invoke-RestMethod -Method 'Get' -Uri $url -Credential $webserviceaccount -headers $headers
    
    If ($tempObj.Count -ne 0)
    {$employees += $tempObj}

    else
    {$AgeObjCount = "end"}

} until ($AgeObjCount -eq "end")

$count = $employees.count

write-Output "Found $count employees"

###########################################################
#GET ORG LEVEL DETAILS FROM ULTIPRO APIS
###########################################################
#Org level description is not part of the normal employee details
#This lookup pulls the descriptions for later usage as "Department Name"
#https://connect.ultipro.com/documentation#/api/136

write-Output "Searching Org Level Details API for Company / Department Info"

#$OrgLevels = @()

$URL = "https://service5.ultipro.com/configuration/v1/org-levels"
$OrgLevels = Invoke-RestMethod -Method 'Get' -Uri $url -Credential $webserviceaccount -headers $headers

$SecondOrgLevels = $OrgLevels | Where-Object {$_.level -eq "2"}

###########################################################
#COLLECT ADDITIONAL EMPLOYEE DETAILS FOR ACTIVE EMPLOYEES
###########################################################
#Loop through all employees
#Load supervisor from AD based on the supervisor employee number
#Load name, address, etc. from the Employee Changes API

write-Output "Assembling employee details array"

$ObjectArray = New-Object System.Collections.Generic.List[System.Object]

foreach($employee in $employees)
{
    if($employee.supervisorEmployeeNumber.length -ne 0)
    {
        $manager = get-aduser -Filter * -Properties EmployeeID | Where-Object {$_.employeeID -eq $employee.supervisorEmployeeNumber} | Select-Object Name, distinguishedname
    }
    
    $employeeID = $employee.employeeID

    #Query Employee Changes API for specific employeeID vs. all employees
    #https://connect.ultipro.com/documentation#/api/199/Employee%20Changes/get__personnel_v1_employee-changes_%7BemployeeId%7D

    $URL = "https://service5.ultipro.com/personnel/v1/employee-changes/$employeeID"
    $employeeChanges = Invoke-RestMethod -Method 'Get' -Uri $url -Credential $webserviceaccount -headers $headers

    #Select only active records; it is possible that 1 employeeID can return multiple records if the person was terminated and rehired at a later date
    $employeeChangesActive = $employeeChanges | where-object {$_.employeeStatus -eq "A"}

    $department = $SecondOrgLevels | Where-Object{$_.code -eq $employee.orgLevel2Code}

    #Build temporary array to house data from difference sources
    $tempArray = New-Object System.Object

        $middle = ""

        if($employeeChangesActive.middleName.length -ne 0)
        {$middle = ($employeeChangesActive.middleName).substring(0, 1)}

        $WorkPhone = $null
        if($employeeChangesActive.workPhone)
        {$WorkPhone = "{0:+1 ###-###-####}" -f ($employeeChangesActive.workPhone -as [int64])}

        #Add attributes from employee-changes API
        $tempArray | Add-Member -MemberType NoteProperty -Name "givenName" -Value $employeeChangesActive.firstName
        $tempArray | Add-Member -MemberType NoteProperty -Name "middleName" -Value $middle
        $tempArray | Add-Member -MemberType NoteProperty -Name "sn" -Value $employeeChangesActive.lastName
        $tempArray | Add-Member -MemberType NoteProperty -Name "telephoneNumber" -Value $WorkPhone
        $tempArray | Add-Member -MemberType NoteProperty -Name "homePhone" -Value $employeeChangesActive.homePhone
        $tempArray | Add-Member -MemberType NoteProperty -Name "streetAddress" -Value $employeeChangesActive.employeeAddress1
        $tempArray | Add-Member -MemberType NoteProperty -Name "streetAddress2" -Value $employeeChangesActive.employeeAddress2
        $tempArray | Add-Member -MemberType NoteProperty -Name "l" -Value $employeeChangesActive.city
        $tempArray | Add-Member -MemberType NoteProperty -Name "st" -Value $employeeChangesActive.state
        $tempArray | Add-Member -MemberType NoteProperty -Name "postalCode" -Value $employeeChangesActive.zipCode
        $tempArray | Add-Member -MemberType NoteProperty -Name "physicalDeliveryOfficeName" -Value $employeeChangesActive.employeeAddress1

        #Add attributes from Organization Levels API
        $tempArray | Add-Member -MemberType NoteProperty -Name "department" -Value $department.description

        #Add attributes from employment-details API
        $tempArray | Add-Member -MemberType NoteProperty -Name "Title" -Value $employee.jobDescription
        $tempArray | Add-Member -MemberType NoteProperty -Name "Company" -Value $employee.companyName
        $tempArray | Add-Member -MemberType NoteProperty -Name "employeeID" -Value $employee.employeeNumber
        $tempArray | Add-Member -MemberType NoteProperty -Name "division" -Value $employee.orglevel1code
        $tempArray | Add-Member -MemberType NoteProperty -Name "extensionAttribute11" -Value $employee.orgLevel2Code
        $tempArray | Add-Member -MemberType NoteProperty -Name "extensionAttribute12" -Value $employee.orgLevel3Code
        $tempArray | Add-Member -MemberType NoteProperty -Name "extensionAttribute13" -Value $employee.employeeStatusCode
        $tempArray | Add-Member -MemberType NoteProperty -Name "employeeIDinUltiPro" -Value $employee.employeeID
    
        #Add Manager from AD referenced from employment-details API
        $tempArray | Add-Member -MemberType NoteProperty -Name "Manager" -Value $manager.distinguishedname

        #Potential future fields
        #$tempArray | Add-Member -MemberType NoteProperty -Name "preferredName" -Value $employeeChangesActive.preferredName
        #$tempArray | Add-Member -MemberType NoteProperty -Name "mail" -Value $employeeChangesActive.emailAddress
        #$tempArray | Add-Member -MemberType NoteProperty -Name "location" -Value $employeeChangesActive.workLocation
        #$tempArray | Add-Member -MemberType NoteProperty -Name "jobCode" -Value $employeeChangesActive.jobCode

    $objectarray.add($tempArray)
}

write-Output "Exporting aggregated employee list to CSV for consumption"
New-PSDrive -Name "L" -PSProvider FileSystem -Root $exportpath -Credential $ADManagerRunAsAccount
$ObjectArray | Export-CSV "L:\$exportFile" -NoTypeInformation
Remove-PSDrive -Name "L"

Create a ManageEngine Automation

This scoops up the CSV and maps the attributes exported from the script directly to the AD user attributes. Again, not going to go into the specifics. ManageEngine has supporting documentation for configuring their jobs.

Active Directory Upgrade Test Plans and Pitfalls

2020-06-10T08:45:00.000-07:00

I was recently asked to describe a test plan for upgrading forest/domain functional levels and any potential pitfalls. Just sharing some of my feedback to this community peer.

Probably the biggest "pitfall" would be any custom schema extensions. There are scripts to check for this sort of thing. In general, the domain/forest upgrades are benign. It's all the secondary stuff that you must watch for. Particularly, if you're putting in domain controllers with new names and IPs - that's where you'll get into trouble. I like to keep the IPs if I can.

Usually, I spin up a new server then pick a night to swap it for the old. Mostly that consists of:

Demote the old server
Shut down old server (IP is now free)
Verify server is missing from ADDS
Re-IP the new to what the old was.
Promote the new (again, IP first)
Watch for NTDS settings to be created automatically. Avoid adding them manually unless you have issues. Not seeing it in 5 minutes isn’t an issue. Give it 15-20 minutes if you need to.

These are the other issues to watch for. Not specific to the domain upgrade but cause more problems IMO.

LDAP pointed to specific names or IPs. I tend to push everyone towards a CNAME for LDAP that resolves to my DC of choice. Do not try to get cute and DNS round robin with A records. Just a single CNAME.

DNS scavenging is a role held by a single DC. If you're doing AD integrated DNS, make sure you move the scavenger role/config or you'll figure out in 6 months that you have a bunch of stale DNS records.

This should be obvious, but, inventory your fSMO roles and move them in advance. Not as scary as it sounds. **You’ll need to be schema admin to move one

Your PDC emulator handles domain time propagation. Make sure that your new PCD emulator is set to NTP in the registry. You can do this with w32tm. Be sure to pick a reliable external time pool source. All other DCs should be set to NT5DS in the registry, not NTP (same as any other domain member server/client)

Migrate any other jobs, agents, etc. I’ve seen several things on DCs.

Scripts to back up DNS, GPO, and so on. Check your scheduled tasks.
Make sure your firewall team doesn’t pull security logs. This can be done via WMI/DCOM or agent based.
Sometimes people like to get a Windows Backup of a primary. Watch for this. Make sure your backup system accounts for it if you’re not replacing it.

Watch for DNS

What are your other DCs pointed to? Check their network adapters as well as their forwarder configs.
What are your members pointed to?
Make sure you set your new DCs up properly. I like to do this…

Local domain controller peer (if there is one)
Remote domain controller peer
Loopback (127.0.0.1)

Don’t “kitchen sink” your DCs. Build them core, keep DHCP, RADIUS, file services, etc. off of them.

Get familiar with the following commands:

dcdiag /skip:systemlog

repadmin /replsum

repadmin /syncall /Aed

repadmin /kcc << helpful for jump starting partner connections in NTDS when replacing your DCs

DNS Bulk Import to Subdomain

2019-07-19T09:09:00.005-07:00

The subdomain in this example is "network". Making the FQDN for a device something like...switch1.network.mydomain.com.
Using -CreatePTR means you don't have to run separate commands to create the reverse lookups like you would with DNSCMD.

*We opted to keep our network devices in their own container for easier management.

Just get a csv and away you go.

###############################
$nodes = Import-Csv C:\temp\networknodelist.csv

foreach($node in $nodes)
{
$name = $node.name + ".network"
Add-DnsServerResourceRecordA -zonename mydomain.com -name $name -ipv4 $node.ip -CreatePtr
}
###############################

Qualys - Dashboards for Windows Server

2019-07-16T08:19:00.003-07:00

The details below are geared towards Sys Admins who need to get details and trending on vulnerabilities Windows Server systems. I'm a Windows guy. So I started with filtering out the noise of our Linux, Cisco, etc. Some unfortunate soul will have to deal with those later (maybe I'll help them make a dashboard).

**I am by no means a Qualys expert**

There are probably alternative ways to get this detail. This is what I was able to do with very little support and no training.

Create Tagging for OSes and Vulnerability Matching

Hurdle number 1: Getting Groovy

You will need Groovy Scripting. I'm sure there are ways to do it with XML or something. But the Groovy syntax is better documented and more widely used in the community from what I saw.

**Caveat: Groovy Scriptlet must be enabled for your tenant if you are in the cloud.**

I was missing the option in the tag rule engine drop down when I started this process. Support had to turn this on. It took 2 phone calls and several emails to get enabled AND working.

Hurdle number 2: Tagging all Windows Servers

It's pretty simple to query for OS using basic regex. Qualys will give you trouble though because of how it tags OS. Servers of the same OS/template will show up with a different Operating System. Even two network adapters on the same server may show differently. Short version: Qualys gives a best guess.

See below: Do I really have 14 Windows Server "Operating Systems"? No. I probably have 5ish (leaving aside Datacenter vs. Standard).

Windows Server 2012 R2 Datacenter 64 bit Edition is the same as Windows Server 2012 R2/8.1. Qualys just didn't get specific enough.

So we'll solve it by baking a little logic into the Groovy Script.

Go to your Tag page > New Tag:
Under Tag Rule > Rule Engine > Select Groovy Scriptlet

Windows Servers Query

###############################################################
if(asset.getAssetType()!=Asset.AssetType.HOST) return false;
if(asset.getOperatingSystem().contains("Windows Server")) return true;
if(asset.getOperatingSystem().contains("Windows 2008")) return true;
if(asset.getOperatingSystem().contains("Windows 2012")) return true;
if(asset.getOperatingSystem().contains("Windows 2016")) return true;
if(asset.getOperatingSystem().contains("Windows 2019")) return true;
###############################################################

Here we're returning false for anything that's not a host asset and true for all Windows Server OSes. But then we specifically search for all the variations of Windows 2008, 2012 and so on.

I opted not to use regex as stating "Windows" would return client OSes. Here's what that query looks like.

Windows Clients Query

Hurdle number 3: "But Shep, my server isn't showing up in the query."

I started with this well documented query. But we'll take it a bit further. This returns assets where the OS is null. Odds are, if you're not seeing an OS at all, you're missing credentials for root, local accounts, snmp, etc. Slightly harder to solve for non-Windows, domain joined systems.

NULL OS Query

###############################################################
if(asset.getAssetType()!=Asset.AssetType.HOST) return false;
return asset.getOperatingSystem()==null || asset.getOperatingSystem().trim().length()<=0;
###############################################################

I also created an "uncategorized" query so I could make sure I was capturing everything. i.e. show me everything I haven't expicitely tagged with an OS tag. In my case, this tag query would return Linux and Cisco. Eventually, when everything is tagged, this should return 0.

Uncategorized OS Query

###############################################################

if(asset.getAssetType()!=Asset.AssetType.HOST) return false;
if(asset.hasTag("Windows Server")) return false;
if(asset.hasTag("Windows Clients")) return false;
if(!asset.hasTag("No OS Found")) return true;
###############################################################

Note: we're using the "No OS Found" here. What we're saying is if the OS is NOT NOT found. i.e. there is actually an OS - just not Windows Server or Client.

Pro Tip: You can turn any query in Qualys into a not condition by placing "!" at the front.

Hurdle number 4: Getting systems with issues (Optional)

I say this step is optional because you can write these same queries into the dashboard widgets later. I find it easier to read and group with tags. The advantage is reusability. If you write the same query for severity 5s three times, you could have just made a tag and updated it once later when you want to add sev 4s.

Assets Based on Vulnerability Severity Query

###############################################################
if(asset.getAssetType()!=Asset.AssetType.HOST) return false;
return asset.hasVulnsWithSeverity(5)
###############################################################

Create a Dashboard

I think the biggest mental block for me was getting past the fact that Qualys treats Assets and Vulnerabilities differently. I was hoping to group, sort, filter assets AND vulnerabilities right on the Asset View page. Here we're strictly looking at Assets.

So go to your AssetView page
Actions > Create New Dashboard

Personally, I like to start with the 0-Day widget or an "Assets with..." widget. Anything that already has the conditional formatting already defined.

From there, you can drag and resize the various items. I opted stick with counts. I found the charts to be very limited.

This is where we start to leverage the tags. In the upper left, click the hamburger menu on the widget. Configure Widget.

Enter a Name
Enter a Query
Select "Compare with another reference query" (I leave defaults but it could be further refined)
Select "Collect trend data" (Collects history for this widget only)

Example Query

All Windows Servers with Zero Days

###############################################################
vulnerabilities.vulnerability.threatIntel.zeroDay:true and tags.name: Windows Server
###############################################################

Azure - Auditing Tag Assigments with PowerShell

2019-07-05T07:52:00.002-07:00

For now this is the best way I have found to effectively audit what tags are assigned to Azure resources. Specifically, I was trying to find items that weren't tagged with a "Cost Center". You could flip the logic and look for things that are tagged a certain way, null values, etc.

#Connect-AzAccount

Get-AzResource | select ResourceGroupName, Name, Resourcetype, Tags | Out-GridView

#Filter Tag not contains "Cost Center" or other appropriate tags

GPO PowerShell Script - Search for Group Policies with Links Disabled

2019-07-02T06:40:00.001-07:00

#Parse XML "<LinksTo>\<Enabled>" looking for $null entries. Output list.
#Can take a few minutes to run

$disabledlinks = New-Object System.Collections.Generic.List[System.Object]

$GPOs = Get-GPO -All

ForEach ($gpo in $GPOs)
{
    [xml]$gpoxml = Get-GPOReport -guid $gpo.Id -ReportType xml

    if(($gpoxml).GPO.LinksTo.Enabled -eq $false)
    {
        $disabledlinks.add($gpoxml.gpo.name)
    }
}

$unlinkedGPOs | out-gridview

GPO PowerShell Script - Search for Group Policies with No Links

2019-07-02T06:38:00.002-07:00

#Parse XML "<LinksTo>\<SOMPath>" looking for $null entries. Output list.
#Can take a few minutes to run

$unlinkedGPOs = New-Object System.Collections.Generic.List[System.Object]
$GPOs = Get-GPO -All

ForEach ($gpo in $GPOs)
{
    [xml]$gpoxml = Get-GPOReport -guid $gpo.Id -ReportType xml

    if(($gpoxml).GPO.LinksTo.SOMPath -eq $null)
    {
        $unlinkedGPOs.add($gpoxml.gpo.name)
    }
}

$unlinkedGPOs | out-gridview

Azure PowerShell Script - Audit Recovery Plans for Missing Protected Items

2019-07-01T14:07:00.000-07:00

Azure doesn't have a great way at the moment to report on items in a Recovery Vault that are Replicated Items but not yet assigned to a Recovery Plan. That's what this script does - list out all items in a vault and what plan (if there is one) that they're assigned to.

This is a bit complicated because the GUIDs assigned to the protected item aren't necessarily the same as what was assigned as the "group protected item" ID.

In this scenarios, we'll

Part 1

Collect the vault, on-prem config server, and query the service fabric.
Get all of the protected servers
Get the recovery plans in the vault

Part 2

Loop through the plans, getting the groups
Loop through the groups in the plan, dumping a complete list of protected items in the plan
Add all protected items in the plan to an array with the recovery plan and group name

Part 3

Add protected items to the array that weren't found in a recovery plan. Leaving the plan and group blank

######################################################

Connect-AzAccount
$ResourceGroup = "RGNAME"
$VaultName = "VAULTNAME"
$configServer = "CONFIGSERVERNAME"

# Get the ASR vault
$vault = Get-AzRecoveryServicesVault -ResourceGroupName $ResourceGroup -Name $VaultName

# Set the context of the vault. This is required for all future commands
Set-AzRecoveryServicesAsrVaultContext -Vault $vault

# Fabric is essentially a configuration server
# FriendlyName is the name of the config server. You can just run Get-AzRecoveryServicesAsrFabric to list all the config servers to get the right name
$asrfabric = Get-AzRecoveryServicesAsrFabric -FriendlyName $configServer

# Get the fabric container. It holds the replication policies and type of replication
$asrcontainer = Get-AzRecoveryServicesAsrProtectionContainer -Fabric $asrfabric

# List all the items that are in a protected state by friendly name and resource ID
$ProtectedVMs = Get-AzRecoveryServicesAsrProtectableItem -ProtectionContainer $asrcontainer | ? { $_.ProtectionStatus -eq 'Protected' } | Select FriendlyName,ReplicationProtectedItemId | sort FriendlyName

# List all recovery plans
$RecoveryPlans = Get-AzRecoveryServicesAsrRecoveryPlan | select -expand name

# Array to house the protected items missing recovery plans
$missingRP = New-Object Collections.ArrayList

# Array to house the protected items missing recovery plans
$ObjectArray = New-Object System.Collections.Generic.List[System.Object]

foreach($recoveryplan in $recoveryplans)
{
    $plandetails = Get-AzRecoveryServicesAsrRecoveryPlan -Name $recoveryplan

    # This will list out the replicated items that are in the recovery plan by resource ID. This may need to be broken out into multiple foreach loops b/c I only tested with a single VM in the recovery plan.
    foreach($group in $plandetails.Groups)
    {
        foreach ($groupprotecteditem in $group.ReplicationProtectedItems)
        {
            $VMmatch = Get-AzRecoveryServicesAsrProtectableItem -ProtectionContainer $asrcontainer | where { $_.ProtectionStatus -eq 'Protected' -and $_.ReplicationProtectedItemId -eq $groupprotecteditem.id} | Select -expand FriendlyName
            $tempArray = New-Object System.Object
            $tempArray | Add-Member -MemberType NoteProperty -Name "VMName" -Value $VMmatch
            $tempArray | Add-Member -MemberType NoteProperty -Name "RecoveryPlan" -Value $recoveryplan
            $tempArray | Add-Member -MemberType NoteProperty -Name "Group" -Value $group.name
            $tempArray | Add-Member -MemberType NoteProperty -Name "ProtectedItem" -Value $groupprotecteditem
            $tempArray | Add-Member -MemberType NoteProperty -Name "ID" -Value $groupprotecteditem.id
            $ObjectArray.add($tempArray)
        }
    }
}

foreach($vm in $ProtectedVMs)
{
    $status = $ObjectArray.VMname.contains($vm.FriendlyName)
    if($status -eq $false)
    {
        $tempArray = New-Object System.Object
        $tempArray | Add-Member -MemberType NoteProperty -Name "VMName" -Value $vm.FriendlyName
        $ObjectArray.add($tempArray)
    }
}

$ObjectArray | Out-GridView

######################################################

SCCM Compliance Item Bitlocker Status

2017-04-10T14:12:00.001-07:00

We recently implemented Health Attestation in SCCM 1610. That took care of reporting requirements for our Windows 10 clients. However, in order to completely eliminate MBAM from our environment we still needed to report on legacy clients. So, how to create a compliance item that queries for Bitlocker status;

**Side note: Some troubleshooting done for the Windows 10 portion of with Health Attestation.
https://social.technet.microsoft.com/Forums/en-US/359c1cb5-5bb0-42a2-9151-0e0b3d769bcd/missing-health-attestation-data-in-sccm?forum=ConfigMgrCompliance

The script;

Verify that bitlocker is enabled (=1) and encryption cipher method is 256 (=4)
Return Compliant or Non-Compliant

#############################

$BitlockerStatus = Get-WmiObject -Namespace “root\CIMV2\Security\MicrosoftVolumeEncryption” -Class Win32_EncryptableVolume -ErrorAction Stop| ?{$_.DriveLetter -eq "C:"} | select EncryptionMethod,ProtectionStatus

#Status (0 = disabled, 1 = enabled)
#Method {0 = none, 1 = 128diffuser, 2 = 256 diffuser, 3 = 128(default), 4 = 256(desired)}

#Verify that Bitlocker is enabled and AES 256 is used
if($BitlockerStatus.ProtectionStatus -eq 1 -and $BitlockerStatus.EncryptionMethod -eq 4)
{write-host "Compliant"}
else
{write-host "Non-Compliant"}
#############################

The compliance item;

View the deployment status under monitoring

Viewing the individual client report

Find Expiring Certificates in Local Computer Personal Store

2017-02-17T13:02:00.001-08:00

I originally wrote this as a monitor for SolarWinds. I'm posting it here as it could also be used to do a foreach against an OU, csv, etc. Basically, search through the computer personal certificate store and return the certs that expire in X days.

########################

Import-Module WebAdministration
$Certificates = dir Cert:\localmachine\my
$today = get-date
$expirationcounter = 0

foreach ($cert in $Certificates)
{
    $thumbprint = $Cert.Thumbprint;
    $certdetails = Get-ChildItem Cert:\LocalMachine\my\$thumbprint | Select NotAfter,Subject,Issuer;

    if($certdetails.notafter -lt $today.AddDays(60))
    {
        $expiresin = $certdetails.NotAfter - $today
        Write-Host "Statistic:" $expiresin.days
        Write-Host 'Message: ' $certdetails.Subject
        $expirationcounter++
    }
}

if($expirationcounter -eq 0)
{
    Write-Host 'Statistic: ' 0
    Write-Host 'Message: No Certificates Found'
}
########################

Find Orphaned Home Drives for Deleted AD Accounts

2016-05-11T12:56:00.003-07:00

This script will find all home drives and for each test if the AD user still exists. If it doesn't, it will gather the folder size and output that with the user ID to a text file. Good one for general housekeeping.

-----------------------------

#Compare all H drive folders to AD user accounts.
#If no match is found output a file with the user name and folder size in MB.
#Add all folders found and output total at the end

$HomeDriveFolders = Get-ChildItem -path "H:\Users" | select -expandproperty Name

$totalsize = 0

foreach($folder in $HomeDriveFolders)
{
    $user = ""
    $user = $(try {get-aduser $folder | select -ExpandProperty SAMACCOUNTNAME} catch {$null})

    if ($user -ne $folder)
        {

        $foldersize = (Get-Item "H:\Users\$folder").GetFiles() | Measure-Object -Sum Length
        $foldersize = [math]::Round($foldersize.sum / 1MB)
        $totalsize = $totalsize + $foldersize
        "$folder,$foldersize" | out-file H:\usercomparisonexport.txt -append
        }
}

"---------------------((TOTAL))",$totalsize | out-file H:\usercomparisonexport.txt -append

Disk Cleanup Missing Server 2008 and Later

2016-02-04T12:37:00.002-08:00

There is plenty of info out there about how to go about getting to Disk Cleanup (cleanmgr.exe). This is just a quick little batch file to restore it. You do have the option of installing the Desktop Experience features through Server Manager. I personally don't like that option because of the junk that comes with it. Try this out instead...

////////////////////////
Copy and Paste into notepad, save as "AddDiskCleanup.bat"
xcopy "C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr_31bf3856ad364e35_6.1.7600.16385_none_c9392808773cd7da\cleanmgr.exe" "%systemroot%\System32"

xcopy "C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.1.7600.16385_en-us_b9cb6194b257cc63\cleanmgr.exe.mui" "%systemroot%\System32\en-US"

cleanmgr.exe
///////////////////////

From this point on you can simply go to start > run > cleanmgr.exe

Active Directory: Find All Users with Specific UPN Suffix

2015-11-04T12:31:00.007-08:00

$ou = "OU=Users,DC=mydomain,DC=local"
Get-ADUser -filter * -SearchBase $ou | Where-Object {$_.userprincipalname -like "*domain.com"} | Export-Csv C:\temp\UPN.csv

Active Directory: Bulk Update User UPNs

2015-10-22T13:33:00.001-07:00

#Bulk Update UPNs

Import-Module ActiveDirectory

#SPECIFY NEW SUFFIX AND OU TO CHANGE
$newSuffix = '@domain.com'
$ou = "OU=Users,DC=mydomain,DC=local"

#EXECUTE CHANGES
Get-ADUser -Filter * -SearchBase $ou | ForEach-Object {
$newUpn = $_.SamAccountName + $newSuffix
Set-ADUser -ID $_ -UserPrincipalName $newUpn
}

Find All Windows Servers Not in a Group in Active Directory

2015-10-21T12:21:00.004-07:00

Example:
(&(objectCategory=computer)(operatingSystem=*Windows Server*)(!memberof:1.2.840.113556.1.4.1941:=CN=ServerHardening,OU=Groups,DC=mydomain,DC=local))

& = And the following conditions together
objectCategory = is it a user, computer, etc.
operatingSystem = what is found in the computer object operating system tab "name" field
! = Condition is "Not"
memberof = Find members of the group
1.2.840.113556.1.4.1941 = Tells the lookup to recurse the member groups of the super group

You must use the full distinguished name of the group in question.

Of course you can adjust this to specific OS versions or group names or even extend it to include additional references to more groups.

Find Inactive Systems to Clean UP Active Directory

2015-10-21T11:57:00.001-07:00

Shows all computers that haven't contacted the domain in 8 weeks or more. You can also use this for user objects. Run in PowerShell with the "sort-object" to sort by the DN which starts with the name of the system so it helps if you have a computer naming convention.

dsquery computer -inactive 8 | sort-object

Get Useful Mailbox Information from Exchange Shell

2015-09-09T11:26:00.000-07:00

Just a quick one-liner for exporting usable info about all mailboxes. Be sure to adjust your domain controller (or leave it out if in a single domain environment).

get-mailbox -ResultSize unlimited -DomainController ADC1 | Select-Object DisplayName,PrimarySmtpAddress,ExchangeUserAccountControl,RecipientTypeDetails,ServerName,Database,ProhibitSendQuota,ProhibitSendReceiveQuota,UseDatabaseQuotaDefaults,IssueWarningQuota,MaxSendSize,MaxReceiveSize,DeliverToMailboxAndForward,HiddenFromAddressListsEnabled,WhenChanged | Export-CSV C:\mailboxes.csv

Returns some good info about mailbox size quotas, send/receive limits, mailbox type, forwarding and address book status.

Active Directory: Bulk Update Logon Script

2015-06-19T08:39:00.005-07:00

Modification of my bulk update home drive script.

# CHANGE LOGON SCRIPT
Import-Module ActiveDirectory
Get-ADUser -Filter * -SearchBase "OU=Users,DC=contoso,DC=com" | Foreach-Object{
$sam = $_.SamAccountName
Set-ADuser -Identity $_ -ScriptPath "LOGON-NEW.bat"
}

Active Directory: Bulk Update Home Folder Path

2015-06-19T08:37:00.002-07:00

Can't take any credit for this one. Just happened to stumble on it in a forum post. Just putting it here for future reference. Added a couple of checks for good measure.

# CHANGE HOME DIRECTORY

$SearchOU="OU=Users,DC=contoso,DC=com"

Import-Module ActiveDirectory

#Search for all users in OU that are not disabled or with blank homedirectory
Get-ADUser -Filter * -SearchBase $SearchOU | where-object {$_.enabled -eq $true -AND $_.homedirectory -ne ""} | Foreach-Object
{
$sam = $_.SamAccountName
Set-ADuser -Identity $_ -HomeDrive "H:" -HomeDirectory \\SERVER02\Users\$sam
}

SBS + DirSync for Office365

2015-03-24T11:16:00.001-07:00

Learned today that SBS still doesn't support DirSync. You can install it on a domain controller which didn't used to be the case. So the requirement remains 2008/2012, including DCs. No SBS 2011 (what I was trying in this case).

I haven't seen any information to suggest anyone has gotten it to work. Feel free to share your experience if you have. My assumption is that SBS being a different beast with its extra SQL Express and such just can't do it.

Cisco RADIUS Authentication w/ Active Directory and Network Policy Server

2014-08-27T11:24:00.001-07:00

I'll try to keep this short and sweet. It took me a bit to find the exact commands I used. The configuration below has been validated to work on Cisco routers, switches, and voice gateways.

Part 1. RADIUS Server Configuration

Assuming you already have a Network Policy Server installed on a DC somewhere...

RADIUS Clients and Servers > RADIUS Clients > Right Click > New RADIUS Client

Add Friendly Name
IP
Vendor = Cisco
Shared Secret

Manual
Enter a key (same as used on the cisco device)

Policies > Network Policies > Right Click > New (use defaults unless specified)

Policy Name (I just called this the same as the client friendly name)
Conditions

Windows Groups: AD Group with network administrator accounts
Client Friendly Name: same as friendly from "RADIUS Clients" (prevents policies from inadvertently being applied to the wrong devices. Optional precaution)

Authentication Methods > Check "Unencrypted authentication"
"Configure Settings"

RADIUS Attributes

Standard > Remove Framed-Protocol PPP
Vendor Specific > Add (allows user to launch in to enable mode by default)

Vendor = Cisco
Attribute Name = Cisco-AV-Pair
Value = shell:priv-lvl=15

Part 2: SSH to Cisco Switch/Router

You'll need...

VLAN/IP that authentication will originate from
IP of your RADIUS Server
RADIUS Secret used in part 1.

Run the following from the command line... conf t

aaa new-model

aaa authentication login default group radius local

aaa authorization exec default group radius local

ip radius source-interface <<VLAN/IP>> Interface (e.g. Vlan1 or GigabitEthernet0/0)

radius-server host <<IP of RADIUS Server>> auth-port 1645 acct-port 1646

radius-server key <<RADIUS SECRET>>

service password-encryption

Part 3: Testing

Ensure your admin account can log in
Ensure that other accounts cannot log in. Especially if you have other RADIUS auth policies like we did.
Validate that your local account no longer works by default.
"Disable" your RADIUS client. Validate that your local account works as a fail safe.

Task Scheduler Change Audit Trail

2014-06-26T08:02:00.001-07:00

Background

Walked in today to some questions about why an important scheduled tasks <cringe face here> had been disabled. Turned out that it was intentional so no big deal. However, the question I was asked before we knew it was intentional was this… “how do we see an audit trail for task scheduler changes?” The short answer is that it’s not enabled by default and it’s not super intuitive.

This has to be auditpol.exe

More Here

http://technet.microsoft.com/en-us/library/cc731451.aspx

Auditing consists of categories and subcategories. To do this you need to enable a subcategory. Technically, this is the recommended way to enable non-standard auditing. You don’t want to audit too much or you’ll make your log difficult to read, fill up space, consume processing time, etc. Per Micrsoft…

http://technet.microsoft.com/en-us/library/cc766468%28v=ws.10%29.aspx

If you are going to use auditing subcategories, you should not use Group Policy to define and distribute your auditing policies. The Group Policy Management Console configures only the top-level auditing categories and enables all of the subcategories within the category and thus cannot be used to set more targeted audit policy. Instead, auditing policy that uses auditing subcategories must be defined by using the command-line tool auditpol.exe and distributed by means of a script.

Execution

So how do I do that? Pretty simple:

From a command prompt:

auditpol.exe /get /category:"Object Access"

I can see that I'm currently set to all "Failure" only which inherited from the Local Policy parent "Object Access". I want to change "Other Object Access Events" which is sort of a misc set of audit events for Task Scheduler and COM+.

http://technet.microsoft.com/en-us/library/dn319119.aspx

All of that to say, here is the command to enable Task Scheduler auditing to the Event Viewer Security logs.

auditpol.exe /set /subcategory:"Other Object Access Events" /Success:Enabled /Failure:Enabled

You can verify your change in the Security log. Event ID 4719.

So I went and disabled/enabled a scheduled task. Now I can see these logs.

We use System Center Operations Manager. So at this point I could capture on the various event IDs (also in the link above) that this new audit log will generate.

Hopefully this will shortcut the process for you. As you can see, I had to pull together a few resources to understand what I really needed.

Grant Access to Specific Runbooks in Orchestrator

2014-04-02T10:43:00.001-07:00

Since I had a hard time finding a clear guide on the topic here goes...

As a systems administrator / domain admin I typically have access to just about any system. As such, no big deal for me to be in the "users" group recommended for the initial Orchestrator install. This group is a bit of a misnomer. These are your designers. So in my case I have a group called “app-scorch-users” which consists for systems administrators.

So the issue is, how to provide runbooks specific to other roles. I want my DBA, developers, and so on to be able to execute their runbooks from the web console with ease but only those we choose. i.e. DBAs can’t run developers runbooks.

I started by creating a domain group: app-scorch-operators. Anyone who will run (not design) runbooks will go in this group.
Add that group to the root Runbooks container (right click > permissions)
Restrict its access to "read"
Go to Advanced > Select the group > change "Applies to:" to "This object only"

The purpose of the operators group is simply to grant access to read objects under the “Runbooks” node in the connections pane.

This opens up the ability to assign perms to users/group on the subfolders or directly to runbooks. In this example, I am attempting to grant several individuals to a folder (you could create domain groups per folder too).

So I'll go to the "Patching" folder > Right click > Permissions
Add any users or groups you'd like to execute runbooks with ONLY Read

Once added, you'll need to add one more thing…."Publish". Otherwise you'll get an error stating the user must have publish to run a runbook.
Go to Advanced > Edit > "Show advanced permissions"
Read properties and list contents will already be checked.

Now when your "Operators" go to the web Orchestrator Console they'll only see the folders where they were granted read/publish. (note below SC2012 Solutions from the first screenshot is not displayed)