change the last line of a variable in a loop

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
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
bibi92Author Commented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

bibi92Author Commented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Justin YeungSenior Systems EngineerCommented:
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
bibi92Author Commented:
Yes, I have used the code.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
bibi92Author Commented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.