Powershell Completing, but not actually working - VisualCron - Forum

Community forum

Maverick494
2018-05-14T13:45:25Z
I have a few powershell scripts that were working fine using vcron until the 6th of May. After that date they continued to run, log as completing successfully, but the end state of the csv file they are supposed to output is blank when run with vcron. When I run them manually they complete properly and the files are not blank.

What should I investigate with visual cron to figure out why these are not completing properly?

- Credentials are saved and tested good.
- Scripts run manually and are working
Maverick494
2018-05-15T15:19:14Z
No ideas at all of what to look at?
Support
2018-05-15T18:08:39Z
I think you should test with a simple script first like:

write-output "Hello world"

If that works please let us know which line fails in yoru script.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Maverick494
2018-05-15T19:21:41Z
Originally Posted by: Support 

I think you should test with a simple script first like:

write-output "Hello world"

If that works please let us know which line fails in yoru script.



I have other scripts that are still working. There is no single line that is failing. The execution completes indicating it completed successfully, but it didn't. The output csv file from these scripts are all blank. The output csv files from the other scripts are working fine. When I run each of the scripts that is outputting a blank file when run with VCron manually with powershell ISE they are outputting the csv as expected.

Example script that works and completes with expected output:

$guid = [guid]::NewGuid()

$json = [ordered]@{
schema = "com.domain.maxis.job.export:2014-05-01"
type = "documentSearch"
authorizations = @([ordered] @{
id = "$guid"
effect = "ALLOW"
action = "CAN_READ"
principal = "kerberos:jppulley@DOMAIN.COM"
resource = "this"
})
parameters = [ordered] @{
search = [ordered] @{
schemaType = "com.domain.maxis.issue"
q = "containingFolder:49f2f05f-611e-44d9-a5aa-e049b2ab85c6"
sort = "lastUpdatedConversationDate desc"
}
exportData = [ordered] @{
type = "table"
columns = @(
@{columnId = "assignedfolderid"},
@{columnId = "issueid"},
@{columnId = "title"},
@{columnId = "shortid"},
@{columnId ="status"},
@{columnId = "nextstepaction"},
@{columnId = "assignedfolderlabel"},
@{columnId = "labels"},
@{columnId = "requesteridentity"},
@{columnId = "createdate"},
@{columnId = "lastupdateddate"},
@{columnId = "lastresolveddate"},
@{columnId = "assigneeidentity"},
@{columnId = "prioritylevel"},
@{columnId = "rank"},
@{columnId = "planningestimate"},
@{columnId = "estimatedstartdate"},
@{columnId = "estimatedcompletiondate"},
@{columnId = "actualstartdate"},
@{columnId = "actualcompletiondate"},
@{columnId = "needbydate"},
@{columnId = "issueurl"},
@{columnId = "submitteridentity"},
@{columnId = "resolvedbyidentity"},
[ordered] @{path = "/customFields/string/targeted_release_date_if_salesforce/value"
outputFieldName="targeted_release_date_of_salesforce"},
[ordered] @{path = "/customFields/string/request_type/value"
outputFieldName="SDS_SIM_Request_Type"},
[ordered] @{path = "/customFields/string/progress_status/value"
outputFieldName="SDS_SIM_Progress_Status"},
[ordered] @{path = "/customFields/string/risk_color_status/value"
outputFieldName="SDS_SIM_Request_Color_Status"},
[ordered] @{path = "/customFields/string/path_back_to_green/value"
outputFieldName="SDS_SIM_Path_Back_to_Green"})
}
exportFiles = @([ordered] @{
id="csv"
format="csv"}
)
exportContent = @{
type="issues"
}
}
} | ConvertTo-Json -Depth 6


# Get a SIM Session Cookie
Invoke-WebRequest -UseDefaultCredentials -UseBasicParsing -Uri https://maxis-service-prod.domain.com/users/whoami  -SessionVariable sim

#Send Post to Create Job
$response = Invoke-RestMethod -UseDefaultCredentials -UseBasicParsing -WebSession $sim -Uri https://maxis-service-prod.domain.com/jobs  -Method Post -Body $json -ContentType "application/json"

#store the document ID for job retrieval
$documentID = $response.id

#Loop until the job is completed
do {

$checkstatus = Invoke-RestMethod -UseDefaultCredentials -UseBasicParsing -WebSession $sim -Uri https://maxis-service-prod.domain.com/jobs/ $documentID
Start-Sleep -S 10
if ($checkstatus.status -eq "Failed"){
break
}

} until ($checkstatus.status -eq "Complete")

#get attachments json section
$attachment = $checkstatus.attachments

#get ID for the attachment
$attachmentID = $attachment.id

#set Document Retrieval URL
$docDownload = 'https://maxis-service-prod.domain.com/jobs/'+$documentID+'/attachments/'+$attachmentID

#set the save file location
$savefileTarget = '\\domain\dept\dcgsi\Extracts\SIM_SDS_Issues.csv'

if (Test-Path $savefileTarget) {
Remove-Item $savefileTarget
}

#Get the csv file
if ($checkstatus.status -eq "Complete") {
Invoke-WebRequest -UseDefaultCredentials -UseBasicParsing -WebSession $sim -Uri $docDownload -OutFile $savefileTarget
}else{
Set-Content -Path "\\domain\dept\dcgsi\Extracts\SIM_SDS_errors.txt" -Value $checkstatus.Content -Force
}


Example of script that says it completes successfully (and does work when manually run) but outputs a blank file:

$uri = 'https://playbook2somain.com/data/folder/export/24125'
$of = '\\domain\dept\DCGSI\Extracts\DCCD Projects.xlsx'
$sf = '\\domain\dept\DCGSI\Extracts\DCCD Modified.csv'

function Get-Data {
wget $uri -outfile $of -UseDefaultCredentials
}

function CSV-Creation {
if (Test-Path $sf) {
Remove-Item $sf
}

$excel = New-Object -ComObject "Excel.Application"
$Workbook = $excel.Workbooks.Open($of)
$page = 'Project Summary'
$ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
Start-Sleep 5
$cells=$ws.Cells
$range = $ws.UsedRange
$rows = $range.Rows.Count
$dataDate = (Get-Date).tostring("yyyy-MM-dd")
$cols = $range.Columns.Count
$newCol = $cols + 1
$ws.Cells(1, $newCol).Value = "Snapshot"

for($i = 2; $i -ne $rows+1; $i++){
$ws.Cells($i, $newCol).Value="$dataDate"
$ws.Cells($i, $newCol).NumberFormat="yyyy-mm-dd"
}

$excel.visible = $true

$excel.DisplayAlerts = $false

$excel.ActiveWorkbook.SaveAs('\\domain\dept\DCGSI\Extracts\DCCD Modified.csv',6)

Close-Excel
}

function Close-Excel {

Start-Sleep 1

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($ws)|out-null
$ws=$null

Start-Sleep 1

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Workbook)|out-null
$Workbook=$null

Start-Sleep 1

$excel.Quit()

Start-Sleep 1

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)|out-null
$excel=$null

[GC]::Collect()
[GC]::WaitForPendingFinalizers()
}

function Clean-Dates {

(Get-Content $sf) | Foreach-Object {$_ -replace '(\d{1,2})/(\d{1,2})/(\d{4})', '$3-$1-$2' } | Set-Content $sf

}

function Clean-Downloads {
Remove-Item $of
}

Get-Data
CSV-Creation
Clean-Dates
Clean-Downloads


Keep in mind that prior to 8.3.4 both of these scripts worked. Now the second one is outputting a blank file when executed through visual cron, but not when executed via powershell ISE or powershell.
Maverick494
2018-05-16T00:20:47Z
Nothing? I guess I have to investigate vcron alternatives until I can get some more assistance.
Support
2018-05-16T06:24:19Z
If you have other scripts working it means that there is something specific in this script. It would be easy to log to each file different values to see what is working in the script or not. You can just write any return values to file and it will be clear which code part does not work. Without knowing that it could be anything and hard to find the actual reason.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Maverick494
2018-05-16T12:54:00Z
Originally Posted by: Support 

If you have other scripts working it means that there is something specific in this script. It would be easy to log to each file different values to see what is working in the script or not. You can just write any return values to file and it will be clear which code part does not work. Without knowing that it could be anything and hard to find the actual reason.



Apparently you don't understand that the code works if I don't use visual cron to run that code. When I open the code in powershell and run it; it works fine. Up until visual cron 8.3.4, visual cron ran the same exact code without a problem. what changed in 8.3.4 that might be causing the second code piece to fail now when it didn't in 8.3.3

Edit:
I have what I think is the issue. I have supplied credentials for the task to use; it is not using those credentials. It is using NT AUTHORITY\SYSTEM unlike the other scripts that ARE using the credentials I have supplied.
Support
2018-05-16T15:22:09Z
Originally Posted by: Maverick494 

Originally Posted by: Support 

If you have other scripts working it means that there is something specific in this script. It would be easy to log to each file different values to see what is working in the script or not. You can just write any return values to file and it will be clear which code part does not work. Without knowing that it could be anything and hard to find the actual reason.



Apparently you don't understand that the code works if I don't use visual cron to run that code. When I open the code in powershell and run it; it works fine. Up until visual cron 8.3.4, visual cron ran the same exact code without a problem. what changed in 8.3.4 that might be causing the second code piece to fail now when it didn't in 8.3.3

Edit:
I have what I think is the issue. I have supplied credentials for the task to use; it is not using those credentials. It is using NT AUTHORITY\SYSTEM unlike the other scripts that ARE using the credentials I have supplied.



I do understand that the code works outside of VisualCron. The reason I am asking you to test various things is to help us understand what is going on - on what level it fails.

Please check so you use Load profile in your Credential.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Maverick494
2018-05-16T15:44:57Z
Originally Posted by: Support 

Originally Posted by: Maverick494 

Originally Posted by: Support 

If you have other scripts working it means that there is something specific in this script. It would be easy to log to each file different values to see what is working in the script or not. You can just write any return values to file and it will be clear which code part does not work. Without knowing that it could be anything and hard to find the actual reason.



Apparently you don't understand that the code works if I don't use visual cron to run that code. When I open the code in powershell and run it; it works fine. Up until visual cron 8.3.4, visual cron ran the same exact code without a problem. what changed in 8.3.4 that might be causing the second code piece to fail now when it didn't in 8.3.3

Edit:
I have what I think is the issue. I have supplied credentials for the task to use; it is not using those credentials. It is using NT AUTHORITY\SYSTEM unlike the other scripts that ARE using the credentials I have supplied.



I do understand that the code works outside of VisualCron. The reason I am asking you to test various things is to help us understand what is going on - on what level it fails.

Please check so you use Load profile in your Credential.


Load profile makes it error out and not work at all. Local vs. Not Local seems to have no effect.

The creds aren't the issue, sadly. The only difference between these two scripts I am testing is that one has to load excel to do a change from XLSX to CSV. The download portion of the script works fine as I see the XLSX file downloaded and it shows 16KB of data. Then the portion of the script that opens that file and does the excel work and is trying to save it as a CSV seems to work, but results in a blank file when used with visual cron.

Edit:

I was able to find a file I could re-write to test only pieces at a time; VCron is choking on:

(Get-Content $sf) | Foreach-Object {$_ -replace '(\d{1,2})/(\d{1,2})/(\d{4})', '$3-$1-$2' } | Set-Content $sf

the CSV up until this point is fine. This section of code works fine in powershell; it causes VCron to create a blank file.
Support
2018-05-16T17:14:08Z
What do you get if you use the Test button in the Edit Credential window?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Maverick494
2018-05-16T17:17:54Z
vcron creds.PNG
Originally Posted by: Support 

What do you get if you use the Test button in the Edit Credential window?



vcron creds.PNG
Maverick494
2018-05-16T17:24:07Z
Originally Posted by: Support 

What do you get if you use the Test button in the Edit Credential window?



Can you elucidate why you are chasing creds when I have isolated a single line in the code that VCron is not executing properly? Is it because you think that it gets all the rest of the way through the script using those creds to access the network share and then suddenly can't?
Support
2018-05-16T17:25:10Z
I am quite sure you need to check Load profile checkbox.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Maverick494
2018-05-16T19:49:38Z
Originally Posted by: Support 

I am quite sure you need to check Load profile checkbox.



and I am quite sure that errors out without running when I do that.

12:48:50: Server->Execute path: D:\VisualCron\\PowerShell\TaskPowerShell.exe
12:48:50: Server->Executing Task process
12:48:50: Server->Executing Task process exited with exit code: -1073741502
Support
2018-05-16T19:51:50Z
Originally Posted by: Maverick494 

Originally Posted by: Support 

I am quite sure you need to check Load profile checkbox.



and I am quite sure that errors out without running when I do that.

12:48:50: Server->Execute path: D:\VisualCron\\PowerShell\TaskPowerShell.exe
12:48:50: Server->Executing Task process
12:48:50: Server->Executing Task process exited with exit code: -1073741502



Ok, we are closer to the problem now. Please try 8.3.5 - you can downgrade if it does not work: https://www.visualcron.c....aspx?g=posts&t=7901 

You can also go to Server settings->Disable UAC and reboot the machine if 8.3.5 does not work.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Maverick494
2018-05-16T20:11:57Z
Originally Posted by: Support 

Originally Posted by: Maverick494 

Originally Posted by: Support 

I am quite sure you need to check Load profile checkbox.



and I am quite sure that errors out without running when I do that.

12:48:50: Server->Execute path: D:\VisualCron\\PowerShell\TaskPowerShell.exe
12:48:50: Server->Executing Task process
12:48:50: Server->Executing Task process exited with exit code: -1073741502



Ok, we are closer to the problem now. Please try 8.3.5 - you can downgrade if it does not work: https://www.visualcron.c....aspx?g=posts&t=7901 

You can also go to Server settings->Disable UAC and reboot the machine if 8.3.5 does not work.



13:10:52: Server->Execute path: D:\VisualCron\\PowerShell\TaskPowerShell.exe
13:10:52: Server->Executing Task process
13:10:52: Server->Executing Task process exited with exit code: -1073741502

local profile load in 8.3.5 AND UAC off.
Support
2018-05-16T20:35:58Z
Can you try creating a local administrator on the machine instead of using your AD user and use that as Credential. Maybe we can rule out any permission issues then.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Maverick494
2018-05-16T20:47:47Z
Originally Posted by: Support 

Can you try creating a local administrator on the machine instead of using your AD user and use that as Credential. Maybe we can rule out any permission issues then.



No, I cannot. Account creation is not something I have access to this machine to do. The only potential option I have is to move visual cron off this machine and to another machine, but that will take me some time.
Support
2018-05-16T21:04:24Z
Originally Posted by: Maverick494 

Originally Posted by: Support 

Can you try creating a local administrator on the machine instead of using your AD user and use that as Credential. Maybe we can rule out any permission issues then.



No, I cannot. Account creation is not something I have access to this machine to do. The only potential option I have is to move visual cron off this machine and to another machine, but that will take me some time.



We want to make sure that it is not anything connected with the AD user. Creating a local admin would be a good test for this. Another suggestion is that you edit the Credential and change to option CreateProcessAsUserW. What is clear is that you need to have Load profile checked. Otherwise it will not start as the user you want and the executable will not be able to autenticate etc.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Maverick494
2018-05-16T21:22:27Z
Originally Posted by: Support 

Can you try creating a local administrator on the machine instead of using your AD user and use that as Credential. Maybe we can rule out any permission issues then.



This may not even be isolated to visual cron; I am going to have to probably go ask for some ideas on this elsewhere; maybe some alternate ways to do what I am doing in the script. I just tried to use the built in windows task scheduler just to see what it would do. It also outputs a blank file.
Scroll to Top