SQL Query

[San_Bangalore]

Hi

I am looking for a SQL query which can display the date and timing of retention files in the vault (For the list of vault). We are using EV- 10.0.4. By this I want to see whether we have latest transcript files or not.

[AndrewB]

what do you mean when you say "latest transcript files" ?

[San_Bangalore]

I mean the files which have been moved to these Vault.Let us say for Example

I have a vault name called “A” and will be doing the journaling items at a regular intervals. If at all I don’t receive files to vault, I would not know until I go ahead a check. So I am looking basically like this

Vault Name - Last Item received Date- Time

[AndrewB]

try this:

USE EnterpriseVaultDirectory
SELECT A.ArchiveName "Archive Name",
       IV.YoungestArchivedDateUTC "Last Archived Date"
FROM Archive A, IndexVolume IV
WHERE IV.RootIdentity = A.RootIdentity
  AND IV.YoungestArchivedDateUTC <= DATEADD(MM, -3, getdate())
ORDER BY A.ArchiveName

[San_Bangalore]

I tried this, but not getting the expected results what I am looking for sorry.

Let me explain in a better way. We have a journaling set for application “A”. On regular intervals we receive journal from it vault. For example every 60 minutes.  By running the SQL query I want to check whether vault “B” has latest files moved to this. Now the time is 10:55 am EST.  When I run this now, it should display latest files in that vault.

[San_Bangalore]

Currently what we are doing is going to search.asp and selecting the vault whether we have received the latest items to it or not. This I want to avoid and make it as automatic

[AndrewB]

i don't understand the terminology you're using to explain your requirements but maybe this is more in the right direction? it will show you how many items have been archived per hour for your Vault store database.

select "Archived Date" = left (convert (varchar, archiveddate,20),14),
"Hourly Rate" = count (*),
"Av Size" = sum (itemsize)/count (*)
from saveset
where archiveddate > DATEADD(d, -1, getDate())
group by left (convert (varchar, archiveddate,20),14)
order by "Archived Date" desc

[TonySterling · Accepted solution]

I think instead of using Archived Date you could use Item date.

This will give you the the Date and Time of the last 10 archived items.  You really don't need a WHERE clause.

SELECT TOP 10 IdDateTime
FROM  Saveset
--WHERE IdDateTime > DATEADD(d, -1, getDate())
GROUP BY IdDateTime
ORDER BY IdDateTime desc

[TonySterling]

@San_Bangalore  Were you able to try the above query?
 

[San_Bangalore]

No, getting the below error when I run it against directory DB. Am I doing it right?

Msg 208, Level 16, State 1, Line 1

Invalid object name 'Saveset'.

[TonySterling]

No, you need to run it against your Journal Vault Store database.  :)

[San_Bangalore]

:) Thank you. It worked but here are the things which I need the improvement in the query.I wan the query output in EST time zone. As well as within last 10 minutes, how many transcripts which it has received it should show that also in the next column

[JesusWept3]

Firstly I want to apologise about what i'm about to say here, but the majority of your threads on Connect have been for SQL Queries, theres been no please, no thankyous, little follow ups and further blunt requests when queries don't quite fit an already vague description you've given us

What i would seriously suggest is look back at the queries you've asked for before, see if they already fit what you're looking for, try googling some of the syntax thats being used, especially when it comes to formatting of dates and times and such

But may be most importantly, ask your employer for SQL classes, these queries are obviously an important part of your job and you rely on them for reporting reasons, however connect shouldn't be used as a replacement for professional services, especially when people like Andy and Tony make a living off of providing these services at cost to their own customers

 

[TonySterling]

Sorry mate, but that is not just a simple query you are requesting.  As Alex said above, for that specific of a request you either need to develop it in-house or pay a Symantec Partner to create it for you.
 

You started out above asking for the last item received date and that is what I gave you. 

[San_Bangalore]

Tony thank you for your valuable time.

Jenus - I have mentioned Thank you multiple times, Anyway I take your feed back and inputs as positive.

[GabeV]

Hi San_Bangalore,

As the other guys mentioned, you can search for these functions in the Internet since it's SQL related, and not specific for EV. For instance, if you need to convert UTC to your localtime, try this:

Convert Datetime column from UTC to local time in select statement
http://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement

If you need to need how many items were archived within the last 10 minutes, you need a GROUP BY with a COUNT in your SELECT statement.

Anyway, there are different ways to get what are you looking for. Ask to your DBA or, if you do not have a DBA, please contact a Symantec partner to assist you onsite with this request.

I hope this helps.