I am writing a program to automate recording process, node → powershell → excel-sheet. It sort of works for now, with little to no formatting of data (which is also a problem that I am unsure of how to solve), but the main problem is that I have hard coded a large portion into it (refer to ADPropertiesFunction.mjs).
Sample Data:
user 1 → (Name: User1), (Group: ['LoggedInViaOidcSso', 'bitbucket-users', 'jira-system-administrators'])
user 2 → (Name: User2), (Group: ['Group not found in hardcode', 'bitbucket-users', 'jira-software-user'])
user 3 → (Name: User3), (Group: ['Guests', 'Domain Admins', 'jira-system-administrators'])
user 4 → (Name: User4), (Group: ['LoggedInViaOidcSso', 'bitbucket-users', 'Schema Admins'])
user 5 → (Name: User5), (Group: [])
Code/Files Used:
ADPropertiesFunction.mjs
import { exec, execSync } from 'child_process';
import { promisify } from 'util';
var userList;
var userGroupList;
const searchUsers = () => {
try {
console.log("Running searchUsers.ps1...");
const commandForFinding = `./searchUsers.ps1`;
var usersName = execSync(
commandForFinding,
{ shell: "powershell.exe" }
)
var arr = usersName.toString().split(/\r\n/);
var len = arr.length;
var i;
for (i = 0; i < len; i++)
arr[i] && arr.push(arr[i]);
arr.splice(0, len);
var results = arr.map(element => {
return element.trim();
});
results.splice(0, 2);
userList = results;
} catch (error) {
console.error(error);
}
}
const searchUserGroup = (Username) => {
var commandForFindingGroup = `./FindADUserGroup.ps1 ${Username}`;
var Group = execSync(
commandForFindingGroup,
{ shell: "powershell.exe" }
)
var results = [];
userGroupList = "";
var arr = Group.toString().split(/\r\n/);
var len = arr.length;
var i;
for (i = 0; i < len; i++)
arr[i] && arr.push(arr[i]);
arr.splice(0, len);
results = arr.map(element => {
return element.trim();
});
results.splice(0, 2);
// userGroupList = results
if (results.length != 0) {
if (results.includes("bitbucket-users"))
userGroupList += "Bitbucket User"
if (results.includes("confluence-users"))
userGroupList += " Confluence User"
if (results.includes("LoggedInViaOidcSso"))
userGroupList += ""
if (results.includes("jira-software-users"))
userGroupList += " Jira User"
if (results.includes("jira-servicedesk-users"))
userGroupList += " Jira Service Desk User"
if (results.includes("Group Policy Creator Owners"))
userGroupList += " Group Policy Creator Owners"
if (results.includes("Domain Admins"))
userGroupList += " Domain Admins"
if (results.includes("Enterprise Admins"))
userGroupList += " Enterprise Admins"
if (results.includes("Schema Admins"))
userGroupList += " Schema Admins"
if (results.includes("Administrators"))
userGroupList += " Administrators"
if (results.includes("Guests"))
userGroupList += " Guest"
if (results.includes("Denied RODC Password Replication Group"))
userGroupList += ""
if (results.includes("confluence-administrators"))
userGroupList += " Confluence Administrator"
if (results.includes("bitbucket-system-administrators"))
userGroupList += " Bitbucket System Administrator"
if (results.includes("jira-system-administrators"))
userGroupList += " Jira System Administrator"
}
}
const SaveExcel = () => {
var j = 1;
for (var i = 0; i < userList.length; i++) {
searchUserGroup(userList[i]);
const commandForExcelCmd = `./ADPropertiesFunction.ps1 ${userList[i]} ${j} "${userGroupList}"`;
execSync(
commandForExcelCmd,
{ shell: "powershell.exe" }
)
console.log("./ADPropertiesFunction.ps1 " + userList[i] + " " + "\"" + userGroupList + "\"");
j += 3;
}
}
searchUsers();
SaveExcel();
ADPropertiesFunction.ps1
$ADusername = $args[0]
$identity = $args[1]
$group = $args[2]
# Importing Active Directory module into PowerShell session
import-module activedirectory
# Excel opening operation section
$ExcelObj = New-Object -comobject Excel.Application
# $ExcelObj.visible = $true #To show Excel in foreground
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\Users\Administrator\Downloads\ADPropertiesVer1\testADpropertiesVer1.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("Sheet1")
$ExcelWorkSheet.Activate()
# Data processing section
$ADuserProp = Get-ADUser $ADusername -properties name, DistinguishedName | select-object name, DistinguishedName
$ExcelWorkSheet.Cells.Item($identity, 1) = $ADuserProp.name
$ExcelWorkSheet.Cells.Item($identity+1, 1) = $ADuserProp.DistinguishedName
$ExcelWorkSheet.Cells.Item($identity+2, 1) = $group
# Save the XLS file and close Excel
$ExcelObj.DisplayAlerts = $FALSE
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)
FindADUserGroup.ps1
$username = $args[0]
(Get-ADUser $username -Properties MemberOf).memberof | Get-ADGroup | Select-Object name
searchUsers.ps1
Get-ADUser -filter * | select-object name
From ADPropertiesFunction.mjs, you would have seen that the large portion of cross checking the users' identity is hardcoded onto the code. I found it is only possible to individually cross-check each group type, but for large scale, with a large groups pool, It is not practical to manually cross check each group's existence. Is there any simpler way to check group's existence without hardcoding a if check for each?
Also, regarding the formatting of the output. if the above problem is solvable in such a manner, where I am able to return-get each group as an string with an array, I can add the groups in excel, one for each column.
Even better, making the excel to give the look and feel of a "checklist" like:
Name|Admin|jira-user|bitbucketuser|bitbucketadmin|...
User1|1 |0 |1 |1 |...
User2|1 |0 |0 |1 |...
User3|1 |1 |1 |0 |...
.
.
.
Any assistance would be helping me a lot!