[563] in Tooltime
How to do Complex Finds in Scopus
daemon@ATHENA.MIT.EDU (Debi Fuchs)
Wed Nov 19 17:34:17 1997
Date: Wed, 19 Nov 1997 17:34:26 -0500
To: nhoward@MIT.EDU, tooltime@MIT.EDU, hd@MIT.EDU
From: Debi Fuchs <debi@MIT.EDU>
Hello,
Nick Howard sent email to tooltime containing a number of functionality
requests for our Help Tools, including several related to complex "finds".
If you are using one of the Scopus clients which allows you to edit SQL
code (I don't think you can do it on a Mac), it is possible to create
fairly complex "finds" in Scopus as we are used to doing in FileMaker. In
Scopus it requires some knowledge of SQL, but it _is_ possible.
I will do some kind of training in SQL for those who are interested
sometime soon. Stay tuned. In the meantime, I am including some examples in
response to Nick's questions, below. I hope these will get you started.
I have reordered Nick's questions. Those having to do with complex finds
are first.
Cheers,
-Debi
>Nick writes:
>I need the following to best do my job of queue management:
-------------------------------
>9. a method of obtaining a found set of a list of log#s (eg. I have a list of
>log#s for logs that are in the housecall area and I want to have a working
>set of that list or I was given a list of logs for log busting and I want to
>creat THAT working set)
Do any Find and then click the "Modify Result Set" button at the top of the
resulting window, replacing the WHERE clause to read as follows:
mit_log_id IN (100374, 100375)
-------------------------------
>6. a script to find all pending logs that were created before a certain date.
Use the following WHERE clause, for example:
mit_log_status NOT IN ('CLOSED','CANCEL','FROZEN') and
mit_modified_ts<to_date('11/12/1997','MM/DD/YYYY')
-------------------------------
>7. a means to find all logs modified within some time period. (eg. all
>logs modified within the last 2 hours or all logs modified during the week
>of .....)
Use the following WHERE clause to find logs modified within the last two hours
mit_modified_ts>=sysdate-1/12
Use the following WHERE clause to find logs modified during a given week
mit_modified_ts>=to_date('11/12/1997','MM/DD/YYYY') and
mit_modified_ts<=to_date('11/18/1997','MM/DD/YYYY') and
Use the following WHERE clause to find logs not modfied in the last week
mit_modified_ts<=sysdate-6
-------------------------------
>4. a script (or means) to find all logs opened by a particular person and a
>script (or means) to find all logs closed by a particular person.
Use the following WHERE clause to find logs opened by nhoward:
mit_closed_user=(select userid from profiles where login_name='nhoward')
-------------------------------
>8. a means of finding all logs taken by any one of list of people. (eg. all
>lags taken by plee or anger)
Use the following WHERE clause:
mit_taken_user IN (select userid from profiles where
login_name IN ('plee','anger')
-------------------------------
>1. a script that takes in a date I give it and gives me all logs that are
>marked housecall that are not closed, cancelled or frozen (where frozen means
>set to frozen with a future thaw date) and all taken logs that have not been
>modified since the entered date. (in other words all
>pending/unseen/thawed/new/
>urgent/taken housecall logs regardless of mod date, and all other taken logs
>that haven't been modified since the entered date)
>[note: this is only for logs that would have been found in the old database,
>not unix logs]
Use the following WHERE clause:
(mit_category LIKE 'Housecall' and mit_status not in
('CANCEL','CLOSED','FROZEN') or (mit_status='TAKEN' and
mit_modified_ts<=mit_created_ts and mit_category<>'UNIX/VMS'))
[or, if you only want "Helpdesk" logs for the latter group you mention (not
sure if you also wanted to exclude SAP, mainframe, dcs, cwis, etc...), do
the following:]
(mit_category LIKE 'Housecall' and mit_status not in
('CANCEL','CLOSED','FROZEN') or (mit_status='TAKEN' and
mit_modified_ts<=mit_created_ts and mit_category='Helpdesk'))
[Frozen logs are marked "PENDING" automatically on a nightly basis when
they should be reopened, so you can exclude ALL FROZEN LOGS...you don't
have to worry about the date]
-------------------------------
>5. a means of finding logs that have some attribute outside of the history
>field and some particular ford in the history field. (eg. all pending
>logs involving pc's that have the name hoiwai in the history field)
I wish I could tell you to use the following WHERE clause:
mit_history LIKE '%hoiwai%' AND mit_status='PENDING'
However, the history field, which can contain up to 2 gigabytes of data, is
stored as a "LONG" field. The normal rules do not apply for LONG fields.
To do a history only search, things are a little more complicated. I wish
I could tell you to modify the resulting WHERE clause after doing a history
only search on hoiwai to be more specific (i.e. find PENDING logs, only)
but that doesn't work, either.
I think what might be possible is to have the history only dialog box in
Scopus include a few other fields, as well, such as log status, hardware,
software, category, etc. I'll look into that.
-------------------------------
>10. a single keystroke or mouse clicable script that shows the next log on the
>found set. (I need to read the last entry in the history of each log to
>determine whether to print it and there is no good reason to have to switch
>to a different window, figure out where to click for the next log and double
>click there)
If your monitor is large enough (as on an Athena machine), it's easy to
have both the found set window AND the log screen showing at the same time.
Then (at least in UNIX) if the "found set" screen is the active window, you
can just use the arrow key to move down to the next record, and hit return
to have the log be displayed in the log screen. You can really move through
logs quite quickly that way. Can someone check this out on NT?
-------------------------------
>2. a method that allows me to, in whatever ever manner, print a selected
>(not algorithmacly determinable) subset of this list, in a format that is
>reasoanble to decipher (unlike what the "print" button gives) and is not
>overly space wasting (people respond better to a few pieces of paper
>rather than a ream that says the same thing) and overly time consuming
>(before I would look at a log for a few seconds, if I thought It needed
>printing would click print and move on rather than write down the log#
>and afterwords go into netscape and type in log#s, wait, click print, type
>in the next # etc.) like the web printing is.
Well, you could try using alt-print-screen from an NT machine, and printing
out from some graphics application. I'm not saying this is a great
solution. Just another idea.
-------------------------------
>3. a method of marking logs that are kept open for traking purposes and don't
>need to have attention brought to them even if they are x days untouched.
>(before this included referred or the word omit added to the secret field)
We kludged this in FileMaker, I'm sure we can find a way to kludge it in
scopus.
-------------
Debi Fuchs, Consultant
MIT Information Systems
Computing Help Desk Group
77 Massachusetts Avenue, 11-319
Cambridge, MA 02139
(617) 253-7812
debi@mit.edu
