Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

echo for the last line

Hello,

On the following loop, how can I add a echo  for the last line in $LIST_DBMIRR :

 foreach ($dbmirr in $LIST_DBMIRR) {

            $DB_NAME = $LIST_DBMIRR.Split("|")[0].Trim()

            # Discover each databases: db_name

            if ($EXCLUDE_DBS -notcontains $db_name) {

              if ($CLUSSVC -eq $null) {

                $line_DB = " { `"{#DBHOST}`":`"" + $HOSTNAME + "`" , `"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBMIRROR}`":`"" + $DB_NAME + "`"},"

              }

              else {
                $line_DB = " { `"{#DBHOST}`":`"" + "(" + $HOSTNAME + ")" + $SER_NAME + "`" , `"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBMIRROR}`":`"" + $DB_NAME + "`"},"
              }

Open in new window

Thanks
0
bibi92
Asked:
bibi92
  • 6
  • 4
  • 3
1 Solution
 
Rainer JeschorCommented:
Hi,
do you just want to output the last line / the last element in the foreach loop?
0
 
QlemoC++ DeveloperCommented:
Your code contains some mistakes; probably you want to iterate thru $List_DBMirr, and build an DB string from each line?
And what do you mean with "the last line in $List_DBMIRR"? The last line of $List_DBMirr is $List_DBMirr[-1], but I don't think that is what you asking for. More likely, you might want to iterate thru $List_DBMirr, and echo $line_DB?
0
 
bibi92Author Commented:
I add to iterate thru $LIST_DBMIRR but I want to add a test for $List_DBMirr[-1] for build line_DB without comma. I add to generate JSON FORMAT


 $line_DB = " { `"{#DBHOST}`":`"" + $HOSTNAME + "`" , `"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBMIRROR}`":`"" + $DB_NAME + "`"}"

Thanks
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
QlemoC++ DeveloperCommented:
Sorry, still not getting you. For example, you can always check for $dbmirr -eq $LIST_DBMIRR[-1] to see if you are processing the last line. Is that what you want to do?
0
 
bibi92Author Commented:
hello

The script generate

{
 "data":[

 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST"},
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST2"},

 ]
}

I have to remove the ',' on the last $line_db

Thanks

Regards
0
 
Rainer JeschorCommented:
Hi,

better approach might be to generate a list of string / array and then use the JOIN function to generate the final "string" for your JSON data:
[string]::join(',', $dataLines)} 

Open in new window

0
 
Rainer JeschorCommented:
Perhaps you can attach more of the script so that we can also see the complete scope of it.
0
 
bibi92Author Commented:
Hello,

Ok following a part of the script which contains 750 lines :
if ($arg1 -eq "DISCOVER") {

  # Open JSON message - with header
  Write-Host "{"
  Write-Host " `"data`":["
  Write-Host

  foreach ($arg2 in $arg1) {

    foreach ($INST in $LIST_INST) {

      if ($CLUSSVC -eq $null -or $SCMODE -eq 'AUTO') {

        $INST_NAME = $INST.Name.REPLACE("MSSQL$","")
      }

      else {

        $INST_NAME = $INST.Name.REPLACE("SQL Server (","")
        $INST_NAME = $INST_NAME.REPLACE(")","")
        $CSERVICES = Get-WmiObject -Class win32_service -ComputerName $HOSTNAME | where { $_.Name -match "$INST_NAME" -and $_.Name -match 'MSSQLF' -or $_.Name -eq 'SQLBrowser' -or $_.Name -eq 'SQLWriter' }

      }
      $REGPATH = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$INST_NAME
      $key_name = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\" + $REGPATH + "\Cluster"
      $key_prop = Get-ItemProperty -Path $key_name -ErrorAction silentlycontinue
      $FULLPATH = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$REGPATH"
      $SQLROOT = (Get-ItemProperty "$fullpath\Setup").SQLDataRoot
      $CONF_INST_TREE_ROOT = ($SQLROOT.Split("\") | Select -First 3) -join ("\")
      if ($CONF_INST_TREE_ROOT -match $INST_NAME) {
        $CONF_INST_CONFIG = $CONF_INST_TREE_ROOT + "\" + "config"
      }
      else {
        $CONF_INST_CONFIG = $CONF_INST_TREE_ROOT + "\" + $INST_NAME + "\" + "config"
      }
      if ($key_prop -eq $null) {
        $ser_name = (Get-WmiObject Win32_Computersystem).Name
      }
      else {
        $ser_name = $key_prop.ClusterName
      }

      $server = $SER_NAME + '\' + $INST_NAME
      if ($inst_name -eq "MSSQLSERVER") {
        $server = $ser_name

      }

       $SUPERVISION_ENV = $CONF_INST_CONFIG + "\" + "SUPERVISION.ENV"
       $CHCK_SUPERVISION_ENV = (Test-Path $SUPERVISION_ENV -PathType Leaf)

       if (($CHCK_SUPERVISION_ENV)) {
       $EXCLUDE_DBS = @( Select-String $SUPERVISION_ENV -Pattern "^(.+):DBS:OFF" | ? { $_ -notmatch "#" } | ForEach-Object { $_.matches } | ForEach-Object { $_.groups[1].value } | Select -Unique -ErrorAction silentlycontinue)

       $SPEC_DBS = @( Select-String $SUPERVISION_ENV -Pattern "^(.+):DBS:" | ? { $_ -notmatch "^([A-Z]:\\.+):DBS:OFF" } | ? { $_ -notmatch "#" } | ForEach-Object { $_.matches } | ForEach-Object { $_.groups[1].value } | Select -Unique -ErrorAction silentlycontinue)

       }
 
      if ($arg2 -eq "DB") {

        $SERVER_VERSION = (& $OSQL_CMD -E -S $server -d master -h -1 -b -W -Q "set nocount on;select SERVERPROPERTY('productversion')").Substring(0,2)

        if ($SERVER_VERSION -match 11)
        {
          $LIST_FG_DB = (& $OSQL_CMD -E -S $server -h -1 -b -W -Q "set nocount on;  select name from sys.databases where source_database_id is null and state_desc='ONLINE' 
        	and (replica_id is null or replica_id in (select replica_id from sys.dm_hadr_availability_replica_states where state_desc not in ('RECOVERING') and role_desc='PRIMARY' and is_local=1)) ")
	
        }

        else
        {
          $LIST_FG_DB = (& $OSQL_CMD -E -S $server -h -1 -b -W -Q "set nocount on;  select name from sys.databases WHERE state_desc='ONLINE'")

        }

        # Check Code Error
        if ($LASTEXITCODE -ne 0) { $st = 1 }

        $nb_fg_db = $LIST_FG_DB.count
        for ($i = 0; $i -lt $nb_fg_db; $i++) {
          $db_name = $LIST_FG_DB[$i]

          # Discover each filegroups: filegroup_name

          $LIST_FG = (& $OSQL_CMD -E -S $server -d $db_name -h -1 -W -b -Q "set nocount on;  select isnull(gr.name, 'LOG') from (select groupid from dbo.sysfiles a group by groupid) FG left outer join sys.filegroups gr on FG.groupid = gr.data_space_id")

          $nb_FG = $LIST_FG.count
          for ($j = 0; $j -lt $nb_FG; $j++) {
            $array_str = $LIST_FG[$j].Split("")

            $FG_name = $array_str[0].Trim()
            $DBSPACE = $db_name + "-" + $FG_NAME
	    
	

            if ($EXCLUDE_DBS -notcontains $db_name) {
              if ($CLUSSVC -eq $null) {
                $line_FG = " { `"{#DBHOST}`":`"" + $HOSTNAME + "`" , `"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBSPACE}`":`"" + $DBSPACE + "`"},"

              }

              else {
                $line_FG = " { `"{#DBHOST}`":`"" + "(" + $HOSTNAME + ")" + $SER_NAME + "`" ,`"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBSPACE}`":`"" + $DBSPACE + "`"},"

              }
              Write-Host $line_FG

            }
          }
        }
      }

      if ($arg2 -eq "MIRROR") {

        $SERVER_VERSION = (& $OSQL_CMD -E -S $server -d master -h -1 -b -W -Q "set nocount on;select SERVERPROPERTY('productversion')").Substring(0,2)

        if ($SERVER_VERSION -eq 11) {
        
	$LIST_DBMIRR = (& $OSQL_CMD -E -S $server -d master -h -1 -b -W -Q "set nocount on;  select DB_NAME(database_id) FROM sys.database_mirroring where mirroring_guid IS NOT NULL union 
        select name from sys.databases where replica_id in (select replica_id from sys.dm_hadr_availability_replica_states where role_desc='SECONDARY' and is_local=1 ) ")
        }

        else
        {
          $LIST_DBMIRR = (& $OSQL_CMD -E -S $server -d master -h -1 -W -b -Q " set nocount on; select DB_NAME(database_id) FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL")
        }

        if ($LIST_DBMIRR -ne $null) {


          foreach ($dbmirr in $LIST_DBMIRR) {

            $DB_NAME = $LIST_DBMIRR.Split("|")[0].Trim()

            # Discover each databases: db_name

            if ($EXCLUDE_DBS -notcontains $db_name) {

              if ($CLUSSVC -eq $null) {

                $line_DB = " { `"{#DBHOST}`":`"" + $HOSTNAME + "`" , `"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBMIRROR}`":`"" + $DB_NAME + "`"},"

              }

              else {
                $line_DB = " { `"{#DBHOST}`":`"" + "(" + $HOSTNAME + ")" + $SER_NAME + "`" , `"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBMIRROR}`":`"" + $DB_NAME + "`"},"
              }

              Write-Host $line_DB
            }
          }
        }
      }
    }
  }

  # Open JSON message - with header
  Write-Host
  Write-Host " ]"
  Write-Host "}"
  Write-Host

}

Open in new window

0
 
QlemoC++ DeveloperCommented:
Replace lines 96 to 106:
            if ($EXCLUDE_DBS -notcontains $db_name) {
              if ($CLUSSVC -eq $null) {
                $line_FG = " { `"{#DBHOST}`":`"" + $HOSTNAME + "`" , `"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBSPACE}`":`"" + $DBSPACE + "`"}"

              }

              else {
                $line_FG = " { `"{#DBHOST}`":`"" + "(" + $HOSTNAME + ")" + $SER_NAME + "`" ,`"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBSPACE}`":`"" + $DBSPACE + "`"}"

              }
              if ($i -lt $nb_FG-1) { $line_FG += ',' }
              Write-Host $line_FG

Open in new window

That is the smallest necessary change.
BTW, you could improve your script by not using OSQL, but direct .NET SQL calls (or SMO) - you'll have access to the fields retrieved directly, without need to parse text results.
0
 
bibi92Author Commented:
hello,

Ok I have modified the script, If the script generate more than two lines, the result is :

{
 "data":[

 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST"},
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST2"},
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST3"},
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST4"}
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST5"}

 ]
}

It's missing a coma here
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST4"},

Thanks
0
 
bibi92Author Commented:
I have modified like :
if ($j -lt $nb_FG-1) { $line_FG += ',' }

The result is not correct :
{
 "data":[

 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST"},
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST2"}
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST3"},
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST4"}
 { "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER" , "{#DBMIRROR}":"TEST5"},

 ]
}
0
 
QlemoC++ DeveloperCommented:
$i would be correct, but nevertheless I was confused by your usage of vars.
It is really necessary to have two nested FOR loops here? I cannot imagine why filegroups should ever be included in JSON, so I can't even guess what is correct here.

On the other hand, if we keep it as-is, we will need to check for the last line of $nb_fg_db and $nb_fg, and since we use reversed logic:
if ($i -lt $nb_FG_DB-1 -or $j -lt $nb_FG-1) { $line_FG += ',' }

Open in new window

0
 
bibi92Author Commented:
Thanks a lot regards
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now