Users never logged in - LiveReport

I'm having a hard time with this report for LL 9.7.1/SQL 2005

This works in other SQL environments, but I can't get it working in a particular one....

Here is my statement:
SELECT    CAST(KUAF.name AS char(50)) AS UserName, KUAF.FirstName ,KUAF.LastName
FROM    KUAF, DAuditNew
WHERE     (KUAF.Deleted = '0')
AND (KUAF.Type = '0')
AND (
       CAST(KUAF.name AS char(50))
   NOT IN (
   select distinct CAST(DAuditNew.Value2 AS char(50))
   from DAuditNew
   where DAuditNew.AuditStr = N'login'
   )
     )
GROUP BY CAST(KUAF.name AS char(50)), KUAF.FirstName ,KUAF.LastName
ORDER BY UserName DESC

The problem is, it's returing Active Users - not users never logged in....which is what I'm after. I've looked at the other 'users never logged in' forum posts but those aren't working either.

Any ideas?

Find more posts tagged with

Comments

Lindsay_Davies

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

Hi Steven,

This depends on your authentication in Livelink / Content Server.

For example, if you use integrated windows authentication from the websever, the user does not go through a login event.

Therefore to see who has NOT used the system, you must check which valid user accounts have NO audit events of any kind.

Typically this is the PerformerID column in DAuditNew, I think.

Regards

Lindsay

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 November 02, Wed 16:36
To: eLink Recipient
Subject: Users never logged in - LiveReport

Users never logged in - LiveReport

Posted by sknoll@axyonconsulting.com (Knoll, Steven) On 11/02/2011 12:27 PM

I'm having a hard time with this report for LL 9.7.1/SQL 2005 This works in other SQL environments, but I can't get it working in a particular one.... Here is my statement:
SELECT    CAST(KUAF.name AS char(50)) AS UserName, KUAF.FirstName ,KUAF.LastName
FROM    KUAF, DAuditNew
WHERE     (KUAF.Deleted = '0')
AND (KUAF.Type = '0')
AND (
       CAST(KUAF.name AS char(50))
   NOT IN (
   select distinct CAST(DAuditNew.Value2 AS char(50))
   from DAuditNew
   where DAuditNew.AuditStr = N'login'
   )
     )
GROUP BY CAST(KUAF.name AS char(50)), KUAF.FirstName ,KUAF.LastName
ORDER BY UserName DESC The problem is, it's returing! Active Users - not users never logged in....which is what I'm after. I've looked at the other 'users never logged in' forum posts but those aren't working either. Any ideas?

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Lindsay_Davies

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

Hi Steven,

Just had a quick test and this query should give you what you are after.

It should be good for Oracle and SQL Server instances.

select name, firstname, lastname, id from kuaf where id not in (select distinct(performerID) from dauditnew) and type=0 and deleted=0 order by 1;

You could also add in a date range test on auditdate if you only want to see those who have not used the system in a given period.

Regards

Lindsay

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 November 03, Thu 08:03
To: eLink Recipient
Subject: RE Users never logged in - LiveReport

RE Users never logged in - LiveReport

Posted by ldavies@opentext.com (Davies, Lindsay) On 11/03/2011 04:02 AM

Hi Steven, This depends on your authentication in Livelink / Content Server. For example, if you use integrated windows authentication from the websever, the user does not go through a login event. Therefore to see who has NOT used the system, you must check which valid user accounts have NO audit events of any kind. Typically this is the PerformerID column in DAuditNew, I think. Regards Lindsay OpenText UK
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 November 02, Wed 16:36
To: eLink Recipient
Subject: Users never logged in - LiveReport

Users never logged in - LiveReport

Posted by sknoll@axyonconsulting.com (Knoll, Steven) On 11/02/2011 12:27 PM

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Users never logged in - LiveReport
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Steven_Knoll

Hi Lindsay,

The statement you provided worked great. Thanks so much.

We are using a combination of SSO and internal named users, but mostly SSO.