0QAs24HS

1#####################################
2# Stage 1 - Information Gathering
3# First script solely for gathering information
4# Ideally it would be nice to marry all 3 scripts to be run with parameters
5#####################################
6
7#set Running Directory
8$runningdir = (Get-Item -Path ".\" -Verbose).FullName
9
10#region check resources
11If (!(Test-Path "$runningdir\Originals")) { New-Item -ItemType Directory -Force -Path "$runningdir\Originals" | Out-Null} #If Originals folder doesn't exist, create it
12If (!(Test-Path "$runningdir\vshr")) { New-Item -ItemType Directory -Force -Path "$runningdir\vshr" | Out-Null} #If vshr folder doesn't exist, create it
13
14Remove-Item "$runningdir\Originals\*" #empty folder
15Remove-Item "$runningdir\vshr\*" #empty folder
16
17#region doesn't work - need to fix
18$objPers = "$runningdir\LongAudit.xlsx"
19If ([bool][System.IO.File]::Exists($objPers) -eq $true) {Rename-Item "$runningdir\Longaudit.xlsx" -NewName ("LongAudit-" + $datetime + ".xlsx")} #If I can get the end product to initially save LongAudit.xlsx with date, this will need to be adjusted
20$objPers = "$runningdir\LongAudit-ITDB.xlsx" #needs to be phased out
21If ([bool][System.IO.File]::Exists($objPers) -eq $true) {Rename-Item "$runningdir\Longaudit.xlsx" -NewName ("LongAudit-ITDB-" + $datetime + ".xlsx")} #needs to be phased out
22#endregion /needs work
23
24#endregion
25
26#region <import special modules and process specialties>
27#this should probably be rewriten to just be a #require
28If (Get-Module -ListAvailable -Name ActiveDirectory) { Import-Module -Name ActiveDirectory } else {
29  Write-Host "Active Directory powershell module required to function"
30  Exit
31}
32#endregion
33
34#region scrape AD
35#AD scrape works fine
36$cmpHash = New-Object System.Collections.Arraylist #AD List Hashtable
37$AttachmentPathCMP = "$runningdir\Originals\cmp.csv"
38$cmp = (get-aduser -Filter{(enabled -eq $True) -AND (passwordneverexpires -eq $False) -AND (passwordnotrequired -eq $False)} -prop distinguishedname,CN,givenname,sn,enabled,passwordneverexpires,passwordnotrequired,samaccountname,Title | select-object distinguishedname,CN,givenname,sn,samaccountname,Title) #AD Scrape
39$cmp | ForEach-Object {
40    [void]$cmpHash.add((New-Object PSObject -Property @{
41            FullName          = ($_.givenname + ' ' + $_.sn).toUpper();
42            distinguishedname = $_.distinguishedname;
43            CN                = $_.CN;
44            givenname         = $_.givenname;
45            sn                = $_.sn;
46            samaccountname    = $_.samaccountname;
47            Title             = $_.Title
48        })
49    )
50}
51$cmpHash | select FullName,distinguishedname,CN,givenname,sn,samaccountname,Title | sort FullName | Export-Csv -Path $AttachmentPathCMP -NoTypeInformation
52#endregion scrape AD
53
54#region process itdb
55#this needs to be PHASED OUT as it was an old private DB of user info and is no longer relevant or necessary
56import-module $runningdir\Resources\PSSQLite\PSSQLite #<Import SQLite module for private DB>
57$DatabaseDB1 = "\\server01\c$\xampp\htdocs\database\data\database.db"
58$AttachmentPathDB = "$runningdir\Originals\db1.csv"
59$SqlQueryDB1 = "SELECT userdesc AS 'Name', license AS 'License', position AS 'Position', site AS 'Property',ma, ca, tbm, pa, ez, ctcaa, ctrr, ctsp, ctst, cttr, md, ig, nw, lastupdate, termdate FROM users WHERE users.license <> '0' AND users.termdate = ''"
60Invoke-SQLiteQuery -DataSource $DatabaseDB1 -Query $SqlQueryDB1 | Export-CSV $AttachmentPathDB -NoTypeInformation
61#endregion process itdb
62
63#region SQL Variables
64#I'm not seeing a more elegant way to do this part. perhaps for those tables or servers that have a common part of 
65#their name maybe I might be able to add a string to a variable to complete a full server name
66#region MA
67$DatabaseMA = "TableMA"
68$Serverma1 = "crServ2"
69$Serverma2 = "stServ2"
70$Serverma3 = "cnServ2"
71$Serverma4 = "spServ2"
72$AttachmentPathma = "$runningdir\Originals\MAExport.csv"
73#endregion MA
74#region CA
75$DatabaseCA = "CA"
76$Serverca1 = "crServ3"
77$Serverca2 = "cnServ3"
78$AttachmentPathca = "$runningdir\Originals\CAExport.csv"
79#endregion CA
80#region PA
81$DatabasePA = "PM"
82$Serverpa1 = "coServ4"
83$AttachmentPathpa = "$runningdir\Originals\PAExport.csv"
84#endregion PA
85#region EZ
86$DatabaseEZ1 = "ezTable5"
87$DatabaseEZ2 = "EZTable50"
88$DatabaseEZ3 = "EzTable500"
89$Serverez1 = "crServ5"
90$Serverez2 = "stServ5"
91$Serverez3 = "cnServ5"
92$Serverez4 = "spServ5"
93$AttachmentPathez = "$runningdir\Originals\EZExport.csv"
94#endregion EZ
95#region Ig
96$DatabaseIG = "cfg"
97$Serverig1 = "cnServ6"
98$AttachmentPathig = "$runningdir\Originals\IgExport.csv"
99#endregion Ig
100#region CT
101$DatabaseCT1 = "CIDB7"
102$DatabaseCT2 = "CIDB7St"
103$DatabaseCT3 = "CIDB7Tr"
104$DatabaseCT4 = "CIDB7RR"
105$DatabaseCT5 = "CIDB7Sp"
106$Servercct1 = "cnServ7sql"
107$AttachmentPathcct = "$runningdir\Originals\CTExport.csv"
108#endregion CT
109#region MG
110$DatabaseMG1 = "MGCfg"
111$Servermg1 = "cnServ8sql"
112$AttachmentPathmg = "$runningdir\Originals\MGExport.csv"
113#endregion MG
114#region Nw
115$DatabaseNW1 = "NWDB9"
116$Servernw1 = "cnServ9"
117$AttachmentPathnw = "$runningdir\Originals\NWExport.csv"
118#endregion Nw
119
120#endregion SQL Variables
121
122#region SQL Queries
123#these seem like an amazing amount of repetition and I wonder if this could be truncated or written to be a singular more modular variable
124#region ma
125$SqlQueryma1 = "select 'CR' AS Source, dbo.ABU.LogName, dbo.ABU.LName, dbo.ABU.FName, dbo.ABU.License, dbo.UserJ.Desc, CASE dbo.ABUser.SID   WHEN 1 THEN 'CN'   WHEN 2 THEN 'CR'   WHEN 3 THEN 'St'   WHEN 4 THEN 'Sp'   ELSE 'Other' END AS SID, dbo.Mk.Mk AS 'Groups' FROM dbo.ABU INNER JOIN dbo.UserJ ON dbo.ABU.JID = dbo.UserJ.JID JOIN dbo.ABUMk ON dbo.ABUser.UID = dbo.ABUMk.UID JOIN dbo.Mk ON dbo.ABUMk.MkNum = dbo.Mk.MkNum WHERE dbo.ABU.Status = 'A' ORDER BY LName,FName"
126$SqlQueryma2 = "select 'St' AS Source, dbo.ABU.LogName, dbo.ABU.LName, dbo.ABU.FName, dbo.ABU.License, dbo.UserJ.Desc, CASE dbo.ABUser.SID WHEN 1 THEN 'CN' WHEN 2 THEN 'CR' WHEN 3 THEN 'St' WHEN 4 THEN 'Sp' ELSE 'Other' END AS SID, dbo.Mk.Mk AS 'Groups' FROM dbo.ABU INNER JOIN dbo.UserJ ON dbo.ABU.JID = dbo.UserJ.JID JOIN dbo.ABUMk ON dbo.ABUser.UID = dbo.ABUMk.UID JOIN dbo.Mk ON dbo.ABUMk.MkNum = dbo.Mk.MkNum WHERE dbo.ABU.Status = 'A' ORDER BY LName,FName"
127$SqlQueryma3 = "select 'CN' AS Source, dbo.ABU.LogName, dbo.ABU.LName, dbo.ABU.FName, dbo.ABU.License, dbo.UserJ.Desc, CASE dbo.ABUser.SID WHEN 1 THEN 'CN' WHEN 2 THEN 'CR' WHEN 3 THEN 'St' WHEN 4 THEN 'Sp' ELSE 'Other' END AS SID, dbo.Mk.Mk AS 'Groups' FROM dbo.ABU INNER JOIN dbo.UserJ ON dbo.ABU.JID = dbo.UserJ.JID JOIN dbo.ABUMk ON dbo.ABUser.UID = dbo.ABUMk.UID JOIN dbo.Mk ON dbo.ABUMk.MkNum = dbo.Mk.MkNum WHERE dbo.ABU.Status = 'A' ORDER BY LName,FName"
128$SqlQueryma4 = "select 'Sp' AS Source, dbo.ABU.LogName, dbo.ABU.LName, dbo.ABU.FName, dbo.ABU.License, dbo.UserJ.Desc, CASE dbo.ABUser.SID WHEN 1 THEN 'CN' WHEN 2 THEN 'CR' WHEN 3 THEN 'St' WHEN 4 THEN 'Sp' ELSE 'Other' END AS SID, dbo.Mk.Mk AS 'Groups' FROM dbo.ABU INNER JOIN dbo.UserJ ON dbo.ABU.JID = dbo.UserJ.JID JOIN dbo.ABUMk ON dbo.ABUser.UID = dbo.ABUMk.UID JOIN dbo.Mk ON dbo.ABUMk.MkNum = dbo.Mk.MkNum WHERE dbo.ABU.Status = 'A' ORDER BY LName,FName"
129#endregion ma
130#region ca
131$SqlQueryca1 = "select 'CR' AS Source, p.LogName, p.FName, p.LName, m.Mk, p.St FROM AbU p (nolock) JOIN ABUMk AS um (nolock) ON p.uid = um.uid JOIN Mk AS m (nolock) ON um.MkNum = m.MkNum WHERE p.St = 'A' ORDER BY p.LName,p.FName"
132$SqlQueryca2 = "select 'CN' AS Source, p.LogName, p.FName, p.LName, m.Mk, p.St FROM AbU p (nolock) JOIN ABUMk AS um (nolock) ON p.uid = um.uid JOIN Mk AS m (nolock) ON um.MkNum = m.MkNum WHERE p.St = 'A' ORDER BY p.LName,p.FName"
133#endregion ca
134#region pa
135$SqlQuerypa1 = "select dbo.ABU.LogName, dbo.ABU.LName, dbo.ABU.FName, dbo.ABU.License, dbo.UJob.Desc AS 'ITDes', dbo.Si.Desc AS 'SID', dbo.Mk.Mk AS 'Groups' FROM dbo.ABU INNER JOIN dbo.UJob ON dbo.ABU.JID = dbo.UJob.JID JOIN dbo.ABUMk ON dbo.ABU.UID = dbo.ABUMk.UID JOIN dbo.Mk ON dbo.ABUMk.MkNum = dbo.Mk.MkNum JOIN dbo.St ON dbo.ABUMk.SID = dbo.St.SID WHERE dbo.ABU.Status = 'A' ORDER BY FName,LName"
136#endregion pa
137#region EZ
138$SqlQueryez1 = "select 'CR' AS Source, ur_name AS 'uname', fl_na AS 'Name', rl_cd AS 'Role', gr_cd AS 'VUG' from dbo.a_ur INNER JOIN dbo.ur_rl ON dbo.a_ur.ur_seq = dbo.ur_rl.ur_seq INNER JOIN dbo.ur_gr ON dbo.a_ur.ur_seq = dbo.ur_gr.ur_seq WHERE a_flag = '1' ORDER BY uname"
139$SqlQueryez2 = "select 'St' AS Source, ur_name AS 'uname', fl_na AS 'Name', rl_cd AS 'Role', gr_cd AS 'VUG' from dbo.a_ur INNER JOIN dbo.ur_rl ON dbo.a_ur.ur_seq = dbo.ur_rl.ur_seq INNER JOIN dbo.ur_gr ON dbo.a_ur.ur_seq = dbo.ur_gr.ur_seq WHERE a_flag = '1' ORDER BY uname"
140$SqlQueryez3 = "select 'CN' AS Source, ur_name AS 'uname', fl_na AS 'Name', rl_cd AS 'Role', gr_cd AS 'VUG' from dbo.a_ur INNER JOIN dbo.ur_rl ON dbo.a_ur.ur_seq = dbo.ur_rl.ur_seq INNER JOIN dbo.ur_gr ON dbo.a_ur.ur_seq = dbo.ur_gr.ur_seq WHERE a_flag = '1' ORDER BY uname"
141$SqlQueryez4 = "select 'Sp' AS Source, ur_name AS 'uname', fl_na AS 'Name', rl_cd AS 'Role', gr_cd AS 'VUG' from dbo.a_ur INNER JOIN dbo.ur_rl ON dbo.a_ur.ur_seq = dbo.ur_rl.ur_seq INNER JOIN dbo.ur_gr ON dbo.a_ur.ur_seq = dbo.ur_gr.ur_seq WHERE a_flag = '1' ORDER BY uname"
142#endregion EZ
143#region IG
144$SqlQueryig1 = "SELECT dbo.Ep_Mt.ep_cd_no AS 'CNum', dbo.Ep_Mt.ep_f_name AS 'FirstName', dbo.Ep_Mt.ep_l_name AS 'LastName', dbo.Ep_Mt.ep_h_dt AS 'HireDate', dbo.Ep_Mt.ep_id AS 'EID', dbo.Ep_Mt.ep_p_name AS 'UName', [it_pm].[dbo].St_Mt.st_name AS 'Location' FROM dbo.Ep_Mt JOIN [it_pm].[dbo].St_Mt ON [it_pm].[dbo].St_Mt.st_k_id = [it_cg].[dbo].Ep_Mt.st_id WHERE dbo.Ep_Mt.ep_tm_dt IS NULL"
145#endregion IG
146#region CT
147$SqlQuerycct1 = "SELECT 'CN' AS Source, dbo.El.OrgId, dbo.El.FName, dbo.El.LName, dbo.Dept.Name AS 'Department', dbo.Rl.Name AS 'Role' FROM dbo.El JOIN dbo.IdRl ON dbo.IdRl.IdId = dbo.El.ElId JOIN dbo.Rl ON dbo.Rl.RlId = dbo.IdRl.RlId JOIN dbo.Dept ON dbo.Dept.DeptId = dbo.El.DeptId WHERE dbo.El.IA = 1"
148$SqlQuerycct2 = "SELECT 'St' AS Source, dbo.El.OrgId, dbo.El.FName, dbo.El.LName, dbo.Dept.Name AS 'Department', dbo.Rl.Name AS 'Role' FROM dbo.El JOIN dbo.IdRl ON dbo.IdRl.IdId = dbo.El.ElId JOIN dbo.Rl ON dbo.Rl.RlId = dbo.IdRl.RlId JOIN dbo.Dept ON dbo.Dept.DeptId = dbo.El.DeptId WHERE dbo.El.IA = 1"
149$SqlQuerycct3 = "SELECT 'TR' AS Source, dbo.El.OrgId, dbo.El.FName, dbo.El.LName, dbo.Dept.Name AS 'Department', dbo.Rl.Name AS 'Role' FROM dbo.El JOIN dbo.IdRl ON dbo.IdRl.IdId = dbo.El.ElId JOIN dbo.Rl ON dbo.Rl.RlId = dbo.IdRl.RlId JOIN dbo.Dept ON dbo.Dept.DeptId = dbo.El.DeptId WHERE dbo.El.IA = 1"
150$SqlQuerycct4 = "SELECT 'CR' AS Source, dbo.El.OrgId, dbo.El.FName, dbo.El.LName, dbo.Dept.Name AS 'Department', dbo.Rl.Name AS 'Role' FROM dbo.El JOIN dbo.IdRl ON dbo.IdRl.IdId = dbo.El.ElId JOIN dbo.Rl ON dbo.Rl.RlId = dbo.IdRl.RlId JOIN dbo.Dept ON dbo.Dept.DeptId = dbo.El.DeptId WHERE dbo.El.IA = 1"
151$SqlQuerycct5 = "SELECT 'Sp' AS Source, dbo.El.OrgId, dbo.El.FName, dbo.El.LName, dbo.Dept.Name AS 'Department', dbo.Rl.Name AS 'Role' FROM dbo.El JOIN dbo.IdRl ON dbo.IdRl.IdId = dbo.El.ElId JOIN dbo.Rl ON dbo.Rl.RlId = dbo.IdRl.RlId JOIN dbo.Dept ON dbo.Dept.DeptId = dbo.El.DeptId WHERE dbo.El.IA = 1"
152#endregion CT
153#region MG
154$SqlQuerymgt1 = "SELECT dbo.Ur.UrID AS 'Login', dbo.Ur.CID, dbo.Ur.FName, dbo.Ur.LName, dbo.Ur.Tle, dbo.UrCl.ClDes AS 'Job', dbo.Ur.EID FROM dbo.Ur JOIN dbo.UrCl ON dbo.UrCl.CID = dbo.Ur.JCl WHERE dbo.Ur.Del = 0"
155#endregion MG
156#region NW
157$SqlQuerynw1 = "SELECT dbo.nw_DM_Ur.UName, dbo.nw_DM_Ur.Name, dbo.nw_DM_Ur.Title, dbo.nw_DM_Ur.GL FROM dbo.nw_DM_Ur WHERE ((dbo.nw_DM_Ur.A = 1) AND (dbo.nw_DM_Ur.SysUser = 0))"
158#endregion NW
159
160#endregion SQL Queries
161
162<#
163.SYNOPSIS: Performs a SQL query and returns an array of PSObjects.
164.NOTES: Author: Jourdan Templeton - hello@jourdant.me
165.LINK: https://blog.jourdant.me/post/simple-sql-in-powershell
166function Invoke-SqlCommand() {
167    [cmdletbinding(DefaultParameterSetName="integrated")]Param (
168        [Parameter(Mandatory=$true)][Alias("Serverinstance")][string]$Server,
169        [Parameter(Mandatory=$true)][string]$Database,
170        [Parameter(Mandatory=$true, ParameterSetName="not_integrated")][string]$Username,
171        [Parameter(Mandatory=$true, ParameterSetName="not_integrated")][string]$Password,
172        [Parameter(Mandatory=$false, ParameterSetName="integrated")][switch]$UseWindowsAuthentication = $true,
173        [Parameter(Mandatory=$true)][string]$Query,
174        [Parameter(Mandatory=$false)][int]$CommandTimeout=0
175    )
176    
177    #build connection string
178    $connstring = "Server=$Server; Database=$Database; "
179    If ($PSCmdlet.ParameterSetName -eq "not_integrated") { $connstring += "User ID=$username; Password=$password;" }
180    ElseIf ($PSCmdlet.ParameterSetName -eq "integrated") { $connstring += "Trusted_Connection=Yes; Integrated Security=SSPI;" }
181    
182    #connect to database
183    $connection = New-Object System.Data.SqlClient.SqlConnection($connstring)
184    $connection.Open()
185    
186    #build query object
187    $command = $connection.CreateCommand()
188    $command.CommandText = $Query
189    $command.CommandTimeout = $CommandTimeout
190    
191    #run query
192    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
193    $dataset = New-Object System.Data.DataSet
194    $adapter.Fill($dataset) | out-null
195    
196    #return the first collection of results or an empty array
197    If ($dataset.Tables[0] -ne $null) {$table = $dataset.Tables[0]}
198    ElseIf ($table.Rows.Count -eq 0) { $table = New-Object System.Collections.ArrayList }
199    
200    $connection.Close()
201    return $table
202}
203#>
204
205#region sqlcreds
206$UserID1 = "User"
207$UserPass1 = "Pass"
208$UserID2 = "User2"
209#endregion sqlcreds
210
211#region SQL Execution (needs to replaced by function)
212#I'm still not entirely solid on the logic that's happening since all queries are returned to $DataSet.Tables[0]
213#yet you can run however many queries you want and until you export, it keeps getting added to the same table.
214#I'm about positive I'm doing this wrong.
215#region <Process MA Queries>
216Write-Progress -Activity 'Processing MA' -Status 'Gathering CR' -PercentComplete 5 #all my progress indicators are crap and need work
217$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
218$SqlConnection.ConnectionString = "Data Source=$Serverma1;Initial Catalog=$DatabaseMA; User Id=$UserID1; Password=$UserPass1"
219$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
220$SqlCmd.CommandText = $SqlQueryma1
221$SqlCmd.Connection = $SqlConnection
222$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
223$SqlAdapter.SelectCommand = $SqlCmd
224$DataSet = New-Object System.Data.DataSet
225$nRecs = $SqlAdapter.Fill($DataSet)
226$nRecs | Out-Null
227$objTable = $DataSet.Tables[0] #Populate Hash Table
228Start-Sleep -s 1 # Wait for SQL Connection to Close
229Write-Progress -Activity 'Processing MA' -Status 'Gathering St' -PercentComplete 10
230$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
231$SqlConnection.ConnectionString = "Data Source=$Serverma2;Initial Catalog=$DatabaseMA; User Id=$UserID1; Password=$UserPass1"
232$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
233$SqlCmd.CommandText = $SqlQueryma2
234$SqlCmd.Connection = $SqlConnection
235$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
236$SqlAdapter.SelectCommand = $SqlCmd
237$nRecs = $SqlAdapter.Fill($DataSet)
238$nRecs | Out-Null
239$objTable = $DataSet.Tables[0] #Populate Hash Table
240Start-Sleep -s 1 # Wait for SQL Connection to Close
241Write-Progress -Activity 'Processing MA' -Status 'Gathering CN' -PercentComplete 15
242$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
243$SqlConnection.ConnectionString = "Data Source=$Serverma3;Initial Catalog=$DatabaseMA; User Id=$UserID1; Password=$UserPass1"
244$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
245$SqlCmd.CommandText = $SqlQueryma3
246$SqlCmd.Connection = $SqlConnection
247$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
248$SqlAdapter.SelectCommand = $SqlCmd
249$nRecs = $SqlAdapter.Fill($DataSet)
250$nRecs | Out-Null
251$objTable = $DataSet.Tables[0] #Populate Hash Table
252Start-Sleep -s 1 # Wait for SQL Connection to Close
253Write-Progress -Activity 'Processing MA' -Status 'Gathering Sp' -PercentComplete 20
254$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
255$SqlConnection.ConnectionString = "Data Source=$Serverma4;Initial Catalog=$DatabaseMA; User Id=$UserID1; Password=$UserPass1"
256$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
257$SqlCmd.CommandText = $SqlQueryma4
258$SqlCmd.Connection = $SqlConnection
259$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
260$SqlAdapter.SelectCommand = $SqlCmd
261$nRecs = $SqlAdapter.Fill($DataSet)
262$nRecs | Out-Null
263$objTable = $DataSet.Tables[0] #Populate Hash Table
264$objTable | Export-CSV $AttachmentPathma -NoTypeInformation #Export Hash Table to CSV File
265#endregion </Process MA Queries>
266#region <Process CA Queries>
267Write-Progress -Activity 'Processing CA' -Status 'Gathering CR' -PercentComplete 25
268$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
269$SqlConnection.ConnectionString = "Data Source=$Serverca1;Initial Catalog=$DatabaseCA; User Id=$UserID1; Password=$UserPass1"
270$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
271$SqlCmd.CommandText = $SqlQueryca1
272$SqlCmd.Connection = $SqlConnection
273$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
274$SqlAdapter.SelectCommand = $SqlCmd
275$DataSet = New-Object System.Data.DataSet
276$nRecs = $SqlAdapter.Fill($DataSet)
277$nRecs | Out-Null
278$objTable = $DataSet.Tables[0] #Populate Hash Table
279Start-Sleep -s 1 # Wait for SQL Connection to Close
280Write-Progress -Activity 'Processing CA' -Status 'Gathering CN' -PercentComplete 30
281$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
282$SqlConnection.ConnectionString = "Data Source=$Serverca2;Initial Catalog=$DatabaseCA; User Id=$UserID1; Password=$UserPass1"
283$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
284$SqlCmd.CommandText = $SqlQueryca2
285$SqlCmd.Connection = $SqlConnection
286$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
287$SqlAdapter.SelectCommand = $SqlCmd
288$nRecs = $SqlAdapter.Fill($DataSet)
289$nRecs | Out-Null
290$objTable = $DataSet.Tables[0] #Populate Hash Table
291$objTable | Export-CSV $AttachmentPathca -NoTypeInformation #Export Hash Table to CSV File
292#endregion </Process CA Queries>
293#region <Process PA Queries>
294Write-Progress -Activity 'Processing Pa' -Status 'Scraping coServ4' -PercentComplete 35
295$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
296$SqlConnection.ConnectionString = "Data Source=$Serverpa1;Initial Catalog=$DatabasePA; User Id=$UserID1; Password=$UserPass1"
297$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
298$SqlCmd.CommandText = $SqlQuerypa1
299$SqlCmd.Connection = $SqlConnection
300$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
301$SqlAdapter.SelectCommand = $SqlCmd
302$DataSet = New-Object System.Data.DataSet
303$nRecs = $SqlAdapter.Fill($DataSet)
304$nRecs | Out-Null
305$objTable = $DataSet.Tables[0] #Populate Hash Table
306$objTable | Export-CSV $AttachmentPathpa -NoTypeInformation #Export Hash Table to CSV File
307#endregion </Process PA Queries>
308#region <Process EZ Queries>
309Write-Progress -Activity 'Processing EZ' -Status 'Gathering CR' -PercentComplete 40
310$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
311$SqlConnection.ConnectionString = "Data Source=$Serverez1;Initial Catalog=$DatabaseEZ1; User Id=$UserID1; Password=$UserPass1"
312$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
313$SqlCmd.CommandText = $SqlQueryez1
314$SqlCmd.Connection = $SqlConnection
315$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
316$SqlAdapter.SelectCommand = $SqlCmd
317$DataSet = New-Object System.Data.DataSet
318$nRecs = $SqlAdapter.Fill($DataSet)
319$nRecs | Out-Null
320$objTable = $DataSet.Tables[0] #Populate Hash Table
321Start-Sleep -s 1 # Wait for SQL Connection to Close
322Write-Progress -Activity 'Processing EZ' -Status 'Gathering St' -PercentComplete 45
323$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
324$SqlConnection.ConnectionString = "Data Source=$Serverez2;Initial Catalog=$DatabaseEZ2; User Id=$UserID1; Password=$UserPass1"
325$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
326$SqlCmd.CommandText = $SqlQueryez2
327$SqlCmd.Connection = $SqlConnection
328$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
329$SqlAdapter.SelectCommand = $SqlCmd
330$nRecs = $SqlAdapter.Fill($DataSet)
331$nRecs | Out-Null
332$objTable = $DataSet.Tables[0] #Populate Hash Table
333Start-Sleep -s 1 # Wait for SQL Connection to Close
334Write-Progress -Activity 'Processing EZ' -Status 'Gathering CN' -PercentComplete 50
335$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
336$SqlConnection.ConnectionString = "Data Source=$Serverez3;Initial Catalog=$DatabaseEZ3; User Id=$UserID1; Password=$UserPass1"
337$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
338$SqlCmd.CommandText = $SqlQueryez3
339$SqlCmd.Connection = $SqlConnection
340$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
341$SqlAdapter.SelectCommand = $SqlCmd
342$nRecs = $SqlAdapter.Fill($DataSet)
343$nRecs | Out-Null
344$objTable = $DataSet.Tables[0] #Populate Hash Table
345Start-Sleep -s 1 # Wait for SQL Connection to Close
346Write-Progress -Activity 'Processing EZ' -Status 'Gathering Sp' -PercentComplete 55
347$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
348$SqlConnection.ConnectionString = "Data Source=$Serverez4;Initial Catalog=$DatabaseEZ3; User Id=$UserID1; Password=$UserPass1"
349$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
350$SqlCmd.CommandText = $SqlQueryez4
351$SqlCmd.Connection = $SqlConnection
352$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
353$SqlAdapter.SelectCommand = $SqlCmd
354$nRecs = $SqlAdapter.Fill($DataSet)
355$nRecs | Out-Null
356$objTable = $DataSet.Tables[0] #Populate Hash Table
357$objTable | Export-CSV $AttachmentPathez -NoTypeInformation #Export Hash Table to CSV File
358#endregion </Process EZ Queries>
359#region <Process IG Queries>
360Write-Progress -Activity 'Processing IG' -Status 'Scraping cnServ6' -PercentComplete 60
361$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
362$SqlConnection.ConnectionString = "Data Source=$Serverig1;Initial Catalog=$DatabaseIG; User Id=$UserID1; Password=$UserPass1"
363$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
364$SqlCmd.CommandText = $SqlQueryig1
365$SqlCmd.Connection = $SqlConnection
366$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
367$SqlAdapter.SelectCommand = $SqlCmd
368$DataSet = New-Object System.Data.DataSet
369$nRecs = $SqlAdapter.Fill($DataSet)
370$nRecs | Out-Null
371$objTable = $DataSet.Tables[0] #Populate Hash Table
372$objTable | Export-CSV $AttachmentPathig -NoTypeInformation #Export Hash Table to CSV File
373#endregion </Process IG Queries>
374#region <Process CT Queries>
375Write-Progress -Activity 'Processing CT' -Status 'Gathering CN' -PercentComplete 65
376$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
377$SqlConnection.ConnectionString = "Data Source=$Serverct1;Initial Catalog=$DatabaseCT1; User Id=$UserID1; Password=$UserPass1"
378$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
379$SqlCmd.CommandText = $SqlQueryct1 #CN
380$SqlCmd.Connection = $SqlConnection
381$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
382$SqlAdapter.SelectCommand = $SqlCmd
383$DataSet = New-Object System.Data.DataSet
384$nRecs = $SqlAdapter.Fill($DataSet)
385$nRecs | Out-Null
386$objTable = $DataSet.Tables[0] #Populate Hash Table
387Start-Sleep -s 1 # Wait for SQL Connection to Close
388Write-Progress -Activity 'Processing CT' -Status 'Gathering St' -PercentComplete 70
389$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
390$SqlConnection.ConnectionString = "Data Source=$Serverct1;Initial Catalog=$DatabaseCT2; User Id=$UserID1; Password=$UserPass1"
391$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
392$SqlCmd.CommandText = $SqlQueryct2 #St
393$SqlCmd.Connection = $SqlConnection
394$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
395$SqlAdapter.SelectCommand = $SqlCmd
396$nRecs = $SqlAdapter.Fill($DataSet)
397$nRecs | Out-Null
398$objTable = $DataSet.Tables[0] #Populate Hash Table
399Start-Sleep -s 1 # Wait for SQL Connection to Close
400Write-Progress -Activity 'Processing CT' -Status 'Gathering TR' -PercentComplete 75
401$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
402$SqlConnection.ConnectionString = "Data Source=$Serverct1;Initial Catalog=$DatabaseCT3; User Id=$UserID1; Password=$UserPass1"
403$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
404$SqlCmd.CommandText = $SqlQueryct3 #TR
405$SqlCmd.Connection = $SqlConnection
406$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
407$SqlAdapter.SelectCommand = $SqlCmd
408$nRecs = $SqlAdapter.Fill($DataSet)
409$nRecs | Out-Null
410$objTable = $DataSet.Tables[0] #Populate Hash Table
411Start-Sleep -s 1 # Wait for SQL Connection to Close
412Write-Progress -Activity 'Processing CT' -Status 'Gathering CR' -PercentComplete 80
413$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
414$SqlConnection.ConnectionString = "Data Source=$Serverct1;Initial Catalog=$DatabaseCT4; User Id=$UserID1; Password=$UserPass1"
415$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
416$SqlCmd.CommandText = $SqlQueryct4 #CR
417$SqlCmd.Connection = $SqlConnection
418$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
419$SqlAdapter.SelectCommand = $SqlCmd
420$nRecs = $SqlAdapter.Fill($DataSet)
421$nRecs | Out-Null
422$objTable = $DataSet.Tables[0] #Populate Hash Table
423Start-Sleep -s 1 # Wait for SQL Connection to Close
424Write-Progress -Activity 'Processing CT' -Status 'Gathering Sp' -PercentComplete 85
425$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
426$SqlConnection.ConnectionString = "Data Source=$Serverct1;Initial Catalog=$DatabaseCT5; User Id=$UserID1; Password=$UserPass1"
427$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
428$SqlCmd.CommandText = $SqlQueryct5 #Sp
429$SqlCmd.Connection = $SqlConnection
430$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
431$SqlAdapter.SelectCommand = $SqlCmd
432$nRecs = $SqlAdapter.Fill($DataSet)
433$nRecs | Out-Null
434$objTable = $DataSet.Tables[0] #Populate Hash Table
435$objTable | Export-CSV $AttachmentPathct -NoTypeInformation #Export Hash Table to CSV File
436#endregion </Process CT Queries>
437#region <Process MG Queries>
438Write-Progress -Activity 'Processing MG' -Status 'Scraping cnServ8sql' -PercentComplete 90
439$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
440$SqlConnection.ConnectionString = "Data Source=$Servermg1;Initial Catalog=$DatabaseMG1; User Id=$UserID2; Password=$UserPass1"
441$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
442$SqlCmd.CommandText = $SqlQuerymg1
443$SqlCmd.Connection = $SqlConnection
444$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
445$SqlAdapter.SelectCommand = $SqlCmd
446$DataSet = New-Object System.Data.DataSet
447$nRecs = $SqlAdapter.Fill($DataSet)
448$nRecs | Out-Null
449$objTable = $DataSet.Tables[0] #Populate Hash Table
450$objTable | Export-CSV $AttachmentPathmg -NoTypeInformation #Export Hash Table to CSV File
451#endregion </Process MG Queries>
452#region <Process Nw Queries>
453Write-Progress -Activity 'Processing Nw' -Status 'Scraping cnServ9' -PercentComplete 100
454$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
455$SqlConnection.ConnectionString = "Data Source=$Servernw1;Initial Catalog=$DatabaseNW1; User Id=$UserID1; Password=$UserPass1"
456$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
457$SqlCmd.CommandText = $SqlQuerynw1
458$SqlCmd.Connection = $SqlConnection
459$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
460$SqlAdapter.SelectCommand = $SqlCmd
461$DataSet = New-Object System.Data.DataSet
462$nRecs = $SqlAdapter.Fill($DataSet)
463$nRecs | Out-Null
464$objTable = $DataSet.Tables[0] #Populate Hash Table
465$objTable | Export-CSV $AttachmentPathnw -NoTypeInformation #Export Hash Table to CSV File
466#endregion </Process Nw Queries>
467#endregion SQL Execution
468
469#region <truncate CT>
470#some of the software scrapes will repeat the same user over and over to show that they exist at multiple properties.
471#that is to say, John Smith may have been scraped out of more than one DB for a given piece of software.
472#these truncate chunks attempt to mitigate some of the repetition by marrying some of the repeats while still indicating there were duplicates and were merged.
473$runningdir = (Get-Item -Path ".\" -Verbose).FullName
474$SourceDir = "$runningdir\Originals"
475$SourceFile = 'CTExport.csv'
476$SourceFileFullName = Join-Path -Path $SourceDir -ChildPath $SourceFile
477$SourceFileObject = Get-ChildItem -Path $SourceFileFullName
478$Suffix = -join ('-truncated', $SourceFileObject.Extension)
479$MergedRowsFile = $SourceFileObject.FullName.Replace($SourceFileObject.Extension, $Suffix)
480$InCSV = Import-Csv -Path $SourceFileObject
481$GO_InCSV = $InCSV | Sort Role | Group-Object -Property Source, OrgId, Department
482
483$MergedRows = ForEach ($Item in $GO_InCSV) {
484    If ($Item.Count -eq 1) {
485        $TempObject = $Item.Group
486    } Else {
487        $TempObject = $Item.Group[0]
488        $TempObject.Role = ($Item.Group.Role) -JOIN ','
489    }
490    $TempObject
491}
492$GO_InCSV2 = $MergedRows | Sort Role,Source | Group-Object -Property OrgId, Department, Role
493$MergedRows2 = ForEach ($Item2 in $GO_InCSV2) {
494    If ($Item2.Count -eq 1) {
495        $TempObject2 = $Item2.Group
496    } Else {
497        $TempObject2 = $Item2.Group[0]
498        $TempObject2.Source = ($Item2.Group.Source) -JOIN '/'
499    }
500    $TempObject2
501}
502$MergedRows2 | Export-Csv -LiteralPath $MergedRowsFile -NoTypeInformation
503#endregion </truncate CT>
504
505#region <truncate ez>
506#some of the software scrapes will repeat the same user over and over to show that they exist at multiple properties.
507#that is to say, John Smith may have been scraped out of more than one DB for a given piece of software.
508#these truncate chunks attempt to mitigate some of the repetition by marrying some of the repeats while still indicating there were duplicates and were merged.
509$runningdir = (Get-Item -Path ".\" -Verbose).FullName
510$SourceDir = "$runningdir\Originals"
511$SourceFilea = 'EZExport.csv'
512$SourceFileFullNamea = Join-Path -Path $SourceDir -ChildPath $SourceFilea
513$SourceFileObjecta = Get-ChildItem -Path $SourceFileFullNamea
514$Suffix = -join ('-truncated', $SourceFileObjecta.Extension)
515$MergedRowsFilea = $SourceFileObjecta.FullName.Replace($SourceFileObjecta.Extension, $Suffix)
516$InCSVa = Import-Csv -Path $SourceFileObjecta
517$GO_InCSVa = $InCSVa | Sort VUG | Group-Object -Property Source, Name, Role
518
519$MergedRowsa = ForEach ($Itema in $GO_InCSVa) {
520    If ($Itema.Count -eq 1) {
521        $TempObjecta = $Itema.Group
522    } Else {
523        $TempObjecta = $Itema.Group[0]
524        $TempObjecta.XVG = ($Itema.Group.VUG) -JOIN ','
525    }
526    $TempObjecta
527}
528$GO_InCSVa2 = $MergedRowsa | Sort VUG,Source | Group-Object -Property Name, Role
529$MergedRowsa2 = ForEach ($Itema2 in $GO_InCSVa2) {
530    If ($Itema2.Count -eq 1) {
531        $TempObjecta2 = $Itema2.Group
532    } Else {
533        $TempObjecta2 = $Itema2.Group[0]
534        $TempObjecta2.XVG = ($Itema2.Group.VUG) -JOIN '/'
535        $TempObjecta2.Source = ($Itema2.Group.Source) -JOIN '/'
536    }
537    $TempObjecta2
538}
539$MergedRowsa2 | Export-Csv -LiteralPath $MergedRowsFilea -NoTypeInformation
540#endregion </truncate ez>
541
542#region <truncate MA>
543#some of the software scrapes will repeat the same user over and over to show that they exist at multiple properties.
544#that is to say, John Smith may have been scraped out of more than one DB for a given piece of software.
545#these truncate chunks attempt to mitigate some of the repetition by marrying some of the repeats while still indicating there were duplicates and were merged.
546$runningdir = (Get-Item -Path ".\" -Verbose).FullName
547$SourceDirb = "$runningdir\Originals"
548$SourceFileb = 'MAExport.csv'
549$SourceFileFullNameb = Join-Path -Path $SourceDirb -ChildPath $SourceFileb
550$SourceFileObjectb = Get-ChildItem -Path $SourceFileFullNameb
551$Suffixb = -join ('-truncated', $SourceFileObjectb.Extension)
552$MergedRowsFileb = $SourceFileObjectb.FullName.Replace($SourceFileObjectb.Extension, $Suffixb)
553$InCSVb = Import-Csv -Path $SourceFileObjectb
554$GO_InCSVb = $InCSVb | Sort Groups | Group-Object -Property Source, License, SID
555
556$MergedRowsb = ForEach ($Itemb in $GO_InCSVb) {
557    If ($Itemb.Count -eq 1) {
558        $TempObjectb = $Itemb.Group
559    } Else {
560        $TempObjectb = $Itemb.Group[0]
561        $TempObjectb.Groups = ($Itemb.Group.Groups) -JOIN ','
562    }
563    $TempObjectb
564}
565
566$GO_InCSV2b = $MergedRowsb | Sort Groups,Source | Group-Object -Property License, SID, Groups
567$MergedRows2b = ForEach ($Item2b in $GO_InCSV2b) {
568    If ($Item2b.Count -eq 1) {
569        $TempObject2b = $Item2b.Group
570    } Else {
571        $TempObject2b = $Item2b.Group[0]
572        $TempObject2b.Source = ($Item2b.Group.Source) -JOIN ','
573    }
574    $TempObject2b
575}
576
577$GO_InCSV3b = $MergedRows2b | Sort Groups,Source | Group-Object -Property License, SID
578$MergedRows3b = ForEach ($Item3b in $GO_InCSV3b) {
579    If ($Item3b.Count -eq 1) {
580        $TempObject3b = $Item3b.Group
581    } Else {
582        $TempObject3b = $Item3b.Group[0]
583        $TempObject3b.Source = ($Item3b.Group.Source) -JOIN '/'
584        $TempObject3b.Groups = ($Item3b.Group.Groups) -JOIN '/'
585    }
586    $TempObject3b
587}
588
589$MergedRows3b | Export-Csv -LiteralPath $MergedRowsFileb -NoTypeInformation
590#endregion </truncate MA>
591
592#region <truncate PA>
593#some of the software scrapes will repeat the same user over and over to show that they exist at multiple properties.
594#that is to say, John Smith may have been scraped out of more than one DB for a given piece of software.
595#these truncate chunks attempt to mitigate some of the repetition by marrying some of the repeats while still indicating there were duplicates and were merged.
596$runningdir = (Get-Item -Path ".\" -Verbose).FullName
597$SourceDir = "$runningdir\Originals"
598$SourceFiled = 'PAExport.csv'
599$SourceFileFullNamed = Join-Path -Path $SourceDir -ChildPath $SourceFiled
600$SourceFileObjectd = Get-ChildItem -Path $SourceFileFullNamed
601$Suffix = -join ('-truncated', $SourceFileObjectd.Extension)
602$MergedRowsFiled = $SourceFileObjectd.FullName.Replace($SourceFileObjectd.Extension, $Suffix)
603$InCSVd = Import-Csv -Path $SourceFileObjectd
604$GO_InCSVd = $InCSVd | Sort Groups,SID | Group-Object -Property License, ITDes
605
606$MergedRowsd = ForEach ($Itemd in $GO_InCSVd) {
607    If ($Itemd.Count -eq 1) {
608        $TempObjectd = $Itemd.Group
609    } Else {
610        $TempObjectd = $Itemd.Group[0]
611        $TempObjectd.Groups = ($Itemd.Group.Groups) -JOIN '/'
612        $TempObjectd.SID = ($Itemd.Group.SiteID) -JOIN '/'
613    }
614    $TempObjectd
615}
616$MergedRowsd | Export-Csv -LiteralPath $MergedRowsFiled -NoTypeInformation
617#endregion </truncate PA>
618
619
620##################################################################
621# Stage 2 - Assembly and output of Personnel list vs DB scrapes
622# This is a second script that will compile all information scraped and gathered from Stage 1
623##################################################################
624
625#set Running Directory
626$runningdir = (Get-Item -Path ".\" -Verbose).FullName #set Running Directory
627
628#region Check if Folders exists / Create if they do not.
629If (!(Test-Path "$runningdir\Originals")) { New-Item -ItemType Directory -Force -Path "$runningdir\Originals" | Out-Null } #if originals folders doesn't exist, create it
630If (!(Test-Path "$runningdir\vshr")) { New-Item -ItemType Directory -Force -Path "$runningdir\vshr" | Out-Null } #if vshr folder doesn't exist, create it
631If (!(Test-Path "$runningdir\itdb")) { New-Item -ItemType Directory -Force -Path "$runningdir\itdb" | Out-Null } #if vshr folder doesn't exist, create it
632#endregion
633
634#Added this Join-Object Function to facilitate merging of the Personnel list CSV and a given piece of software CSV.
635#  The end product I'm going for is keep all of the personnel list and align accordingly where it matches a user on the software scrape side.
636#  at the same time, I want all users found from a software scrape to be listed as well.
637#  Those that are in the personnel list get matched.  Those that are not need to be listed so we know about them and can remove them.
638#region <Join-Object function>
639function Join-Object
640{
641    <#
642    .SYNOPSIS : Join data from two sets of objects based on a common value
643    .DESCRIPTION : Join data from two sets of objects based on a common value. For more details, see the accompanying blog post: http://ramblingcookiemonster.github.io/Join-Object/ - For even more details,  see the original code and discussions that this borrows from: (Dave Wyatt's Join-Object - http://powershell.org/wp/forums/topic/merging-very-large-collections) / (Lucio Silveira's Join-Object - http://blogs.msdn.com/b/powershell/archive/2012/07/13/join-object.aspx)
644    .PARAMETER Left - 'Left' collection of objects to join.  You can use the pipeline for Left. The objects in this collection should be consistent. We look at the properties on the first object for a baseline.
645    .PARAMETER Right - 'Right' collection of objects to join. The objects in this collection should be consistent. We look at the properties on the first object for a baseline.
646    .PARAMETER LeftJoinProperty - Property on Left collection objects that we match up with RightJoinProperty on the Right collection
647    .PARAMETER RightJoinProperty - Property on Right collection objects that we match up with LeftJoinProperty on the Left collection
648    .PARAMETER LeftProperties - One or more properties to keep from Left.  Default is to keep all Left properties (*). Each property can: - Be a plain property name like "Name" - Contain wildcards like "*" - Be a hashtable like @{Name="Product Name";Expression={$_.Name}}. | Name is the output property name. Expression is the property value ($_ as the current object). | Alternatively, use the Suffix or Prefix parameter to avoid collisions. Each property using this hashtable syntax will be excluded from suffixes and prefixes
649    .PARAMETER RightProperties - One or more properties to keep from Right.  Default is to keep all Right properties (*). Each property can: - Be a plain property name like "Name" - Contain wildcards like "*" - Be a hashtable like @{Name="Product Name";Expression={$_.Name}}. Name is the output property name. Expression is the property value ($_ as the current object). Alternatively, use the Suffix or Prefix parameter to avoid collisions. Each property using this hashtable syntax will be excluded from suffixes and prefixes
650    .PARAMETER Prefix - If specified, prepend Right object property names with this prefix to avoid collisions. Example: Property Name = 'Name' | Suffix = 'j_' | Resulting Joined Property Name = 'j_Name'
651    .PARAMETER Suffix - If specified, append Right object property names with this suffix to avoid collisions. Example: Property Name = 'Name' | Suffix = '_j' | Resulting Joined Property Name = 'Name_j'
652    .PARAMETER Type - Type of join.  Default is AllInLeft. AllInLeft will have all elements from Left at least once in the output, and might appear more than once if the where clause is true for more than one element in right, Left elements with matches in Right are preceded by elements with no matches. SQL equivalent: outer left join (or simply left join) AllInRight is similar to AllInLeft. OnlyIfInBoth will cause all elements from Left to be placed in the output, only if there is at least one match in Right. SQL equivalent: inner join (or simply join). AllInBoth will have all entries in right and left in the output. Specifically, it will have all entries in right with at least one match in left, followed by all entries in Right with no matches in left, followed by all entries in Left with no matches in Right. SQL equivalent: full join
653    .EXAMPLE ##Define some input data. $l = 1..5 | Foreach-Object { [pscustomobject]@{ Name = "jsmith$_", Birthday = (Get-Date).adddays(-1) }}, $r = 4..7 | Foreach-Object{ [pscustomobject]@{ Department = "Department $_" Name = "Department $_" Manager = "jsmith$_" }} #We have a name and Birthday for each manager, how do we find their department, using an inner join? Join-Object -Left $l -Right $r -LeftJoinProperty Name -RightJoinProperty Manager -Type OnlyIfInBoth -RightProperties Department - # Name    Birthday             Department  , # ----    --------             ----------  , # jsmith4 4/14/2015 3:27:22 PM Department 4, # jsmith5 4/14/2015 3:27:22 PM Department 5
654    .EXAMPLE ##Define some input data. $l = 1..5 | Foreach-Object { [pscustomobject]@{ Name = "jsmith$_" Birthday = (Get-Date).adddays(-1) }} | $r = 4..7 | Foreach-Object{ [pscustomobject]@{ Department = "Department $_" Name = "Department $_" Manager = "jsmith$_" }} #We have a name and Birthday for each manager, how do we find all related department data, even if there are conflicting properties? $l | Join-Object -Right $r -LeftJoinProperty Name -RightJoinProperty Manager -Type AllInLeft -Prefix j_ | # Name    Birthday             j_Department j_Name       j_Manager, # ----    --------             ------------ ------       ---------, # jsmith1 4/14/2015 3:27:22 PM , # jsmith2 4/14/2015 3:27:22 PM , # jsmith3 4/14/2015 3:27:22 PM , # jsmith4 4/14/2015 3:27:22 PM Department 4 Department 4 jsmith4  , # jsmith5 4/14/2015 3:27:22 PM Department 5 Department 5 jsmith5  
655    .EXAMPLE ##Hey!  You know how to script right?  Can you merge these two CSVs, where Path1's IP is equal to Path2's IP_ADDRESS? #Get CSV data | $s1 = Import-CSV $Path1 | $s2 = Import-CSV $Path2 #Merge the data, using a full outer join to avoid omitting anything, and export it | Join-Object -Left $s1 -Right $s2 -LeftJoinProperty IP_ADDRESS -RightJoinProperty IP -Prefix 'j_' -Type AllInBoth | Export-CSV $MergePath -NoTypeInformation
656    .EXAMPLE ## "Hey Warren, we need to match up SSNs to Active Directory users, and check if they are enabled or not. #  I'll e-mail you an unencrypted CSV with all the SSNs from gmail, what could go wrong?" # Import some SSNs.  $SSNs = Import-CSV -Path D:\SSNs.csv #Get AD users, and match up by a common value, samaccountname in this case: Get-ADUser -Filter "samaccountname -like 'wframe*'" | Join-Object -LeftJoinProperty samaccountname -Right $SSNs -RightJoinProperty samaccountname -RightProperties ssn -LeftProperties samaccountname, enabled, objectclass
657    .NOTES This borrows from: Dave Wyatt's Join-Object - http://powershell.org/wp/forums/topic/merging-very-large-collections/ | Lucio Silveira's Join-Object - http://blogs.msdn.com/b/powershell/archive/2012/07/13/join-object.aspx | Changes: Always display full set of properties | Display properties in order (left first, right second) | If specified, add suffix or prefix to right object property names to avoid collisions | Use a hashtable rather than ordereddictionary (avoid case sensitivity)
658    .LINK http://ramblingcookiemonster.github.io/Join-Object/
659    .FUNCTIONALITY PowerShell Language
660    #>
661    [CmdletBinding()]
662    Param
663    (
664        [Parameter(Mandatory=$true, ValueFromPipeLine = $true)]
665        [object[]] $Left,
666        # List to join with $Left
667        [Parameter(Mandatory=$true)]
668        [object[]] $Right,
669        [Parameter(Mandatory = $true)]
670        [string] $LeftJoinProperty,
671        [Parameter(Mandatory = $true)]
672        [string] $RightJoinProperty,
673        [object[]]$LeftProperties = '*',
674        # Properties from $Right we want in the output.
675        # Like LeftProperties, each can be a plain name, wildcard or hashtable. See the LeftProperties comments.
676        [object[]]$RightProperties = '*',
677        [validateset( 'AllInLeft', 'OnlyIfInBoth', 'AllInBoth', 'AllInRight')]
678        [Parameter(Mandatory=$false)]
679        [string]$Type = 'AllInLeft',
680        [string]$Prefix,
681        [string]$Suffix
682    )
683    Begin
684    {
685        function AddItemProperties($item, $properties, $hash) {
686            if ($null -eq $item) { return }
687            foreach($property in $properties) {
688                $propertyHash = $property -as [hashtable]
689                if($null -ne $propertyHash) {
690                    $hashName = $propertyHash["name"] -as [string]         
691                    $expression = $propertyHash["expression"] -as [scriptblock]
692                    $expressionValue = $expression.Invoke($item)[0]
693                    $hash[$hashName] = $expressionValue
694                } else { foreach($itemProperty in $item.psobject.Properties) { if ($itemProperty.Name -like $property) { $hash[$itemProperty.Name] = $itemProperty.Value }}}}
695        }
696        function TranslateProperties {
697            [cmdletbinding()]
698            param(
699                [object[]]$Properties,
700                [psobject]$RealObject,
701                [string]$Side)
702            foreach($Prop in $Properties) {
703                $propertyHash = $Prop -as [hashtable]
704                if($null -ne $propertyHash) {
705                    $hashName = $propertyHash["name"] -as [string]         
706                    $expression = $propertyHash["expression"] -as [scriptblock]
707                    $ScriptString = $expression.tostring()
708                    if($ScriptString -notmatch 'param\(') {
709                        Write-Verbose "Property '$HashName'`: Adding param(`$_) to scriptblock '$ScriptString'"
710                        $Expression = [ScriptBlock]::Create("param(`$_)`n $ScriptString")
711                    }
712                    $Output = @{Name =$HashName; Expression = $Expression }
713                    Write-Verbose "Found $Side property hash with name $($Output.Name), expression:`n$($Output.Expression | out-string)"
714                    $Output
715                } else {
716                    foreach($ThisProp in $RealObject.psobject.Properties) {
717                        if ($ThisProp.Name -like $Prop) {
718                            Write-Verbose "Found $Side property '$($ThisProp.Name)'"
719                            $ThisProp.Name
720                        }
721                    }
722                }
723            }
724        }
725        function WriteJoinObjectOutput($leftItem, $rightItem, $leftProperties, $rightProperties)
726        {
727            $properties = @{}
728            AddItemProperties $leftItem $leftProperties $properties
729            AddItemProperties $rightItem $rightProperties $properties
730            New-Object psobject -Property $properties
731        }
732        #Translate variations on calculated properties.  Doing this once shouldn't affect perf too much.
733        foreach($Prop in @($LeftProperties + $RightProperties)) {
734            if($Prop -as [hashtable]) {
735                foreach($variation in ('n','label','l')) { if(-not $Prop.ContainsKey('Name') ) { if($Prop.ContainsKey($variation) ) { $Prop.Add('Name',$Prop[$Variation]) }}}
736                if(-not $Prop.ContainsKey('Name') -or $Prop['Name'] -like $null )
737                { Throw "Property is missing a name`n. This should be in calculated property format, with a Name and an Expression:`n@{Name='Something';Expression={`$_.Something}}`nAffected property:`n$($Prop | out-string)" }
738                if(-not $Prop.ContainsKey('Expression') )
739                { if($Prop.ContainsKey('E') ) { $Prop.Add('Expression',$Prop['E']) }}
740                if(-not $Prop.ContainsKey('Expression') -or $Prop['Expression'] -like $null ) { Throw "Property is missing an expression`n. This should be in calculated property format, with a Name and an Expression:`n@{Name='Something';Expression={`$_.Something}}`nAffected property:`n$($Prop | out-string)" }
741            }
742        }
743        $leftHash = @{}
744        $rightHash = @{}
745        # Hashtable keys can't be null; we'll use any old object reference as a placeholder if needed.
746        $nullKey = New-Object psobject
747        $bound = $PSBoundParameters.keys -contains "InputObject"
748        if(-not $bound)
749        { [System.Collections.ArrayList]$LeftData = @() }
750    }
751    Process
752    { if($bound) { $LeftData = $Left } Else { foreach($Object in $Left) { [void]$LeftData.add($Object) }}} #We pull all the data for comparison later, no streaming
753    End
754    {
755        foreach ($item in $Right) {
756            $key = $item.$RightJoinProperty
757            if ($null -eq $key) { $key = $nullKey }
758            $bucket = $rightHash[$key]
759            if ($null -eq $bucket) {
760                $bucket = New-Object System.Collections.ArrayList
761                $rightHash.Add($key, $bucket)
762            }
763            $null = $bucket.Add($item)
764        }
765        foreach ($item in $LeftData) {
766            $key = $item.$LeftJoinProperty
767            if ($null -eq $key) { $key = $nullKey }
768            $bucket = $leftHash[$key]
769            if ($null -eq $bucket) {
770                $bucket = New-Object System.Collections.ArrayList
771                $leftHash.Add($key, $bucket)
772            }
773            $null = $bucket.Add($item)
774        }
775        $LeftProperties = TranslateProperties -Properties $LeftProperties -Side 'Left' -RealObject $LeftData[0]
776        $RightProperties = TranslateProperties -Properties $RightProperties -Side 'Right' -RealObject $Right[0]
777        #I prefer ordered output. Left properties first.
778        [string[]]$AllProps = $LeftProperties
779        #Handle prefixes, suffixes, and building AllProps with Name only
780        $RightProperties = foreach($RightProp in $RightProperties) {
781            if(-not ($RightProp -as [Hashtable])) {
782                Write-Verbose "Transforming property $RightProp to $Prefix$RightProp$Suffix"
783                @{
784                    Name="$Prefix$RightProp$Suffix"
785                    Expression=[scriptblock]::create("param(`$_) `$_.'$RightProp'")
786                }
787                $AllProps += "$Prefix$RightProp$Suffix"
788            } else {
789                Write-Verbose "Skipping transformation of calculated property with name $($RightProp.Name), expression:`n$($RightProp.Expression | out-string)"
790                $AllProps += [string]$RightProp["Name"]
791                $RightProp
792            }
793        }
794        $AllProps = $AllProps | Select -Unique
795        Write-Verbose "Combined set of properties: $($AllProps -join ', ')"
796        foreach ( $entry in $leftHash.GetEnumerator() ) {
797            $key = $entry.Key
798            $leftBucket = $entry.Value
799            $rightBucket = $rightHash[$key]
800            if ($null -eq $rightBucket) { if ($Type -eq 'AllInLeft' -or $Type -eq 'AllInBoth') { foreach ($leftItem in $leftBucket) { WriteJoinObjectOutput $leftItem $null $LeftProperties $RightProperties | Select $AllProps }}} else { foreach ($leftItem in $leftBucket) { foreach ($rightItem in $rightBucket) { WriteJoinObjectOutput $leftItem $rightItem $LeftProperties $RightProperties | Select $AllProps }}}
801        }
802        if ($Type -eq 'AllInRight' -or $Type -eq 'AllInBoth') {
803            foreach ($entry in $rightHash.GetEnumerator()) {
804                $key = $entry.Key
805                $rightBucket = $entry.Value
806                $leftBucket = $leftHash[$key]
807                if ($null -eq $leftBucket) { foreach ($rightItem in $rightBucket) { WriteJoinObjectOutput $null $rightItem $LeftProperties $RightProperties | Select $AllProps }}
808            }
809        }
810    }
811}
812#endregion </Join-Object function>
813
814#region <process personnel>
815$perso   = Import-Csv -Path "$runningdir\perso.csv" #collect Personnel list
816$persHash   = New-Object System.Collections.ArrayList
817$perso | ForEach-Object {
818    [void]$persHash.add((New-Object PSObject -Property @{
819        PDM             = ($_.Property + ' ' + ($_.PD)).Trim();
820        HRProperty      = $_.Property;
821        HRLastName      = $_.LastName;
822        HRFirstName     = $_.FirstName;
823        HRTitle         = $_.Title;
824        HRPD            = $_.PD;
825        HRDOH           = $_.DOH;
826        HRLicenseNumber = $_.LicenseNumber
827    }))
828}
829#endregion </process personel>
830#region <process matrix>
831$matr     = Import-Csv -Path "$runningdir\Resources\matr.csv" #collect Software Permissions List
832$matrHash = New-Object System.Collections.ArrayList
833$matr | ForEach-Object {
834    [void]$matrHash.add((New-Object PSObject -Property @{
835        PDM       = ($_.Property + ' ' + ($_.PD)).Trim();
836        mProperty = $_.Property;
837        mPD       = $_.PD;
838        mPDO      = $_.PDO;
839        mAD       = $_.AD;
840        mCT       = $_.CT;
841        mCA       = $_.CA;
842        mEZ       = $_.EZ;
843        mIg       = $_.Ig;
844        mMA       = $_.MA;
845        mMG       = $_.MG;
846        mNw       = $_.Nw;
847        mPa       = $_.Pa;
848        mMS       = $_.MS;
849        mSl       = $_.Sl;
850        mNT       = $_.NT;
851        mRE       = $_.RE
852    }))
853}
854#join the personnel list to the permission matrix on the column labeled PDM | select all fields to be included
855$HRMA = Join-Object -Left $persHash -Right $matrHash -LeftJoinProperty PDM -RightJoinProperty PDM -Type AllInLeft -RightProperties * | select PDM,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mProperty,mPD,mPDO,mAD,mCC,mCT,mEZ,mIg,mMA,mMT,mNw,mPa,mMS,mSl,mNT,mRE
856#sort the now joined personnel list and permissions matrix on first name and last name | then export to a file.
857$HRMA | sort HRFirstName,HRLastName | Export-Csv "$runningdir\HR v Matrix.csv" -NoTypeInformation 
858#endregion </process matrix>
859#region <create hr hashtable for fullname sort>
860#This is an attempt to try to deal with HR listing users like "Patricia / Trish" or "Charles / Chuck" --> NEEDS WORK
861$HRtempHash = New-Object System.Collections.Hashtable
862$HRMA | ForEach-Object {
863    If ($_.HRFirstName -match '/') {
864        If ($_.HRFirstName -match "\."){
865            $tempFullName = (($_.HRFirstName.Split("/")[0]).Trim() -replace "(?:([A-Z][a-z]+))(\s+)([A-Z])(\.)",'$1')
866            $FullName = (($tempFullName + " " + $_.HRLastName).toUpper()).Trim()
867        } else {
868            $FullName = (($_.HRFirstName.Split("/")[0].Trim() + " " + $_.HRLastName).toUpper()).Trim()
869        }
870    } elseif ($_.HRFirstName -match "\.") {
871        $tempFullName = (($_.HRFirstName.Trim() -replace "(?:([A-Z][a-z]+))(\s+)([A-Z])(\.)",'$1')).Trim()
872        $FullName = (($tempFullName + " " + $_.HRLastName).toUpper()).Trim()
873    } elseif ($_.HRFirstName -notmatch "/") {
874        $FullName = (($_.HRFirstName.Trim() + ' ' + $_.HRLastName).toUpper()).Trim()
875    } else { 
876    } 
877    $HRtempHash.Set_Item($FullName, $_) 
878}
879#endregion </create hr hashtable for fullname sort>
880#region <add matrix to fullname sort>
881$persHash2  = New-Object System.Collections.ArrayList
882ForEach ($key in $HRtempHash.keys) { 
883    $persHash2 += New-Object PSObject -Property @{ 
884        FullName        = ((Get-Culture).textinfo.totitlecase($key.tolower())).Trim(); 
885        HRPDM           = $HRtempHash.$key.PDM; 
886        HRProperty      = $HRtempHash.$key.HRProperty; 
887        HRLastName      = ($HRtempHash.$key.HRLastName).Trim(); 
888        HRFirstName     = ($HRtempHash.$key.HRFirstName).Trim(); 
889        HRTitle         = $HRtempHash.$key.HRTitle; 
890        HRPD            = $HRtempHash.$key.HRPD; 
891        HRDOH           = $HRtempHash.$key.HRDOH; 
892        HRLicenseNumber = $HRtempHash.$key.HRLicenseNumber; 
893        mProperty       = $HRtempHash.$key.mProperty; 
894        mPD             = $HRtempHash.$key.mPD; 
895        mPDO            = $HRtempHash.$key.mPDO; 
896        mAD             = $HRtempHash.$key.mAD; 
897        mCT             = $HRtempHash.$key.mCT; 
898        mCA             = $HRtempHash.$key.mCA; 
899        mEZ             = $HRtempHash.$key.mEZ; 
900        mIg             = $HRtempHash.$key.mIg; 
901        mMA             = $HRtempHash.$key.mMA; 
902        mMG             = $HRtempHash.$key.mMG; 
903        mNw             = $HRtempHash.$key.mNw; 
904        mPa             = $HRtempHash.$key.mPa; 
905        mMS             = $HRtempHash.$key.mMS; 
906        mSl             = $HRtempHash.$key.mSl; 
907        mNT             = $HRtempHash.$key.mNT; 
908        mRE             = $HRtempHash.$key.mRE }
909}
910$final = $persHash2 
911#create array of personnel aligned to matrix and output as file
912$final | Select FullName,HRPDM,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mProperty,mPD,mPDO,mAD,mCA,mCT,mEZ,mIg,mMA,mMG,mNw,mPa,mMS,mSl,mNT,mRE | sort FullName | Export-Csv "$runningdir\personnel-withFullName.csv" -NoTypeInformation
913#endregion </add matrix to fullname sort>
914#region <process itdb> -----> SHOULD BE PHASED OUT
915$itdbimp = Import-Csv -Path "$runningdir\Originals\itdb.csv"
916$itHash     = New-Object System.Collections.ArrayList
917$itdbimp | ForEach-Object { 
918    [void]$itHash.add((New-Object PSObject -Property @{ 
919        FullName     	= ($_.Name).Trim(); 
920        ITDBName     	= ($_.Name).Trim(); 
921        ITDBLicense  	= $_.License; 
922        ITDBPosition 	= $_.Position; 
923        ITDBProperty 	= $_.Property; 
924        ITDBMA			= $_.MA; 
925        ITDBCA      	= $_.CTA; 
926        ITDBTM 			= $_.TableManager; 
927        ITDBPa       	= $_.Pa; 
928        ITDBEZ        	= $_.EZ; 
929        ITDBCTCN       	= $_.CTCN; 
930        ITDBCTCR      	= $_.CTCR; 
931        ITDBCTSp      	= $_.CTSp; 
932        ITDBCTSt      	= $_.CTSt; 
933        ITDBCTTR       	= $_.CTTR; 
934        ITDBMD          = $_.MDA; 
935        ITDBIG  		= $_.IG; 
936        ITDBNw       	= $_.Nw; 
937        ITDBlastupdate	= $_.lastupdate; 
938        ITDBtermdate	= $_.termdate 
939    }))
940}
941#endregion </process itdb>
942#region <process cmp>
943$cmp     = Import-CSV $runningdir\Originals\cmp.csv #AD
944$cmpHash = New-Object System.Collections.Arraylist #AD List Hashtable
945$cmp | ForEach-Object {
946    [void]$cmpHash.add((New-Object PSObject -Property @{
947            FullName          = ((Get-Culture).textinfo.totitlecase(($_.FullName).tolower())).Trim();
948            distinguishedname = ($_.distinguishedname).Trim();
949            CN                = ($_.CN).Trim();
950            givenname         = ($_.givenname).Trim();
951            sn                = ($_.sn).Trim();
952            samaccountname    = ($_.samaccountname).Trim();
953            Title             = ($_.Title).Trim()
954        })
955    )
956}
957#join personnel/matrix and AD lists on 'FullName' and keeping everything from both lists | select all fields to keep and list | sort on 'FullName' | export to file
958Join-Object -Left $final -Right $cmpHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mAD,distinguishedname,CN,givenname,sn,samaccountname,Title | sort FullName | Export-Csv "$runningdir\vshr\HRvAD.csv" -NoTypeInformation 
959#endregion </process CMP>
960#region <process ma>
961$ma     = Import-CSV $runningdir\Originals\MAExport-truncated.csv #MA
962$MAHash = New-Object System.Collections.ArrayList #MA Array List
963$ma | ForEach-Object {
964    [void]$MAHash.add((New-Object PSObject -Property @{
965            FullName    = (Get-Culture).textinfo.totitlecase(($_.FirstName + ' ' + $_.LastName.subString(0, $_.LastName.lastIndexOf(' - '))).tolower()).Trim();
966            MASource	= $_.Source;
967            MALogName   = $_.LoginName;
968            MALName    	= $_.LastName.Trim();
969            MAFName   	= $_.FirstName.Trim();
970            MALicense   = $_.License;
971            MADesc 		= $_.Description;
972            MASID      	= $_.SiteID;
973            MAGroups    = $_.Groups
974        })
975    )
976}
977#join personnel/matrix and MA lists on 'FullName' and keeping everything from both lists | select all fields to keep and list | sort on 'FullName' | export to file
978Join-Object -Left $final -Right $MAHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mMA,MASource,MALogName,MALName,MAFName,MALicense,MADesc,MASID,MAGroups | sort FullName | Export-Csv "$runningdir\vshr\HRvMA.csv" -NoTypeInformation 
979#endregion </Parse MA to ArrayList>
980#region <process CA>
981$ca     = Import-CSV $runningdir\Originals\CAExport.csv #CA
982$CAHash = New-Object System.Collections.ArrayList #CA Array List
983$ca | ForEach-Object {
984    [void]$CAHash.add((New-Object PSObject -Property @{
985            FullName	= (Get-Culture).textinfo.totitlecase(($_.FName + ' ' + $_.LName.subString(0, $_.LName.lastIndexOf(' - '))).tolower()).Trim();
986            CASource	= $_.Source;
987            CALogName	= $_.LogName;
988            CALName		= $_.LName.Trim();
989            CAFName		= $_.FName.Trim();
990            CAMk		= $_.Mk;
991            CASt		= $_.St
992        })
993    )
994}
995#join personnel/matrix and CA lists on 'FullName' and keeping everything from both lists | select all fields to keep and list | sort on 'FullName' | export to file
996Join-Object -Left $final -Right $CAHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mCA,CASource,CALogName,CALName,CAFName,CAMk,CASt | sort FullName | Export-Csv "$runningdir\vshr\HRvCA.csv" -NoTypeInformation 
997#endregion </process CA>
998#region <process PA>
999$pa     = Import-CSV $runningdir\Originals\PAExport-truncated.csv #PA
1000$PAHash = New-Object System.Collections.ArrayList #PA Array List
1001$pa | ForEach-Object {
1002    [void]$PAHash.add((New-Object PSObject -Property @{
1003            FullName	= (Get-Culture).textinfo.totitlecase(($_.FName + ' ' + $_.LName.subString(0, $_.LName.lastIndexOf(' - '))).tolower()).Trim();
1004            PALogName	= $_.LogName;
1005            PALName		= $_.LName.Trim();
1006            PAFName		= $_.FName.Trim();
1007            PALicense	= $_.License;
1008            PAITDes		= $_.ITDes;
1009            PASID		= $_.SID;
1010            PAGroups	= $_.Groups
1011        })
1012    )
1013}
1014#join personnel/matrix and PA lists on 'FullName' and keeping everything from both lists | select all fields to keep and list | sort on 'FullName' | export to file
1015Join-Object -Left $final -Right $PAHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mPa,PALogName,PALName,PAFName,PALicense,PAITDes,PASID,PAGroups | sort FullName | Export-Csv "$runningdir\vshr\HRvPA.csv" -NoTypeInformation 
1016#endregion </process PA>
1017#region <process EZ>
1018$ez     = Import-CSV $runningdir\Originals\EZExport-truncated.csv #EZ
1019$EZHash = New-Object System.Collections.ArrayList #EZ Array List
1020$ez | ForEach-Object {
1021    [void]$EZHash.add((New-Object PSObject -Property @{
1022            FullName	= (Get-Culture).textinfo.totitlecase(($_.Name).tolower()).Trim();
1023            EZSource	= $_.Source;
1024            EZuname		= $_.uname;
1025            EZName		= $_.Name.Trim();
1026            EZRole		= $_.Role;
1027            EZVUG		= $_.VUG
1028        })
1029    )
1030}
1031#join personnel/matrix and EZ lists on 'FullName' and keeping everything from both lists | select all fields to keep and list | sort on 'FullName' | export to file
1032Join-Object -Left $final -Right $EZHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mEZ,EZSource,EZuname,EZName,EZRole,EZVUG | sort FullName | Export-Csv "$runningdir\vshr\HRvEZ.csv" -NoTypeInformation 
1033#endregion </process EZ>
1034#region <process IG>
1035$ig = Import-CSV $runningdir\Originals\IgExport.csv #ig
1036$IGHash = New-Object System.Collections.Arraylist #IG Array List
1037$ig | ForEach-Object {
1038    [void]$IGHash.add((New-Object PSObject -Property @{
1039            FullName	= (Get-Culture).textinfo.totitlecase(($_.FirstName + ' ' + $_.LastName).tolower()).Trim();
1040            IGCNum		= $_.CNum;
1041            IGFirstName	= $_.FirstName.Trim();
1042            IGLastName	= $_.LastName.Trim();
1043            IGHireDate	= $_.HireDate;
1044            IGEID		= $_.EID;
1045            IGUName		= $_.UName;
1046            IGLocation	= $_.Location
1047        })
1048    )
1049}
1050#join personnel/matrix and IG lists on 'FullName' and keeping everything from both lists | select all fields to keep and list | sort on 'FullName' | export to file
1051Join-Object -Left $final -Right $IGHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mIg,IGCNum,IGFirstName,IGLastName,IGHireDate,IGEID,IGUName,IGLocation | sort FullName | Export-Csv "$runningdir\vshr\HRvIg.csv" -NoTypeInformation 
1052#endregion </process IG>
1053#region <process CT>
1054$ct     = Import-CSV $runningdir\Originals\CTExport-truncated.csv #ct
1055$CTHash = New-Object System.Collections.Arraylist #CT Array List
1056$ct | ForEach-Object {
1057    [void]$CTHash.add((New-Object PSObject -Property @{
1058            FullName	= (Get-Culture).textinfo.totitlecase(($_.FName.Trim() + ' ' + $_.LName.Trim()).tolower()).Trim();
1059            CTSource	= $_.Source;
1060            CTOrgId		= $_.OrgId;
1061            CTFName		= $_.FName.Trim();
1062            CTLName		= $_.LName.Trim();
1063            CTDepartment	= $_.Department;
1064            CTRole		= $_.Role
1065        })
1066    )
1067}
1068#join personnel/matrix and CT lists on 'FullName' and keeping everything from both lists | select all fields to keep and list | sort on 'FullName' | export to file
1069Join-Object -Left $final -Right $CTHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mCT,CTSource,CTOrgId,CTFName,CTLName,CTDepartment,CTRole | sort FullName | Export-Csv "$runningdir\vshr\HRvCT.csv" -NoTypeInformation 
1070#endregion </process CT>
1071#region <process MG>
1072$mg     = Import-CSV $runningdir\Originals\MGExport.csv #mg
1073$MGHash = New-Object System.Collections.Arraylist #MG Array List
1074$mg | ForEach-Object {
1075    [void]$MGHash.add((New-Object PSObject -Property @{
1076            FullName	= (Get-Culture).textinfo.totitlecase(($_.FName + ' ' + $_.LName).tolower()).Trim();
1077            MGLogin		= $_.Login;
1078            MGCID		= $_.CID;
1079            MGFName		= $_.FName.Trim();
1080            MGLName		= $_.LName.Trim();
1081            MGTle		= $_.Tle;
1082            MGJob		= $_.Job;
1083            MGEID		= $_.EID
1084        })
1085    )
1086}
1087#join personnel/matrix and MG lists on 'FullName' and keeping everything from both lists | select all fields to keep and list | sort on 'FullName' | export to file
1088Join-Object -Left $final -Right $MGHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mMG,MGLogin,MGCID,MGFName,MGLName,MGTle,MGJob,MGEID | sort FullName | Export-Csv "$runningdir\vshr\HRvMG.csv" -NoTypeInformation 
1089#endregion </process MG>
1090#region <process NW>
1091$nw       = Import-CSV $runningdir\Originals\NWExport.csv #nw
1092$NWHash   = New-Object System.Collections.Arraylist #NW Array List
1093$nw | ForEach-Object {
1094    [void]$NWHash.add((New-Object PSObject -Property @{
1095            FullName	= (Get-Culture).textinfo.totitlecase($_.Name.tolower()).Trim();
1096            NWUName		= $_.UName;
1097            NWName		= $_.Name.Trim();
1098            NWTitle		= $_.Title;
1099            NWGL		= $_.NWGL
1100        })
1101    )
1102}
1103#join personnel/matrix and NW lists on 'FullName' and keeping everything from both lists | select all fields to keep and list | sort on 'FullName' | export to file
1104Join-Object -Left $final -Right $NWHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mNw,NWUName,NWName,NWTitle,NWGL | sort FullName | Export-Csv "$runningdir\vshr\HRvNW.csv" -NoTypeInformation 
1105#endregion </process NW>
1106
1107#region Merge CSVs to XLS
1108#Function Merge-CSVFiles is ....self-explanatory
1109Function Merge-CSVFiles
1110{
1111Param(
1112  [string[]]$CSVPath,
1113  [string]$XLOutput
1114)
1115
1116$csvFiles = Get-ChildItem ("$CSVPath\*") -Include *.csv
1117$Excel = New-Object -ComObject excel.application 
1118$Excel.visible = $false
1119$Excel.sheetsInNewWorkbook = $csvFiles.Count
1120$workbooks = $excel.Workbooks.Add()
1121$CSVSheet = 1
1122
1123Foreach ($CSV in $Csvfiles) {
1124  $worksheets = $workbooks.worksheets
1125  $CSVFullPath = $CSV.FullName
1126  $SheetName = ($CSV.name -split "\.")[0]
1127  $worksheet = $worksheets.Item($CSVSheet)
1128  $worksheet.Name = $SheetName
1129  $TxtConnector = ("TEXT;" + $CSVFullPath)
1130  $CellRef = $worksheet.Range("A1")
1131  $Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)
1132  $worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True
1133  $worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1
1134  $worksheet.QueryTables.item($Connector.name).Refresh()
1135  $worksheet.QueryTables.item($Connector.name).delete()
1136  $worksheet.UsedRange.EntireColumn.AutoFit()
1137  $CSVSheet++
1138}
1139
1140$workbooks.SaveAs($XLOutput,51)
1141$workbooks.Saved = $true
1142$workbooks.Close()
1143[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbooks) | Out-Null
1144$excel.Quit()
1145[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
1146[System.GC]::Collect()
1147[System.GC]::WaitForPendingFinalizers()
1148}
1149#endregion Merge CSVs to XLS
1150
1151Merge-CSVFiles -CSVPath $runningdir\vshr -XLOutput $runningdir\LongAudit.xlsx #wish it would save with date on filename
1152
1153
1154######################################################
1155# Stage 3 - compare private user DB vs HR and Scrapes
1156# Script 3 - more of less the same as stage 2, but adding in a privately kept user DB to make sure it is correct as well.
1157######################################################
1158
1159$env:PSModulePath = $env:PSModulePath + ";C:\Program Files\WindowsPowerShell\Modules"
1160Import-Module -Name PSExcel
1161
1162#set Running Directory
1163$runningdir = (Get-Item -Path ".\" -Verbose).FullName #set Running Directory
1164
1165#region Check if Folders exists / Create if they do not.
1166If (!(Test-Path "$runningdir\Originals")) { New-Item -ItemType Directory -Force -Path "$runningdir\Originals" | Out-Null } #if originals folders doesn't exist, create it
1167If (!(Test-Path "$runningdir\vshr"))      { New-Item -ItemType Directory -Force -Path "$runningdir\vshr"      | Out-Null } #if vshr folder doesn't exist, create it
1168If (!(Test-Path "$runningdir\itdb"))      { New-Item -ItemType Directory -Force -Path "$runningdir\itdb"      | Out-Null } #if itdb folder doesn't exist, create it
1169If (!(Test-Path "$runningdir\Clients"))   { New-Item -ItemType Directory -Force -Path "$runningdir\Clients"   | Out-Null } #if Clients folder doesn't exist, create it
1170#endregion
1171
1172#all of the functions and methods used in this one are the same as stage 2.  just more of them.
1173$clientlist = Import-XLSX $runningdir\clients2.xlsx
1174
1175#region <process personnel>
1176$perso   = Import-Csv -Path "$runningdir\perso.csv"
1177$persHash   = New-Object System.Collections.ArrayList
1178$perso | ForEach-Object {
1179    [void]$persHash.add((New-Object PSObject -Property @{
1180        HRProperty      = $_.Property;
1181        HRLastName      = $_.LastName;
1182        HRFirstName     = $_.FirstName;
1183        HRTitle         = $_.Title;
1184        HRPD            = $_.PD;
1185        HRDOH           = $_.DOH;
1186        HRLicenseNumber = $_.LicenseNumber
1187    }))
1188}
1189
1190$HRtempHash = New-Object System.Collections.Hashtable
1191$persHash | ForEach-Object {
1192    If ($_.HRFirstName -match '/') {
1193        If ($_.HRFirstName -match "\."){
1194            $tempFullName = (($_.HRFirstName.Split("/")[0]).Trim() -replace "(?:([A-Z][a-z]+))(\s+)([A-Z])(\.)",'$1')
1195            $FullName = (($tempFullName + " " + $_.HRLastName).toUpper()).Trim()
1196        } else {
1197            $FullName = (($_.HRFirstName.Split("/")[0].Trim() + " " + $_.HRLastName).toUpper()).Trim()
1198        }
1199    } elseif ($_.HRFirstName -match "\.") {
1200        $tempFullName = (($_.HRFirstName.Trim() -replace "(?:([A-Z][a-z]+))(\s+)([A-Z])(\.)",'$1')).Trim()
1201        $FullName = (($tempFullName + " " + $_.HRLastName).toUpper()).Trim()
1202    } elseif ($_.HRFirstName -notmatch "/") {
1203        $FullName = (($_.HRFirstName.Trim() + ' ' + $_.HRLastName).toUpper()).Trim()
1204    } else { 
1205    } 
1206    $HRtempHash.Set_Item($FullName, $_) 
1207}
1208
1209$persHash2  = New-Object System.Collections.ArrayList
1210ForEach ($key in $HRtempHash.keys) { 
1211    $persHash2 += New-Object PSObject -Property @{ 
1212        FullName        = ((Get-Culture).textinfo.totitlecase($key.tolower())).Trim(); 
1213        HRProperty      = $HRtempHash.$key.HRProperty; 
1214        HRLastName      = ($HRtempHash.$key.HRLastName).Trim(); 
1215        HRFirstName     = ($HRtempHash.$key.HRFirstName).Trim(); 
1216        HRTitle         = $HRtempHash.$key.HRTitle; 
1217        HRPD            = $HRtempHash.$key.HRPD; 
1218        HRDOH           = $HRtempHash.$key.HRDOH; 
1219        HRLicenseNumber = $HRtempHash.$key.HRLicenseNumber }
1220}
1221$final = $persHash2 
1222#endregion </process personel>
1223
1224$ClientHR = Join-Object -Left $clientlist -Right $final -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,Client_FName,Client_LName,Client_UName,Client_EMail,Client_Phone,Client_Notes,Client_Location,Client_Department,Client_Company,Client_Job,Client_PD,Client_License,Client_Ticket,Client_Site,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber
1225$ClientHR | sort FullName | Export-Csv "$runningdir\Clients\Clients-v-HR.csv" -NoTypeInformation 
1226
1227#region <process cmp>
1228Copy-Item -Path $runningdir\vshr\HRvAD.csv -Destination $runningdir\Clients\HrvAD.csv
1229#endregion </process CMP>
1230#region <process ma>
1231$ma     = Import-CSV $runningdir\vshr\HRvMA.csv #MA
1232$MAHash = New-Object System.Collections.ArrayList #MA Array List
1233$ma | ForEach-Object {
1234    [void]$MAHash.add((New-Object PSObject -Property @{
1235            FullName        = $_.FullName;
1236            HRProperty      = $_.HRProperty;
1237            HRLastName      = $_.HRLastName;
1238            HRFirstName     = $_.HRFirstName;
1239            HRTitle         = $_.HRTitle;
1240            HRPD            = $_.HRPD;
1241            HRDOH           = $_.HRDOH;
1242            HRLicenseNumber = $_.HRLicenseNumber;
1243            mPDO            = $_.mPDO;
1244            mMA             = $_.mMA;
1245            MASource        = $_.MASource;
1246            MALogName		= $_.MALogName;
1247            MALName			= $_.MALName;
1248            MAFName			= $_.MAFName;
1249            MALicense       = $_.MALicense;
1250            MADesc			= $_.MADesc;
1251            MASID			= $_.MASID;
1252            MAGroups        = $_.MAGroups
1253        })
1254    )
1255}
1256Join-Object -Left $clientlist -Right $MAHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,Client_ITJobDesc,Client_MA,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mMA,MASource,MALogName,MALName,MAFName,MALicense,MADesc,MASID,MAGroups | sort FullName | Export-Csv "$runningdir\Clients\ClientsvMA.csv" -NoTypeInformation 
1257#endregion </process MA>
1258#region <process CA>
1259$ca     = Import-CSV $runningdir\vshr\HRvCA.csv #CA
1260$CAHash = New-Object System.Collections.ArrayList #CA Array List
1261$ca | ForEach-Object {
1262    [void]$CAHash.add((New-Object PSObject -Property @{
1263            FullName        = $_.FullName;
1264            HRProperty      = $_.HRProperty;
1265            HRLastName      = $_.HRLastName;
1266            HRFirstName     = $_.HRFirstName;
1267            HRTitle         = $_.HRTitle;
1268            HRPD            = $_.HRPD;
1269            HRDOH           = $_.HRDOH;
1270            HRLicenseNumber = $_.HRLicenseNumber;
1271            mPDO            = $_.mPDO;
1272            mCA             = $_.mCA;
1273            CASource		= $_.CASource;
1274            CALogName		= $_.CALogName;
1275            CALName			= $_.CALName;
1276            CAFName			= $_.CAFName;
1277            CAMk			= $_.CAMk;
1278            CASt			= $_.CASt;
1279        })
1280    )
1281}
1282Join-Object -Left $clientlist -Right $CAHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,Client_ITJobDesc,Client_CA,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mCA,CASource,CALogName,CALName,CAFName,CAMk,CASt | sort FullName | Export-Csv "$runningdir\Clients\ClientsvCA.csv" -NoTypeInformation 
1283#endregion </process CA>
1284#region <process PA>
1285$pa     = Import-CSV $runningdir\vshr\HRvPA.csv #PA
1286$PAHash = New-Object System.Collections.ArrayList #PA Array List
1287$pa | ForEach-Object {
1288    [void]$PAHash.add((New-Object PSObject -Property @{
1289            FullName        = $_.FullName;
1290            HRProperty      = $_.HRProperty;
1291            HRLastName      = $_.HRLastName;
1292            HRFirstName     = $_.HRFirstName;
1293            HRTitle         = $_.HRTitle;
1294            HRPD            = $_.HRPD;
1295            HRDOH           = $_.HRDOH;
1296            HRLicenseNumber = $_.HRLicenseNumber;
1297            mPDO            = $_.mPDO;
1298            mPa             = $_.mPa;
1299            PALogName		= $_.PALogName;
1300            PALName			= $_.PALName;
1301            PAFName			= $_.PAFName;
1302            PALicense       = $_.PALicense;
1303            PAITDesc		= $_.PAITDesc;
1304            PASID			= $_.PASID;
1305            PAGroups        = $_.PAGroups
1306        })
1307    )
1308}
1309Join-Object -Left $clientlist -Right $PAHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,Client_ITJobDesc,Client_PA,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mPa,PALogName,PALName,PAFName,PALicense,PAITDesc,PASID,PAGroups | sort FullName | Export-Csv "$runningdir\Clients\ClientsvPA.csv" -NoTypeInformation 
1310#endregion </process PA>
1311#region <process EZ>
1312$ez     = Import-CSV $runningdir\vshr\HRvEZ.csv #EZ
1313$EZHash = New-Object System.Collections.ArrayList #EZ Array List
1314$ez | ForEach-Object {
1315    [void]$EZHash.add((New-Object PSObject -Property @{
1316            FullName        = $_.FullName;
1317            HRProperty      = $_.HRProperty;
1318            HRLastName      = $_.HRLastName;
1319            HRFirstName     = $_.HRFirstName;
1320            HRTitle         = $_.HRTitle;
1321            HRPD            = $_.HRPD;
1322            HRDOH           = $_.HRDOH;
1323            HRLicenseNumber = $_.HRLicenseNumber;
1324            mPDO            = $_.mPDO;
1325            mEZ             = $_.mEZ;
1326            EZSource        = $_.EZSource;
1327            EZuname			= $_.EZuname;
1328            EZName          = $_.EZName;
1329            EZRole          = $_.EZRole;
1330            EZVUG			= $_.EZVUG
1331        })
1332    )
1333}
1334Join-Object -Left $clientlist -Right $EZHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,Client_ITJobDesc,Client_EZ,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mEZ,EZSource,EZuname,EZName,EZRole,EZVUG | sort FullName | Export-Csv "$runningdir\Clients\ClientsvEZ.csv" -NoTypeInformation 
1335#endregion </process EZ>
1336#region <process IG>
1337$ig     = Import-CSV $runningdir\vshr\HRvIG.csv #ig
1338$IGHash = New-Object System.Collections.Arraylist #IG Array List
1339$ig | ForEach-Object {
1340    [void]$IGHash.add((New-Object PSObject -Property @{
1341            FullName        = $_.FullName;
1342            HRProperty      = $_.HRProperty;
1343            HRLastName      = $_.HRLastName;
1344            HRFirstName     = $_.HRFirstName;
1345            HRTitle         = $_.HRTitle;
1346            HRPD            = $_.HRPD;
1347            HRDOH           = $_.HRDOH;
1348            HRLicenseNumber = $_.HRLicenseNumber;
1349            mPDO            = $_.mPDO;
1350            mIg             = $_.mIg;
1351            IGCNum      	= $_.IGCNum;
1352            IGFirstName    	= $_.IGFirstName;
1353            IGLastName     	= $_.IGLastName;
1354            IGHireDate     	= $_.IGHireDate;
1355            IGEID   		= $_.IGEID;
1356            IGUName     	= $_.IGUName;
1357            IGLocation     	= $_.IGLocation
1358        })
1359    )
1360}
1361Join-Object -Left $clientlist -Right $IGHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,Client_IG,Client_IGC,Client_IGA,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mIg,IGCNum,IGFirstName,IGLastName,IGHireDate,IGEID,IGUName,IGLocation | sort FullName | Export-Csv "$runningdir\Clients\ClientvIg.csv" -NoTypeInformation 
1362#endregion </process IG>
1363#region <Parse CT to ArrayList>
1364$ct     = Import-CSV $runningdir\vshr\HRvCT.csv #ct
1365$CTHash = New-Object System.Collections.Arraylist #CT Array List
1366$ct | ForEach-Object {
1367    [void]$CTHash.add((New-Object PSObject -Property @{
1368            FullName        = $_.FullName;
1369            HRProperty      = $_.HRProperty;
1370            HRLastName      = $_.HRLastName;
1371            HRFirstName     = $_.HRFirstName;
1372            HRTitle         = $_.HRTitle;
1373            HRPD            = $_.HRPD;
1374            HRDOH           = $_.HRDOH;
1375            HRLicenseNumber = $_.HRLicenseNumber;
1376            mPDO            = $_.mPDO;
1377            mCT             = $_.mCT;
1378            CTSource       	= $_.CTSource;
1379            CTOrgId     	= $_.CTOrgId;
1380            CTFName    		= $_.CTFName;
1381            CTLName     	= $_.CTLName;
1382            CTDepartment   	= $_.CTDepartment;
1383            CTRole         	= $_.CTRole
1384        })
1385    )
1386}
1387Join-Object -Left $clientlist -Right $CTHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,Client_CTRR,Client_CTTR,Client_CTST,Client_CTCN,Client_CTSP,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mCT,CTSource,CTOrgId,CTFName,CTLName,CTDepartment,CCTRole | sort FullName | Export-Csv "$runningdir\Clients\ClientsvCT.csv" -NoTypeInformation 
1388#endregion </process CT>
1389#region <Parse NW to ArrayList>
1390$nw        = Import-CSV $runningdir\vshr\HRvNW.csv #nw
1391$NWHash   = New-Object System.Collections.Arraylist #NW Array List
1392$nw | ForEach-Object {
1393    [void]$NWHash.add((New-Object PSObject -Property @{
1394            FullName        = $_.FullName;
1395            HRProperty      = $_.HRProperty;
1396            HRLastName      = $_.HRLastName;
1397            HRFirstName     = $_.HRFirstName;
1398            HRTitle         = $_.HRTitle;
1399            HRPD            = $_.HRPD;
1400            HRDOH           = $_.HRDOH;
1401            HRLicenseNumber = $_.HRLicenseNumber;
1402            mPDO            = $_.mPDO;
1403            mNw             = $_.mNw;
1404            NWUName      	= $_.NWUName;
1405            NWName     		= $_.NWName;
1406            NWTitle         = $_.NWTitle;
1407            NWGL 			= $_.NWGL
1408        })
1409    )
1410}
1411Join-Object -Left $clientlist -Right $NWHash -LeftJoinProperty FullName -RightJoinProperty FullName -Type AllInBoth | select FullName,Client_Nw,HRProperty,HRLastName,HRFirstName,HRTitle,HRPD,HRDOH,HRLicenseNumber,mPDO,mNw,NWUName,NWName,NWTitle,NWGL | sort FullName | Export-Csv "$runningdir\Clients\ClientsvNW.csv" -NoTypeInformation 
1412#endregion </Parse NW to ArrayList>
1413
1414#region Merge CSVs to XLS
1415Function Merge-CSVFiles
1416{
1417Param(
1418  [string[]]$CSVPath,
1419  [string]$XLOutput
1420#$CSVPath = "$runningdir\vshr", ## Soruce CSV Folder
1421#$XLOutput="$runningdir\LongAudit.xlsx" ## Output file name
1422)
1423
1424$csvFiles = Get-ChildItem ("$CSVPath\*") -Include *.csv
1425$Excel = New-Object -ComObject excel.application 
1426$Excel.visible = $false
1427$Excel.sheetsInNewWorkbook = $csvFiles.Count
1428$workbooks = $excel.Workbooks.Add()
1429$CSVSheet = 1
1430
1431Foreach ($CSV in $Csvfiles) {
1432  $worksheets = $workbooks.worksheets
1433  $CSVFullPath = $CSV.FullName
1434  $SheetName = ($CSV.name -split "\.")[0]
1435  $worksheet = $worksheets.Item($CSVSheet)
1436  $worksheet.Name = $SheetName
1437  $TxtConnector = ("TEXT;" + $CSVFullPath)
1438  $CellRef = $worksheet.Range("A1")
1439  $Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)
1440  $worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True
1441  $worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1
1442  $worksheet.QueryTables.item($Connector.name).Refresh()
1443  $worksheet.QueryTables.item($Connector.name).delete()
1444  $worksheet.UsedRange.EntireColumn.AutoFit()
1445  $CSVSheet++
1446}
1447
1448$workbooks.SaveAs($XLOutput,51)
1449$workbooks.Saved = $true
1450$workbooks.Close()
1451[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbooks) | Out-Null
1452$excel.Quit()
1453[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
1454[System.GC]::Collect()
1455[System.GC]::WaitForPendingFinalizers()
1456}
1457
1458#endregion Merge CSVs to XLS
1459
1460Merge-CSVFiles -CSVPath $runningdir\Clients -XLOutput $runningdir\LongAudit-wClients.xlsx #would be nice to add date to filename