05_filters.txt

05_filters.txt
// Communication between the Squirro iFrame and the Squirro endpoint is done via HTTP GET. This literally means that
// a logic has to be implemented to create a query string and potential other parameters (e.g. year)which can then be 
// submitted to Squirro by the iFrame. If the "read" tab is enabled in the iFrame the query string sent to Squirro can be seen. 

// Ticket priority filter
// If at least one ticket priority is selected the corresponding query string for Squirro is concatenated together
SET vPriorityFilter =
    if (GetSelectedCount(priority) > 0, '(' & concat(distinct 'priority:"' & priority & '"', ' OR ') & ')');
// example: (priority:"P1" OR priority:"P2")
	
	
// Ticket status filter
// If at least one ticket status is selected the corresponding query string for Squirro is concatenated together
SET vStatusFilter = 
    if (GetSelectedCount(GeneralStatus) > 0, '(' & concat(distinct 'GeneralStatus:"' & GeneralStatus & '"', ' OR ') & ')');
// example: (GeneralStatus:"Open" OR GeneralStatus:"Pending")
	
	
// severity filter
// If at least one severity is selected the corresponding query string for Squirro is concatenated together
SET vSeverityFilter =
    if (GetSelectedCount(GeneralSeverity) > 0, '(' & concat(distinct 'GeneralSeverity:"' & GeneralSeverity & '"', ' OR ') & ')');
// example: (GeneralSeverity:"Critical" OR GeneralSeverity:"High")

// Reflect time slicing (by year) from the dashboard to the Squirro items displayed in the iFrame
// year created_after filter
// example: 2008-01-01
SET vCreatedAfter = if(GetSelectedCount(ts_year) > 0, Min(ts_year) & '-01-01');

// year created_before filter
// example: 2010-12-31
SET vCreatedBefore = if(GetSelectedCount(ts_year) > 0, Max(ts_year) & '-12-31');
	
	
	
// Dashboard version WITHOUT the possibility to choose smart filter. This version would simply react to the choice
// of "General Status", "General Severity", "Priority" and "Year". 
// Note: the GET query string visible in the "read" tab of the iFrame does not show the "created_before" and "created_after" values. 
// those are passed to Squirro in the background as additional GET parameters
// "vQuery" expression must be set in the properties of the Squirro iFrame for the "Query" field
// In this simple case the query filter is putting together the URL string 
// IF is a priority chosen, put the priority string there
// IF in addition to a priority severity OR status is also chosen, put "AND" there as those items will be concatenated
// IF severity is set, put the severity string there after the "AND". IF NOT, move on. 
// IF severity is set AND status is set, put "AND" there as those items will be concatenated
// put status string behind the AND
SET vQuery =
    if (Len($(vPriorityFilter)) > 0, $(vPriorityFilter)) &
    if (Len($(vPriorityFilter)) > 0 AND (Len($(vSeverityFilter)) > 0 OR Len($(vStatusFilter)) > 0), ' AND ') &
    if (Len($(vSeverityFilter)) > 0, $(vSeverityFilter)) &
    if (Len($(vSeverityFilter)) > 0 AND Len($(vStatusFilter)) > 0, ' AND ') &
    if (Len($(vStatusFilter)) > 0, $(vStatusFilter));
// example: (priority:"P1" OR priority:"P2") AND (GeneralSeverity:"High") AND (GeneralStatus:"Open")


// Dashboard version WITH the possibility to select SmartFilters with STATIC NoiseLevel. 
// This version would react to the choice of "General Status", "General Severity", "Priority", "Year" and "SmartFilter".
// Note: the GET query string visible in the "read" tab of the iFrame does not show the "created_before" and "created_after" values. 
// those are passed to Squirro in the background as additional GET parameters
// Per selected SmartFilter each item matching the static "vThreshold" (see 00_main.txt) rating will be concatenated
// into the Squirro GET query string. 
SET vTicketFilter =
	//1st part of LEFT function -> ensuring that GET query string does not exceed maximum URL length
    left(concat({$<NoiseLevel={'<=$(vThreshold)'}>} distinct '$external_id:' & id, ' OR ', NoiseLevel), 
	
	//2nd part of LEFT function
	// count # of results -> IF less than 100
	if(substringcount(concat({$<NoiseLevel={'<=$(vThreshold)'}>} distinct '$external_id:' & id, ' OR ', NoiseLevel), ' OR ')<100,
	
	// THEN take the whole URL string 
	len(concat({$<NoiseLevel={'<=$(vThreshold)'}>} distinct '$external_id:' & id, ' OR ', NoiseLevel)), 
	
	// ELSE  take a maximum of 100 results (Reason: InternetExplorer does not allow more than 2048 characters for a GET URL request)
	index(concat({$<NoiseLevel={'<=$(vThreshold)'}>} distinct '$external_id:' & id, ' OR ', NoiseLevel), ' OR ', 100) -1 )); 
// 	example: $external_id:a11427 OR $external_id:a25659 OR $external_id:a26562
	
	
	
// "vQueryAdv" expression must be set in the properties of the Squirro iFrame for the "Query" field
//
// IF at least one SmartFilter has been selected 
// THEN choose item resulting from vTicketFilter
// ELSE choose from Priority, Severity and Status Filters (see "vQuery" description above)
SET vQueryAdv =
    if (GetSelectedCount(SmartFilter) > 0,
        // item filtering by ticket identifier, only choose the top 100
        $(vTicketFilter),
        // item filtering by Priority, Severity and Status dimensions
        if (Len($(vPriorityFilter)) > 0, $(vPriorityFilter)) &
        if (Len($(vPriorityFilter)) > 0 AND (Len($(vSeverityFilter)) > 0 OR Len($(vStatusFilter)) > 0), ' AND ') &
        if (Len($(vSeverityFilter)) > 0, $(vSeverityFilter)) &
        if (Len($(vSeverityFilter)) > 0 AND Len($(vStatusFilter)) > 0, ' AND ') &
        if (Len($(vStatusFilter)) > 0, $(vStatusFilter))
    );
// example: (priority:"P1" OR priority:"P2") AND (GeneralSeverity:"Critical" OR GeneralSeverity:"High") AND (GeneralStatus:"Open" OR GeneralStatus:"Pending")

	
// Dashboard version WITH the possibility to select SmartFilters with DYNAMIC NoiseLevel slider. 
// This version would react to the choice of "General Status", "General Severity", "Priority", "Year", "SmartFilter" and "NoiseLevel" selection.
// Note: the GET query string visible in the "read" tab of the iFrame does not show the "created_before" and "created_after" values. 
// those are passed to Squirro in the background as additional GET parameters
// Per selected SmartFilter each item matching the selected "vThreshold" rating will be concatenated
// into the Squirro GET query string. 
	
// "vQueryAdvSlider($(vThreshold))" expression must be set in the properties of the Squirro iFrame for the "Query" field
// the selected value of the NoiseLevel slider will be submitted into the vQueryAdvSlider SET expression
	
// instead of the static "vThreshold" -> the dynamic value $1 is evaluated in the vTicketFilterSlider SET expression
SET vTicketFilterSlider =
    left(concat({$<NoiseLevel={'<=$1'}>} distinct '$external_id:' & id, ' OR ', NoiseLevel), 
	if(substringcount(concat({$<NoiseLevel={'<=$1'}>} distinct '$external_id:' & id, ' OR ', NoiseLevel), ' OR ')<100,
	len(concat({$<NoiseLevel={'<=$1'}>} distinct '$external_id:' & id, ' OR ', NoiseLevel)), 
	index(concat({$<NoiseLevel={'<=$1'}>} distinct '$external_id:' & id, ' OR ', NoiseLevel), ' OR ', 100) - 1));
// 	example: $external_id:a11427 OR $external_id:a25659 OR $external_id:a26562
	
	
// IF at least one SmartFilter has been selected 
// THEN choose item resulting from vTicketFilterSlider
// ELSE choose from Priority, Severity and Status Filters (see "vQuery" description above)
	
SET vQueryAdvSlider =
    if (GetSelectedCount(SmartFilter) > 0,
        // item filtering by ticket identifier, only choose the top 100
		// $1 is the first parameter submitted into "vQueryAdvSlider" and contains the selected NoiseLevel for evaluation on selection of tickets 
        $(vTicketFilterSlider($1)),
        // item filtering by dimensions
        if (Len($(vPriorityFilter)) > 0, $(vPriorityFilter)) &
        if (Len($(vPriorityFilter)) > 0 AND (Len($(vSeverityFilter)) > 0 OR Len($(vStatusFilter)) > 0), ' AND ') &
        if (Len($(vSeverityFilter)) > 0, $(vSeverityFilter)) &
        if (Len($(vSeverityFilter)) > 0 AND Len($(vStatusFilter)) > 0, ' AND ') &
        if (Len($(vStatusFilter)) > 0, $(vStatusFilter))
    );
// example: $external_id:a37680 OR $external_id:a51714 OR $external_id:a40506 OR $external_id:a50335