Turning Powershell output into Column CSV file

I have a powershell script to pull information from IIS, currently it just drops the output on the screen and for the time that was fine, but now we need to drop the output into columns in a csv file. This will then be imported into a Database to allow us to query for more information.

I am having issues with getting this into columns and into a CSV file output. Any help is appreciated.

-------------------------
Set-ExecutionPolicy RemoteSigned
#
Import-Module WebAdministration

Get-WebApplication | `
ForEach-Object {

Write-Host "Web Application: $($_.path)"
"Physical Path: $($_.PhysicalPath)"
"Application Pool: $($_.ApplicationPool)"


$test_webconfig_exists = test-path "$($_.PhysicalPath)\Web.config"

if($test_webconfig_exists)
{
$webConfigFile = [xml](Get-Content "$($_.PhysicalPath)\Web.config")

foreach($connString in $webConfigFile.configuration.connectionStrings.add)
{
  Write-Host "-----------------"
  Write-Host "Connection String $($connString.name): $($connString.connectionString)"
  $dbRegex = "((Initial\sCatalog)|((Database)))\s*=(?<ic>[a-z\s0-9]+?);"
  $found = $connString.connectionString -match $dbRegex
  if ($found)
  {
  Write-Host "~~~~~~~~~~~~~~~~~~~~"
   Write-Host "Database: $($Matches["ic"])"
  Write-Host "~~~~~~~~~~~~~~~~~~~~"
  }
  #Write-Host "-----------------"
}
}

if($test_webconfig_exists -eq "")
{
Write-Host "Missing Web.Config File: Yes"
} Else
{
Write-Host "Missing Web.Config File: No"
}
Write-Host "**======================================================**"

}

Open in new window

Ed WalshSenior Director of Applications and Cloud ServicesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

footechCommented:
Can you identify what the columns are that you actually want?
0
slightwv (䄆 Netminder) Commented:
Is there a reason you need to go to a CSV file first and not directly to the database?
0
oBdACommented:
The script below encapsulates your script into a function and returns the information as PS custom objects, one object for each combination of WebApp and connection string.
You can do the usual with the results, see the examples at the end.
Function Get-WebApplicationReport() {
	Import-Module WebAdministration
	$dbRegex = '((Initial\sCatalog)|((Database)))\s*=(?<ic>[a-z\s0-9]+?);'
	Get-WebApplication | ForEach-Object {
		$webApp = $_
		$(
			If (Test-Path -Path "$($webApp.PhysicalPath)\Web.config") {
				$webConfig = [xml](Get-Content "$($webApp.PhysicalPath)\Web.config")
				ForEach ($connString in $webConfig.configuration.connectionStrings.add) {
					[PsCustomObject][ordered]@{
						'web.config File' =		'YES'
						'Connection Name' =		$connString.name
						'Connection String' =	$connString.connectionString
						'Database' =			$(If ($connString.connectionString -match $dbRegex) {$Matches['ic']} Else {'<UNKNOWN>'})
					}
				}
			} Else {
				[PsCustomObject][ordered]@{'web.config File' = 'NO'}
			}
		) | Select-Object -Property `
				@{n='Web Application';		e={$webApp.Path}},
				@{n='Physical Path';		e={$webApp.PhysicalPath}},
				@{n='Application Pool';		e={$webApp.ApplicationPool}},
				'web.config File',
				'Connection Name',
				'Connection String',
				'Database'
	} 
}
$Report = Get-WebApplicationReport
$Report
$Report | Out-GridView
$Report | Export-Csv -NoTypeInformation -Path 'C:\Temp\WebApplicationReport.csv'
$Report | Where-Object {$_.'web.config File' -eq 'NO'}

Open in new window

0
Redefine Your Security with AI & Machine Learning

The implications of AI and machine learning in cyber security are massive and constantly growing, creating both efficiencies and new challenges across the board. Check out our on-demand webinar to learn more about how AI can help your organization!

Ed WalshSenior Director of Applications and Cloud ServicesAuthor Commented:
@footech


"Web Application: $($_.path)"
"Physical Path: $($_.PhysicalPath)"
"Application Pool: $($_.ApplicationPool)"
"Connection String $($connString.name): $($connString.connectionString)"
"Database: $($Matches["ic"])"
Missing Web.Config File: (Yes/No)
0
Ed WalshSenior Director of Applications and Cloud ServicesAuthor Commented:
@slightwv

I didn't think of that, but truth it could go straight into a MSSQL DB, however we will run this script every so often, maybe 2x a month, and the PS script would need to update existing entries in database if there is changes. and if possible would still like to do a csv output.
0
Ed WalshSenior Director of Applications and Cloud ServicesAuthor Commented:
One thing I forgot and sorry about this omission in original question. I also need to run this script against a file that has a list of servers to pull the information from and drop into the CSV, or database as @slightwv suggests.

Will run this from one server and it will pull from the list of IIS web servers we have from a text file. Will also need to add two new columns to identify the machine the information came from and also a date/time data was collected.

@footech, would need to add these two new columns to the list I posted earlier.

Again sorry about the omission.

Thanks again.
0
oBdACommented:
Requires remote PowerShell enabled on the target servers, since Get-WebApplication doesn't natively support remote operations.
You can pipe a server list to the function, or pass a string array using the -ComputerName argument.
Function Get-WebApplicationReport {
[CmdletBinding()]
Param(
	[Parameter(ValueFromPipeline=$true)]
	[string[]]$ComputerName
)
	Begin {
	}
	Process {
		$ComputerName | ForEach-Object {
			$Server = $_
			$icArgs = @{}
			If ($Server -and ($Server -ne $ENV:ComputerName)) {$icArgs['ComputerName'] = $Server}
			$Time = Get-Date
			Invoke-Command @icArgs -ScriptBlock {
				Import-Module WebAdministration
				$dbRegex = '((Initial\sCatalog)|((Database)))\s*=(?<ic>[a-z\s0-9]+?);'
				Get-WebApplication | ForEach-Object {
					$webApp = $_
					$(
						If (Test-Path -Path "$($webApp.PhysicalPath)\Web.config") {
							$webConfig = [xml](Get-Content "$($webApp.PhysicalPath)\Web.config")
							ForEach ($connString in $webConfig.configuration.connectionStrings.add) {
								[PsCustomObject][ordered]@{
									'web.config File' =		'YES'
									'Connection Name' =		$connString.name
									'Connection String' =	$connString.connectionString
									'Database' =			$(If ($connString.connectionString -match $dbRegex) {$Matches['ic']} Else {'<UNKNOWN>'})
								}
							}
						} Else {
							[PsCustomObject][ordered]@{'web.config File' = 'NO'}
						}
					) | Select-Object -Property `
							@{n='ComputerName';			e={$ENV:ComputerName}},
							@{n='Time';					e={$Time}},
							@{n='Web Application';		e={$webApp.Path}},
							@{n='Physical Path';		e={$webApp.PhysicalPath}},
							@{n='Application Pool';		e={$webApp.ApplicationPool}},
							'web.config File',
							'Connection Name',
							'Connection String',
							'Database'
				} 
			} 
		} 
	}
	End {
	}
}

$Report = Get-Content -Path C:\Temp\ServerList.txt | Get-Content -Path Get-WebApplicationReport

Open in new window

0
slightwv (䄆 Netminder) Commented:
If the server that is running the script can see both the web servers and the database server I see no reason why it cannot go straight to the database.  As far as inserts/updates, I also don't see a problem.  It is a lot more involved but doable.

Personally, I don't think this is a PowerShell project.  Sure, it is doable but if you look at the bigger picture, I would look more towards a .Net Console App to do it all.  If you are pulling from IIS and looking for Powershell, you likely have access to .Net folks.  I would use them.

You haven't stated yet how you are planning of getting the CSV file into the database. I'm also not sure why you would still want the CSV if you could go straight into the database.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Steve, there is no reason to switch over from PowerShell, as it uses the same techniques as .NET applications. Using PS here is perfectly fine.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The last line of oBdA's code got messed up, and missing the CSV export. it should be
$Report = Get-Content -Path C:\Temp\ServerList.txt | Get-WebApplicationReport
$Report | Export-CSV -NoType C:\Temp\WebApplicationReport.csv

Open in new window

0
Ed WalshSenior Director of Applications and Cloud ServicesAuthor Commented:
#oBdA

The RegEx doesn't seem to work as planned.
$dbRegex = "((Initial\sCatalog)|((Database)))\s*=(?<ic>[a-z\s0-9]+?);"

In my original script if the connection string had "Initial Catalog" or "Database" in it it would pull out the information after the = sign, in this case the database name. But your script only pulls the information after the = if the connection string has "Database" in it, if it has "Initial Catalog" it puts in "Unknown".
0
Ed WalshSenior Director of Applications and Cloud ServicesAuthor Commented:
#oBdA

Sorry what I said before is not totally correct after I looked more closely at output file.

So it seems to pull it sometimes if it sees Initial Catalog but not always.

Sample connection strings are slightly altered for security but they follow same Punctuation, letter count and capitalization.

Example of Connection String that did pull the Database name:
Data Source=server.domain.com;Initial Catalog=SystemDeliverable;User ID=user1;Password=xxxxx;

Example of Connection String that it did not pull the Database name and instead placed "UnKnown":
Data Source=server.domain.com;;Initial Catalog=dev_dbSpecific;User ID=user1;Password=xxxxx;
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The only difference I can see is the leading ';;', but I don't think that's the culprit. However, doesn't harm to remove the double semi-colon for test.
0
oBdACommented:
The RegEx is completely unchanged from your version.
The issue is that you were too selective concerning the allowed characters (the underscore in this case), allowed whitespace after the equal was missing, and the database name might be enclosed in single quotes.
Try this:
$dbRegex = "((Initial\sCatalog)|((Database)))\s*=\s*'?(?<ic>.+?)('|;|\Z)"

Open in new window

0
Ed WalshSenior Director of Applications and Cloud ServicesAuthor Commented:
If its possible, I also need a column for the Website Name the Web Application is associated to.
0
Ed WalshSenior Director of Applications and Cloud ServicesAuthor Commented:
Another observation,if I run this script from one of the webservers found in the serverlist.txt file it puts in the date/time for that servers entries in the output, but for a remote server on the list it leaves the Time column blank.
0
oBdACommented:
Setting the time slipped outside the scriptblock, now with site name:
Function Get-WebApplicationReport {
[CmdletBinding()]
Param(
	[Parameter(ValueFromPipeline=$true)]
	[string[]]$ComputerName
)
	Begin {
	}
	Process {
		$ComputerName | ForEach-Object {
			$Server = $_
			$icArgs = @{}
			If ($Server -and ($Server -ne $ENV:ComputerName)) {$icArgs['ComputerName'] = $Server}
			Invoke-Command @icArgs -ScriptBlock {
				Import-Module WebAdministration
				$Time = Get-Date
				$dbRegex = "((Initial\sCatalog)|((Database)))\s*=\s*'?(?<ic>.+?)('|;|\Z)"
				Get-WebApplication | ForEach-Object {
					$webApp = $_
					$(
						If (Test-Path -Path "$($webApp.PhysicalPath)\Web.config") {
							$webConfig = [xml](Get-Content "$($webApp.PhysicalPath)\Web.config")
							ForEach ($connString In $webConfig.configuration.connectionStrings.add) {
								[PsCustomObject][ordered]@{
									'web.config File' =		'YES'
									'Connection Name' =		$connString.name
									'Connection String' =	$connString.connectionString
									'Database' =			$(If ($connString.connectionString -match $dbRegex) {$Matches['ic']} Else {'<UNKNOWN>'})
								}
							}
							If (-not $connString) {
								[PsCustomObject][ordered]@{
									'web.config File' =		'YES'
									'Connection Name' =		$null
									'Connection String' =	$null
									'Database' =			$null
								}
							}
						} Else {
							[PsCustomObject][ordered]@{'web.config File' = 'NO'}
						}
					) | Select-Object -Property `
							@{n='ComputerName';			e={$ENV:ComputerName}},
							@{n='Time';					e={$Time}},
							@{n='Web Application';		e={$webApp.Path}},
							@{n='Site Name';			e={If ($webApp.ItemXPath -match "@name\s*=\s*'(?<Name>.*?)'") {$Matches['Name']} Else {'<UNKNOWN>'}}},
							@{n='Physical Path';		e={$webApp.PhysicalPath}},
							@{n='Application Pool';		e={$webApp.ApplicationPool}},
							'web.config File',
							'Connection Name',
							'Connection String',
							'Database'
				} 
			} 
		} 
	}
	End {
	}
}

$Report = Get-Content -Path C:\Temp\ServerList.txt | Get-WebApplicationReport
$Report
$Report | Export-Csv -NoTypeInformation -Path 'C:\Temp\WebApplicationReport.csv'

Open in new window

2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ed WalshSenior Director of Applications and Cloud ServicesAuthor Commented:
#oBdA  Thank you for your help on this, it works perfect.
0
Ed WalshSenior Director of Applications and Cloud ServicesAuthor Commented:
Thanks again, it works perfect
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.