SQL Query for getting Ad username with ADdomain for all archives

Hello.

We have currently a total "number of active archives" of 69784, according to the Vault Usage Summary Report.

Several countries (represented in subdomains within our Active Directory) are now asking for daily country-based archiving reports. I nearly blew my mind on how to create suitable queries on the sql servers. I can get a result with any archive and any user, but just without the domain part. That makes impossible to sort this on a country-level.

If I check e.g. the permissions of an archive via the GUI, I can see the domainpart in front of the users account name (e.g. usa\john.deere). So the information must be somewhere in the databases. But I could not find out where....

Can  someone help me out ?

Thanks,

 

SK2017-03-23 16_02_03-Einstellungen.jpg

 

 

7 Replies

Re: SQL Query for getting Ad username with ADdomain for all archives

All EV stores for each user is a SID (in the Trustees table of the EnterpriseVaultDirectory database).

Believe it or not, what you see in the permissions dialog (and the other similar dialogs in the VAC) is actually generated via AD lookups on the SIDs at the time the dialog is opened. The string versions are not stored in the database.

We do store domain and username strings associated to each targeted mailbox in the ExchangeMailboxEntry table, so if you're dealing only with Exchange archiving, and you don't have any archives that are not associated to mailboxes, you might be able to make use of the MbxNTDomain and MbxNTUser columns in there. I'd need to know more about the rest of your query to be sure though.

--Chris

Re: SQL Query for getting Ad username with ADdomain for all archives

It might be a wild guess, but can you arrange the users in seperate provisioning groups? Based on some common parameter (OU, Lookup, DL, AD-group) or something? Give them the same policies as used.

I believe there is a query somewhere which then can get you the archiving rate per provisioning group

BElow what I found in my old repository. Probably from one of the more knowledgeable experts here... Change YourVaultStore to the correct name, and change PG Name in the name of the Provisioning Group your reporting

SELECT 
 left(convert (varchar, S.archiveddate,20),10) "Archived Date",
 PTG.DisplayName "Provisioning Group",
 COUNT(S.IdTransaction) "Items Archived",
 SUM(S.ItemSize)/1024   "Size of Items Archived (MB)"

FROM
 YourVaultStore.dbo.Saveset S,
 YourVaultStore.dbo.ArchivePoint AP,
 EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
 EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG

WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND AP.ArchivePointId = EME.DefaultVaultId
  AND EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId
  AND S.ArchivedDate > DATEADD(d, -7, getDate())
  AND PTG.DisplayName = 'PG Name'

GROUP BY left(convert (varchar, archiveddate,20),10), PTG.DisplayName
ORDER BY "Archived Date" DESC

 

Regards. Gertjan
Highlighted

Re: SQL Query for getting Ad username with ADdomain for all archives

Hello & thanks for your reply.

That explains why during my attempts on the SQL servers I only got SID's back....Robot Frustrated

Drove me nearly nuts....

Hmm...maybe I can export the Archive name with SID and the AD accounts from the AD ...and then try to match this in Excel...could be challenging seeng the 69.000 archives...

regards,

 

SK

 

 

 

 

Re: SQL Query for getting Ad username with ADdomain for all archives

somehow, my previous reply got lost in translation..

If it is possible for you to group the country users in seperate provisioning groups, you should be able to run reports (or SQL query) to get the archiving rate per provisioning group.

I used the below some time ago.

SELECT 
 left(convert (varchar, S.archiveddate,20),10) "Archived Date",
 PTG.DisplayName "Provisioning Group",
 COUNT(S.IdTransaction) "Items Archived",
 SUM(S.ItemSize)/1024   "Size of Items Archived (MB)"

FROM
 YourVaultStore.dbo.Saveset S,
 YourVaultStore.dbo.ArchivePoint AP,
 EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
 EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG

WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND AP.ArchivePointId = EME.DefaultVaultId
  AND EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId
  AND S.ArchivedDate > DATEADD(d, -7, getDate())
  AND PTG.DisplayName = 'Provisioning Group Name'

GROUP BY left(convert (varchar, archiveddate,20),10), PTG.DisplayName
ORDER BY "Archived Date" DESC

Regards. Gertjan

Re: SQL Query for getting Ad username with ADdomain for all archives

Lost in translation probably....

If you can seperate the users in provisioning groups, you can use below query to get the archiving rate per provisioning group. I used that a while ago.

SELECT 
 left(convert (varchar, S.archiveddate,20),10) "Archived Date",
 PTG.DisplayName "Provisioning Group",
 COUNT(S.IdTransaction) "Items Archived",
 SUM(S.ItemSize)/1024   "Size of Items Archived (MB)"

FROM
 YourVaultStore.dbo.Saveset S,
 YourVaultStore.dbo.ArchivePoint AP,
 EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
 EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG

WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND AP.ArchivePointId = EME.DefaultVaultId
  AND EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId
  AND S.ArchivedDate > DATEADD(d, -7, getDate())
  AND PTG.DisplayName = 'Provisioning Group Name'

GROUP BY left(convert (varchar, archiveddate,20),10), PTG.DisplayName
ORDER BY "Archived Date" DESC

Regards. Gertjan

Re: SQL Query for getting Ad username with ADdomain for all archives

Something else you might try is using PowerShell to run the SQL query. Then when you get the results back, you can have it try to translate the SIDs into System.Security.Principal.NTAccount objects, which will yield values like "DOMAIN\Username". From there it's only a bit of string parsing to separate the domain and user parts, and from there you can either:

  • output it all in a nice PowerShell table (Format-Table cmdlet), grouping by the domain name
  • export it to CSV (Export-CSV cmdlet) and slice and dice it in Excel

I have attached an example PowerShell function that could be a decent starting point. The part that translates the SID to the NTAccount is on line 51. Example output:

Capture.JPG

 

 

Re: SQL Query for getting Ad username with ADdomain for all archives

Hi

If you are looking for converting SID to AD objects (user/group..etc) with your existing queries then you need to create/include temporary function as there is no inbuilt SQL function for similar work. May be you can review following for such use cases.

Archive permission using SQL queries. https://vox.veritas.com/t5/Articles/Script-to-know-AD-permission-assigned-on-Archives-Folders/ta-p/8...

SID to AD objects conversion using SQL query https://pradeeppapnai.com/2017/01/24/sidconvert/

Regards

Pradeep Papnai

Regards

Pradeep Papnai