Quote from: actcognosuser on Yesterday at 11:50:49 AMHi All,

Date prompts in a pas through SQL is not working.
Cognos version is 10.2 and database is Db2.

FIlter in the pass through SQL
 
date(OrderDate ) between date(TIMESTAMPFORMAT (?Start_Date? ,'Mon DD, YYYY')) and date(TIMESTAMPFORMAT(?End_Date?,'Mon DD, YYYY'))

When hardcoded values of Oct 20, 2024 is substituted in this filter it works.

Throwing a datasource adaptor error when prompts are used
XQE-DAT-0001 Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

Also tried using these formats, nothing works.
AND date(OrderDate )=date(TO_DATE(?P_StartDate? ,'YYYY-MM-DD'))


Using order date between ?P_StartDate? and ?P_EndDate? as detail filter in the query is working. But this is not an option Since it is a union query and results of first part filtered by date needs to be applied to second query.

Hi,

Have you tried using prompt macros in your SQL rather than Cognos parameters? For example, replacing ?Start_Date? with #prompt('Start_Date','Date')#

Is there a reason you are coding this as SQL rather than adding detail filters to each query that feeds into your Union object? If you must use SQL, why are you using pass-thru SQL rather than Cognos SQL (or Native SQL)?

Cheers!

MF.

Hi All,

Date prompts in a pas through SQL is not working.
Cognos version is 10.2 and database is Db2.

FIlter in the pass through SQL
 
date(OrderDate ) between date(TIMESTAMPFORMAT (?Start_Date? ,'Mon DD, YYYY')) and date(TIMESTAMPFORMAT(?End_Date?,'Mon DD, YYYY'))

When hardcoded values of Oct 20, 2024 is substituted in this filter it works.

Throwing a datasource adaptor error when prompts are used
XQE-DAT-0001 Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

Also tried using these formats, nothing works.
AND date(OrderDate )=date(TO_DATE(?P_StartDate? ,'YYYY-MM-DD'))


Using order date between ?P_StartDate? and ?P_EndDate? as detail filter in the query is working. But this is not an option Since it is a union query and results of first part filtered by date needs to be applied to second query.

@wyconian:  more details please

Cognos version (and kit_version from cmplst.txt)
OS name and version
single-server or multi-server install
What is common and special about the reports that fail?
  Interactive or compatible viewer?
  Styles version?
  Do they contain images?
  Are you running the Image service?
  Scheduled, run as, or run interactively?

We're having the same issue after upgrading to 12.0.3. At first we thought it was related to this support issue but after looking at logs and network tracing it seems to be something else. Still investigating.

https://www.ibm.com/support/pages/connection-was-reset-while-generating-pdf-output-checkpoint-vpn

Thanks for your response @dougp - greatly appreciated - our script is (unsurprisingly) quite similar.

We have a sleep of 10 seconds in our script after the stop service has finished because we have found that the powershell script is just too quick for it's own good and Windows sometimes lies to us (shocker).

Also we do this to remove any orphaned processes after the processes are "stopped" as we have often found some lingering around, usually java processes, but this definitely gets rid of them:

Get-Process | Where-Object {$_.Path -like '*cognos*'} | stop-process -Force

We don't do any log file monitoring in the script so I'm going to look into it, it seems an interesting approach.

And thanks again.

Sounds like nonsense to me.  But you do need to wait until the service has stopped before starting it again.

Also running on Windows Server, here's something like what I do.  This stops the service, waits to be sure the service has stopped, displays the end of cognosserver.log (so -- details to see any associated errors), (optionally, so something here), starts the service, waits to be sure the service has started, displays the end of cognosserver.log.  The log tail can be dumped to a different log file (instead of printed to the screen) so it's headless and you need only look at the shutdown and startup stuff, rather than needing to sift through thousands of lines of user activity if something went wrong.

This is a basic concept I just grabbed from my notes.  I have several scripts that use similar code to perform actual tasks.  And I don't remember if this was for PowerShell 5 or 7.  (What I'm saying here is that I never run exactly this code.  It may not work.  And since there's no chance your environment exactly matches mine, your mileage may vary.  Use at your own risk.  No warranty expressed or implied.)

# PowerShell

## Stop service

    $servername = "supersecretcomputername"
    $servicename = "IBM Cognos"
    $coglog = "\\$servername\c`$\Program Files\ibm\cognos\analytics\logs\cognosserver.log"
   
    $linesbefore = (Get-Content $coglog | Measure-Object –Line).Lines
    $cogsvc = Get-Service -ComputerName $servername -Name $servicename
    $cogsvc.Stop()
    "Waiting for the $servicename service on $servername to stop."
    $cogsvc.WaitForStatus("Stopped")
    "The $servicename service on $servername is stopped."
    $linesafter = (Get-Content $coglog | Measure-Object –Line).Lines
    $len = $linesafter - $linesbefore
    Get-Content -Path $coglog -Tail $len

## Do something here


## Start service

    # $servername = "supersecretcomputername"
    # $servicename = "IBM Cognos"
    # $coglog = "\\$servername\c`$\Program Files\ibm\cognos\analytics\logs\cognosserver.log"
   
    $linesbefore = (Get-Content $coglog | Measure-Object –Line).Lines
    # $cogsvc = Get-Service -ComputerName $servername -Name $servicename
    $cogsvc.Start()
    "Waiting for the $servicename service on $servername to start."
    $cogsvc.WaitForStatus("Running")
    "The $servicename service on $servername is running."
    $linesafter = (Get-Content $coglog | Measure-Object –Line).Lines
    $len = $linesafter - $linesbefore
    Get-Content -Path $coglog -Tail $len

Quoteboth the Dispatcher URI for gateway (p2pd/servlet/dispatch/ext) and controller URI for gateway (ibmcognos/controllerServer) are using HTTP.

What about the other URIs?


So what you're saying is that the pen testers have determined that anyone on the planet can get into the EC2 instances behind the AWS ALB.  I'm just a Cognos guru, so I don't know what all of that means, but it sounds bad.

Hi dougp,

The load balancer is an AWS ALB. We have a load balancer in front of our cognos gateway servers. The cognos gateway servers are running on EC2 MS Windows Server 2016 instances as provided by AWS. When our main application requests a cognos report, the app communicates with Cognos strictly through the ALB over https. This is not a problem, even from our overly scrupulous pentesters.

If I run cogconfig application, under Local Configuration\Environment, both the Dispatcher URI for gateway (p2pd/servlet/dispatch/ext) and controller URI for gateway (ibmcognos/controllerServer) are using HTTP.

All that to say that this scenario is a hypothetical dreamed up by our pentesters and they are insistent that we "fix" this. We terminate HTTPS at load balancers, per AWS recommendations. What is happening is the pentesters are able to get onto the EC2 instances behind the AWS ALB, and when they request http://localhost/bi they are served the Cognos Analytics logon page over HTTP, and claim that the credentials could be sniffed out over HTTP. This would never happen of course because the only people who can login to the server with a powerful set of credentials would definitely not waste time going into an EC2 instance to do so; we'd just use the Load balancer URL!

 

I help administer several farms of Cognos servers on Windows servers and automation through scripts is key to managing them effectively and efficiently.

IBM have recently told us that we shouldn't be using stop-service and start-service commands to stop and start the services and that they "recommend stopping and starting services using Cognos Configuration buttons, and expand the details to see any associated errors."  We are also cleaning up orphaned processes after shutting Cognos down.

We already have both script and log file monitoring - so unless they are outputting errors to the screen only (and I wouldn't put it past IBM to do so) I can't see the benefit unless it is a hidden one. 

So the big questions are:
Is IBM talking out of their derriere and we can ignore them?
Is it going to be doing extra bits and pieces we don't know about if we use the configuration buttons to stop and start Cognos?
Do others here use scripts for stopping and starting their Cognos services on Windows and do you have issues with restarting?

Thanks in advance.

I think we need more specifics about your environment.  What is receiving the request?  Is it a Cognos gateway, or is something else (like IIS) in front of it?  SSO?  What happens when you request http://cognosserver:9300?  Are any of your URIs in Cognos Configuration still using http?

When you say "load balancer", that's not load balancing in Cognos Analytics, right?  That's something like NetScaler?

I use SSO through IIS, have configured IIS to use https, have a rewrite rule to change http to https, have installed SSL certificates in Cognos Analytics, and changed all of the URIs to https in Cognos Configuration.  I'm not a pen tester, but I don't see any way someone using a web browser can get to anything in my environment using http.