Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, May 28, 2013 6:04 PM
I currently have a powershell script that returns one row of data, the script goes like this:
$Query = "SELECT
t1.BSM_NM
,t1.D_DTM AS MAXDATETIME
,CASE
WHEN SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.MAJOR_VOICE_BLOCK) AND SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) < MAX(t2.CRITICAL_VOICE_BLOCK)
OR
SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.MAJOR_VOICE_DROP) AND SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) < MAX(t2.CRITICAL_VOICE_DROP)
OR
SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.MAJOR_VOICE_AXSFAIL) AND SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) < MAX(t2.CRITICAL_VOICE_AXSFAIL)
THEN 1
WHEN SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.CRITICAL_VOICE_BLOCK)
OR
SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.CRITICAL_VOICE_DROP)
OR
SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.CRITICAL_VOICE_AXSFAIL)
THEN 2
ELSE 0
END MAJORCRITICAL
FROM DMSN.DS3R_FH_1XRTT_BTS_LVL_KPI t1
INNER JOIN
ZDMSN.DS3R_1XRTT_TRIGGERS_THRESHOLD t2
ON
t1.BSM_NM = t2.BSC_NM
WHERE t1.BSM_NM = 'ARL1' and t1.D_DTM = (SELECT MAX(D_DTM) FROM DS3R_FH_1XRTT_BTS_LVL_KPI WHERE BSM_NM = 'ARL1')
GROUP BY
t1.BSM_NM, t1.D_DTM"
$data_set = new-object system.data.dataset
$adapter = new-object system.data.oracleclient.oracledataadapter ($Query, $Connection)
[void] $adapter.Fill($data_set)
$table = new-object system.data.datatable
$table = $data_set.Tables[0]
$bsmNM = $data_set.Tables[0].Rows[0].BSM_NM
$maxDT = $data_set.Tables[0].Rows[0].MAXDATETIME
$majorC = $data_set.Tables[0].Rows[0].MAJORCRITICAL
if ($majorC -eq "1" )
the above query would return results in one row like this:
now, lets say I change it so it returns between 2 and 10 rows of data, and if there is just a 1 in the MAJORCRITICAL column, to do something and if there is a 1 and a 2 in the MAJORCRITICAL column to do something else. The query would now return data as such
How can I set it to read through multiple row results in the query?
All replies (12)
Wednesday, May 29, 2013 6:28 PM ✅Answered
The ForEach-Object loop goes through each row of the table one at a time, so you can't use :
if ($_.MAJORCRITICAL -eq "1" -and $_.MAJORCRITICAL -eq "2"){}
One row of the table cannot contain MAJORCRITICAL with the value of 1 and 2, which is what the above does.
This will put the value of MAJORCRITICAL for on each row of $table into another array:
$testthis=$table | Where-Object{($_.MAJORCRITICAL -eq 1) -or ($_.MAJORCRITICAL -eq 2)} | Select -ExpandProperty MAJORCRITICAL
$testthis will contain an array:
1
2
(using your example data above)
This comparison will be TRUE if the table contains property MAJORCRITICAL with values of 1 and 2:
if($testthis -match 1) -and ($testthis -match 2){<1 and 2 exist in the table>}
Inspired by Carlsberg.
Tuesday, May 28, 2013 6:13 PM
is it a value of 1 or count of 1?
and did this not work? if not try -match or -contains
if ($majorC -eq "1"){
}
if ($majorC -eq "1" -and $majorC -eq "2" ){
}
Tuesday, May 28, 2013 6:19 PM
$table is an array objects containing the query results. You would iterate through it like any other array of objects.
$table
shows all the results.
$table | Select BSM_NM
will show just the BSM_NM property of all rows.
$table | ForEach-Object{
If($_.MajorC -eq 1){<code>}
}
Iterate through the data using a ForEach-Object loop.
Inspired by Carlsberg.
Tuesday, May 28, 2013 7:01 PM
so would my code look like this then? Also, just started picking up powershell a couple weeks ago, very new at this...please bear with me
$Query = "SELECT
t1.BSM_NM
,t1.D_DTM AS MAXDATETIME
,CASE
WHEN SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.MAJOR_VOICE_BLOCK) AND SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) < MAX(t2.CRITICAL_VOICE_BLOCK)
OR
SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.MAJOR_VOICE_DROP) AND SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) < MAX(t2.CRITICAL_VOICE_DROP)
OR
SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.MAJOR_VOICE_AXSFAIL) AND SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) < MAX(t2.CRITICAL_VOICE_AXSFAIL)
THEN 1
WHEN SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.CRITICAL_VOICE_BLOCK)
OR
SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.CRITICAL_VOICE_DROP)
OR
SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.CRITICAL_VOICE_AXSFAIL)
THEN 2
ELSE 0
END MAJORCRITICAL
FROM DMSN.DS3R_FH_1XRTT_BTS_LVL_KPI t1
INNER JOIN
ZDMSN.DS3R_1XRTT_TRIGGERS_THRESHOLD t2
ON
t1.BSM_NM = t2.BSC_NM
WHERE t1.BSM_NM = 'ARL1' and t1.D_DTM = (SELECT MAX(D_DTM) FROM DS3R_FH_1XRTT_BTS_LVL_KPI WHERE BSM_NM = 'ARL1')
GROUP BY
t1.BSM_NM, t1.D_DTM"
$data_set = new-object system.data.dataset
$adapter = new-object system.data.oracleclient.oracledataadapter ($Query, $Connection)
[void] $adapter.Fill($data_set)
$table = new-object system.data.datatable
$table | Select MAJORCRITICAL
if ($majorC -eq "1" ){CODE}
if ($majorC -eq "1" -OR $majorC -eq "2"){CODE}
Tuesday, May 28, 2013 7:22 PM
is it a value of 1 or count of 1?
and did this not work? if not try -match or -contains
if ($majorC -eq "1"){
}
if ($majorC -eq "1" -and $majorC -eq "2" ){
}
Hey ImMax, the 1 is a value in the MAJORCRITICAL columns (can have values of 1,2, or 0)
Tuesday, May 28, 2013 7:38 PM
$table is an array objects containing the query results. You would iterate through it like any other array of objects.
$table
shows all the results.
$table | Select BSM_NM
will show just the BSM_NM property of all rows.
$table | ForEach-Object{
If($_.MajorC -eq 1){<code>}
}
Iterate through the data using a ForEach-Object loop.
Inspired by Carlsberg.
I don't know if I necessary need to loop through it. What I want to do is have it look for either a 1 or 2 in the MAJORCRITICAL column. If there is either of those values in any row, then do something.
Tuesday, May 28, 2013 8:07 PM
This will return only the rows where MajorC property has a numeric value of 1 or 2:
$table | Where-Object{($_.MajorC -eq 1) -or ($_.MajorC -eq 2)} | ForEach-Object{<do something>}
The comparison (-eq) could be affected by what type of value MajorC is... [string],[int] etc.
To show what types of data the properties are, use this:
$table | Get-Member
It will show what types of data the properties of the object contain.
Actually, what type of data does the above command show for MajorC ?
The query could be altered to return only the results required.
Inspired by Carlsberg.
Tuesday, May 28, 2013 8:14 PM
ok, so I wrote it like this now:
$Query = "SELECT
t1.BSM_NM
,t1.D_DTM AS MAXDATETIME
,t1.TECHNOLOGY
,t1.VOICEDATA
,CASE
WHEN t1.VOICEDATA = 'VOICE'
AND
(
t1.CUST_BLK_CNT/t1.ATT_CNT >= t2.MAJOR_VOICE_BLOCK AND t1.CUST_BLK_CNT/t1.ATT_CNT < t2.CRITICAL_VOICE_BLOCK
OR
t1.DRP_CALL_CNT/t1.ATT_CNT >= t2.MAJOR_VOICE_DROP AND t1.DRP_CALL_CNT/t1.ATT_CNT < t2.CRITICAL_VOICE_DROP
OR
t1.AXS_F_CNT/t1.ATT_CNT >= t2.MAJOR_VOICE_AXSFAIL AND t1.AXS_F_CNT/t1.ATT_CNT < t2.CRITICAL_VOICE_AXSFAIL
)
THEN 1
WHEN t1.VOICEDATA = 'DATA'
AND
(
t1.CUST_BLK_CNT/t1.ATT_CNT >= t2.MAJOR_VOICE_BLOCK AND t1.CUST_BLK_CNT/t1.ATT_CNT < t2.CRITICAL_DATA_BLOCK
OR
t1.DRP_CALL_CNT/t1.ATT_CNT >= t2.MAJOR_VOICE_DROP AND t1.DRP_CALL_CNT/t1.ATT_CNT < t2.CRITICAL_DATA_DROP
OR
t1.AXS_F_CNT/t1.ATT_CNT >= t2.MAJOR_VOICE_AXSFAIL AND t1.AXS_F_CNT/t1.ATT_CNT < t2.CRITICAL_DATA_AXSFAIL
)
THEN 1
WHEN t1.VOICEDATA = 'VOICE'
AND
(
t1.CUST_BLK_CNT/t1.ATT_CNT >= t2.CRITICAL_VOICE_BLOCK
OR
t1.DRP_CALL_CNT/t1.ATT_CNT >= t2.CRITICAL_VOICE_DROP
OR
t1.AXS_F_CNT/t1.ATT_CNT >= t2.CRITICAL_VOICE_AXSFAIL
)
THEN 2
WHEN t1.VOICEDATA = 'DATA'
AND
(
t1.CUST_BLK_CNT/t1.ATT_CNT >= t2.CRITICAL_DATA_BLOCK
OR
t1.DRP_CALL_CNT/t1.ATT_CNT >= t2.CRITICAL_DATA_DROP
OR
t1.AXS_F_CNT/t1.ATT_CNT >= t2.CRITICAL_DATA_AXSFAIL
)
THEN 2
ELSE 0
END MAJORCRITICAL
FROM DS3R_FH_ALL_TRIGGER_VIEW t1
INNER JOIN
ZDMSN.DS3R_1XRTT_TRIGGERS_THRESHOLD t2
ON
t1.BSM_NM = t2.BSC_NM
AND
t1.TECHNOLOGY = t2.TECHNOLOGY
WHERE t1.BSM_NM = 'ARL1' and t1.D_DTM = (SELECT MAX(D_DTM) FROM DS3R_FH_1XRTT_BTS_LVL_KPI WHERE BSM_NM = 'ARL1')"
$data_set = new-object system.data.dataset
$adapter = new-object system.data.oracleclient.oracledataadapter ($Query, $Connection)
[void] $adapter.Fill($data_set)
$table = new-object system.data.datatable
$table = $data_set.Tables[0]
$bsmNM = $data_set.Tables[0].Rows[0].BSM_NM
$maxDT = $data_set.Tables[0].Rows[0].MAXDATETIME
$majorC = $data_set.Tables[0].Rows[0].MAJORCRITICAL
$table | Select MAJORCRITICAL
{
if ($majorC -match "*2*" -and $majorC -match "*1*")
if ($majorC -match "*1*" )
if ($majorC -match "*2*")
but it didn't seem to run any of the if statements even though I had one row with a value of 1. It should have ran the second if statement.
heres what it looked like when I ran it.
Tuesday, May 28, 2013 8:30 PM
I appreciate you're new at this, but at least try to read and follow the examples.
Why do you use $MajorC instead of $_.MajorC in your loop?
Why do you use -match "*1*" - did you prove what I posted didn't work?
Why do you use -and instead of -or ?
You need to get the basics working first. One step at a time.
Run the query and look at the output, Enter this command at the prompt:
$table
do the results look correct?
Now try this:
$table | Where-Object{($_.MajorC -eq 1) -or ($_.MajorC -eq 2)}
does it return the correct results?
Inspired by Carlsberg.
Tuesday, May 28, 2013 8:45 PM
I appreciate you're new at this, but at least try to read and follow the examples.
Why do you use $MajorC instead of $_.MajorC in your loop?
Why do you use -match "*1*" - did you prove what I posted didn't work?
Why do you use -and instead of -or ?
You need to get the basics working first. One step at a time.
Run the query and look at the output, Enter this command at the prompt:
$table
do the results look correct?
Now try this:
$table | Where-Object{($_.MajorC -eq 1) -or ($_.MajorC -eq 2)}
does it return the correct results?
Inspired by Carlsberg.
Hey Riffy Riot
So I used $MajorC instead of $_.MajorC cause I thought the latter was a type...whoops...lol. Also, I used the -match cause it was something I saw online, and I was using -and because if there is a 1 AND a 2, that means there was a major and critical that occured. if just a 1, thats a major, and if just a 2, thats a critical.
I also ran the above and got this when I ran just $table which is correct...
when I ran $table | Where-Object{($_.MajorC -eq 1) -or ($_.MajorC -eq 2)}, I didn't get anything back...
Tuesday, May 28, 2013 8:56 PM
OK ,the result for $table looks good... it tells us a little about that the query is actually returning.
$table | Get-Member
will tell us even more about what the query is returning, and help with the next commands.
Replace $_.MajorC with $_.MAJORCRITICAL that's what the property is actually named.
Using the loop, you are looking at 1 row at a time.
Using -and you are comparing one property form each row with two values, similar to: "if 1 equals 1 and 2" # that won't happen.
-or looks like this: "if 1 equals 1 or 2" # there's a possibility that will happen.
Using code that you find online that you don't understand isn't going to help you.
Inspired by Carlsberg.
Wednesday, May 29, 2013 2:04 PM
OK ,the result for $table looks good... it tells us a little about that the query is actually returning.
$table | Get-Member
will tell us even more about what the query is returning, and help with the next commands.
Replace $_.MajorC with $_.MAJORCRITICAL that's what the property is actually named.
Using the loop, you are looking at 1 row at a time.
Using -and you are comparing one property form each row with two values, similar to: "if 1 equals 1 and 2" # that won't happen.
-or looks like this: "if 1 equals 1 or 2" # there's a possibility that will happen.
Using code that you find online that you don't understand isn't going to help you.
Inspired by Carlsberg.
Hey RiffyRiot,
So I got the code to work except for the if statements following your steps...heres what the final code looks like:
$Query = ""
$data_set = new-object system.data.dataset
$adapter = new-object system.data.oracleclient.oracledataadapter ($Query, $Connection)
[void] $adapter.Fill($data_set)
$table = new-object system.data.datatable
$table = $data_set.Tables[0]
$bsmNM = $data_set.Tables[0].Rows[0].BSM_NM
$maxDT = $data_set.Tables[0].Rows[0].MAXDATETIME
$majorC = $data_set.Tables[0].Rows[0].MAJORCRITICAL
$table | Where-Object{($_.MAJORCRITICAL -eq 1) -or ($_.MAJORCRITICAL -eq 2)} | ForEach-Object{
if ($_.MAJORCRITICAL -eq "1"){}
if ($_.MAJORCRITICAL -eq "1" -and $_.MAJORCRITICAL -eq "2"){}
if ($_.MAJORCRITICAL -eq "2")
the if statements are running but all three are running. The results from the query looked like this:
What i was expecting would happen is that if there is a 1 AND a 2 returned, run the 2nd if...if just 1, run the 1st...if just 2, run the 3rd if. Do I just need to rearrange the logic like this for the if statements?
if ($_.MAJORCRITICAL -eq "1" -and $_.MAJORCRITICAL -eq "2")
{
if ($_.MAJORCRITICAL -eq "1"){}
if ($_.MAJORCRITICAL -eq "2"){}
}
else{}
or like this?
if ($_.MAJORCRITICAL -eq "1" -and $_.MAJORCRITICAL -eq "2"){}
elseif ($_.MAJORCRITICAL -eq "1"){}
elseif ($_.MAJORCRITICAL -eq "2"){}