Thursday, May 28, 2009

Zombie Crystal Reports, part 2

See part 1.

I am hunting for long-running Crystal Reports on a PeopleSoft report server. Obviously there are a number of ways to see these, e.g., the PeopleSoft process monitor or Oracle data dictionary v$ views. I need a way to see these at the Windows process level, so I can kill them.

The PowerShell way to get process information is get-process. How did I learn this? Well, I googled it. That's a limitation of the command line: it's not all that discoverable. In contrast to a GUI, it's often hard to just poke around the tool itself to find stuff out. This page at Microsoft shows a number of things you can do with get-process.

Aside: get-process is a cmdlet (pronounced commandlet), which are the basic building-blocks in PowerShell.

By default, it shows all processes, and eight columns of attributes, none of which has anything to do with start time:

PS C:\temp\ps1> get-process

Handles NPM(K) PM(K)  WS(K) VM(M)  CPU(s)    Id ProcessName
------- ------ -----  ----- -----  ------    -- -----------
88       3     2124    4300    39    5.39   308 Avconsol
86       3     2248    4096    44   25.66  1280 Avsynmgr
89       3     1916    3552   421    1.58  3468 bash
65       4    19216   18972    74    4.23   200 chrome
713     15    42024   56864   138  481.77  1372 chrome
61       3     8948    2448    60    0.80  1700 chrome
61       5    17168    3148    73    4.50  1916 chrome
61       3    11548    4684    65   10.64  2056 chrome
62       3    54004   15736   121   13.83  2228 chrome

Yes, I have a lot of Chrome tabs open. You don't know the half of it...

To find out what other attributes get-process can report, pipe it to cmdlet get-member:

PS C:\temp\ps1> get-process | get-member

TypeName: System.Diagnostics.Process

Name          MemberType     Definition
----          ----------     ----------
Handles       AliasProperty  Handles = Handlecount
Name          AliasProperty  Name = ProcessName
NPM           AliasProperty  NPM = NonpagedSystemMemorySize
PM            AliasProperty  PM = PagedMemorySize
VM            AliasProperty  VM = VirtualMemorySize
WS            AliasProperty  WS = WorkingSet
add_Disposed  Method         System.Void add_Disposed(EventHandler value)

It goes on and on and on from there, eventually showing us this:

StartTime  Property  System.DateTime StartTime {get;}

Get used to "get-member." I know I'll be using it all the time.

To see that property, pipe it to the cmdlet Select-Object. We'll also send it a process name with a wildcard to just see PeopleSoft-related processes:

PS C:\temp\ps1> get-process ps* | select-object Id, ProcessName, StartTime

Id   ProcessName   StartTime
--   -----------   ---------
3484 psadmin       3/26/2009 8:48:36 AM
2024 psaesrv       5/27/2009 5:41:20 PM
2328 psaesrv       5/26/2009 5:58:13 PM
3116 psaesrv       5/19/2009 12:23:18 PM
3344 psdstsrv      5/28/2009 3:32:49 AM
3472 psdstsrv      4/2/2009 6:05:18 AM
2344 psmonitorsrv  5/28/2009 6:05:17 AM
3132 psprcsrv      4/2/2009 6:05:19 AM
3388 pssqr         5/28/2009 1:13:50 PM

Here we can see some App Engine servers, various report server processes like distribution servers, and an SQR. No Crystal Reports at all. I was hoping for a zombie so we could kill it. Another day.

So we can report on the start time, but how do we filter on it? Let's say we want to see everthing started in the last two hours:

PS C:\temp\ps1> $now = Get-Date
PS C:\temp\ps1> $then = $now.AddSeconds(-7200)
PS C:\temp\ps1> get-process | where-object {$_.StartTime -gt $then}

Handles  NPM(K) PM(K)  WS(K) VM(M)  CPU(s)    Id ProcessName
-------  ------ -----  ----- -----  ------    -- -----------
31       1     1888     2608    12    0.03  2480 pssqr
79       4     5220    10056    44    0.06  1624 sqlplus
78       5    12816    16828    66    0.20  2576 sqrw

Grab the current time with Get-Date; calculate a time two hours ago (7200 seconds = 2 hours), and, via cmdlet Where-Object, show all processes started after that. Here we see that the report server has recently launched an SQR and a SQL*Plus script.

I could easily have asked for just the Crystal Reports, though since there weren't any running, it's a good thing I didn't.

In part three I may finally get around to killing the zombies.

Wednesday, May 27, 2009

Zombie Crystal Reports, part 1

I've struggled with Crystal Reports and the PeopleSoft report server over the years. Back when we were on PeopleTools 8.42, it took me several weeks to get them running smoothly. I had to carefully limit the number that would run simultaneously on any server, or they'd lock up on us. It took a lot of experimentation to find the right limit that would minimize zombie reports, yet still give us the throughput we required. I ended up adding another server just to handle the load.

Another thing I did, which was an ugly but effective hack, was to run a VBscript that would find and kill any Crystal that had been running for more than an hour. I know nothing about VBScript, but I found a generic process-killing script somewhere, and modified it for my purposes. I scheduled it to run periodically on each report server, and it did what it was supposed to do.

On our last PeopleSoft upgrade, we deployed all new Windows servers, including the report servers. I didn't put those VBScripts on the new servers, and so far on PeopleTools 8.49, I haven't seen many Crystal zombies.

On Tuesday of this week, I found a zombie that had been running over the three-day holiday weekend. I'd received complaints about Crystals sitting in queue for a long time. Part if it was just a tremendous number of report requests, but that one stuck report was apparently taking up a slot in the report server queue, so we were running one less report at a time than we normally do.

The PeopleSoft process request table (PSPRCSRQST) and the Oracle V$SESSION table showed me the thing had been running for a few days, but the Windows task manager showed three pscrrun process. The task manager doesn't show a start time. Which was the zombie?

Well, one clue was that one of them was using a crazy amount of memory -- I believe it was close to 1 GB. I was fairly sure that was the culprit, but to be extra sure, I put that report server in "suspend" mode, which completes any current processes, but doesn't start any new ones. Sure enough, the other two Crystals finished within a minute. I killed the zombie using the Windows task manager, and un-suspended the report server.

Had I scheduled that VBScript, that Crystal would have been long gone. I considered doing just that, but it seemed like a good opportunity to learn a little PowerShell.

Next, in part 2: finding long-running reports.

Tuesday, May 26, 2009

Getting started

Your Windows-based PeopleSoft servers and your Windows desktop machine probably don't have PowerShell installed. As of today, PowerShell is an optional feature on Windows Server 2008, is built in to Windows 7, and is installable for Windows XP, Vista, and Windows Server 2003.

PowerShell version 1 requires .NET framework version 2.0 or higher, which you also may not have. There's a preview of PowerShell version 2, which I have not tried. I believe when Windows 7 ships it will have PowerShell 2.

You can get the .NET framework 3.5 SP1 here, and PowerShell here. The installations are pretty straightforward. I'm lucky enough to have free reign over my servers (well, within reason...), but depending on your workplace policies, you may have to convince your nework or server admin to allow these on to the servers.

From what I gather, PowerShell works quite well over the network, so you may not have to install it everywhere. I'll be exploring that idea on a later date.


Here begins a very narrowly-focused blog describing one PeopleSoft administrator's use of PowerShell.

If you're here, you probably are looking for something quite specific, but I feel obliged to make some general introductory remarks. What is this? Who am I? What do I hope to accomplish here? Just what am I talking about, anyway?

PeopleSoft is a bit of an ambiguous term. It was the name of a company, and also the name of the product suite that they sold. The company is now owned by Oracle Corporation, which continues to sell, support, and develop the products. PeopleSoft made their bones back in the 1990s mostly with their HR/Payroll applications, and over the years added numerous other applications, like financials, distribution, manufacturing, and campus solutions. All of these are built on a proprietary development platform called PeopleTools, which started out as a client-server system. Several years ago PeopleTools migrated to a web-based design, known as PIA -- the PeopleSoft Internet Architecture. This blog will be mainly concerned with administration of the various servers that run a PeopleSoft application.

PowerShell is Microsoft's relatively new command-line shell and scripting language for Windows. It's been around since 2006. Windows has historically been a pointy-clicky sort of system, in stark contrast with Unix, which has a very long history of doing most things from the command line. As time goes on, there's a lot of blurring of that line, but the difference is still there. I work with both Windows and Unix, and I can't count the number of times I've said to myself "I wish Windows would do x" where x is some really simple Unix shell command. There's something to be said for the Windows GUI way of doing things -- you can often just point-and-click your way around and find what you're looking for. But what if you want that same thing to happen 100 times in a row, or every Monday at 4 a.m.? Command line to the rescue!

OK, now, who am I? I've been working with PeopleSoft since 1998. Since day one, I've used both Unix (specifically IBM's AIX) and Windows to run these applications. I've always had an Oracle database back-end running on Unix, and other associated servers on Windows. Back in the PS version 7.5 days, this included application servers and process schedulers (aka report servers), and since version 8, web servers as well.

I'm a complete newbie when it comes to PowerShell. I quite literally have not done a single useful thing with it yet. I've fiddled with it just long enough to see that it's very powerful, and if I can just learn it, it can become very useful in my daily work. I'm sure I'll often be doing things the hard way, so I encourage any PowerShell users out there to chime in with any advice you may have. And any PeopleSoft admins, as well -- if there's one thing I've learned in the last decade, it's that a decade of PeopleSoft admin experience isn't enough!