[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

change the last line of a variable in a loop

Posted on 2014-08-31
10
Medium Priority
?
116 Views
Last Modified: 2014-09-14
Hello,

I have to change the last line of $line_FG for remove comma in a loop :

		$LIST_INST="TEST,TEST2"
		foreach ($INST in $LIST_INST) {
		$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 ($CLUSSVC -eq $null ) {
                $line_FG = " { `"{#DBHOST}`":`"" + $HOSTNAME + "`" , `"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBSPACE}`":`"" + $DBSPACE + "`"},"
	 	
              	}

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

              	}
		}
		}

Open in new window

How can I do it?
Thanks
0
Comment
Question by:bibi92
  • 5
  • 4
10 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 40295341
Firstly, you should either use single quotes here (no need for escaping double quotes then), or double quotes and string substitution inside those. Example for line 14:
                $line_FG = ' { "{#DBHOST}":"' + $HOSTNAME + '" , "{#INSTANCE}":"' + $INST_NAME + '" , "{#DBSPACE}":"' + $DBSPACE + '"},'

Open in new window

or
                $line_FG = " { `"{#DBHOST}`":`"$HOSTNAME`" , `"{#INSTANCE}`":`"$INST_NAME`" , `"{#DBSPACE}`":`"$DBSPACE`"},"

Open in new window

But what do you ask for exactly? Lines 14 and 19 are different in that the former as a trailing comma, the latter not.
I suppose both should have a trailing comma, and the very last line of the output should get it removed - correct?
0
 

Author Comment

by:bibi92
ID: 40295414
Hello,

For answer to your question :
I suppose both should have a trailing comma, and the very last line of the output should get it removed - correct?
--> yes, the very last line of the output should get removed trailing comma.

I can't use single quotes because it's JSON format for ZABBIX discover rules.

Thanks
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 40295453
In PowerShell you can use single quotes. The result needs to be in JSON format using double quotes. Both variations of your line in http:#a40295341 will have the exact same result, but are much better to read and edit.
Looking at your code, you are only storing one line. Don't think that is intentional either.
$LIST_INST='TEST,TEST2'
foreach ($INST in $LIST_INST) {
  $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")

  $line_FG = @()
  $nb_FG = $LIST_FG.count
  foreach ($fg in $LIST_FG) {
    $DBSPACE = $db_name + "-" + $_.Split()[0].Trim()

    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 ($line_FG) { $line_FG[-1] = $line_FG[-1].Remove($line_FG[-1].Length-1) }

Open in new window

This now creates an array of strings, with each array element corresponding to a single line.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:bibi92
ID: 40295476
Thanks, the following error message is returned :
Method invocation failed because [System.Char] doesn't contain a method named 'Remove'.
At d:\zab_ctrol_db.ps1:331 char:61
+           if ($line_FG) { $line_FG[-1] = $line_FG[-1].Remove <<<< ($line_FG[-1].Length-1) }
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 40295510
That's not possible. $line_FG is an array, so [-1] refers to an array element and not a char. Are you sure you used the code as supplied respective did not change too much?
0
 
LVL 14

Expert Comment

by:Justin Yeung
ID: 40295660
I don't think system.char has the function of remove

You will probably make it as a string and then do a remove method
0
 

Author Comment

by:bibi92
ID: 40299746
Yes, I have used the code.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 40299777
Oh, wait. There are several errors in your original code:

You are running the same SQL against the same server each time ($server and $db_name do not change).

$HOSTNAME and $INST_NAME are not set nor do they change.

Looks like you oversimplified the code for posting here, removing too much.
After you have corrected that by setting and/or using the correct set of vars, move my line 5 before or after line 1.
0
 

Author Comment

by:bibi92
ID: 40321727
Ok, I have tested but the following line remove the trailing comma for all lines and not the last :
if ($line_FG) { $line_FG[-1] = $line_FG[-1].Remove($line_FG[-1].Length-1) }
Thanks
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 40321742
Not with my code. Make sure the cited IF is outside of the FOREACH, otherwise it gets executed for each line instead of the last one.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
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 anti-spam), the admin…
Screencast - Getting to Know the Pipeline

830 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