bibi92
asked on
echo for the last line
Hello,
On the following loop, how can I add a echo for the last line in $LIST_DBMIRR :
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 + "`"},"
}
Thanks
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?
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?
ASKER
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
$line_DB = " { `"{#DBHOST}`":`"" + $HOSTNAME + "`" , `"{#INSTANCE}`":`"" + $INST_NAME + "`" , `"{#DBMIRROR}`":`"" + $DB_NAME + "`"}"
Thanks
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?
ASKER
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
The script generate
{
"data":[
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
]
}
I have to remove the ',' on the last $line_db
Thanks
Regards
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:
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)}
Perhaps you can attach more of the script so that we can also see the complete scope of it.
ASKER
Hello,
Ok following a part of the script which contains 750 lines :
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
}
Replace lines 96 to 106:
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.
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
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.
ASKER
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
Ok I have modified the script, If the script generate more than two lines, the result is :
{
"data":[
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
]
}
It's missing a coma here
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
Thanks
ASKER
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"},
]
}
if ($j -lt $nb_FG-1) { $line_FG += ',' }
The result is not correct :
{
"data":[
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
{ "{#DBHOST}":"SRV0TEST" , "{#INSTANCE}":"MSSQLSERVER
]
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot regards
do you just want to output the last line / the last element in the foreach loop?