g9hkVt0W

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