How to determine frequency and use of Objects?

jaja_bings

Dear forum members,

we have NAV2009R2 and I want to collect informations about our use of our developments and how (often) parts of the nav system are used (and not used).

For that I think about to use the SQL Server to analyze the frequency and use of all kind of NAV Objects.

Did someone of you something in the history?

What I tried for the first time is the following:

• Take the NAV Easy Security Sql Profiler Script fpr SQL Server 2005 and log the (entire) db traffic.
• Try to analyze the big amount of data to determine what kind of object (and maybe what is the access type like read, write, execute)

I stuck at the point to analyze the big amount of data.

Any suggestions?

Best regards,
jaja_bings

evandeveerdonk

Hi Jaja,

I am a bit struggling with the "frequency of use" issue. If you have a monthly procedure, the frequency is low, the year-end procedures will even have a lower frequency, but they are still vital for NAV. So it's very hard I think to put any meaning to that number. (Although I can imagine that you want to see if certain customer objects are still used.

I had a customer who wanted to know how often certain reports and forms were executed by which users. So I build a simple function to create an entry in the "Time register" table to record the use of certain objects.

Perhaps that will help you

Ernst

jaja_bings

Dear Ernst,

thank you for your answer.

I am a bit struggling with the "frequency of use" issue

It seems to me that my explaination was not so great :oops:

The aim is to determine what is in use and in the second step how often.

If you have a monthly procedure, the frequency is low, the year-end procedures will even have a lower frequency, but they are still vital for NAV.

You are right, but I am aware of it.

So I build a simple function to create an entry in the "Time register" table to record the use of certain objects.

Could you give me (and the other forum members) an insight, how you did that?

Best regards,
jaja_bings

evandeveerdonk

Sure:

In the table User Setup (but this could be anywhere, I created this function:

LogReportUsage(Report_pCode : Code[10])

IF UserTimeRegister_lRec.GET(USERID,WORKDATE,Report_pCode) THEN BEGIN
UserTimeRegister_lRec."No. Of Times" += UserTimeRegister_lRec."No. Of Times" + 1;
UserTimeRegister_lRec.MODIFY;
END
ELSE BEGIN
UserTimeRegister_lRec.INIT;
UserTimeRegister_lRec."User ID" := USERID;
UserTimeRegister_lRec.Date := WORKDATE;
UserTimeRegister_lRec.Code := Report_pCode;
UserTimeRegister_lRec."No. Of Times" := 1;
UserTimeRegister_lRec.INSERT;
END;

(As you can see, I expanded the primary key of the Time Register Table and I added a Report counter: "No. of Times")

In every report I wanted to register, I added this code:

UserSetup_lRec.get(USERID);
UserSetup_lRec('S-001'); // S-001 us the report no.

This records on what days, and how often during a day, a user starts this S-001 report

Btw. I always try to give my reports a Customer facing number, like S-001 dor a sales report. this speeds up communication with users

Hope this helps you

jaja_bings

Dear Ernst,

that is a good solution, but resolves not exactly my (complicated) requirements.

I want a overview about all used objects and I do not want to edit every object to log the use.

So that is the reason for my approach via the SQL Server and the SQL Profiler. There should(?) recorded all executed/called objects. So I assume that is the reason that tools like Easy Security take this captured information to extract/analyse the used objects to create User Rights.

What I do:

• Capture the data via SQL Profiler
• Export to XML
• Import into Excel
• Set for example some text filter like Contains -> exec sp_execute *,*,'',*

I know there could better search criterias, but for the simple approach we will take this one.

You get a filtered list, where you have entries like

exec sp_execute 91,1,'',92129

First interesting parts are

• after the 1 comma -> Type of Object (for example 1 Table, 2 Form, 3 Reports?, 5 Codeunit)
• after the last comma -> ID of Object, here a end user specific table id

can search for.

I know that there are no information about read,write and so on in this filtered set. But the information is in the other lines in more complex statements.

Maybe it is no better to understand, what is my aim?

Best regards and a happy weekend,
jaja_bings