Advisory Examples

Below are a few examples of typical advisories used by VT users. Note, the specific checks will be based upon the processes and procedures within your incubator or accelerator program.

Each advisory requires an SQL report, a ’trigger value’ and a few additional settings. The examples below will illustrate by example.

The Trigger Value (triggerval), will determine whether the results of an SQL report signify a check passing or failing.

To explain further how these work, here is the internal codebase:

// triggerval set to zero.
// if any results are generated from the SQL query, this indicates a pass
// if no results are generated, this indicates a fail
 
if ($advisory->triggerval == 0 && $sql_result_count > 0) {
	// no message - test passes 
	continue;  // check next rule
}
 
// triggerval set to a positive number. This is now a threshold that must be exceeded
// the SQL query generates fewer results than triggerval, indicates a pass
// the SQL query generates at least the same number of results as triggerval, indicates a fail
 
if ($sql_result_count < $advisory->triggerval) {
	// no message - test passes
	continue;  // check next rule
}
 
// if we reach here, one of the above conditions failed - trigger a warning

// add the associated message to the warning strings to be displayed
$warnings[] = $advisory->message;				

SELECT
Companies.title, Companies.dateJoined AS Companies__dateJoined,
Logs.id AS Logs__id, Logs.company_id AS Logs__company_id, Logs.was_meeting AS Logs__was_meeting,
Logs.meeting_type_id AS Logs__meeting_type_id, Logs.log_date AS Logs__log_date,
MeetingTypes.title AS MeetingTypes__title
FROM
companies AS Companies
LEFT JOIN logs AS Logs ON Companies.id = Logs.company_id
LEFT JOIN meeting_types AS MeetingTypes ON Logs.meeting_type_id = MeetingTypes.id
WHERE
Companies.id = ?
AND (
   (Companies.dateJoined > date_sub(now(), INTERVAL 13 week))
   OR
   (Companies.last_eir_meeting > date_sub(now(), INTERVAL 13 week))
   OR
   Companies.priority <> 1
)

Then Triggerval is set to 0.

Strictly you don’t need the Logs and Meeting Types for this example:

SELECT
Companies.title, Companies.dateJoined
FROM
companies AS Companies
WHERE
Companies.id = ?
AND (
   (Companies.dateJoined > date_sub(now(), INTERVAL 13 week))
   OR
   (Companies.last_eir_meeting > date_sub(now(), INTERVAL 13 week))
   OR
   Companies.priority <> 1
)

The logic here is to test the company in several checks:

• If they only joined in the last 13 weeks, they are ok, they will not trigger the warning
• If they have an EiR meeting on the system in the last 13 weeks they are ok
• If they are not priority 1, we don’t need this check

If any of those conditions are met, this query will return the record.

As the triggerval for this test is zero, we are only interested in companies that fail all of those tests, i.e. return no record.

SELECT companies.title AS company, companies.id, GROUP_CONCAT(tags.label) AS tags, COUNT(tags.id) AS tagCount 
FROM companies
INNER JOIN tags_tagged AS tagged ON (companies.id = tagged.fk_id AND tagged.fk_model = "Companies")
INNER JOIN tags_tags AS tags ON (tags.id = tagged.tag_id)
WHERE companies.id = ? 
AND tags.slug IN ('ncsc-for-startups', 'cyber-runway')
GROUP BY companies.id
HAVING tagCount > 1;

Strictly you don’t need all the fields in the SELECT - that was just helping me debug the example.

Then Triggerval is set to 1.

SELECT 
Companies.title,
Companies.dateJoined AS Companies__dateJoined, 
Logs.id AS Logs__id, 
Logs.company_id AS Logs__company_id, 
Logs.was_meeting AS Logs__was_meeting, 
Logs.meeting_type_id AS Logs__meeting_type_id, 
Logs.log_date AS Logs__log_date,
MeetingTypes.title AS MeetingTypes__title
FROM 
companies AS Companies

LEFT JOIN logs AS Logs ON Companies.id = Logs.company_id 

LEFT JOIN meeting_types AS MeetingTypes ON Logs.meeting_type_id = MeetingTypes.id

WHERE 
Companies.id = ?

AND (
(Companies.dateJoined > date_sub(now(), INTERVAL 6 week)) 
OR 
(MeetingTypes.title LIKE '%1 month%')
)

Then Triggerval is set to 0.