Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

Report on searches performed for any/all DA reviewers needed

Created: 14 Feb 2013 • Updated: 15 Feb 2013 | 9 comments
This issue has been solved. See solution.

I'm a bit stuck at the moment but still looking for the answer myself (though I need one by tomorrow morning).  I've just been asked to produce a report (or output - CSV or any format should be acceptable) on all of the searches run in DA by a particular user.

It looks like the built-in DA reports contains an option for type "Searches" but when I go to add a value, only the searches conducted under the account I am logged in as (the Vault Service Account) are returned.  I don't understand why the Vault Service Account, which I've given every possible persmission, can't add the searches from all other DA users that have been conducted.  I would imagine if I could log in as the user I need to report on (which I cannot, by the way), I would be able to generate a report on their searches.

Comments 9 CommentsJump to latest comment

TonySterling's picture

This might give you what you want, you can remove columns you don't need.

Runs against the DA Customer database.

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
 

BigAnvil's picture

Thanks for the info Tony!  If I only want it to return results for this one reviewer/user, how would I go about doing that?  Sorry, I'm not a SQL guy at all.

BigAnvil's picture

I guess I should mention, I do really need it to be in some kind of "report" output.

BigAnvil's picture

Tony, I hope my previous replies didn't come off as thankless - I certainly appreciate the query you provided.  I just know that someone who doesn't understand EV is going to need to look at it and make sense of it, and I'm no EV genius either (you can tell by most of my previous posts) - I'm really just an admin guy who has no opportunity to become an expert at anything because I'm told to do too many other things... I know we can all appreciate that!

BigAnvil's picture

I supposed another question I just realized needs to be answered is, will the result returned include searches that were run but where the results were not accepted or searches that were done within research folders rather than cases?  Meaning, is the output all-inclusive or will certain types of searches not show up in the output?

Sorry to hammer you with so many questions/posts.  I just know I will be asked these very same things... Thanks!!!

TonySterling's picture

So I will try to answer your questions in one post.  :)

This query will find searches that were created in the case or research folder, that is what the Creation Type will tell you.  Department = Case, Folder = Research Folder.

It will also give you searches that were not accepted.

Sorry, I can't put this in a pretty format but it basically gets the job done.

I have changed it up a bit to allow you run it for just one person.  You need to update 'YourPrincipalName' with the PrincipalName of the person you want.

SELECT SearchID,
            tblintsearches.StatusID AS 'Statua ID',
            tblStatus.Name AS 'Status',
            tblcase.Name AS 'Case',
            NumHits,
            PrincipalName AS ModifiedBy,
            tblIntSearches.Name AS SearchName,
            tblIntSearches.ModifiedDate AS DateModified,
            tblIntSearches.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.[StatusID] = tblStatus.[StatusID]
      INNER JOIN [tblCase] ON tblcase.CaseID = tblintsearches.CaseID
      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
WHERE  PrincipalName = 'YourPrincipalName'      
UNION ALL
SELECT  TypeID AS SearchID,
            tblintsearches.StatusID AS 'Statua ID',
            tblStatus.Name AS 'Status',
            tblCase.Name AS 'Case',
            NumHits,
            PrincipalName AS ModifiedBy,
            tblIntSearches.Name AS SearchName,
            AuditDate AS DateModified,
            tblintsearches.CreateDate,
            tblStatus.[Name] AS SearchType,
            CreationType.[Name] AS CreationType,
            SampleResultSize,
            NativeQuery,
            NativeLegacyQuery,
            XMLText
FROM tblIntSearches
      INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID
            AND tblAudit.AuditTypeID = 1052
      LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]
      INNER JOIN [tblStatus] ON tblIntSearches.StatusID = tblStatus.[StatusID]
            INNER JOIN [tblCase] ON tblcase.CaseID = tblintsearches.CaseID
      INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
WHERE  PrincipalName = 'YourPrincipalName'
ORDER BY CreateDate DESC

BigAnvil's picture

Again, thank you very much for your help with this.  This will have to do for now.

Is it just a function of creating a report like this in SQL Reporting in order to have it available from within the Reports option in the DA console?

BigAnvil's picture

So, the big question is - how to I award you a bajillion points?  cheeky