Please check out my other post about PowerShell, which is titled “PowerShell Script to generate queries“.
I had another post that showed the script, which could look up a domain’s IP and create a query for a specific product I use at my job. My boss gave us another script that we can use to enter a user’s email, and the script will list all the groups to which the member belongs. I combined these two scripts. The result of the combination is shown below. I named this script “daily.ps1” because I use this script daily at work.
function Get-Ad-Groups {
$email = Read-Host -Prompt "Enter the Email address you want to query";
$which_groups = "all";
$obj = $null;
$username = "";
$obj = Get-aduser -Filter {UserPrincipalName -eq $email -and Enabled -eq $true } -Properties Enabled, UserPrincipalName, SamAccountName, mail | select SamAccountName
$username = $obj.SamAccountName;
$group_list=@();
$users_groups = $null;
$users_groups = Get-ADPrincipalGroupMembership $username | select name;
Write-Host "";
Write-Host "The following is a list of AD groups for $email :";
Write-Host "";
foreach ($group in $users_groups) {
Write-Host $group.name;
}
}
function Get-Ip-Query {
$site = Read-Host "Enter site"
$ips = [System.Net.Dns]::GetHostAddresses($site)
$count = $ips.count
$out = ""
$i =0
Foreach($item in $ips) {
$i += 1
If ($i -eq $count) {
$out += "( addr.dst in '$item' ) "
}
else {
$out += "( addr.dst in '$item' ) or "
}
}
Write-Host "(" $out ")"
$clip = "(" + $out + ")"
Set-Clipboard -Value $clip
}
Write-Host "
1) Get Groups
2) Get site IP for Query
"
Write-Host `
$option = Read-Host "Enter Option"
Write-Host `
switch ($option) {
"1" {
Get-Ad-Groups
}
"2" {
Get-Ip-Query
}
}I created two functions, one named Get-Ad-Groups and another named Get-Ip-Query. After the script is run, the user is prompted with two options, as seen below.
The script takes two options: either option 1 or 2. If option 1 is picked, it will now display the following message:
It will ask you to enter the user’s email. After you press Enter, it will show a list of all the AD groups that the user is in.
Now, if you picked option 2, then the next prompt will look like:
The code will copy the query into the user’s clipboard so they just have to paste it into the search bar. The code uses a DNS query to get the site’s IP. Notice how you should not enter “https://” or the protocol information.
Update: At lunch, I was a little bored, so I modified the script. Instead of using the menu and options shown above, I made it be able to automatically detect if the input is an email or a site. This is done via a regex. After detecting if it is an email or a site, it will run either the group look-up function or the nslookup function.
Powershell script to add the ticket numbers to a word document
The first thing that has to be done for this script to work is to visit our ticketing systems, and I have to manually export a JSON file for the incidents and RITMs that are in my bucket. I tried to programmatically download the files automatically, but it just was not possible.
First, the script checks the download folder for the two files. It makes sure that they exist. If the JSON files exist, the script will parse the JSON files and get all the RITM and INC numbers. Next, it will create a Word document with the following name: “Meade Daily Report-{DATE}.doc“.
It will take the RITMS and INC numbers gathered from the JSON Files and add them to the Word document. After putting the incident and RITM numbers, it will put a bullet point where I can write about the tickets. Lastly, it will delete the JSON files downloaded in the first step so that the next day, when I download the JSON files, they will have the correct names.
function Get-Json {
param (
$jsonFilePath
)
$fileCheck = Test-Path -Path $jsonFilePath -PathType Leaf
if ($fileCheck) {
$jsonData = Get-Content -Path $jsonFilePath -Raw | ConvertFrom-Json
$ritms = $jsonData.Records.Number
return $ritms
} else {
$fileName = Split-Path -Path $jsonFilePath -Leaf
Write-Host $fileName "does not Exist`n"
}
}
function Create-Doc {
param (
$ritms,
$inc
)
$word = New-Object -ComObject Word.Application
$date = Get-Date -Format "MM-dd-yyyy"
$word.Visible = $true
$document = $word.Documents.Add()
$selection = $word.Selection
Foreach($item in $ritms) {
$selection.TypeText($item)
$selection.TypeParagraph()
$selection.TypeText(" • ")
$selection.TypeParagraph()
}
Foreach($item2 in $inc) {
$selection.TypeText($item2)
$selection.TypeParagraph()
$selection.TypeText(" • ")
$selection.TypeParagraph()
}
$filePath = "C:\Users\Meade67\Services\Desktop\daily_reports\Meade Daily Report-" + $date + ".docx"
$document.SaveAs([ref]$filePath)
$document.Close()
$word.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($selection) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($document) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($word) | Out-Null
Remove-Variable word, document, selection -ErrorAction SilentlyContinue
}
$filePath= "C:\Users\Meade67\Downloads\"
$ritmPath = $filePath + "sc_req_item.json"
$incPath = $filePath + "incident.json"
$ritms = Get-Json -jsonFilePath $ritmPath
$inc = Get-Json -jsonFilePath $incPath
Create-Doc -ritms $ritms -inc $inc
$fileCheckRitm = Test-Path -Path $ritmPath -PathType Leaf
if ($fileCheckRitm) {
Remove-Item -Path $ritmPath
write-Host "Deleting sc_req_item.json...`n"
}
$fileCheckInc = Test-Path -Path $incPath -PathType Leaf
if ($fileCheckInc) {
Remove-Item -Path $incPath
write-Host "Deleting incident.json...`n"
}PowerShell Script to create Email
The code below first creates a new email. It adds two emails to the to field, the emails of my boss and manager, which I removed for privacy concerns. The emails are separated by a semicolon.
$Outlook = New-Object -ComObject Outlook.Application
$mailBox = $Outlook.CreateItem(0)
$mailBox.To = "email1; email2"
$filePath = "C:\Users\Meade67\Services\Desktop\daily_reports\Meade Daily Report-" + $date + ".docx"
$checkReport = Test-Path -Path $filePath -PathType Leaf
$date = Get-Date -Format "MM-dd-yyyy"
if ($checkReport){
$MailBox.Attachments.Add($filePath)
} else {
Write-Host "Error with attachment. Try again"
}
$subject = "Meade Daily Report " + $date
$mailBox.Subject = $subject
$mailBox.Display()Next, it looks in a certain folder for a doc that was created by the script earlier. The file path has been changed a little, again for privacy reasons.
Before adding the docx as an attachment, it will check to make sure it exists. If it does not exist, then it will write out an error. Next, it will add a subject line that has “Meade Daily Report {DATE}“. When the script is run, a new window will pop up, and the information will be added to the email. Every time the script is run, it will be the same format with just the date changing. Lastly, all I have to do is click send.
Add Data to a Excel Sheet
The origin of this script is that my boss contacted me that he had an Excel sheet filled with User’s usernames, this Excel sheet has different worksheets. Anyways, we needed to get the usernames from the Excel sheet and grab their emails from AD, and add those emails to the Excel sheet.
First, the script will enumerate the worksheets and save them in an array. Next, the code will close the instance of Excel.
After the sheet is closed, it will loop through the sheets array and call the ‘Excel’ function. The Excel function makes sure that the module is installed and creates an instance of the Excel sheet. This instance will allow us to grab the usernames and add the emails to the spreadsheet. Note: You have to create a new row next to the username row.
The ‘Get-Email’ function takes the username that is grabbed from the Excel sheet and uses the ‘Get-ADUser‘ module to get the user’s email, which it returns for the email to be added to the row.
function Get-Email{
param(
$userName
)
# this function takes one param, which is $userName. The code takes the rows of each sheets and uses
# this function to get the email by using the user's username.
# I had to rescue or catch the error as some of the usernames don't belong to any user or give some type of error
try {
# queries the DC by the username. Which will return is the user's email
$getEmail = Get-ADUser -Identity $userName -Properties EmailAddress
# defines a variable of the newly found email address.
$userEmail = $getEmail.EmailAddress
Write-Host $userEmail
Write-Host "Got username: $userEmail...`n"
# returns the email. The returned email will 'Excel' function to save to the
# spreadsheets. It will also check if it is null or empty.
return $userEmail
} catch {
# if any error occurs it will print out the error on the terminal.
Write-Host "An error occurred: $($_.Exception.Message)"
}
}
function Excel{
param(
$i,
$workSheetName
)
# installs Import Excel if not installed
$install = Install-Module -Name ImportExcel -Scope CurrentUser
# path of the EXCEL FILE
$pathExcel = "C:\Users\Mike\OneDrive\Downloads\top.xlsx"
$ExcelData = Import-Excel -Path $pathExcel -WorksheetName $workSheetName
# Get the rows containing the usernames
$userList = $ExcelData | Select-Object -Property "Associated User"
# opens excel; defines the variable where we can access the spreadsheet
$excel = New-Object -ComObject Excel.Application
# uses pathExcel to open a connection to the excel sheet.
$workbook = $excel.Workbooks.Open($pathExcel)
# gets the worksheets and store it as a variable $worksheet.
$worksheet = $workbook.Sheets.Item($i)
$worksheets = $workbook.Sheets
# have to start at 2 because Excel starts at 1 and the first row
# of the column is the header
$ii = 2
# loops through the user list that was defined earlier.
ForEach($user in $userList) {
$userName = $user.'Associated User'
# calls the Get-Email function which takes the username from
# the username columns and looks up its Email from AD
$email = Get-Email $userName
# We gotta make sure the email gotten from the Get-Email function
# returns an email. If checks to make sure it not null or empty.
if ([String]::IsNullOrEmpty($email)) {
Write-Host "Email Does not exist... $email`n`n"
} else {
# the Get-Email function returned a email
Write-Host "Got user $userName..."
$email = Get-Email $userName
Write-Host $email `n`n
# This is where we get the cell that we add the
# email to. The $ii variable increases by one
# each loop
$cell = $worksheet.Cells.Item($ii, 2)
# this is where we add the email to the cell that we
# got the value ( blank )
$cell.Value = $email
}
# increases each row by one
$ii += 1
}
# the job is done, this saves the work that was performed
$workbook.Save()
# this is where we close the access to the excel file. Only one
# process is allowed to edit or access the excel
$workbook.Close()
# now the access to the file is finished.
$excel.Quit()
}
# the path to the excel file
$pathExcel = "C:\Users\Mike\OneDrive\Downloads\top.xlsx"
# we create a new variable named '$excel'. We will use this later to edit
# and access the file
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
# this is what opens the access to the file. It reads the variable
# where we defined the excel file paths
$workbook = $excel.Workbooks.Open($pathExcel)
# this gets the sheets
$worksheets = $workbook.Sheets
$newSheet = $worksheets
# creates an array named sheetsList. This is where we will store
# the worksheets name to later enumerate into 'Excel' function.
$sheetsList = @()
# We loop through the $sheetsList
foreach ($sheet in $newSheet) {
# we get each of the sheets name and define it as $sheetName
$sheetName = $sheet.Name
# we add the sheet name to the array.
$sheetsList += $sheetName
}
# closes the connection to the excel file without
# saving any changes as we did not make any changes, we
# just need to access the file to get the sheets names.
$workbook.Close($false)
# This will close the excel connection.
$excel.Quit()
$i = 1
# loops through the array that includes the sheets name.
foreach ($sheet in $sheetsList) {
# calls the Excel function, which has two params:
# $i which is the number of the sheets
# $sheet which is the sheet name
Excel $i $sheet
# increase the variable $i by one.
$i += 1
}
The script worked and was able to add over 1300 emails to the spreadsheet. It was much easier than doing it by hand. It took maybe a couple of hours to get the script working as I wanted.
Updated Version Of Daily.ps1
This is an updated version of the first script in this post. The only difference is that it uses regexes to detect if the input given is an email or a domain. Depending on whether the input is an email or a domain, it will run the right function and either display the groups the user is in or copy the IPs to the user’s clipboard.
function Get-Add-Groups {
param (
$email
)
$which_groups = "all";
$obj = $null;
$username = "";
$obj = Get-aduser -Filter {UserPrincipalName -eq $email -and Enabled -eq $true } -Properties Enabled, UserPrincipalName, SamAccountName, mail | select SamAccountName
$username = $obj.SamAccountName;
$group_list=@();
$users_groups = $null;
$users_groups = Get-ADPrincipalGroupMembership $username | select name;
Write-Host "";
Write-Host "The following is a list of AD groups for $email :";
Write-Host "";
foreach ($group in $users_groups) {
Write-Host $group.name;
}
}
function Get-Ip-Query {
param (
$site
)
$ips = [System.Net.Dns]::GetHostAddresses($site)
$count = $ips.count
$out = ""
$i =0
Foreach($item in $ips) {
$i += 1
If ($i -eq $count) {
$out += "( addr.dst in '$item' ) "
}
else {
$out += "( addr.dst in '$item' ) or "
}
}
Write-Host "(" $out ")"
$clip = "(" + $out + ")"
Set-Clipboard -Value $clip
}
$option = Read-Host "Enter Option"
Write-Host `
$siteCheck = $option -match "(?:[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?\.)+[a-z0-9][a-z0-9-]{0,61}[a-z0-9]"
$emailCheck = $option -match "^\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$"
if ($emailCheck) {
Write-Host "Email was detected...`n"
Get-Add-Groups $option
}
elseif ($siteCheck) {
Write-Host "Site detected.."
Get-Ip-Query $option
}