Powershell - Creating New PSObject from variable

[tri_94]

Hi there

 

I'm trying to collate some data and have the following information being pulled in from xml

 

Name Check

---- -----

(default) {Domain Controller Test, SQL Server/MSDE Security Mode, CmdExec role, Registry Permissions...}

MSAS13.MSSQLSERVER {Domain Controller Test, SQL Server/MSDE Security Mode, CmdExec role, Registry Permissions...}

MSRS13.MSSQLSERVER {Domain Controller Test, SQL Server/MSDE Security Mode, CmdExec role, Registry Permissions...}

MSSQL13.MSSQLSERVER {Domain Controller Test, SQL Server/MSDE Security Mode, CmdExec role, Registry Permissions...}

(default) (32-bit) {Domain Controller Test, SQL Server/MSDE Security Mode, CmdExec role, Registry Permissions...}

 

What I was thinking is to create a new ps object for each name and the throw the data in to a new object

 

however I cant seem to get it to create new object in a foreach loop or am i going about this in totally the wrong way?

 

Thanks

[fordea]

What kind of output are you looking for - could you give an example maybe of how you want one of the lines from your post to look? It seems like you already have objects being output with a Name and Check property but I'm guessing you want it to be displayed differently?

[tri_94]

Hi there

 

The idea is that i want to extract the data held within "Check" and output it to excel for each "Name", as in MSAS13.MSSQLSERVER, MSRS13.MSSQLSERVER

 

Does that make sense?

 

Thanks

[DaveTheTech]

So you'd like to have this for each server?

 

[TABLE=class: grid, width: 500]

[TR]

[TD]Name[/TD]

[TD]Check[/TD]

[/TR]

[TR]

[TD]MSAS13.MSSQLSERVER[/TD]

[TD]Domain Controller Test[/TD]

[/TR]

[TR]

[TD]MSAS13.MSSQLSERVER[/TD]

[TD]SQL Server/MSDE Security Mode[/TD]

[/TR]

[TR]

[TD]MSAS13.MSSQLSERVER[/TD]

[TD]CmdExec role[/TD]

[/TR]

[TR]

[TD]MSAS13.MSSQLSERVER[/TD]

[TD]Registry Permissions[/TD]

[/TR]

[TR]

[TD]MSAS13.MSSQLSERVER[/TD]

[TD]....[/TD]

[/TR]

[/TABLE]

[tri_94]

yes that's basically it

[tri_94]

I've got a method that is working. I'm sure not the best way, using .SQLInstance[0]

 

and then using this foreach loop

 foreach($Scan in $Scanned)   
   { 

      
   		$Object = New-Object PSObject
              $Object | add-member Noteproperty $Scanned[0] $Scannedad[0]
              $Object | add-member Noteproperty $Scanned[1] $Scannedad[1]
              $Object | add-member Noteproperty $Scanned[2] $Scannedad[2]
              $Object | add-member Noteproperty $Scanned[3] $Scannedad[3]
              $Object | add-member Noteproperty $Scanned[4] $Scannedad[4]
              $Object | add-member Noteproperty $Scanned[5] $Scannedad[5]
              $Object | add-member Noteproperty $Scanned[6] $Scannedad[6]
              $Object | add-member Noteproperty $Scanned[7] $Scannedad[7]
              $Object | add-member Noteproperty $Scanned[8] $Scannedad[8]
              $Object | add-member Noteproperty $Scanned[9] $Scannedad[9]
              $Object | add-member Noteproperty $Scanned[10] $Scannedad[10]
              $Object | add-member Noteproperty $Scanned[11] $Scannedad[11]

and writing the data to excel and then changing SQLInstance[1] and next loop

 

Thanks

[DaveTheTech]

Not too sure whats in the Scanned variable.

You might be able to condense that by looping over $i and incrementing it?

 

Using Get-Aduser as an example(obviously you wouldnt need to do this) :

$output = $get-aduser |%{ [pscustomobject][ordered] @{    DisplayName = $_.DisplayName    email = $_.UserPrincipalName    Name = $_.Name    Role = $_.Title    Enabled = $_.enabled    }}

You would pass a list of the servers into a for loop into the function that your getting scanned from.

You would then send the output to "| export-csv myfile.csv -nti"

[HPlum78]

I am going to point you at Kevin's excellent post about all things hash tables (Kevin knows his onions, I have been in many of his presentations at PowerShell conferences all over the world, and MS have stolen parts of this post for their own docs... )

 

https://powershellexplained.com/2016-11-06-powershell-hashtable-everything-you-wanted-to-know-about/

[ThomL]

Any chance you can upload up the xml file you're trying to import?

[tri_94]

Test.xml

 

Attached is a sample xml

 

Thanks

[tri_94]

Attached is a copy of the script

 

Its too long to upload in the normal fashion.

 

 

Code.ps1

 

Thanks

[fordea]

It sounds like you want to loop through each of the SQL instance Check data and then create an object for each with all the Name and Advice properties. I agree with HPlum78 that hashtables would be the way to go with this (+ the recommendation to check out Kevin Marquette's website as he has loads of great posts that I've learned a lot from). You could do something like this:

 

foreach ($SqlInstance in $ScanResult.SecScan.SqlInstance) {
   $SqlInstanceData = [ordered]@{}
   $SqlInstanceData['Name'] = $SqlInstance.Name
   foreach ($Check in $SqlInstance.Check) {
       $sqlInstanceData[$Check.name] = $Check.Advice
   }
   [PSCustomObject]$SqlInstanceData
}

 

Having looked at the rest of your script I think you can consolidate it to something like this (although this doesn't have any excel formatting so you'd need to add that in):

 

$CheckNameSortOrder = @(
   'Security Updates',
   'Automatic Updates',
   'Incomplete Updates',
   'Password Expiration',
   'Windows Firewall',
   'Local Account Password Test',
   'File System',
   'Autologon',
   'Guest Account',
   'Restrict Anonymous',
   'Administrators',
   'Auditing',
   'Services',
   'Shares',
   'Windows Version',
   'IIS Status',
   'SQL Server/MSDE Status',
   'IE Zones',
   'IE Enhanced Security Configuration for Administrators',
   'IE Enhanced Security Configuration for Non-Administrators',
   'Macro Security'
)
$SQLInstanceNameAbbr = @{
   '(default)' = 'SQL'
   'MSAS13.MSSQLSERVER' = 'MSAS13'
   'MSRS13.MSSQLSERVER' = 'MSRS13'
   'MSSQL13.MSSQLSERVER' = 'MSSQL13'
   '(default) (32-bit)' = 'SQL32'
}
foreach ($SecScan in $ScanResult.SecScan) {
   $Data = [ordered]@{}
   $Data['Server name'] = $SecScan.Machine
   foreach ($SecScanCheck in ($SecScan.Check | Sort {$CheckNameSortOrder.IndexOf($_.Name)})) {
       $Data[$SecScanCheck.Name] = $SecScanCheck.Advice
   }
   foreach ($SQLInstance in $SecScan.SQLInstance) {
       foreach ($SQLInstanceCheck in $SQLInstance.Check) {
           $Data["{0}_{1}" -f $SQLInstanceNameAbbr[$SQLInstance.Name], $SQLInstanceCheck.name] = $SQLInstanceCheck.Advice
       }
   }
   [PSCustomObject]$Data
}

Edited May 22, 2021 by fordea

[HPlum78]

I know I have touched upon this with you in the past @tri_94 (and by the way nice bit of coding by @fordea) you could approach this a slightly different way. So instead of testing a servers config you could use DSC (desired state config) to say this is my configuration and I want you (the server) to make sure that your configuration matches.

 

By doing this you do two things you build your configuration documents for your servers as you go, and you can also build a pester test to verify that configuration, using essentially the same artifacts.

 

The PowerShell team release a 6 month statement of investment (I don't know if you look at this, if not I would suggest you take a look. Look for Steve Lee PowerShell in Google and you will find him..). Essentially in the next sprint they are looking at decoupling DSC from PowerShell (so they can develop them both separately, and we are not waiting on PS versions to unlock developments in DSC). Also in this sprint they have said they would look at getting DSC to support JSON rather than MOF files for the configuration management, if that becomes possible we will be able to use that directly and consume these JSON files within Pester tests that we can run against servers and pump out and in turn report on configuration.

 

Slightly different approach than writing a a load of scripts and tooling when there are some good tools already out there that you could make fit your requirements, and gain a load of community support.

 

H.

Edited May 23, 2021 by HPlum78

[tri_94]

I'll have a good look at this when I have the time, but thanks to everyone for you help.

 

- - - Updated - - -

 

That's brill I'll rework the script

 

Thanks again.

 

It sounds like you want to loop through each of the SQL instance Check data and then create an object for each with all the Name and Advice properties. I agree with HPlum78 that hashtables would be the way to go with this (+ the recommendation to check out Kevin Marquette's website as he has loads of great posts that I've learned a lot from). You could do something like this:

 

foreach ($SqlInstance in $ScanResult.SecScan.SqlInstance) {
   $SqlInstanceData = [ordered]@{}
   $SqlInstanceData['Name'] = $SqlInstance.Name
   foreach ($Check in $SqlInstance.Check) {
       $sqlInstanceData[$Check.name] = $Check.Advice
   }
   [PSCustomObject]$SqlInstanceData
}

 

Having looked at the rest of your script I think you can consolidate it to something like this (although this doesn't have any excel formatting so you'd need to add that in):

 

$CheckNameSortOrder = @(
   'Security Updates',
   'Automatic Updates',
   'Incomplete Updates',
   'Password Expiration',
   'Windows Firewall',
   'Local Account Password Test',
   'File System',
   'Autologon',
   'Guest Account',
   'Restrict Anonymous',
   'Administrators',
   'Auditing',
   'Services',
   'Shares',
   'Windows Version',
   'IIS Status',
   'SQL Server/MSDE Status',
   'IE Zones',
   'IE Enhanced Security Configuration for Administrators',
   'IE Enhanced Security Configuration for Non-Administrators',
   'Macro Security'
)
$SQLInstanceNameAbbr = @{
   '(default)' = 'SQL'
   'MSAS13.MSSQLSERVER' = 'MSAS13'
   'MSRS13.MSSQLSERVER' = 'MSRS13'
   'MSSQL13.MSSQLSERVER' = 'MSSQL13'
   '(default) (32-bit)' = 'SQL32'
}
foreach ($SecScan in $ScanResult.SecScan) {
   $Data = [ordered]@{}
   $Data['Server name'] = $SecScan.Machine
   foreach ($SecScanCheck in ($SecScan.Check | Sort {$CheckNameSortOrder.IndexOf($_.Name)})) {
       $Data[$SecScanCheck.Name] = $SecScanCheck.Advice
   }
   foreach ($SQLInstance in $SecScan.SQLInstance) {
       foreach ($SQLInstanceCheck in $SQLInstance.Check) {
           $Data["{0}_{1}" -f $SQLInstanceNameAbbr[$SQLInstance.Name], $SQLInstanceCheck.name] = $SQLInstanceCheck.Advice
       }
   }
   [PSCustomObject]$Data
}