nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Querying on audittrail table is not getting results.

NewWindow_Hexaware_ey

HI Experts,

Actally i have created the one custom component called reports in webtop. after lunching this component , this auditrail report will come in to dropdownlist and will select the name of the report..once if i click the execute button(like in dql editor,in my jsp i have customised the dql_editor.jsp according to my requirement),then the above my dql quries will execute and will return the data into the datagrid on webtop.

Required columns names- User name, - Date of last mywebtop access, - Total numbers of days of mywebtop

For these columns names i am using below queries.

main query
select user_name,'$1$','$2$' from dm_user where r_is_group = FALSE and user_state = 0 and user_name not like 'dm%' order by user_name

sub queries

select time_stamp from dm_audittrail where user_name ='%s' and event_name = 'dm_connect' order by time_stamp desc enable(RETURN_TOP 1)

select distinct DATETOSTRING(time_stamp,'mm/dd/yyyy') from dm_audittrail where user_name ='%s' and event_name in ('dm_connect') and DATEDIFF(day, time_stamp, DATE(TODAY))<=180

i have created index on dm_audittrail table.like bellow.but no luck.

EXECUTE make_index WITH type_name='dm_audittrail',

attribute='event_name',attribute='user_name'

we have three envronments dev ,qa ,production. in dev the report(all my queries returning data into the datagrid on webtop level) is working fine.

in qa and prod is not getting data.it's showing 0 results.

please help.

your help is highly appriciated.

Thanks&Regards

Hari

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Button

Accepted answers

NewWindow_Hexaware_ey

Thank you so much for all of your participation.

finally,we have achieved the solution.

I have combined three queries in sql server.after that created a view(general_reports_vw) for that single query.

I took the required columns from view(created in sql server) and the below query is inserted in the register table.

select user_name, access, days from dm_dbo.general_reports_vw order by user_name.

Query wrote in sql server:
==========================

SELECT DISTINCT TOP (100) PERCENT u.user_name, MAX(a.time_stamp) AS 'access', COUNT(DISTINCT CONVERT(char(10), a.time_stamp, 101)) AS 'days'
FROM         dbo.dm_user_sp AS u LEFT OUTER JOIN
                      dbo.dm_audittrail_sp AS a ON u.user_name = a.user_name AND (a.event_name = 'dm_connect' OR
                      a.event_name IS NULL) AND CONVERT(float, DATEDIFF(minute, a.time_stamp, CONVERT(datetime, GETDATE(), 103))) / 1440 <= 180 AND
                      u.r_is_group = 0 AND u.user_state = 0
WHERE     (u.r_is_group = 0) AND (u.user_state = 0) AND (u.user_name NOT LIKE 'dm%')
GROUP BY u.user_name
ORDER BY u.user_name

Now the query is getting results very faster.

once again thanks for everyone.

All comments

DCTM_Guru

Make sure your user account has been granted privileges to read audittrail in your qa and prod environment.

NewWindow_Hexaware_ey

Hi Johnny Gee,

Thanks for your reply.

I have update the one of user account with following query and verified user_xprivileges.it's returning user_xprivileges as 56..but no luck,still report results(Audittrail query) is giving 0 results.

update dm_user objects set user_xprivileges = 56 where user_login_name ='test1';

select user_name,user_xprivileges from dm_user where user_login_name=''test1'

Note: i ran the same Audittrail query(report) using superuser account ,with this account also giving 0 results.

2.In dev envroment user_xprivileges is 0(zero) for the login user,then also it's giving results in dev environment.

Please suggest me what else i can do,to solve this problem.

Thanks&Regards

Hari.

bacham2

Several things:

1. When you say it doesn't return any results, are you talking about the webtop component? Or does the DQL return results?

2. Are you sure that there is data in your audit trail for the requested user?

3. This shouldn't change the results but use the time_stamp_utc attribute in your WHERE clause, since it is indexed. Attribute time_stamp is not.

4. Do not set privieges using DQL update. Use the DQL GRANT statement.

the_lord

Hi NewWindow_Hexaware,

I think there is problem in the subqueries you gave :

1) select time_stamp from dm_audittrail where user_name ='%s' and event_name = 'dm_connect' order by time_stamp desc enable(RETURN_TOP 1)2) select distinct DATETOSTRING(time_stamp,'mm/dd/yyyy') from dm_audittrail where user_name ='%s' and event_name in ('dm_connect') and DATEDIFF(day, time_stamp, DATE(TODAY))<=180

you should use "like" instead of "=" where you are using '%s'.

also verify that there is an user whose name ends with "s".

NewWindow_Hexaware_ey

Hi Bacham,

Thanks for your reply.

1. When you say it doesn't return any results, are you talking about the webtop component? Or does the DQL return results?

first i will launch webtop component ,for this webtop component i am passing dql queries in the behaviour class.
so all results return by the DQL

in my case: first i will launched the webtop component(tools->reports)

then there will be two dropdown controls : in first dropdown control i will select the reporttype:General report

once if i select the reporttype ,then related reportname will be displayed in 2nd dropdown control.

for this customisation i used dql_editor.jsp
.

here once if click the execute button dql query will be passed ,results will be display in webtop data grid.

Actully i have two registered tables:main query table,subquery table.

whenever control found $1 and $2 in the main query,then subqueries will be substitute and result will be display in webtop.

2. Are you sure that there is data in your audit trail for the requested user?

Yes.

3. This shouldn't change the results but use the time_stamp_utc attribute in your WHERE clause, since it is indexed. Attribute time_stamp is not.

i have midified as time_stamp_utc and verified ,still no luck

4. Do not set privieges using DQL update. Use the DQL GRANT statement

the follwing command i used to grand,but no luck

Grant view Audit to 'Test User'

Thanks&Regars

Hari

bacham2

Did you read the previous post from the_lord? He makes valid points.

About my first question: I don't really care how your component works. I just want to determine if the DQL works, i.e. is the issue on the Content Server or in your code. So again: does the DQL return results when you run it directly in idql or DA DQL Editor?

About GRANT VIEW AUDIT TO 'Test User': it should work but you can't grant that privilege to yourself. In other words, you can't be 'Test User' when you run this. And make sure 'Test User' is the user_name not the user_login_name.

NewWindow_Hexaware_ey

HI Lord,

Thanks for your suggestion.

you should use "like" instead of "=" where you are using '%s'.

also verify that there is an user whose name ends with "s".

Using "=" is not helped in my case instead of '%s'.

Actually here user names will comes from my main query.then where ever '%s' is find in subquery,'%s' will be replaced by that username.

I am using test_user1 this user doesn't ends with ''s''.

the below is code snippet.

public static String getSubQueryDataValue(String subquery,int index, String replaceValue, IDfSession session) throws DfException{

subquery = String.format(subquery, replaceValue);

IDfTypedObject handler = DfcQueryUtils.getFirstQueryResult(subquery, session);

if(handler == null){

return "";

}

return handler.getValueAt(index).asString();

}

public static String getSubQueryCountDataValue(String subquery,String replaceValue, IDfSession session) throws DfException{

//subquery = String.format(subquery, replaceValue);

subquery = subquery.replaceAll("%s", replaceValue);

List handler = DfcQueryUtils.getAllQueryResults(subquery, session);

return handler.size()+"";

}

Thanks&regards.

Hari.

NewWindow_Hexaware_ey

Hi Bacham,

Thanks for your valueble suggestions,

does the DQL return results when you run it directly in idql or DA DQL Editor?

Yes .it's giving results

I am granting the privileges with the superuser account.and Test user is the user_name .

I am granting the privileges with the superuser account to the Test User..and i am sure Test user is the user_name not the user_login_name. .

Note : i have tested with the superuser account also,but still it's giving 0 results.

Thanks&Regards

Hari

bacham2

OK, so the problem is not the DQL nor user privileges. Your problem is on the WDK/Webtop.

If you want people to help you, you need to clarify your posts a little bit. Unfortunately I'm not a WDK expert so I can't help you further.

One last thing: the GRANT statement doesn't return any results but it still works. Go and check the privileges after you issued the command and you should see that they were modified.

titlebar.jsp.txt

the_lord

WDK code might not have any problem as you mentioned in your post :

we have three envronments dev ,qa ,production. in dev the report(all my queries returning data into the datagrid on webtop level) is working fine.in qa and prod is not getting data.it's showing 0 results.

So I will also suggest you to verify the privileges or acls once more.

Also there is possibility that in dev many times user_name and user_login_name are same but in QA and Prod they might be different.

so please verify that the when you query the user_name = '%s', "%s" is replaced by user_name and not user_login_name.

NewWindow_Hexaware_ey

Hi Lord,

Thanks for your quick reply.

Test_user account in dev doesn't have any exeteneded privileges also,and cliet capability is :Contributor.but here the report(query) is working fine.

both user_name and user_login_name are same for supersuer account in dev,qa,production.

Test user account user_name and user_login_name are different in all environments.

Any have In dev environment the query(report)is giving results for both test user and superuser.

My ACl info

ACL providing Relate access to ECS Users
Is internal -F
class:public

we have assigned Myuser Default Acl in my all environments.

following are the permissions in all environments..

all CSusers:relate
dm_world :none
admingroup:delete
dm_owner delete

I have verified all permissions and privileges,i didn't find any thing odd.

Please suggest if any idea to solve my prob.

Thanks&Regards.

Hari

NewWindow_Hexaware_ey

Thank you so much for all of your participation.

finally,we have achieved the solution.

I have combined three queries in sql server.after that created a view(general_reports_vw) for that single query.

I took the required columns from view(created in sql server) and the below query is inserted in the register table.

select user_name, access, days from dm_dbo.general_reports_vw order by user_name.

Query wrote in sql server:
==========================

Now the query is getting results very faster.

once again thanks for everyone.