Highlighted

get info from DA SQL DB

Hello all,

If I want to have an overview of Case Name, Date created, Owner, Items on hold for DA cases which have Legal Hold set, can that be easily done using an SQL query?

Does anyone have such a query, and is willing to share?

Thanks.

Regards. Gertjan
1 Solution

Accepted Solutions
Accepted Solution!

Re: get info from DA SQL DB

Not exactly what you are after but should get you going in the right direction:

SELECT SearchID,

            CaseID,

            NumHits,

            PrincipalName AS ModifiedBy,

            tblIntSearches.Name AS SearchName,

            tblIntSearches.ModifiedDate AS DateModified,

            CreateDate,

            tblStatus.[Name] AS SearchType,

            CreationType.[Name] AS CreationType,

            SampleResultSize,

            NativeQuery,

            NativeLegacyQuery,

            XMLText

FROM tblIntSearches

      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[ModifiedByID] = tblPrincipal.[PrincipalID]

      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

WHERE tblIntSearches.[StatusID] = 858 AND PrincipalName IS NOT NULL

UNION ALL

SELECT  TypeID AS SearchID,

            tblAudit.CaseID,

            NumHits,

            PrincipalName AS ModifiedBy,

            tblIntSearches.Name AS SearchName,

            AuditDate AS DateModified,

            CreateDate,

            tblStatus.[Name] AS SearchType,

            CreationType.[Name] AS CreationType,

            SampleResultSize,

            NativeQuery,

            NativeLegacyQuery,

            XMLText

FROM tblIntSearches

      INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID

            AND tblIntSearches.StatusID = 858

            AND tblAudit.AuditTypeID = 1052

      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]

      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

ORDER BY CreateDate DESC

For the best O365 Reporting Solution click here Radar-Reporting

View solution in original post

1 Reply
Accepted Solution!

Re: get info from DA SQL DB

Not exactly what you are after but should get you going in the right direction:

SELECT SearchID,

            CaseID,

            NumHits,

            PrincipalName AS ModifiedBy,

            tblIntSearches.Name AS SearchName,

            tblIntSearches.ModifiedDate AS DateModified,

            CreateDate,

            tblStatus.[Name] AS SearchType,

            CreationType.[Name] AS CreationType,

            SampleResultSize,

            NativeQuery,

            NativeLegacyQuery,

            XMLText

FROM tblIntSearches

      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[ModifiedByID] = tblPrincipal.[PrincipalID]

      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

WHERE tblIntSearches.[StatusID] = 858 AND PrincipalName IS NOT NULL

UNION ALL

SELECT  TypeID AS SearchID,

            tblAudit.CaseID,

            NumHits,

            PrincipalName AS ModifiedBy,

            tblIntSearches.Name AS SearchName,

            AuditDate AS DateModified,

            CreateDate,

            tblStatus.[Name] AS SearchType,

            CreationType.[Name] AS CreationType,

            SampleResultSize,

            NativeQuery,

            NativeLegacyQuery,

            XMLText

FROM tblIntSearches

      INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID

            AND tblIntSearches.StatusID = 858

            AND tblAudit.AuditTypeID = 1052

      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]

      INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]

      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]

ORDER BY CreateDate DESC

For the best O365 Reporting Solution click here Radar-Reporting

View solution in original post