KVAZDjQL

1<#
2 - This script is intended to collect information about your Passwordstate environment, and the server that hosts your Passwordstate web site.  This will not change any settings on your server.
3 - To run this script, ensure you are running Powershell as an Administrator
4 - When the script finishes running, please email the serverinfo.txt file to Click Studios on support@clickstudios.com.au
5
6Updated 15th Febrary 2019
7
8#>
9
10Write-Host "Script has started"
11
12#Add the required .NET assembly to allow popups
13Add-Type -AssemblyName System.Windows.Forms
14
15#Perform a check to ensure Powershell is running as Administrator
16If (-NOT ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator"))
17{
18	$wshell = New-Object -ComObject Wscript.Shell
19	$wshell.Popup("This script requires your Powershell session to be elevated.`n`nPlease run again in a new shell that is run as Administrator", 0, "Warning", 0x0)
20	Break
21}
22
23
24#Set path to store serverinfo.txt file
25Function Select-Folder
26{
27	param ([string]$Description = "Select Folder",
28		[string]$RootFolder = "Desktop")
29	
30	[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
31	$objForm = New-Object System.Windows.Forms.FolderBrowserDialog
32	$objForm.Rootfolder = $RootFolder
33	$objForm.Description = $Description
34	$Show = $objForm.ShowDialog()
35	If ($Show -eq "OK")
36	{
37		Return $objForm.SelectedPath
38	}
39	Else
40	{
41		Write-Error "Operation cancelled by user."
42		Break
43	}
44}
45
46$path = Select-Folder
47
48
49Write-Host "Removing existing serverinfo.zip file if it exists on disk"
50#Remove the out file if it already exists on the system
51$File = Test-Path $path\serverinfo.zip
52If ($File -eq $true) { Remove-item $path\serverinfo.zip -force }
53
54Write-Host "Getting the start time of the script"
55# Get the start time of the script
56$Start = get-date
57"Script begin time is $Start" | Out-File "$path\ServerInfo.txt"
58"" | Out-File "$path\ServerInfo.txt" -append
59"" | Out-File "$path\ServerInfo.txt" -append
60
61# Funtion to get all installed software on machine
62Function Get-Software  {
63
64  [OutputType('System.Software.Inventory')]
65
66  [Cmdletbinding()] 
67
68  Param( 
69
70  [Parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)] 
71
72  [String[]]$Computername=$env:COMPUTERNAME
73
74  )         
75
76  Begin {
77
78  }
79
80  Process  {     
81
82  ForEach  ($Computer in  $Computername){ 
83
84  If  (Test-Connection -ComputerName  $Computer -Count  1 -Quiet) {
85
86  $Paths  = @("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall","SOFTWARE\\Wow6432node\\Microsoft\\Windows\\CurrentVersion\\Uninstall")         
87
88  ForEach($Path in $Paths) { 
89
90  Write-Verbose  "Checking Path: $Path"
91
92  #  Create an instance of the Registry Object and open the HKLM base key 
93
94  Try  { 
95
96  $reg=[microsoft.win32.registrykey]::OpenRemoteBaseKey('LocalMachine',$Computer,'Registry64') 
97
98  } Catch  { 
99
100  Write-Error $_ 
101
102  Continue 
103
104  } 
105
106  #  Drill down into the Uninstall key using the OpenSubKey Method 
107
108  Try  {
109
110  $regkey=$reg.OpenSubKey($Path)  
111
112  # Retrieve an array of string that contain all the subkey names 
113
114  $subkeys=$regkey.GetSubKeyNames()      
115
116  # Open each Subkey and use GetValue Method to return the required  values for each 
117
118  ForEach ($key in $subkeys){   
119
120  Write-Verbose "Key: $Key"
121
122  $thisKey=$Path+"\\"+$key 
123
124  Try {  
125
126  $thisSubKey=$reg.OpenSubKey($thisKey)   
127
128  # Prevent Objects with empty DisplayName 
129
130  $DisplayName =  $thisSubKey.getValue("DisplayName")
131
132  If ($DisplayName  -AND $DisplayName  -notmatch '^Update  for|rollup|^Security Update|^Service Pack|^HotFix') {
133
134  $Date = $thisSubKey.GetValue('InstallDate')
135
136  If ($Date) {
137
138  Try {
139
140  $Date = [datetime]::ParseExact($Date, 'yyyyMMdd', $Null)
141
142  } Catch{
143
144  Write-Warning "$($Computer): $_ <$($Date)>"
145
146  $Date = $Null
147
148  }
149
150  } 
151
152  # Create New Object with empty Properties 
153
154  $Publisher =  Try {
155
156  $thisSubKey.GetValue('Publisher').Trim()
157
158  } 
159
160  Catch {
161
162  $thisSubKey.GetValue('Publisher')
163
164  }
165
166  $Version = Try {
167
168  #Some weirdness with trailing [char]0 on some strings
169
170  $thisSubKey.GetValue('DisplayVersion').TrimEnd(([char[]](32,0)))
171
172  } 
173
174  Catch {
175
176  $thisSubKey.GetValue('DisplayVersion')
177
178  }
179
180  $UninstallString =  Try {
181
182  $thisSubKey.GetValue('UninstallString').Trim()
183
184  } 
185
186  Catch {
187
188  $thisSubKey.GetValue('UninstallString')
189
190  }
191
192  $InstallLocation =  Try {
193
194  $thisSubKey.GetValue('InstallLocation').Trim()
195
196  } 
197
198  Catch {
199
200  $thisSubKey.GetValue('InstallLocation')
201
202  }
203
204  $InstallSource =  Try {
205
206  $thisSubKey.GetValue('InstallSource').Trim()
207
208  } 
209
210  Catch {
211
212  $thisSubKey.GetValue('InstallSource')
213
214  }
215
216  $HelpLink = Try {
217
218  $thisSubKey.GetValue('HelpLink').Trim()
219
220  } 
221
222  Catch {
223
224  $thisSubKey.GetValue('HelpLink')
225
226  }
227
228  $Object = [pscustomobject]@{
229
230  Computername = $Computer
231
232  DisplayName = $DisplayName
233
234  Version  = $Version
235
236  InstallDate = $Date
237
238  Publisher = $Publisher
239
240  UninstallString = $UninstallString
241
242  InstallLocation = $InstallLocation
243
244  InstallSource  = $InstallSource
245
246  HelpLink = $thisSubKey.GetValue('HelpLink')
247
248  EstimatedSizeMB = [decimal]([math]::Round(($thisSubKey.GetValue('EstimatedSize')*1024)/1MB,2))
249
250  }
251
252  $Object.pstypenames.insert(0,'System.Software.Inventory')
253
254  Write-Output $Object
255
256  }
257
258  } Catch {
259
260  Write-Warning "$Key : $_"
261
262  }   
263
264  }
265
266  } Catch  {}   
267
268  $reg.Close() 
269
270  }                  
271
272  } Else  {
273
274  Write-Error  "$($Computer): unable to reach remote system!"
275
276  }
277
278  } 
279
280  } 
281
282}  
283
284Write-Host "Listing all programs installed on server"
285#Get all programs installed on the server
286"Contents of Add/Remove Progams" | Out-File "$path\ServerInfo.txt" -append
287Get-Software | Select Publisher, DisplayName, Version, InstallDate | Sort DisplayName | Out-File "$path\ServerInfo.txt" -append
288"" | Out-File "$path\ServerInfo.txt" -append
289"" | Out-File "$path\ServerInfo.txt" -append
290
291Write-Host "Getting the name of the server"
292#Get web server name
293"Web Server Name" | Out-File "$path\ServerInfo.txt" -append
294$Servername = (Get-WmiObject win32_computersystem).DNSHostName + "." + (Get-WmiObject win32_computersystem).Domain
295$Servername | Out-File "$path\ServerInfo.txt" -append
296"" | Out-File "$path\ServerInfo.txt" -append
297
298Write-Host "Getting detailed IP Address information on server"
299#Getting the IP Address of the web server
300"Getting detailed IP Address information on server $Servername" | Out-File "$path\ServerInfo.txt" -append
301ipconfig /all | Out-File "$path\ServerInfo.txt" -append
302"" | Out-File "$path\ServerInfo.txt" -append
303
304Write-Host "Finding out last boot up time"
305#Get Server last book up time
306"Last boot time of $Servername" | Out-File "$path\ServerInfo.txt" -append
307[string](Get-CimInstance -ClassName win32_operatingsystem).lastbootuptime | Out-File "$path\ServerInfo.txt" -append
308"" | Out-File "$path\ServerInfo.txt" -append
309"" | Out-File "$path\ServerInfo.txt" -append
310
311Write-Host "Calculating how much free memory is available"
312#Get free memory on server
313"Free Memory on $servername" | Out-File "$path\ServerInfo.txt" -append
314Get-WmiObject -Class win32_operatingsystem | ft @{ Name = "Total Visible Memory Size (GB)"; e = { [math]::truncate($_.TotalVisibleMemorySize /1MB) } }, @{ Name = "Free Physical Memory (GB)"; e = { [math]::truncate($_.FreePhysicalMemory /1MB) } } -AutoSize | Out-File "$path\ServerInfo.txt" -append
315"" | Out-File "$path\ServerInfo.txt" -append
316
317Write-Host "Getting Detailed Information about Server Memory"
318#Get Detailed Information about Server Memory
319"Getting Detailed Information about Server Memory from $servername" | Out-File "$path\ServerInfo.txt" -append
320$Computername = $env:computername
321get-process -computername $Computername | Group-Object -Property ProcessName | Format-Table Name, @{n='Mem (KB)';e={'{0:N0}' -f (($_.Group|Measure-Object WorkingSet -Sum).Sum / 1KB)};a='right'} -AutoSize | Out-File "$path\ServerInfo.txt" -append
322 
323Function Test-MemoryUsage {
324[cmdletbinding()]
325Param()
326$os = Get-Ciminstance Win32_OperatingSystem
327$pctFree = [math]::Round(($os.FreePhysicalMemory/$os.TotalVisibleMemorySize)*100,2)
328if ($pctFree -ge 45) {
329$Status = "OK"
330}
331elseif ($pctFree -ge 15 ) {
332$Status = "Warning"
333}
334else {
335$Status = "Critical"
336}
337$os | Select @{Name = "Status";Expression = {$Status}},
338@{Name = "PctFree"; Expression = {$pctFree}},
339@{Name = "FreeGB";Expression = {[math]::Round($_.FreePhysicalMemory/1mb,2)}},
340@{Name = "TotalGB";Expression = {[int]($_.TotalVisibleMemorySize/1mb)}}
341}
342 
343Test-MemoryUsage | Out-File "$path\ServerInfo.txt" -append
344"" | Out-File "$path\ServerInfo.txt" -append
345"" | Out-File "$path\ServerInfo.txt" -append
346
347Write-Host "Calculating free disk space"
348#Get Free Disk Sace on Server
349"Free Disk Space on $Severname" | Out-File "$path\ServerInfo.txt" -append
350$Space = (Get-WmiObject Win32_LogicalDisk -Filter "DeviceID='C:'").freespace /1gb
351"This server has $space GB free disk space on C:" | Out-File "$path\ServerInfo.txt" -append
352"" | Out-File "$path\ServerInfo.txt" -append
353"" | Out-File "$path\ServerInfo.txt" -append
354
355Write-Host "Checking is server is part of a domain"
356#Is web server part of domain?
357$domain = (Get-WmiObject -Class Win32_ComputerSystem).PartOfDomain
358"Domain check" | Out-File "$path\ServerInfo.txt" -append
359if ($domain -eq $true) { Write-Output "$servername is part of a domain" | Out-File "$path\ServerInfo.txt" -append 
360
361Write-Host "Getting Domain Information"
362#Get Netbios and FQDN domain information
363"Netbios and FQDN domain information" | Out-File "$path\ServerInfo.txt" -append
364$DomainInfo = gwmi Win32_NTDomain | Select DomainName, DNSForestName
365$DomainInfo | Out-File "$path\ServerInfo.txt" -append
366"" | Out-File "$path\ServerInfo.txt" -append
367
368}
369"" | Out-File "$path\ServerInfo.txt" -append
370"" | Out-File "$path\ServerInfo.txt" -append
371
372Write-Host "Querying what language the server is using"
373#Get Server Language
374"Operating System Language" | Out-File "$path\ServerInfo.txt" -append
375[string](Get-Culture).DisplayName | ft -AutoSize | Out-File "$path\ServerInfo.txt" -append
376"" | Out-File "$path\ServerInfo.txt" -append
377"" | Out-File "$path\ServerInfo.txt" -append
378
379Write-Host "Finding out information about the Application Pools in IIS"
380#Getinformation about Passwordstate application pools
381"Look for all Application Pools and find their state, and identity" | Out-File "$path\ServerInfo.txt" -append
382Get-WebApplication -Site passwordstate | ft -AutoSize | Out-File "$path\ServerInfo.txt" -append
383Import-Module WebAdministration; Get-ChildItem -Path IIS:\AppPools\ | Select-Object name, state, managedRuntimeVersion, managedPipelineMode, @{ e = { $_.processModel.username }; l = "username" }, @{ e = { $_.processModel.identityType }; l = "identityType" } | format-table -AutoSize | ft -AutoSize | Out-File "$path\ServerInfo.txt" -append
384"" | Out-File "$path\ServerInfo.txt" -append
385
386Write-Host "Querying the web bindings for the Passwordstate website"
387#Get IIS Web Bindings for Passwordstate
388"Passwordstate Web Bindings" | Out-File "$path\ServerInfo.txt" -append
389Get-webbinding -Name Passwordstate | Select Protocol, BindingInformation | ft -AutoSize | Out-File "$path\ServerInfo.txt" -append
390"" | Out-File "$path\ServerInfo.txt" -append
391
392Write-Host "Listing the Folders and Files in the Passwordstate directory"
393#Get folder and files information
394"Passwordstate Install Directory Properties" | Out-File "$path\ServerInfo.txt" -append
395$Site = Get-WebSite -Name Passwordstate
396$InstallPath = $Site.PhysicalPath
397Get-ChildItem -Path $Installpath | select fullname | Out-File "$path\ServerInfo.txt" -append
398"" | Out-File "$path\ServerInfo.txt" -append
399"" | Out-File "$path\ServerInfo.txt" -append
400
401Write-Host "Getting the content of the Upgrade log if there is any"
402"Upgrade Log Contents" | Out-File "$path\ServerInfo.txt" -append
403#Get contents of Upgrade Log
404$UpgradeLog = "$InstallPath\Upgrades\Upgradelog.txt"
405$UpgradeLogResults = test-path $Upgradelog
406
407If ($UpgradelogResults -eq $true)
408{
409"Upgrade Log Contents" | Out-File "$path\ServerInfo.txt" -append
410Get-Content $Upgradelog | Out-File "$path\ServerInfo.txt" -append
411}
412else
413{
414"Upgrade Log file was not found indicating last upgrade was successful" | Out-File "$path\ServerInfo.txt" -append
415}
416"" | Out-File "$path\ServerInfo.txt" -append
417"" | Out-File "$path\ServerInfo.txt" -append
418
419
420Write-Host "Passwordstate Gateway Directory Properties"
421#Get folder and files information from Gateway folder
422"Passwordstate Gateway Directory Properties" | Out-File "$path\ServerInfo.txt" -append
423$Site = Get-WebSite -Name Passwordstate
424$InstallPath = $Site.PhysicalPath
425$HostsPath = "$InstallPath/Hosts/Gateway"
426Get-ChildItem -Path $Hostspath | select fullname | Out-File "$path\ServerInfo.txt" -append
427"" | Out-File "$path\ServerInfo.txt" -append
428"" | Out-File "$path\ServerInfo.txt" -append
429
430
431Write-Host "Perform an nslookup of the https binding"
432#Perform an NSLookup
433"NSLookup of Passwordstate URL (From IIS Bindings)" | Out-File "$path\ServerInfo.txt" -append
434$url = Get-webbinding -Name Passwordstate | Where-Object { $_.protocol -eq 'https' }
435[string]$a = $url.bindinginformation
436($url = $a.split(":")[-1]) > $null 2>&1
437nslookup $url | Out-File "$path\ServerInfo.txt" -append
438"" | Out-File "$path\ServerInfo.txt" -append
439"" | Out-File "$path\ServerInfo.txt" -append
440
441Write-Host "Listing all certificates"
442#Get Information about Certificates installed on Server
443"Server Certificate information" | Out-File "$path\ServerInfo.txt" -append
444Get-ChildItem "Cert:\LocalMachine\My" | Select-Object Subject, FriendlyName, Issuer, DNSNameList, NotAfter | Format-Table -AutoSize | Out-File "$path\ServerInfo.txt" -append
445"" | Out-File "$path\ServerInfo.txt" -append
446
447Write-Host "Querying the version of Powershell installed"
448#Get information about Powershell version
449"Powershell Version" | Out-File "$path\ServerInfo.txt" -append
450$host.version | Out-File "$path\ServerInfo.txt" -append
451"" | Out-File "$path\ServerInfo.txt" -append
452
453Write-Host "Finding out information about the Passwordstate service"
454#Get information about Passwordstate Service
455$status = (Get-Service "passwordstate service").Status
456$pwsuser = (Get-WmiObject Win32_Service -Filter "Name='passwordstate Service'").StartName
457"Passwordstate Service Information" | Out-File "$path\ServerInfo.txt" -append
458"Passwordstate service is currently $status" | Out-File "$path\ServerInfo.txt" -append
459"Passwordstate service Log On As user is $pwsuser" | Out-File "$path\ServerInfo.txt" -append
460"" | Out-File "$path\ServerInfo.txt" -append
461"" | Out-File "$path\ServerInfo.txt" -append
462
463Write-Host "Finding out information about the Passwordstate-Gateway service if it exists"
464#Get information about Passwordstate Gateway Service WIP
465$PWSGate = Get-WmiObject -Class Win32_Service -Filter "Name='passwordstate-gateway'"
466
467If ($PWSGate -eq $null)
468{
469    "Passwordstate-Gateway Service Information" | Out-File "$path\ServerInfo.txt" -append
470	Write-Output "The Passwordstate-Gateway service is not installed on this computer" | Out-File "$path\ServerInfo.txt" -append
471	"" | Out-File "$path\ServerInfo.txt" -append
472}
473Else
474{
475	# Get information about the Gateway Service
476    $status1 = (Get-Service "passwordstate-gateway").Status
477	$pwsuser1 = (Get-WmiObject Win32_Service -Filter "Name='passwordstate-Gateway'").StartName
478	"Passwordstate Gateway Service Information" | Out-File "$path\ServerInfo.txt" -append
479	"Passwordstate Gateway service is currently $status1" | Out-File "$path\ServerInfo.txt" -append
480	"Passwordstate Gateway service Log On As user is $pwsuser1" | Out-File "$path\ServerInfo.txt" -append
481	"" | Out-File "$path\ServerInfo.txt" -append
482
483    Write-Host "Finding out information about the Gateway.conf file"
484    #Get information from the Gateway.conf file
485    "Information out of the Gateway.conf file" | Out-File "$path\ServerInfo.txt" -append
486    import-csv "$HostsPath\gateway.conf"  | Out-File "$path\ServerInfo.txt" -append
487    "" | Out-File "$path\ServerInfo.txt" -append
488    
489}
490
491Write-Host "Finding out what version of Passwordstate web files are installed"
492#Get information about Passwordstate web files version
493$installpath = (Get-WebFilePath "IIS:\Sites\Passwordstate").FullName
494$exe = "$installpath\bin\passwordstate.exe"
495$fileversion = [System.Diagnostics.FileVersionInfo]::GetVersionInfo("$exe").FileVersion
496"Passwordstate Executable Information" | Out-File "$path\ServerInfo.txt" -append
497"Passwordstate executable version is $fileversion" | Out-File "$path\ServerInfo.txt" -append
498"" | Out-File "$path\ServerInfo.txt" -append
499
500Write-Host "Getting all permissions on the Passwordstate install folder"
501#Get permissions on Passwordstate folder
502"Passwordstate Folder Permissions" | Out-File "$path\ServerInfo.txt" -append
503$perms = Get-Acl $installpath | select -expand access
504$perms | select IdentityReference, FileSystemRights | Out-File "$path\ServerInfo.txt" -append
505"" | Out-File "$path\ServerInfo.txt" -append
506
507Write-Host "Getting all permissions on the Passwordstate executable"
508#Get permissions on Passwordstate.exe
509"Passwordstate.exe Permissions" | Out-File "$path\ServerInfo.txt" -append
510$Site = Get-WebSite -Name Passwordstate
511$InstallPath = $Site.PhysicalPath
512(get-acl "$Installpath\bin\passwordstate.exe").access | ft IdentityReference,FileSystemRights,AccessControlType,IsInherited,InheritanceFlags -auto | Out-File "$path\ServerInfo.txt" -append
513"" | Out-File "$path\ServerInfo.txt" -append
514"" | Out-File "$path\ServerInfo.txt" -append
515
516Write-Host "Getting all permissions on the HighAvailability.db file"
517#Get permissions on HighAvailability.db file
518"HighAvailability.db Permissions" | Out-File "$path\ServerInfo.txt" -append
519$Site = Get-WebSite -Name Passwordstate
520$InstallPath = $Site.PhysicalPath
521(get-acl "$Installpath\App_Data\HighAvailability.db").access | ft IdentityReference,FileSystemRights,AccessControlType,IsInherited,InheritanceFlags -auto | Out-File "$path\ServerInfo.txt" -append
522"" | Out-File "$path\ServerInfo.txt" -append
523"" | Out-File "$path\ServerInfo.txt" -append
524
525Write-Host "Currently looking in the web.config file to check for SetupStage and PassiveNode status"
526#Querying web.config file to obtain information out of the AppSettings section
527"Information out of the AppSettings section of the web.config file" | Out-File "$path\ServerInfo.txt" -append
528$webConfigFile = [xml](Get-Content "$installpath\web.config")
529$root = $webConfigFile.get_DocumentElement();
530$root = $root.appsettings.add
531if ($root -eq '')
532{
533	"We've tried to query the Web.config file AppSettings but it is encrypted, so We'll now skip this step and pass onto the next system check." | Out-File "$path\ServerInfo.txt" -append
534	"" | Out-File "$path\ServerInfo.txt" -append
535	"" | Out-File "$path\ServerInfo.txt" -append
536}
537else
538{
539	$root | Where-Object { $_.Key -eq "SetupStage" -or $_.Key -eq "PassiveNode" } | Out-File "$path\ServerInfo.txt" -append
540	"" | Out-File "$path\ServerInfo.txt" -append
541}
542
543Write-Host "Currently looking in the web.config file to see if we can find connection details to database server"
544#Querying web.config file to obtain database connection string
545"Information out of the Connection String section of the web.config file" | Out-File "$path\ServerInfo.txt" -append
546$webConfigFile = [xml](Get-Content "$installpath\web.config")
547[string]$string = $webConfigFile.configuration.connectionstrings.add | Select connectionstring
548
549if ($string -like "*Integrated Security=SSPI*" -or $string -eq '')
550{
551	"We've tried to query the Connection String in the Web.config file but it is either encrypted or is using an MSA account, so we cannot make connections to the database.  We'll now skip this step and pass onto the next system check." | Out-File "$path\ServerInfo.txt" -append
552	"" | Out-File "$path\ServerInfo.txt" -append
553	"" | Out-File "$path\ServerInfo.txt" -append
554}
555else
556{
557	$string = $string.substring(2)
558	$string = $string.substring(0, $string.length - 1)
559	$res = $string.Split(";")
560	$connString = $res[0]
561	$connString = $connString.replace('connectionString=Data Source=', '')
562	$initialCatalog = $res[1]
563	$initialCatalog = $initialCatalog.replace('Initial Catalog=', '')
564	$userId = $res[2]
565	$userId = $userId.replace('User ID=', '')
566	$password = $res[3]
567	$password = $password.replace('Password=', '')
568	
569	
570	Write-Host "We have found connection details in the Web.config file"
571	#Write servername, SQL Instance and database to log file
572	"Web.config file Details" | Out-File "$path\ServerInfo.txt" -append
573	"The database server and SQL Instance is: $connString" | Out-File "$path\ServerInfo.txt" -append
574	"The name of the Database is: $initialCatalog" | Out-File "$path\ServerInfo.txt" -append
575	"" | Out-File "$path\ServerInfo.txt" -append
576	
577	#Add all SQL Queries into variables to be called later
578	$SQLQuery1 = "SELECT PasswordLists.PasswordListID, PasswordLists.PasswordList, PasswordLists.TreePath, PasswordLists.PrivatePasswordList, `
579(SELECT COUNT(PasswordID) FROM [Passwords] PSSWD WHERE (PSSWD.PasswordListID = PasswordLists.PasswordListID) AND (PSSWD.Deleted = 0)) As TotalPasswords, (SELECT COUNT(PasswordListID) FROM [PasswordListsACL] PSSWD WHERE (PSSWD.PasswordListID = PasswordLists.PasswordListID)) As TotalPermissions `
580FROM [PasswordLists] `
581WHERE (PasswordLists.Folder = 0) `
582GROUP BY PasswordLists.PasswordListID, PasswordLists.PasswordList, PasswordLists.TreePath, PasswordLists.PrivatePasswordList`
583ORDER BY PasswordLists.PrivatePasswordList, PasswordLists.PasswordList"
584	
585	$SQLQuery2 = "SELECT PasswordLists.PasswordListID, PasswordLists.PasswordList, PasswordLists.TreePath, PasswordLists.PrivatePasswordList, `
586(SELECT COUNT(PasswordID) FROM [Passwords] PSSWD WHERE (PSSWD.PasswordListID = PasswordLists.PasswordListID) AND (PSSWD.Deleted = 0)) As TotalPasswords, (SELECT COUNT(PasswordListID) FROM [PasswordListsACL] PSSWD WHERE (PSSWD.PasswordListID = PasswordLists.PasswordListID)) As TotalPermissions `
587FROM [PasswordLists] `
588WHERE (PasswordLists.Folder = 1) `
589GROUP BY PasswordLists.PasswordListID, PasswordLists.PasswordList, PasswordLists.TreePath, PasswordLists.PrivatePasswordList`
590ORDER BY PasswordLists.PrivatePasswordList, PasswordLists.PasswordList"
591	
592	$SQLQuery3 = "SELECT COUNT(*) AS TotalAuditingRecords FROM Auditing"
593	
594	$SQLQuery4 = "SELECT COUNT(*) AS TotalUsers FROM UserAccounts"
595	
596	$SQLQuery5 = "SELECT COUNT(*) AS TotalSecurityGroups FROM SecurityGroups"
597	
598	$SQLQuery6 = "SELECT BuildNo, SiteURL, FIPSMode, FormsBasedAuthentication,ADSyncSchedule, SAMLLegacyCode, RecordingFolder, SelfDestructURL, GatewayURL, GatewayPort FROM SystemSettings"
599	
600	$SQLQuery7 = "SELECT DebugDateTime, Category, DebugInformation, Eventtype FROM DebugInfo"
601	
602    $SQLQuery8 = "SELECT BackupFilePath, BackupUserName, ExcludeDBBackup FROM BackupSettings"
603
604    $SQLQuery9 = "SELECT @@Version"
605
606    $SQLQuery10 = "SELECT @@ServiceName"
607
608    $SQLQuery11 = "SELECT LicenseType, RegistrationName, NoLicenses, SupportExpires, TrialPeriod, DisplayLabel, TrialDays FROM Licenses"
609	
610	$Server = $connstring
611	$database = $initialcatalog
612	$Connection = New-Object System.Data.SQLClient.SQLConnection
613	$Connection.ConnectionString = "server=$($Server);database=$($Database);User ID=$userID;Password=$password"
614	$Connection.Open()
615	$Command = New-Object System.Data.SQLClient.SQLCommand
616	$Command.Connection = $Connection
617	Write-Host "Retrieving some information from the SQL Database"
618	
619	"Information about PasswordLists" | Out-File "$path\ServerInfo.txt" -append
620	$Command.CommandText = $SQLQuery1
621	$Reader = $Command.ExecuteReader()
622	$Datatable = New-Object System.Data.DataTable
623	$Datatable.Load($Reader)
624	$datatable | ft -autosize | Out-File "$path\ServerInfo.txt" -append
625	
626	"Information about Folders" | Out-File "$path\ServerInfo.txt" -append
627	$Command.CommandText = $SQLQuery2
628	$Reader = $Command.ExecuteReader()
629	$Datatable = New-Object System.Data.DataTable
630	$Datatable.Load($Reader)
631	$datatable | ft -autosize | Out-File "$path\ServerInfo.txt" -append
632	
633	"Count of Auditing records in main Auditing Table" | Out-File "$path\ServerInfo.txt" -append
634	$Command.CommandText = $SQLQuery3
635	$Reader = $Command.ExecuteReader()
636	$Datatable = New-Object System.Data.DataTable
637	$Datatable.Load($Reader)
638	$datatable | ft -autosize | Out-File "$path\ServerInfo.txt" -append
639	
640	"Count of Total users in the system" | Out-File "$path\ServerInfo.txt" -append
641	$Command.CommandText = $SQLQuery4
642	$Reader = $Command.ExecuteReader()
643	$Datatable = New-Object System.Data.DataTable
644	$Datatable.Load($Reader)
645	$datatable | ft -autosize | Out-File "$path\ServerInfo.txt" -append
646	
647	"Count of Total Security Groupss" | Out-File "$path\ServerInfo.txt" -append
648	$Command.CommandText = $SQLQuery5
649	$Reader = $Command.ExecuteReader()
650	$Datatable = New-Object System.Data.DataTable
651	$Datatable.Load($Reader)
652	$datatable | ft -autosize | Out-File "$path\ServerInfo.txt" -append
653	
654	"Information From System Settings" | Out-File "$path\ServerInfo.txt" -append
655	$Command.CommandText = $SQLQuery6
656	$Reader = $Command.ExecuteReader()
657	$Datatable = New-Object System.Data.DataTable
658	$Datatable.Load($Reader)
659	$datatable | ft -autosize | Out-File "$path\ServerInfo.txt" -append
660	"" | Out-File "$path\ServerInfo.txt" -append
661	
662    "Information From Backups Table" | Out-File "$path\ServerInfo.txt" -append
663	$Command.CommandText = $SQLQuery8
664	$Reader = $Command.ExecuteReader()
665	$Datatable = New-Object System.Data.DataTable
666	$Datatable.Load($Reader)
667	$datatable | ft -autosize | Out-File "$path\ServerInfo.txt" -append
668	"" | Out-File "$path\ServerInfo.txt" -append
669
670    "Information From Licensing table" | Out-File "$path\ServerInfo.txt" -append
671	$Command.CommandText = $SQLQuery11
672	$Reader = $Command.ExecuteReader()
673	$Datatable = New-Object System.Data.DataTable
674	$Datatable.Load($Reader)
675	$datatable | Format-List | Out-File "$path\ServerInfo.txt" -append
676	"" | Out-File "$path\ServerInfo.txt" -append
677
678    "SQL Server Version" | Out-File "$path\ServerInfo.txt" -append
679	$Command.CommandText = $SQLQuery9
680	$Reader = $Command.ExecuteReader()
681	$Datatable = New-Object System.Data.DataTable
682	$Datatable.Load($Reader)
683	$datatable | Format-List | Out-File "$path\ServerInfo.txt" -append
684	"" | Out-File "$path\ServerInfo.txt" -append
685    
686    "SQL Server Instance Name" | Out-File "$path\ServerInfo.txt" -append
687	$Command.CommandText = $SQLQuery10
688	$Reader = $Command.ExecuteReader()
689	$Datatable = New-Object System.Data.DataTable
690	$Datatable.Load($Reader)
691	$datatable | Format-List | Out-File "$path\ServerInfo.txt" -append
692	"" | Out-File "$path\ServerInfo.txt" -append
693    "Information From DebugInfo table" | Out-File "$path\ServerInfo.txt" -append
694	$Command.CommandText = $SQLQuery7
695	$Reader = $Command.ExecuteReader()
696	$Datatable = New-Object System.Data.DataTable
697	$Datatable.Load($Reader)
698	$datatable | Format-List | Out-File "$path\ServerInfo.txt" -append
699	"" | Out-File "$path\ServerInfo.txt" -append
700
701  
702	
703    $Connection.Close()
704}
705
706
707Write-Host "Listing all errors in the Application Event log"
708#Get all Application event log errors within the last 7 days
709Set-Variable -Name EventAgeDays -Value 7 #we will take events for the latest 7 days
710Set-Variable -Name CompArr -Value @("localhost") # replace it with your server names
711Set-Variable -Name LogNames -Value @("Application") # Checking app and system logs
712Set-Variable -Name EventTypes -Value @("2") # Loading only Errors and Warnings
713Set-Variable -Name ExportFolder -Value $path
714
715$el_c = @() #consolidated error log
716$now = get-date
717$startdate = $now.adddays(- $EventAgeDays)
718$ExportFile = $ExportFolder + "el" + $now.ToString("yyyy-MM-dd---hh-mm-ss") + ".csv" # we cannot use standard delimiteds like ":"
719
720foreach ($comp in $CompArr)
721{
722	foreach ($log in $LogNames)
723	{
724		$el = get-winevent -ComputerName $comp -FilterHashtable @{ logname = "$log"; level = $eventtypes; starttime = $startdate } -ErrorAction SilentlyContinue
725		$el_c += $el #consolidating
726	}
727}
728$el_sorted = $el_c | Sort-Object TimeGenerated #sort by time
729#Write-Host Exporting to $ExportFile
730"All Application Event Log Errors are Listed Below" | Out-File "$path\ServerInfo.txt" -append
731$el_sorted | Select LevelDisplayName, TimeCreated, ProviderName, ID, MachineName, Message | Format-List | Out-File "$path\ServerInfo.txt" -append
732"" | Out-File "$path\ServerInfo.txt" -append
733
734Write-Host "Getting the end time of the script"
735# Get the end time of the script
736$End = get-date
737"Script finish time is $End" | Out-File "$path\ServerInfo.txt" -append
738"" | Out-File "$path\ServerInfo.txt" -append
739"" | Out-File "$path\ServerInfo.txt" -append
740
741Write-Host "Testing whether the compress-archive commandlet is available in Powershell"
742if ($host.version.Major -ge 5)
743{
744Write-Host "Compressing the text file"
745# Next we will compress the txt file to a zip file
746"Currently Compressing the output file - The script has now ended" | Out-File "$path\ServerInfo.txt" -append
747compress-archive -literalpath $path\ServerInfo.txt -CompressionLevel Optimal -DestinationPath $path\ServerInfo.zip -Force
748remove-Item -path $path\ServerInfo.txt -force
749
750#Add pop up to confirm script has complete
751$wshell = New-Object -ComObject Wscript.Shell
752$wshell.Popup("Script is complete. Please email $path\serverinfo.zip to support@clickstudios.com.au",0,"Process Complete",0x0) | Out-Null
753}
754else
755{
756#Add pop up to confirm script has complete
757$wshell = New-Object -ComObject Wscript.Shell
758$wshell.Popup("Script is complete. Please email $path\serverinfo.txt to support@clickstudios.com.au. 
759
760You may need to use a program to manually compress this file if it is too large to send via email.",0,"Process Complete",0x0) | Out-Null
761}
762
763
764Break