SQL-007: clsAppInfoCollection.RetrieveSQL

This query returns the page of matching App_Info rows for an audit/info search. It supports date filtering, optional date ranges, machine and application filters, user filtering, full-text search over message/data text, and a built-in exclusion for EPAY and DirectDeposit application names.

Classification

Field	Value
SQL ID	SQL-007
Operation type	Read
Execution method	Inline
Database	SQL Server
Schema	[unqualified]
Command type	Text
Business object	clsAppInfoCollection
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve (DataRequest1)

Parameters

Parameter	Direction	Type	Max length	Description
@CreateDate	Input	DateTime	N/A	Exact-date filter when a single create date is supplied instead of a range.
@MachineName	Input	String	50	Machine-name filter value.
@Username	Input	String	50	User-name filter value used for LIKE matching or paired with a generated IN (...) branch.
@AppName	Input	String	50	Application-name filter value.
@SEARCHTEXT	Input	String	100	Full-text predicate assembled by GetSearchValue().

Tables

Table	Access	Columns used
App_Info	SELECT	App_Info_Seq_Num, App_Name, Create_Date, Data_Text, Environment, Machine_Name, Message_Text, Page_Name, Purge_Date, User_Name

Sample SQL

SELECT TOP 300
    App_Info.App_Info_Seq_Num as AppInfoSeqNum,
    App_Info.App_Name as AppName,
    App_Info.Create_Date as CreateDate,
    App_Info.Data_Text as DataText,
    App_Info.Environment as Environment,
    App_Info.Machine_Name as MachineName,
    App_Info.Message_Text as MessageText,
    App_Info.Page_Name as PageName,
    App_Info.Purge_Date as PurgeDate,
    App_Info.User_Name as UserName
FROM App_Info
WHERE cast(cast(App_Info.Create_Date as varchar(11))as datetime) = @CreateDate
  and App_Info.Machine_Name = @MachineName
  and App_Info.App_Name = @AppName
  and App_Info.User_Name like @UserName
  AND CONTAINS((App_Info.Message_Text,App_Info.Data_Text),@SEARCHTEXT)
  and not (App_Info.App_Name in ('EPAY','DirectDeposit'))

Operations and processes

• Search application info logs
• Normalize free-text search terms

Business rules

• BR-001: Search results are capped at 300 rows
• BR-002: All sentinel suppresses selected filters
• BR-003: Semicolon-delimited filters expand to SQL IN lists
• BR-004: Search text is normalized for full-text queries
• BR-008: Audit searches exclude EPAY and DirectDeposit

Sources

• Application\SAIF.Application.LogView\clsAppInfoCollection.vb