Solved

echo for the last line

Posted on 2013-12-14
14
279 Views
Last Modified: 2013-12-15
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
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
14 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39718953
Hi,
do you just want to output the last line / the last element in the foreach loop?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39718954
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
 

Author Comment

by:bibi92
ID: 39718961
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 70

Expert Comment

by:Qlemo
ID: 39718977
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
 

Author Comment

by:bibi92
ID: 39718989
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
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39719014
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
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39719016
Perhaps you can attach more of the script so that we can also see the complete scope of it.
0
 

Author Comment

by:bibi92
ID: 39719035
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 39719233
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
 

Author Comment

by:bibi92
ID: 39719339
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
 

Author Comment

by:bibi92
ID: 39719367
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
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39719881
$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
 

Author Closing Comment

by:bibi92
ID: 39720446
Thanks a lot regards
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Powershell database 5 44
output in HTML format powershell 6 50
Sharepoint 2013 edit permissions powershell 8 58
Health check of winows and webservces 5 51
Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question