Solved

echo for the last line

Posted on 2013-12-14
14
276 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
  • 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 69

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 69

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 69

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 69

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now