Oracle SQL DB with Powershell

%3CLINGO-SUB%20id%3D%22lingo-sub-283234%22%20slang%3D%22en-US%22%3EOracle%20SQL%20DB%20with%20Powershell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-283234%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%20I%20made%20this%20script%20for%20a%20client%20of%20mine%2C%20But%20I%20am%20now%20trying%20to%20make%20it%20more%20intelligent%20by%20adding%20conditions%2C%20ie.%20If%20statements%2FSwitch%20But%20I%20am%20hitting%20that%20%22System.Object%5B%5D%20cannot%20be%20Int32%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EScript%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%24webhookID%20%3D%20%24(curl%20-s%20%3CA%20href%3D%22https%3A%2F%2Fdiscordapp.com%2Fapi%2Fwebhooks%2FbotID%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdiscordapp.com%2Fapi%2Fwebhooks%2FbotID%3C%2FA%3E%20jq%20-r%20.id)%3CBR%20%2F%3E%24webhookToken%20%3D%20%24(curl%20-s%20%3CA%20href%3D%22https%3A%2F%2Fdiscordapp.com%2Fapi%2Fwebhooks%2FbotID%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdiscordapp.com%2Fapi%2Fwebhooks%2FbotID%3C%2FA%3E%20%7C%20jq%20-r%20.token)%3CBR%20%2F%3E%24sqlOutput%20%3D%20'%40query.sql'%20%7C%20sqlplus%20-silent%20username%2Fpassword%40host%20as%20sysdba%20%7Cout-string%20-stream%3CBR%20%2F%3E%24ServiceString%20%3D%20(%22The%20DataBase%20has%20only%22%20%2B%20%24sqlOutput%20%2B%20%22%25%2C%20free%20space%20as%20of%20and%20it%20is%20considered%20%22%20%2B%20%22%20on%20%22%20%2B%20%24(Get-Date%20-format%20g))%3CBR%20%2F%3Ecurl%20-s%20--request%20POST%20--url%20%3CA%20href%3D%22https%3A%2F%2Fdiscordapp.com%2Fapi%2Fwebhooks%2F%24webhookid%2F%24webhookToken%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdiscordapp.com%2Fapi%2Fwebhooks%2F%24webhookid%2F%24webhookToken%3C%2FA%3E%3CBR%20%2F%3E--header%20'Cache-Control%3A%20no-cache'%20--header%20'content-type%3A%20multipart%2Fform-data%3B%3CBR%20%2F%3Eboundary%3D----WebKitFormBoundary7MA4YWxkTrZu0gW'%20--form%20content%3D%22%24ServiceString%22%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3BConditional%20Element%3A%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3Eswitch%20(%24int)%20%7B%20%7B%24_%20-lt%2010%7D%20%7B%24strStatus%20%3D%20'DANGER'%7D%3C%2FP%3E%3CP%3Edefault%20%7B%24strStatus%20%3D%20'OK'%7D%20%7D%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59194iB0951E402C6FB9C6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image1.PNG%22%20title%3D%22image1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59195iABDD809EB0459645%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22IssuesFaced.PNG%22%20title%3D%22IssuesFaced.PNG%22%20%2F%3E%3C%2FSPAN%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-283234%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289318%22%20slang%3D%22en-US%22%3ERe%3A%20Oracle%20SQL%20DB%20with%20Powershell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289318%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sean%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20use%20the%20%22get-member%22%20and%20its%20shows%20as%20an%20object%2C%20despite%20I%20ended%20up%20making%20a%20working%20solution%20by%20running%20a%20%7Bif%7D%20that%20compare%20string%20as%20string%20with%20a%20predefined%20string%2C%20given%20that%20the%20output%20was%20sent%20to%20a%20webhook%20as%20a%20string.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20though%2C%20I%20did%20tried%20using%20SQL%20end%2C%20but%20its%20does%20not%20do%20what%20I%20expected.%20As%20a%20result%20%24string%20-match%20does%20the%20job.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289304%22%20slang%3D%22en-US%22%3ERe%3A%20Oracle%20SQL%20DB%20with%20Powershell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289304%22%20slang%3D%22en-US%22%3E%3CP%3EOne%20of%20the%20things%20is%20to%20identify%20which%20lines%20are%20producing%20the%20Errors.%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20pop%20this%20in%20PowerShell%20ISE%20*or*%20Vscode%20and%20run%20an%20individual%20trace%20on%20each%20line.%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20other%20part%20is%20to%20run%20each%20individual%20line%20in%20PowerShell%20(Good%20only%20Copy%2FPaste%20will%20work)%20and%20show%20which%20line%20is%20producing%20the%20%22System.Object%22%20output.%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20you%20have%20that%20you%20can%20try%20piping%20that%20line%20into%20Get-Member%20to%20see%20the%20Object%20type%20and%20properties.%20%26nbsp%3B%20Not%20knowing%20the%20Object%20I%20can't%20comment%20yet%20on%20how%20to%20pull%20the%20pieces%20you%20need%20out.%26nbsp%3B%20But%20if%20we%20can%20fix%20the%20first%20problem%2C%20the%20rest%20could%20be%20easier%20to%20solve%20or%20might%20even%20just%20clear%20up%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20me%20know%20how%20I%20can%20help!%3C%2FP%3E%0A%3CP%3ESean%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi All, I made this script for a client of mine, But I am now trying to make it more intelligent by adding conditions, ie. If statements/Switch But I am hitting that "System.Object[] cannot be Int32

 

Script

$webhookID = $(curl -s https://discordapp.com/api/webhooks/botID jq -r .id)
$webhookToken = $(curl -s https://discordapp.com/api/webhooks/botID | jq -r .token)
$sqlOutput = '@query.sql' | sqlplus -silent username/password@host as sysdba |out-string -stream
$ServiceString = ("The DataBase has only" + $sqlOutput + "%, free space as of and it is considered " + " on " + $(Get-Date -format g))
curl -s --request POST --url https://discordapp.com/api/webhooks/$webhookid/$webhookToken
--header 'Cache-Control: no-cache' --header 'content-type: multipart/form-data;
boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW' --form content="$ServiceString"

 Conditional Element:

switch ($int) { {$_ -lt 10} {$strStatus = 'DANGER'}

default {$strStatus = 'OK'} }

image1.PNGIssuesFaced.PNGThanks

 

2 Replies
Highlighted

One of the things is to identify which lines are producing the Errors.  

You could pop this in PowerShell ISE *or* Vscode and run an individual trace on each line.  

 

The other part is to run each individual line in PowerShell (Good only Copy/Paste will work) and show which line is producing the "System.Object" output.   

 

Once you have that you can try piping that line into Get-Member to see the Object type and properties.   Not knowing the Object I can't comment yet on how to pull the pieces you need out.  But if we can fix the first problem, the rest could be easier to solve or might even just clear up

 

Let me know how I can help!

Sean

Highlighted

Hi Sean,

 

I did use the "get-member" and its shows as an object, despite I ended up making a working solution by running a {if} that compare string as string with a predefined string, given that the output was sent to a webhook as a string.

 

Thanks though, I did tried using SQL end, but its does not do what I expected. As a result $string -match does the job.

 

Thanks