Clatent

Technology | Fitness | Food

Excel

Getting Started with 365AutomatedLab Part 3

by Leave a Comment

Adding/Removing a User from Groups by User Role

I have to say that this function is one of my favorites. The reason why is how many companies can use only dynamic groups for adding users to their required groups? Using this function, you can create any job role name, then add the groups that job role requires. If you need help mass created groups check out Creating Groups with Excel. It can become the source of truth for standard groups per job role so you are only updating one location. Please test on your dev tenant first, but with most of the other cmdlets in this module they can be used in production, after proper testing.

Adding a User to Groups by User Role

The quickest way to add groups to a user is to use the 365DataEnvironment Excel file in the LabSources folder. I’ll show you that now, and how to customize it for your environment.

New-CT365GroupByUserRole -FilePath "C:\\365AutomatedLab\\LabSources\\365DataEnvironment.xlsx" -UserEmail mwhite@yourdomain.onmicrosoft.com -Domain yourdomain.onmicrosoft.com -UserRole NY-IT

This one line of PowerShell will add Mary White to all the groups associated with the user role “NY-IT,” which are “IT 365 Group, IT, IT NY, Printer – NY – 1, and Printer – NY – 2.”

FilePath: Is the location of the Excel workbook

UserEmail: The full email of the user you want to add groups to

Domain: The domain of your tenant

UserRole: This be the name of the worksheet for that user role.

If you want to create your own user roles, all you have to do is create a new worksheet and label it with the user role you want. Then you will need 4 columns which will be “DisplayName, PrimarySMTP, Description, and Type.” For type, the 4 different options are “365Group, 365MailEnabledSecurity, 365Distribution, and 365Security” depending on which groups you want to add them to. See image below for the template:

You can create as many user roles as you want. I used NY-IT naming convention as an example, but you can use whichever you want, as I know there are too many variations on how companies handle this and didn’t want to do a validateset on it.

That’s it, that is how you create the different job roles then how you add those groups to a user!

Removing a User from Groups by Job Role

I have a feeling after reading how to add, you already know how to remove groups from a user.

Remove-CT365GroupByUserRole -FilePath "C:\\365AutomatedLab\\LabSources\\365DataEnvironment.xlsx" -UserEmail mwhite@yourdomain.onmicrosoft.com -Domain yourdomain.onmicrosoft.com -UserRole NY-IT

Was I right? I really try to make these as simple as possible as I know we all are busy enough, and don’t want to make your life any busier.

Summary

You made it! Now you can easily add and remove user groups by job role with an Excel workbook. I know this is in 365AutomatedLab, but once you have tested, you could use this in production as it is great for onboarding and offboarding users.

As always please feel free to reach out if you have any questions, comments, concerns on this project or any others! If you can please star the GitHub repository as it will help others see it. Have a great day!

GitHub: https://github.com/DevClate/365AutomatedLab

Part 1 – Creating Users with Excel

Part 2 – Creating Groups with Excel

Getting Started with 365AutomatedLab Part 2

by Leave a Comment

Adding/Removing Groups

Adding and removing groups with 365AutomatedLab is as easy as it is for adding and removing users(If you missed part one, check out Getting Started with 365AutomatedLab Part 1) It can add and remove the 4 different groups(Group, Mail Enabled Security, Distribution, and Security) that 365 has from one excel sheet. As of right now it will fill in the Display Name, Primary SMTP, and Description. I’ll be adding more parameters, but wanted to get at least these available. What are some other parameters you want to see first?

Adding Groups

Now the fun part, adding groups with one line of code. But, first we need to have a worksheet named “Groups” with the column headers

  • DisplayName
  • PrimarySMTP
  • Description
  • Type

After you have created those, you can input the data that you want in those fields. Remember for type you will use 365Group, 365MailEnabledSecurity, 365Distribution, or 365Security, which will tell 365 what type of group you want created. If you don’t have test data feel free to go to the already premade Excel workbook in the LabSources section in the repository. Below is a quick snapshot of the Excel workbook.

Data all set? Let’s run this little One-Liner to import all these groups into 365!

New-CT365Group -FilePath C:\\Scripts\\LabSources\\365DataEnvironment3.xlsx -UserPrincipalName admin@yourdomainname.onmicrosoft.com -Domain yourdomainname.onmicrosoft.com

Depending on when you last logged in, you’ll have to confirm access twice, once for Exchange Online and once for Graph. If you don’t allow these, the script will look like it froze on you. Trying to save you some of the headaches that I went through!

Your 365 Portal should now look like this

All set! That’s really how easy it is!

Removing Groups

The beauty of this setup, is now you only have to one PowerShell command, and you are all set.

Remove-CT365Group -FilePath C:\\Scripts\\LabSources\\365DataEnvironment3.xlsx -UserPrincipalName admin@yourdomainname.onmicrosoft.com

All done, now all your groups in your Excel workbook are removed!

Summary

Wasn’t it easy? Testing larger data sets will be a breeze or setting up and removing tests. You should never have to test in a production environment, as you can copy those groups to the Excel workbook and import them into your Dev tenant. I hope you found this helpful and makes your life easier as it has mine. If you have any recommendations or questions, please feel free to reach out and/or create a Github Issue.

GitHub: 365AutomatedLab

Extracting Excel Worksheet Names to a CSV

by Leave a Comment

I know it’s Friday, but I’m changing it a bit up today. I just created this little script that I think could be helpful to others. This one isn’t for the masses, but I have a feeling it will help out more than a few people, and trigger some more ideas for script ideas.

# Specify the path to the Excel file
$excelFilePath = "C:\Scripts\WorksheetTitle.xlsx"
$outputCsvPath = "C:\Scripts\exportworksheetlabels.csv"

if (!(Test-Path $ExcelFilePath)) {
    Write-Error "Excel file not found at the specified path: $ExcelFilePath"
    return
}

# Open the Excel file
$excel = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList (Get-Item $excelFilePath)

# Get the worksheet names
$worksheetNames = $excel.Workbook.Worksheets | ForEach-Object { $_.Name }

# Add quotes around each worksheet name and convert them to a comma-separated string
$commaSeparatedWorksheetNames = ($worksheetNames | ForEach-Object { '"' + $_ + '"' }) -join ','

# Export the worksheet names to a CSV file
$commaSeparatedWorksheetNames | Set-Content -Path $outputCsvPath

# Dispose the ExcelPackage object to release resources
$excel.Dispose()

What this script does, is takes all of your worksheet names in an excel sheet, and exports them with quotes and commas as if they were an array. An example would be “Dog”, “Cat”, “Cheetah”, where Dog, Cat, and Cheetah were the 3 different worksheet names. Don’t ask how I picked those names, but I’d be curious to see who has those worksheet names in an excel workbook!

Now you are probably wondering what the use case for this is, and for me, which I found very useful was we have an excel workbook that is separated by job title. And inside each of these worksheets are the 365 Distribution/Security Lists and 365 Groups for that title. This makes it so we have one “database” of lists, and only need to make the change once, if a standard changes. This works for both adding and removing them. But, the user who is adding/removing these groups for that user, needs to know the exact title for that user and how it is spelled on the worksheet…

This is why we export the titles this way, so then we can copy and paste them into a validateset within the function for the script, so the user has tab completion and so the script won’t even start without having the correct title.

Hope this helped you out and/or gave you some more ideas, and if you see anyway it can be improved, I’m all about being more efficient. Let me know if you would want to see the script to add users by title from an excel worksheet. Have a great Friday!

Github – Copy-ExcelWorksheetName