Solved

echo for the last line

Posted on 2013-12-14
14
273 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
Comment Utility
Hi,
do you just want to output the last line / the last element in the foreach loop?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 44

Expert Comment

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

Author Comment

by:bibi92
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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
Comment Utility
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 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
$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
Comment Utility
Thanks a lot regards
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hi all.   The other day I had to change the passwords for a bunch of users on the fly. Because they were so many, I decided to do it in an automated way and I would like to share it with you all.   If you are not doing it directly in a Domain Co…
The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

11 Experts available now in Live!

Get 1:1 Help Now