Solved

echo for the last line

Posted on 2013-12-14
14
277 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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