Hours of activity report

[eswbitto]

Hey fcorti,

I'm having a bit of a time trying to edit the query to this report.

document_hours_of_activity


The reason that I think this report is wrong is because of the way alfresco writes their entries.

For example…when you look at the table dm_dim_documents it will show you all the files that have been created in the repository.

However there are some files that are actually core files from the alfresco install that were never actually uploaded in my test environment.

To test the accuracy of the reporting I had created two sites with one site having two files and the other site having like 21 files. The numbers were low so I could evaluate the reporting.

When I run this report I get a result of a time period where like over 225 documents were created and I know that is false.


My issue is trying to actually filter out what I want to get rid of from the report. Any ideas?

[fcorti]

Hi ESWBitto,

You are right!
The report is developed on the whole repository, working documents comprised and your need is to filter out all that documents in some way.

There is no a right or wrong answer but there is the relevant business question for your need.
So…
Do you want to develop a report filtering only the documents in the sites?
Do you prefer to filter out the documents in a specific folder '/Data Document/…' for example?

Depending on your strategy and your need we could start developing a brand new report (starting from the one you mentioned).
Again (like you did in the past): once you will have developed a brand new report, please consider to share it with the community… 😉

Let us know how it goes.

[eswbitto]

Hey fcorti,


I was playing around with the query yesterday and tried to just filter out file extensions that I know would not be necessary. I came to the conclusion that that solution is too tedious. lol

So I think an approach where anything in the sites' document library would be what is counted. The problem I'm having though is that there is not really a way that I see would be a viable solution. Do you have a suggestion of the approach I should take? Here's the code for the query so you can see what I've been doing.

SELECT
 dm_dim_hours.id,
 dm_dim_hours.desc,
 CONCAT('From ',TRIM(dm_dim_hours.desc),':00 to ',TRIM(dm_dim_hours.desc),':59') AS desc2,
 COUNT(creation_minute_id) AS num
FROM
 dm_dim_hours 
 JOIN dm_dim_minutes ON dm_dim_minutes.hour_id = dm_dim_hours.id
 LEFT JOIN (
  SELECT
   dm_dim_documents.creation_minute_id AS creation_minute_id,
   dm_dim_paths.path AS yoface,
   dm_dim_documents.name AS myname
  FROM
   dm_dim_documents,
   dm_dim_paths
  WHERE
   dm_dim_paths.path NOT LIKE '%surf_config%' AND
   dm_dim_paths.path NOT LIKE '%system%' AND 
   dm_dim_paths.path NOT LIKE '%user_homes%' AND 
   dm_dim_paths.path NOT LIKE '%swsdp%' AND
   dm_dim_paths.path NOT LIKE '%Unknown%' AND 
   dm_dim_paths.path NOT LIKE '%sites%' AND
   NAME NOT LIKE '%.ftl' AND
   NAME NOT LIKE '%.sample' AND
   NAME NOT LIKE '%.xml' AND
   NAME NOT LIKE '%.js'
  UNION ALL
  SELECT
   dm_dim_folders.creation_minute_id AS creation_minute_id,
   '' AS yoface,
   '' AS myname
   FROM
   dm_dim_folders) t ON t.creation_minute_id = dm_dim_minutes.id
WHERE
 dm_dim_hours.id >= 0
GROUP BY
 dm_dim_hours.id,
 dm_dim_hours.desc 
ORDER BY
 dm_dim_hours.id ASC
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[fcorti]

Hi ESWBitto,

You are doing a good job!
Just to share some opinions, why do use the "NOT" approach instead of the direct one with  dm_dim_paths.path LIKE '%/sites/%'?
If I understand correctly, your need is to have all and only the site documents… so, why not to select only those?
Feel free to discuss on it.

In the past I have received many contacts asking for a "report that lists the documents in the sites".
Let's define it and we will add to the next AAAR release… if you will want. 😉

[eswbitto]

Hey fcorti,

That is actually where I need help with. I've written it several different ways and got different results.

Using :

dm_dim_paths.path LIKE '%sites%'
dm_dim_paths.path NOT LIKE '%sites%'
‍‍‍‍

It gives different results, but the count is really large. To me I should be seeing a count where they total up to 24. That's how many files I have put in.

I'm getting results of like 2400 or 643…etc. To me that just means that its counting alfresco install files that are in the dm_dim_documents.name or dm_dim_paths.path. I think it could also be counting files twice. I'm not sure.


And yes you understand me correctly. I only want to count documents in the document library from a site and nothing else.

[fcorti]

Hi ESWBitto,

I suggest you to isolate the query that lists the documents from the whole one so you will have the chance to see the duplications and the "wrong" ones.
From a first view, I can see you include in the results also the folders, and I think you don't want them.

I hope this help you.