/*
	Pentana Audit - find login history v1.0
	
	When a login occurs, a line is written to the [login] table.
	
	This is not quite the full picture though, because not all logins are equal:
	
		- in newer versions, the 'system' user is utilised for service accounts (i.e. the Robot, WebUI),
		  and this user does not cause a login to be registered (individual Web users *are* logged though)
		- in older systems (set up back when the system user wasn't available) the Robot and WebUI identities would have been 
		  allocated to a real Person, and will have left a row in [Login] at each start.  As this user may have any ID or
		  UniqueID, we can't easily exclude in an off-the-shelf query - you need to review the results, check the name and modify
		  the query used.  Normally it will be called something like 'Pentana Robot' or have some other obviously non-human reference
		  
	There are numerous questions you can ask of the Login table, but the most common queries are shown below
	
*/


-- List all logins in the last two weeks (most recent first)
SELECT P.Name, L.StartTime 
FROM [Login] AS L
INNER JOIN [Person] AS P
ON L.PersonID = P.ID
WHERE P.Name <> 'Pentana Robot' AND L.StartTime > dateadd(day,-14,GETDATE())
ORDER BY L.StartTime DESC;


-- List all unique logins in the last 365 days
SELECT DISTINCT(P.Name)
FROM [Login] AS L
INNER JOIN [Person] AS P
ON L.PersonID = P.ID
WHERE P.Name <> 'Pentana Robot' AND L.StartTime > dateadd(day,-365,GETDATE())
ORDER BY P.Name;


-- Show login history for a given person (most recent first)
SELECT P.Name, L.StartTime 
FROM [Login] AS L
INNER JOIN [Person] AS P
ON L.PersonID = P.ID
WHERE P.Name = 'Someone'
ORDER BY L.StartTime DESC;