LiveReport for the number of external users logged in

I am trying to create a LiveReport that will give the number of external users that log in for a certain time period. The report I've created only gives a value of zero no matter the time frame so I need some help.SELECT COUNT (*) FROM kuaf, daudit WHERE kuaf.userdata NOT LIKE '%%NTLM%%' and daudit.auditdate = %1 and daudit.auditdate = %2

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi Lisa,

This should be easy, and it is, provided you understand enough about SQL and both the peculiarities of the version of Livelink and to which relational database system you are connecting.

There are a number of mistakes in your statement....

Assuming you are trying to specify a start date and an end date in that order, you will want to say

daudit.auditdate >= %1 and daudit.auditdate < %2

In your statement, not having this was the main reason zero rows (rather than thousands) were returned because you were trying to find rows where the auditdate was equal to two values at the same time.

You are trying to 'join' Kuaf and DAudit, but not specifying a join condition....

Without this you are get a Cartesian join, where each row in table 1 is matched with every row table 2 - that means if there are 10 rows in each table you would get 100 rows back.

So you need to add

and kuaf.id = daudit.userid

Then, you would get each event for every user being counted, but I assume you want each individual user counted only once, so you need

select count(distinct(ID)) from...

Ok, that was the easy bit - so we have a statement now which looks like this....

SELECT COUNT(distinct(ID)) FROM kuaf, daudit WHERE kuaf.ID = Daudit.UserID and kuaf.userdata NOT LIKE '%%NTLM%%' and daudit.auditdate >= %1 and daudit.auditdate < %2

Now, it gets slightly more difficult because I don't know whether you are using MS SQL server or Oracle and whether you are using a version of Livelink before 9.5 or not.

In Livelink 9.5, the auditing was moved from the DAudit table to DAuditNew and more was details recorded (and as a result some column names changed).

So if you are on Livelink 9.5 or above, then it changes to...

SELECT COUNT(distinct(ID)) FROM kuaf, DAuditNew WHERE kuaf.ID = DAuditNew.PerformerID and kuaf.userdata NOT LIKE '%%NTLM%%' and DAuditNew.auditdate >= %1 and DAuditNew.auditdate < %2

Finally if you want to work with a smaller date range that includes the time component then you need to adapt the statement to handle input dates when comparing them to timestamps in the database.

For Oracle, the a whole date can be specified in the short form that is configured for the instance, but to be on the save side, it is worth using the to_date function to convert your literal to a proper timestamp format. This is necessary if you want to include a time component.

For example:

and dauditnew.auditdate >= to_date(%1,'dd-mm-yyyy hh24:mi:ss')

and dauditnew.auditdate < to_date(%2,'dd-mm-yyyy hh24:mi:ss')

but you would change your inputs to strings and give those parameters as 01-12-2008 09:00:00 and 01-12-2008 17:30:00 when you execute.

MS SQL server tends to be more forgiving in this respect, but you can check by selecting one row to see the format used and copy that.

I used the following

SQL and DA.auditdate >= %1 and DA.auditdate < %2

parameters 2009-01-19 09:00:00 2009-01-19 17:30:00

All things are (normally) possible....good luck.

Lindsay

Open Text UK

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 20 January 2009 07:15
To: eLink Recipient
Subject: LiveReport for the number of external users logged in

LiveReport for the number of external users logged in

Posted by lisa.hart@amylin.com (Hart, Lisa) on 2009/01/20 02:10

I am trying to create a LiveReport that will give the number of external users that log in for a certain time period. The report I've created only gives a value of zero no matter the time frame so I need some help.

SELECT COUNT (*) FROM kuaf, daudit WHERE kuaf.userdata NOT LIKE '%%NTLM%%' and daudit.auditdate = %1 and daudit.auditdate = %2