It was decided to take the above two scripts further and take out the manual collation of data, and to offer slices of the dataset to highlight ‘High privileged users’, ‘60+days inactive/idle’, ‘90+days PW not changed, ‘180+days disabled’.
To accomplish this I decided to use ‘Talend Open Studio’, A tool I had used in the past to process Qualys data into KPI’s for vulnerability management. It is free software and open source, based upon eclipse ide.
The task involved running the two scripts ‘ADPrivilegeAccounts-Audit-v3.1.ps1’ & ‘AD-User-Last-Logon.ps1’.
Stitching the two output files together, like a DB join, normalising the date fields, creating new columns that calculate the date difference from current date to that of either ‘last logon’ or ‘password last set’, outputting the dataset and slices.
Attaching output files to email and sending.
Cleaning up temp files.
The directory structure to run this was as follows;
Data < temp files wrote here
Scripts < powershell scripts invoked here
Etc < various folders from the talend job
I setup the scheduled job on the PC in the comm room XYZ to run Monday at 08:00 weekly
I extracted zipfile to the root of C: drive like c:\TS-Job so that command paths do not break.
Requires Java as job was built in talend studio.
For Windows 7 install the RSAT tools, and add all features in program files.
I set the task to run as NETWORK SERVICE and to my surprise it works with AD.
The scheduled task triggers C:\TS-Job\ProcessReport\ProcessReport_run.bat
I had to edit above script to include an updated java path before the java command;
I also copied from my pc the JRE folder to C:\TS-Job\jre1.8.0_111 as the version 6 was too old to run the job.
Also had to run as administrator from cmd.exe
c:\windows\syswow64\WindowsPowerShell\v1.0\powershell.exe -command set-executionpolicy unrestricted
Zipfile can be obtained from https://github.com/kempy007/AD-Reports
Talend Job looks like below;