Does SEC database contains information on alert's detection type? i.e to distinguish between on-access and scheduled scan's alerts

Question

I can find very little information on SEC to distinguish if an alert has been triggered by an on-access scan or by a scheduled scan. 
 I know that Sophos has this knowledge as I can find this information on the email alerts that are sent by SEC.i.e scan: on-access or Daily, .... 
 But I'd like to be able to retrieve this information also from the SEC logs/database to build use cases for the SIEM on top of it. 
 Thanks

thatguy_dave · Answer

You can look at the username of the reported event, to some extent - as scheduled scans will never have an actual user reported, it will always be NT AUTHORITY\SYSTEM as the user with a scheduled scan; since this is the use the Sophos EP engine runs as. It isn't necessarily guaranteed - but it is relatively accurate. 
 You do however need to be weary that some actions, even those performed by a user will be performed only by SYSTEM (particularly with automatic actions). The Sophos EC records events as a set of actions performed by the endpoint, from detection to cleanup at every phase typically. This would typically see the first event, being the threat detection having the actual username of how the threat was detected. Subsequent events for that threat instance however you cannot guarantee what user performs them, as it varies by configuration and who triggered the cleanup. There is no guarantee either that there will only be a detection then a cleanup - I have seen cases of more than 1600 events for a single threat before (typically when the behavioural engine sees the threat, but there is no virus definition for the actual threat). 
 The way I typically work with this scenario is I perform a "partition by" threat instance ID, grabbing only the first and last events. This gives me two rows per instance, irrespective of the number of intermediary events. The first event for an instance has the user detecting the threat (e.g. user/scheduled scan), whilst the last event contains the final result of that threat (e.g. whether it was cleaned and by who - automatic or by a user). This is very database intensive however (I work with typically 20 GB datasets consisting of millions of rows and database clusters), and you would need some experience building such SQL queries as they are not easy. OVER xxx PARTITION BY xxx are MUCH less database intensive than GROUP BY, so look into this method - it is very powerful (provided you are on SQL 2012+, since prior versions are very limited). Make sure you have a decent TEMPDB for the database however, since these actions are very heavy on it when using the reporting interface with a big dataset. 
 So finally to answer your question, whilst the data is not *specifically* in a neat column, you can perform the interpretation relatively easily by simply analysing the data stored and build a truth table of what the possible values could be and under what conditions. Dig around in the actual tables (not just reporting interface, as that information is rarely useful IMO) and understand the stored procedures, views and functions to determine what fields are updated and when. Use a copy of a real database instance (don't do this work on the prod one!) and do some digging and analysis - the data is there, but the meaning is often up to you to build. I for one can say that the data available in the database is less-than-desirable in numerous ways, but I suspect will never be improved in the EC prior to some move to an on-premise cloud solution, if and when this may be.