Automagisk risikoklassifisering i Excel

Innledning
Dersom man har mange krav som skal risikoklassifiseres basert på ”Chance of failure” og ”Damage in case of failure”, kan det være tidkrevende å finne den endelige risikoen ved å gjøre manuelle oppslag i tabell. 
Jeg har laget et verktøy for oppslag i risikotabell i Excel, som gjør at man kan skrive inn ”Chance of failure” og ”Damage in case of failure”, og automagisk få den absolutte risikoklassifiseringen ut fra dette.

Oppslagstabell
Lag først en oppslagstabell som inneholder risikotabellen ”Absolute Risk Classification” (TMap Next s. 479). Tabellen har i dette eksempelet verdiene ”1 – High”, ”2 – Medium” og ”3 – Low” som absolutte risikoklasser, ettersom det er disse som brukes i prosjektet jeg er på nå. Dette kan selvsagt enkelt endres til ”A”, ”B”, ”C” eller hva man måtte ønske. 

Kolonne A inneholder klassene for ”Chance of failure”.
Rad 2 inneholder klassene for ”Damage in case of failure”.  

Markér A2:D5, og kall området ”Tabell”; denne kan også refereres til fra andre ark/faner i regnearket:

Kravspesifikasjon 
Selve kravspesifikasjonen inneholder følgende felter: 
A: Navn på kravet 
B: Beskrivelse 
C: Chance of failure: må tilsvare en av verdiene i kolonne A i ”Tabell” (High/Medium/Low).
D: Damage i.c.o failure: må tilsvare en av verdiene i rad 2 i ”Tabell” (High/Medium/Low). 
E: Kalkulert risikoklasse (hentes fra oppslagstabell).

 

Formel 
Formelen i celle E12 (og nedover) sjekker verdien i C12 og D12, og gjør et oppslag i ”Tabell” for å hente riktig verdi i forhold til klassifikasjonsmetoden. Kravspesifikasjonen over viser alle varianter, og ”Risk Class” gitt utfra spesifikasjon i ”Tabell”.  

Formel på norsk:
=FORSKYVNING(Tabell;SAMMENLIGNE($C12;FORSKYVNING(Tabell;0;0;RADER(Tabell);1);0)-1;SAMMENLIGNE($D12;FORSKYVNING(Tabell;0;0;1;KOLONNER(Tabell));0)-1) 

Formel på engelsk:
=OFFSET(Tabell;MATCH($C12;OFFSET(Tabell;0;0;ROWS(Tabell);1);0)-1;
MATCH($D12;OFFSET(Table;0;0;1;COLUMNS(Tabell));0)-1) 

OBS: Etter å ha skrevet inn eller editert formelen, er det viktig å trykke CTRL+SHIFT+ENTER for å gå ut av cellen (ikke bare TAB eller ENTER). Dette for å fortelle Excel at formelen inkluderer et område/array.

Risikoklassifisering i Excel.doc

HPQC Excel Report 3: Requirement Direct Cover Status with Test Instance Status

– Report shows Direct Cover Status for all requirements (or filtered), including Test Instance Status from Test Lab.
SELECT REQ.RQ_REQ_ID AS “Req ID”,
REQ.RQ_REQ_STATUS AS “Direct Cover Status”,
REQ.RQ_REQ_NAME AS “Requirement”,
TEST.TS_NAME AS “Test Case”,
CYCL_FOLD.CF_ITEM_NAME AS “Test Set Folder”,
CYCLE.CY_CYCLE AS “Test Set”,
TESTCYCL.TC_STATUS AS “Test Instance Status”
FROM REQ, REQ_COVER, TEST, TESTCYCL, CYCLE, CYCL_FOLD
WHERE REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
AND TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID
AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
AND TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
– AND RQ_REQ_PATH LIKE ‘??????%’
ORDER BY REQ.RQ_REQ_ID

HPQC Excel Report 2: Number of tests covering requirements

– Report showing how many tests in Test Plan are connected to the Requirement.
SELECT REQ.RQ_REQ_ID,
REQ.RQ_REQ_NAME,
COUNT (REQ_COVER.RC_ITEM_ID) AS “Covered by number of tests”
FROM REQ, REQ_COVER
WHERE REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
– AND RQ_REQ_PATH LIKE ‘??????%’
GROUP BY REQ.RQ_REQ_ID, REQ.RQ_REQ_NAME

HPQC Excel Report 1: Requirements and Folders Paths

– This Report is a tool for finding Path to requirements. This is needed for some Reports as a filter.
SELECT REQ.RQ_REQ_ID AS “Requirement ID”,
REQ.RQ_REQ_NAME AS “Requirement Name”,
REQ.RQ_REQ_PATH AS “Requirement Path”
FROM REQ
– WHERE RQ_REQ_PATH LIKE ‘??????%’
ORDER BY RQ_REQ_PATH, RQ_REQ_NAME

Excel spreadsheet in Word

http://www.lockergnome.com/it/2005/10/07/importing-excel-data-into-word/

If you want to be able to edit that data within Excel and have those changes updated automatically in Word, you can do so by creating a dynamic link.

1. Open your Word document.
2. Open your Excel spreadsheet.
3. With Excel, select the cells you want to copy into Word.
4. From the Edit menu, click Copy.
5. Switch back to your Word document.
6. Place the insertion point where you want the data placed.
7. From the Edit menu, click Paste Special.
8. Select Microsoft Excel Worksheet Object.
9. Select the Paste Link radio button.
10. Click OK.

Now when you double click the table in Word, the worksheet will open in Excel.

- – -

If you want to include the contents of a cell within your text, and have it update automaticly if it changes in the spreadsheet, you can do this too:

Follow the same instructions as above, but simply copy the cell containing the value you want to include in the text.

Then, in Word:
Edit -> Paste Special -> Unformatted Text (make sure to select the “paste link” option). Now when you have both the Word and Excel documents open, F9 will update the text in your Word document as you change the numbers in your Excel.

Windows XP nifities

Task Manager can be started three ways:

- Press CTRL + ALT + DELETE and click on the Task Manager button on the Windows Security dialog.
- Right click on the taskbar and chose Task Manager
- Press CTRL + SHIFT + ESC and Task Manager will be launched instantly.

Control Panel apps from the command line:

Accessibility Options	access.cpl
Add/Remove Programs + Windows Scheduler	appwiz.cpl
Add Hardware Wizard	hdwwiz.cpl
Automatic Updates	wuaucpl.cpl
Bluetooth Properties	bthprops.cpl
Date/Time	timedate.cpl
Display Properties	desk.cpl
Firewall Properties	firewall.cpl
Game Controllers	joy.cpl
Internet Options	inetcpl.cpl
Java Control Panel	jpicpl32.cpl
Mouse Properties + keyboard	main.cpl main.cpl keyboard (‘tastatur’ in norwegian)
Multimedia Properties	mmsys.cpl
Network Connections	ncpa.cpl
Network Setup Wizard	netsetup.cpl
ODBC Properties	odbccp32.cpl
Power Options	powercfg.cpl
Printers folder	control printers
Regional and Language Options	intl.cpl
Sound and Audio Devices	mmsys.cpl
System Properties	sysdm.cpl
Time and Date Settings	timedate.cpl
User Accounts	nusrmgr.cpl
Wireless Link	irprops.cpl

Windows substitutes the name of the tool you want to run for %1%. For example:
“rundll32.exe shell32.dll,Control_RunDLL appwiz.cpl”.
To run the Users tool in Control Panel, type control Ncpa.cpl users, and then press ENTER.

Usefull GPG

Introduction

Just collecting some useful basic encryption-knowledge here.

Checksums

GnuPG supports new and secure algorithms:
$ gpg –print-md sha1 filename
filename: E83A 42B9 BC84 31A6 6450  99BE 50B6 341A 35D3 DCEB

It also will take multiple files:
$ gpg –print-md sha1 *.txt
test.txt: E0D6 3F44 4253 CED5 9205  4047 4AA6 4E0F FD0F 130D
test2.txt: 32AC 34F9 B7AF 1972 C015  E5EE 456E 89BD CC3C 7246

If you still need MD5, that’s available too:
$ gpg –print-md md5 filename
filename: 26 E9 85 5F 8A D6 A5 90  6F EA 12 12 83 C7 29 C4

The more recent GnuPG versions also support much more secure hash algorithms, such as SHA-512:
$ gpg –print-md sha512 filename
filename: FC37410D 9336DD60 22AEB6A2 A42E82F1 2EA3470D 4982E958 B35C14A0
CF381CD2 3C4CBA35 BE5F11CB 05505ED2 DBF1C7A0 397EFF75 007FAEBB
30B43B30 6514990D

By the way, you can validate these –print-md examples by creating a file called filename containing the single line: The Linux Journal.

Your hash values should have exactly the same hexadecimal value as those in this article if the contents of the file is the same.

(From The Linux Journal)

Key Admin

gpg –list-keys
all present keys will be displayed. To see the signatures as well type:
gpg –list-sigs

Export Keys

The command for exporting a key for a user is:
gpg –export [UID]
If no UID has been submitted all present keys will be exported. By default the output is set to stdout. But with the -o option this is sent to a file. It may be advisable using the option -a to write the key to a 7-bit ASCII file instead of a binary file.

By exporting public keys you can broaden your horizon. Others can start contacting you securely. This can be done by publishing it on your homepage, by finger, through a key server like http://www.pca.dfn.de/dfnpca/pgpkserv/ or any other method you can think of.

Import Keys

When you received someone’s public key (or several public keys) you have to add them to your key database in order to be able to use them. To import into the database the command looks like this:
gpg –import [Filename]

Encrypt

The command to encrypt is
gpg -e Recipient [Data]
or
gpg –encrypt Recipient [Data]

Decrypt

The command for decrypting is:
gpg [-d] [Data]
or
gpg [--decrypt] [Data]
Also here stdout is preset, but with the -o option you can redirect the output to a file.

Signing and Checking Signatures

To sign data with your own key, use the command:
gpg -s (or –sign) [Data]
By doing this also compression takes place. This means that the result is not legible. If you want a legible result you can use:

gpg –clearsign [Data]
this will make sure that the results are clearly legible. Furthermore it does the same (signing data).

With
gpg -b (or –detach-sign) [Data]
you can write the signature in a separate file. It is highly recommended to use this option especially when signing binary files (like archives for instance). Also the –armor option can be extremely useful here.

Quite often you find that data is encrypted and signed as well. The full instruction looks like:
gpg [-u Sender] [-r Recipient] [--armor] –sign –encrypt [Data]

The functionality of the options -u (–local-user) and -r (–recipient) are as described before.

When encrypted data has been signed as well, the signature is checked when the data is decrypted. You can check the signature of signed data by using the command:
gpg [--verify] [Data]
This will only work (of course) when you own the public key of the sender.

(From Gnu Privacy Guard (GnuPG) Mini Howto (English), Brenno J.S.A.A.F. de Winter)

"Other" experience in LinkedIn

Connecting to people you know, but haven’t been in touch with for a while, can be a bit frustrating on LinkedIn.

I like LinkedIn as a tool for maintaining contact with people I meet, and also it is a good place to let others search for my cv/qualifications etc.

Since I don’t use it “profesionally” to search for others etc, I don’t want to pay money for a subscription, and this is the root cause of my (now former) problem with adding people.

We all meet a lot of people along the way in our life, and alot of the would be nice to hook up with on LinkedIn. The problem is, often their email address have changed since last time you talked, and even if you find them using a search on LinkedIn, you can not connect without the email they used when they registered!

 

Thus, I have added a position to the Experience part of my profile. I have called it “Other”, and it is a self-employed position which might as well have been called “Life” or something like that.

Using this position, I can add a conact simply by doing a search, and clicking “Add Tore to your network”. Then, you can add the person under “Business Partner”, selecting your “Other” company.

 

This may not be the nicest method, but if you want to save the $19.95 per month, is is quite ok.

I reckon this could be used for spamming people you don’t know and try to make them add you, but serious people will just ignore your request if they don’t know you, and also I don’t think it would make a good impression…

TED Talks – Isabel Allende: Tales of passion

TED Talks reposted one of their favorite videoes of 2007, which is Isabel Allende (famous author, born in Peru, brother of Chiles former president Salvador Allende) talking about ”writing, women, passion, feminism”.

The speach is truly worth listening to, as she is as good as a speacher, as she is as a writer.

“Heart is what drives us and determines our lives”

I found the link to her talk at Presentation Zen, which as usual had a great post.

Six Ways to Start Off Secure in ‘08

Web Worker Daily has a nice article about staying on the secure side in 2008: Six Ways to Start Off Secure in ‘08.

• Get a USB Thumb Drive for Backups.
• Use a VPN in Public.
• Use the “S” with GMail; https://mail.google.com.
• Run and Regularly Update a Security Suite.
• Be Sure Your Home Wi-Fi Network is Secure.
• Clean Up and Tune Up Your Computer—Free.

There is some great stuff in the article, so it’s well worth a read.