Compliance Accelerator Report Listing Searches

Question

I am looking for a report that lists all active searches for each department.&nbsp; Does this exist?

&nbsp;

Thank you.

tonysterling · Accepted Answer

I think this change should get those:

&nbsp;

SELECT SearchID,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CaseID,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NumHits,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PrincipalName AS ModifiedBy,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblIntSearches.Name AS SearchName,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblIntSearches.ModifiedDate AS DateModified,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CreateDate,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblStatus.[Name] AS SearchType,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CreationType.[Name] AS CreationType,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SampleResultSize,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NativeQuery,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NativeLegacyQuery,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; XMLText
	FROM tblIntSearches
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[ModifiedByID] = tblPrincipal.[PrincipalID]
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
	WHERE tblIntSearches.[StatusID] &lt;&gt; 858 AND PrincipalName IS NOT NULL
	UNION ALL
	SELECT&nbsp; TypeID AS SearchID,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblAudit.CaseID,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NumHits,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PrincipalName AS ModifiedBy,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblIntSearches.Name AS SearchName,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AuditDate AS DateModified,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CreateDate,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblStatus.[Name] AS SearchType,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CreationType.[Name] AS CreationType,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SampleResultSize,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NativeQuery,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NativeLegacyQuery,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; XMLText
	FROM tblIntSearches
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND tblIntSearches.StatusID &lt;&gt; 858
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND tblAudit.AuditTypeID = 1052
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
	ORDER BY CreateDate DESC

&nbsp;

tonysterling · Answer

I had a look here:

Available Compliance Accelerator reports

Article:HOWTO95818
			&nbsp;|&nbsp;
			Created: 2014-05-07
			&nbsp;|&nbsp;
			Updated: 2014-06-06
			&nbsp;|&nbsp;
			Article URL  http://www.symantec.com/docs/HOWTO95818

&nbsp;But don't think there is a report that list out searches.

tonysterling · Answer

I created this for DA but it might work, you could try it and let me know if it bombs out.&nbsp; I don't have my lab up just now so if you let me know if it doesn't work I will see about fixing it up for CA.

SELECT SearchID,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CaseID,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NumHits,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PrincipalName AS ModifiedBy,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblIntSearches.Name AS SearchName,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblIntSearches.ModifiedDate AS DateModified,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CreateDate,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblStatus.[Name] AS SearchType,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CreationType.[Name] AS CreationType,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SampleResultSize,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NativeQuery,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NativeLegacyQuery,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; XMLText
	FROM tblIntSearches
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[ModifiedByID] = tblPrincipal.[PrincipalID]
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
	WHERE tblIntSearches.[StatusID] = 858 AND PrincipalName IS NOT NULL
	UNION ALL
	SELECT&nbsp; TypeID AS SearchID,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblAudit.CaseID,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NumHits,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PrincipalName AS ModifiedBy,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblIntSearches.Name AS SearchName,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AuditDate AS DateModified,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CreateDate,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tblStatus.[Name] AS SearchType,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CreationType.[Name] AS CreationType,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SampleResultSize,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NativeQuery,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NativeLegacyQuery,
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; XMLText
	FROM tblIntSearches
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND tblIntSearches.StatusID = 858
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND tblAudit.AuditTypeID = 1052
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
	ORDER BY CreateDate DESC

admin_laura · Answer

Hello and thank you for this query.

It appears this logic returns completed searches but not active searches. &nbsp;Is there a&nbsp;tblIntSearches.[StatusID]&nbsp;number specific for searches currently scheduled?

Thank you.

tonysterling · Answer

Hey mate,

Did this work for you?

TS

Forum Discussion

Compliance Accelerator Report Listing Searches

5 Replies

Available Compliance Accelerator reports

Related Content

Veritas Compliance Accelerator Configuration

Compliance Accelerator searches SQL query

Compliance Accelerator Search

Compliance Accelerator

compliance accelerator RPC searching issue

Recent Discussions

Importing Search criteria using an XML file into CA

Compliance Accelerator Deep Dive On Wednesday Dec. 18th

Is it possible to remove the CLOSED button from an esclation reviewer in CA?

Exclude Items From Search Results

Upgrading Compliance Accelerator to 12.2 from 11.0.1