DA Reporting on Search Terms

[c2]

In order to report on the search terms used for searches in EV Discovery Accelerator I was using tblIntSearches.XMLText, but the terms are not included in the XML if the search was 'Deleted' status.

The search terms appear to be in  NativeQuery and NativeLegacyQuery, but the format is not XML.

Is there documentation or examples of how to parse the search terms from those fields for reporting?

Or is there another source for this information?

[Ben_Watts]

Hi C2,

 Not sure if this will be what you are after but DA has reports available that may well suit your needs:-

http://www.symantec.com/docs/HOWTO58649 - Reports in General

http://www.symantec.com/docs/HOWTO58647 - Searches Reports

Not sure EXACTLY what you are asking, is it a case of:-

'Is there a way to find out exactly how DA is parsing the search queries?'

As in, if you put two words in a search for the Subject, are the seen as (Word1 OR Word2) or (Word1 AND Word2)?

[Kenneth_Adams]

Hello, C2;

To add to Ben's information, if you have the 'Save search criteria' option enabled in the Configuration tab's Settings sub-tab, Diagnostics section, you can obtain the search criteria on the DA server in any of 3 files per search.  One of the 3 files is in XML format, with the other 2 in plain text.  Each file name will include the SearchID of the search (i.e., Criteria_1771.txt where 1771 is the SearchID of the search).

If you look in either of the text files, you need to look at the section with the header of 'Native Query'.  This is the section where we put the terms in their logical representation.  For example from my CA server (DA has the same Native Query section and format):

Native Query:
-------------
(((ksqeqbkkkizkleptkiuthor:8 OR ksqeqbkkkizkleptqaecips:8) AND date:[01/01/1970-01/01/2038] AND anum:[anum:0-0] AND (qeaultzkusgklirection:2 OR qeaultzkusgklirection:3 OR qeaultzkusgklirection:1 OR qeaultzkusgklirection:0) AND (ssid:{**} AND NOT qeaultzkxolicykiction:exclude) AND snum:[snum:1-4294967294]) $ ((subj:re OR subj:thank** OR cont:thank**) AND snum:[snum:1-4294967294])) AND NOT (ssid:{MD.**})
#rank  #rankend

This example is for a search that was looking for all messages that hand in their subject or content fields any word beginning with thank or with the subject containing RE:.  So, the part of the above example with 'subj:' shows 2 entries (1 for RE: and one for thank*) and 1 entry with 'cont:'.

I hope this helps.  Let us know if you need anything more.

[c2]

I saw the sample search reports, and it appears that the parsing 'code'  uses string functions in .rdl file.

I was trying to do the translation using SQL or T-SQL, before presenting to SSRS.

Where are the plain text  files you mentioned located?

I was trying to get something like the example posted above from tblIntSearches.NativeQuery, with the criteria like 'xxxx' AND 'yyyy'.  Also, Subject/Content contains terms.

I was using fields in tblIntSearches.

When the search status is 'Deleted' the tblIntSearches.XMLText doesn't see to have the same terms as NativeQuery or LegacyNativeQuery.

Also, the NativeQuery colum doesn't have the boolean terms in text (?).  They seem to be digits (0,6) ?

I was trying to parse that, but it the files you mentioned may be easier to work with, if I can link to them using a query.

auth 0 "xxx yyy zzz"

recp 0 "abc.com"

date 80 [20120516:050000-20380101:000000]

anum 0 0-0

VaultPolicyAction 1 "-EXCLUDE"

snum 0 1-fffffffe

0,6

name 0 "zzzz qqqq -asjasda -asja;sljasjfa -ajsjjsjsj -mmmmm -uuususus"

date 80 [20130901:050000-20380101:000000]

anum 0 0-0

VaultPolicyAction 1 "-EXCLUDE"

snum 0 1-fffffffe

0,5

[Kenneth_Adams]

The search criteria files that are created by the 'Save search criteria' option will be found in the DA installation folder (default on 64-bit OS is 'C:\Program Files (x86)\Enterprise Vault Business Accelerator') in a sub-folder named 'Search Criteria'.  Within this folder will be another folder that is named after the DA Customer.  Insidt that folder should be the 3 files created for each search.

[c2]

I was able to 'parse' some of the search criteria from tblIntSearches.NativeQuery using T-SQL in a table function, but it's not obvious how to determing the conditionals (AND, OR) from NativeQuery.

--"name" 0 "--

SET @string = @DelimitedString

WHILE (LEN(@string) > 0)

BEGIN

IF (CHARINDEX('"name" 0 "',@string)>0)

BEGIN

SET @iToken = CHARINDEX('"name" 0 "',@string)+10

SET @strout  = SUBSTRING(@string,@iToken,@strLen)

SET @iTerminator   = CHARINDEX('"',@strout)

SET @strout  = SUBSTRING(@string,@iToken,@iTerminator-1)

BEGIN

INSERT INTO @tblArray (Element)

VALUES (LTRIM(RTRIM('name: ' + @strout)))

END

SET @string  = SUBSTRING(@string,@iToken+@iTerminator,LEN(@string))

END

ELSE

BREAK

END

Then select/format the results in the SSRS dataset using:

(SELECT

STUFF(

(SELECT Char(10) + Element

FROM dbo.fx_tpg_ParseNativeQuery2(tblIntSearches.NativeQuery,'')

FOR XML PATH(''),TYPE

).value('.','VARCHAR(MAX)'), 1, 1, ''

)

) AS SearchCriteria,