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