Audit of existing queries, reports, dashboards

Milena Damianova

In a period of transition on the team I was tasked with doing an inventory of reports, queries, lists, dashboards, etc. that we have accumulated in RE over 10+ years, most of these not used for 2+ years and created by staff long gone. How can I export the complete list of queries, reports, etc. with their create date, create user, modify date, modify user, last run date, last run user. Any best practices around archiving old reports? We use the hosted environment so SQL unfortunately is not an option (from my understanding). Thanks in advance. 

 

Dennis Ladnier

Milena, I had a good solution until I read you were hosted (as you noted, no SQL access) [insert sad face]

JoAnn Strommen

For Query, Reports and Export you can right click on the list in a folder and export the list to excel.  Have all the column you want visible and they will export. 

Downside is you have to do it for each folder so you may want to name file with type and folder name.  And Reports does not have a last run date. To me issue is that name really doesn't tell much either. 


NOTE: For Query the last run date may not be accurate.  I was very surprised to find when I've gone in and run predecessors queries the date does not update UNLESS I make a change to something in the query.  

I'm looking at a similar clean up project in the future - will be tough because most have name only, no description. I have hundreds that based on last date run are over 5 years old.  BUT I found that one report admin assist uses several times a week is one of those 'old' queries.  It was set up for her to run and date does not change as she makes no changes to it. I'd be in big trouble if I had deleted it. 


Don't think it works for Dashboards. You can certainly do screen shots of ones you can't export grid for. 


I've been thinking about asking all users to Save As the queries they are using so they will be created by current staff with current date but I know some are only used once a year for reports. 


Will be following your post to see if anyone has some good suggestions.

Alan French

dennis ladnier‍ would you mind sharing your solution, for the benefit of those of us who do have SQL access? Our queries and reports are in desperate need of pruning after 6 years.

 

JoAnn Strommen:


NOTE: For Query the last run date may not be accurate.  I was very surprised to find when I've gone in and run predecessors queries the date does not update UNLESS I make a change to something in the query. 

I've found the same is true of imports - I have one that I run in validation mode every week yet it has a 'last run' date of 2 years ago. I've put a huge note on it saying "THE DATE LAST RUN IS INACCURATE - DO NOT DELETE" just in case anyone gets any ideas about doing some spring-cleaning! ?

Rachel Cavalier

I've been thinking about this, even though we've only been using RE for a little over a year. So far, I've been thinking that at some point, armed with a detailed list of all the queries and what folders they're in, I'll then move the ones that don't seem to get used into a "To Delete + date" folder to just...hang out there for a while and then, after enough time where no one has said "oh hey, where's that query I was using for this thing?" it'll be deleted. I suspect it might have to hang out in that pending delete folder for a year, just to make sure.


When we do it, we'll let all our users know that this is happening and what to do if one of the queries they use sporadically has been marked for deletion.

JoAnn Strommen

LOL - don't know about your office. I'd need to be ready for dealing with frustrated staff when their report/export/queue/other process doesn't run because query can't be found. 


I be sure to let them know you're not deleting it, just moving it and it can be moved back. 

Rachel Cavalier

Haha oh for sure! An email maybe a month before I think about doing it, to encourage them to delete some of their own queries that they know can go. Another a couple of weeks later to remind them and then a few when the deed's done!


Handily, there's that box at the bottom that says what reports and exports use the query, which will help and I expect I'm going to be picking a "volunteer" to go through the queries in their team's area.

Tatyana Leifman

Be careful with that box--unfortunately, it only shows the report or export that uses that query at that moment.  That is, those where the query is currently listed as "use selected records."  If you have an export or a report which regularly uses several different queries, only the last one used will show which report/export it is used in.  The other queries that go with that export/report will show as not being used in anything.  I've found it quite disappointing as sometimes I find the query I built for some export that was requested from me ages ago but then I spend an inordinate amount of time checking which export it was used in, if I used an existing export rather than built a specific one just for that query.


Tatyana