Absolute musthave for vi(m)

• "Get me out!!!": Esc Esc :q!
• Save: Esc Esc :w
• Save&Exit: Esc Esc :wq
• Insert: Esc Esc i
• Search for regex: Esc Esc /regex
• Do search further: n
• Delete line: Esc Esc dd
• Delete n lines: Esc Esc ndd
• Undo: u
• Go to given line: Esc Esc :number
• Go to beginning: g
• Go to end: G

Linux: IO things

system activity

sar -P ALL

If %iowait is greater than 5%, something is DJ-ing with your disks.

iostat w/ nfs

iostat -mnh

This shown which block devices are being read/write right now. If Blk_read/s or Blk_wrtn/s is greater than 50, you can start being suspicious.

LabVIEW: LV By a Programmer

Some thoughts I had while working in LabVIEW for a couple of years:

1. Mathematics. Think before draw. "The less you plan, the more complex your VI will be."
2. Pre-compute. Reuse. Pipeline. "Used twice? SubVI!"
3. Avoid Express VI-s.
4. Organize wires into a cluster if they are related, and are more than 3 of them.
5. If and only if an input is required by the algorithm, set it to required. Not required inputs should have sane defaults. Otherwise, you’ll trash the block diagram with unnecessary constants.
6. Customize the Clean-up Diagram button for your favourite style, and Use It.
7. Always go from left to right. Do not make wires that go around from the right, rather extend the space on the left.

Confluence: Search for things skipping the shipped "search engine"

Using Atlassian Confluence, and have to report on exact things, like "How many, and which pages refer to a given phrase"?
This searches through all the latest version of all the pages:

SELECT DISTINCT
   'http://confluence.example.com/confluence/pages/viewpage.action?pageId=' || contentid AS URL
FROM
   CONTENT
INNER JOIN BODYCONTENT USING(contentid)
WHERE
   prevver      IS NULL
   AND SPACEID  IS NOT NULL
   AND PARENTID IS NOT NULL
   AND REGEXP_LIKE(BODY, 'internal-webserver\d(\.example\.com)?\\public', 'i');

HP BSM: Using Oracle Services instead of SID (useful if having a RAC)

This cannot be set on the wizard, this has to be done by hand. Origin of this information

1. Log into the server. (rdp or other)
2. Stop BSM.
3. Create the file D:\HPBSM\conf\bsm-tnsnames.ora with content:
   

   hpbsm_service = 
   (DESCRIPTION = 
       (ADDRESS_LIST = 
           (ADDRESS = 
               (PROTOCOL = TCP)
               (HOST = oraclerac-vip.example.com)
               (PORT = 1521)
           )
           (LOAD_BALANCE = on)
           (FAILOVER = on)
       )
       (CONNECT_DATA = 
           (SERVER = DEDICATED)
           (SERVICE_NAME = hpbsm_service)
       )
   )
   

4. Edit D:\HPBSM\conf\jdbc.drivers.properties
   
   1. Comment out the line starting with: ddoracle.url=
   2. Add new line (literally): ddoracle.url=jdbc:mercury:oracle:TNSNamesFile=D:\\HPBSM\\conf\\bsm-tnsnames.ora;TNSServerName=${sid}
5. Create the file D:\HPBSM\odb\conf\jdbc.properties:
   

   Oracle = ddoracle
   cmdb.url = jdbc:mercury:oracle:TNSNamesFile=D:\\HPBSM\\conf\\bsm-tnsnames.ora;TNSServerName=hpbsm_service
   

   (here you have to include the service name from the bsm-tnsnames.ora)
   
6. Run the "Configure HP Business Service Management" utility, and enter hpbsm_service in the SID field (it will be converted to a tnsnames.ora entry lookup by our hack above).
7. Connect to hp_mgr with SQL Developer or TOAD (or whatever you like):
   

   SELECT SESSION_ID, SESSION_DB_SID FROM sessions WHERE SESSION_DB_NAME = 'profile_mgr'; -- Must be hpbsm_service. If not, update.
   SELECT SP_ID, SP_VALUE FROM setting_parameters WHERE SP_NAME IN ('opr.db.connection.sid', 'dal.bpi.runtime.sid', 'dal.bpi.pr.repos.sid');  -- All must be hpbsm_service. If not, update.
   
   UPDATE SESSIONS SET SESSION_DB_SID = 'hpbsm_service' WHERE SESSION_ID IN (SELECT SESSION_ID FROM SESSIONS WHERE SESSION_DB_NAME = 'profile_mgr');
   UPDATE SETTING_PARAMETERS SET SP_VALUE = 'hpbsm_service' WHERE SP_ID IN (SELECT SP_ID FROM SETTING_PARAMETERS WHERE SP_NAME IN ('opr.db.connection.sid', 'dal.bpi.runtime.sid', 'dal.bpi.pr.repos.sid'));
   
   

8. Start BSM.
9. VALIDATE

HP BSM: Removing licenses

As you already know, you cannot remove licenses through the BUI. The only way there is to remove it from the database directly!
Log in to the DB, into the hp_mgr schema, and:

SELECT license_key FROM licenses;

The rows ending with a "T*" identifier are the actual licenses. Select the license you want to remove, and delete that row (note: the license_key is the key candidate here), and restart BSM, to get the change in effect!

Useful, if you have mistakenly added a prod license to your dev tier, or vice versa.

Jira: Get latest activity for all users after a given date

This query lists active users after a given date. Useful for filtering out inactive users.
In this example, list users who were active in this year (2013):

WITH w_all_user_activities AS
  (SELECT 
    author,
    g.created
  FROM jiraissue i
  INNER JOIN changegroup g ON i.id = g.issueid
  INNER JOIN changeitem c ON c.groupid = g.id
  UNION
  SELECT author, created FROM jiraaction
  UNION
  SELECT reporter, created FROM jiraissue
),
w_tmp_latest_user_activities AS
  (SELECT
    author,
    created,
    RANK() OVER (PARTITION BY author ORDER BY created DESC) dest_rank
  FROM w_all_user_activities
),
w_latest_user_activities AS
  (SELECT 
    author,
    created
  FROM w_tmp_latest_user_activities
  WHERE dest_rank = 1
)
SELECT * FROM w_latest_user_activities 
WHERE created >= TO_DATE('2013-01-01', 'yyyy-mm-dd');

Jira: SQL for "Check workflow entry states are correct" integrity check

If this query returns any rows, run the "Check workflow entry states are correct" action in the Integrity Checker.
This issue happens as a result of the lack of using database transactions during the workflow transitions (which changes numerous tables) in Jira.

SELECT jiraissue.pkey, jiraissue.workflow_id, OS_WFENTRY.*
FROM jiraissue
INNER JOIN OS_WFENTRY ON jiraissue.workflow_id = OS_WFENTRY.id
WHERE OS_WFENTRY.STATE IS NULL OR OS_WFENTRY.STATE = 0;

Update: For JIRA version 6.x I suggest reading this: http://kozelljozsef.blogspot.com/2014/11/jira-check-workflow-entry-states-are-correct.html

Jira: List users assigned explicitly to project roles

Assigning people to project roles explicitly is a bad habit, you should avoid it, as it will lay much work on your shoulders when a user's privileges will be changed.

SELECT actor.roletypeparameter AS username, r.name AS rolename, p.pkey, p.pname AS projectname
FROM projectroleactor actor
INNER JOIN cwd_user u ON u.lower_user_name = actor.roletypeparameter
INNER JOIN projectrole r ON actor.projectroleid = r.ID
INNER JOIN project p ON actor.pid = p.ID;

Jira: Activity Stream stopped working, throws error

Using Atlassian Jira, and having faulty activity streams? Also, using Oracle?
You have an issue (usually from an email), where the subject is empty.
In Oracle, the empty string is stored as null.
Therefore, when Jira reads back an issue, its subject will be null (not empty string).
This screws up the Activity Stream gadget, as it calls String.substring() on it, without any error checking.

Get rid of the null subjects.

UPDATE JIRAISSUE SET SUMMARY = '(no subject)' WHERE SUMMARY IS NULL;

Jira: "Passed List had more than one value."

This error happens in Atlassian Jira, as they maliciously and intentionally avoid using foreign keys and unique indices in their table design.
If you see the following error in your tomcat log, you have to do a cleanup:

2013-01-01 01:01:01,111 ajp-8009-1 ERROR      [500ErrorPage.jsp] Exception caught in 500 page org.apache.jasper.JasperException: java.lang.RuntimeException: java.lang.IllegalArgumentException: Passed List had more than one value. 
org.apache.jasper.JasperException: org.apache.jasper.JasperException: java.lang.RuntimeException: java.lang.IllegalArgumentException: Passed List had more than one value. 
(...)
Caused by: java.lang.IllegalArgumentException: Passed List had more than one value. 
        at org.ofbiz.core.entity.EntityUtil.getOnly(EntityUtil.java:62) 
(...)

This actually deletes all the duplicated rows, to make every row to be unique (over 'username', at least):

DELETE FROM columnlayout
WHERE ROWID IN (
      SELECT "ROWID" FROM (
            SELECT RANK() OVER(PARTITION BY USERNAME ORDER BY ROWID) RANK_N, ROWID AS "ROWID" FROM columnlayout
            WHERE USERNAME IN (
                  SELECT USERNAME FROM columnlayout
                  GROUP BY USERNAME
                  HAVING COUNT(*) > 1
            )
      )
      WHERE RANK_N > 1
);

Jira: deleted user coming from external directory (like LDAP)

You are using Atlassian Jira, but your users are authenticated from LDAP (AD/whatever)?
Surely, when one of your users leaves or gets her name changed, you'll be stuck with malfunctioning watcher lists, as Jira does not use any of those pesky "relational" stuff in RDBMS-s (valid of any DBMS vendor).
Get rid of the faulty associations.

DELETE FROM userassociation 
WHERE LOWER(source_name) IN (
    SELECT DISTINCT LOWER(source_name) FROM userassociation
    MINUS
    SELECT DISTINCT lower_user_name FROM cwd_user)

Jira: "No scrollbar" problem

Using Atlassian Jira, and an issue view page does not have scrollbars but it should?
You've got an email containing "overflow: hidden". Get rid of it.

This will list you all the issues where the description contains the offending CSS.

SELECT * FROM jiraissue
WHERE REGEXP_LIKE(description, 'overflow: *hidden', 'i');