///////////////////////////////////////////////////////////////////////////////////////////
// Netgear Router Logs
// v1.0.0
// 16-03-2019
// Steve Dark @ Quick Intelligence
///////////////////////////////////////////////////////////////////////////////////////////
// Example application showing how logs can be loaded from an IMAP Server and parsed
// to provide a Sense applciation over the received values.
//
// The application has been tested with a number of log entries created by a 
// R7000P Nighthowk router. Further log entry types and logs from other
// routers will require additions or changes to the parsing code below
//
// Provided as a tutorial only and is to be used without any support or warranty
//
// See https://www.quickintelligence.co.uk/netgear-router-logs-qlik-sense for details
///////////////////////////////////////////////////////////////////////////////////////////

// Create an IMAP connection and connecto to it here
LIB CONNECT TO 'SenseMail (qlikcloud_quintelligence)';

// Load the text of a selected set of records
// If you want details of the emails containing the logs uncomment the relevant fields
tmpRouterLogs:
LOAD
	*
    ;
SELECT 
	text     //,
// 	id,
// 	UID,
// 	UID_validity,
// 	UID_id,
// 	inReplyTo,
// 	folder,
// 	to,
// 	cc,
// 	bcc,
// 	from,
// 	subject,
// 	sentLocalTime,
// 	sentOriginalTime,
// 	sentUniversalTime,
// 	html,
// 	size,
// 	flags,
// 	seen,
// 	attachments,
// 	Status
FROM MessagesInFolder
// Define where to find the log entrie emails and what the subject is
// Change this for different router models of router
// Provide since date for incremental load
WITH PROPERTIES (
Folder='Inbox',
MaxNoEmails='',
Search='NETGEAR R7000P Log',
Since='',
Before=''
);


// Parse the los through a number of preceding loads
RouterLogs:
LOAD
	// Add a counter for our measure
	1 as LogCount,
	*,
    // Move text that does not fit into other fields into an Other dimension
    if(Match([Log Type], 'Initialized', 'email failed', 'email sent to', 'USB device attached', 'USB device dettached') > 0, [Message Body],
    	if([Log Type] = 'DoS attack', mid([Message Body], 1, index([Message Body], '[') -2),
    		null())) as Other
	;
LOAD
	// Build all the date parts we require
    Date(DayStart(DateTime), 'DD MMM YYYY') as Date,
    Date(MonthStart(DateTime), 'MMM-YY') as Month,
    Month(DateTime) as [Month Name],
    Year(DateTime) as Year,
    WeekDay(DateTime) as Day,
    Hour(DateTime) as Hour,
    Time(Round(DateTime - DayStart(DateTime), 1/1440), 'hh:mm') as Time,
    *,
    // Deal with prefixes with two parts to get a more distinct list of types
    if(index(Prefix, ':') > 0, subfield(Prefix, ':', 1), Prefix) as [Log Type],
    // Where extra text in the prefix move it into the message body
    if(index(Prefix, ':') > 0, PurgeChar(subfield(Prefix, ':', 2), '()') & ' ','') & tmpMessageBody as [Message Body]
    ;
LOAD DISTINCT
	// Find the date and time from the end of the log entry and convert it to a dual data type
    Date(Date#(mid(LogEntry, index(LogEntry, ' ', -3) + 1, 99),
    	'MMM DD,YYYY hh:mm:ss'), 'DD MMM YYYY hh:mm:ss') as DateTime,
    // Our raw log entry - required for EXISTS statement
    LogEntry,
    // Split the message into prefix and body - we are going to put part of the prefix in the body later
    Replace(TextBetween(LogEntry, '[', ']'), ',', ':') as Prefix,  // make commas colons so logic above works
    Mid(LogEntry, index(LogEntry, ']') + 2, index(LogEntry, ' ', -4) - (index(LogEntry, ']') + 2) -1) as tmpMessageBody,
    // Strip out various parts of the log entry into separate fields
    // As no two log types are the same quite an ugly chunk of code - sorry!
    TextBetween(LogEntry, 'MAC address ', ',') as [MAC Address],
    if(index(LogEntry, 'DHCP IP') > 0,
    		TextBetween(LogEntry, '(', ')'),
    	if(index(LogEntry, 'LAN access') > 0,
        	TextBetween(LogEntry, 'to ', ':'),
    	if(index(LogEntry, 'Admin login') + index(LogEntry, 'from source') > 0,
        	TextBetween(LogEntry, 'source ', ','),
        	null()))) as [Internal IP],
    if(index(LogEntry, 'LAN access') > 0, TextBetween(LogEntry, '] from ', ':'),
   		if(index(LogEntry, 'Internet connected') > 0, TextBetween(LogEntry, 'address: ', ','),
   		if(index(LogEntry, 'DoS attack') > 0, TextBetween(LogEntry, '[', ']', 2),
       		null()))) as [External IP],
    if(index(LogEntry, '[Site') > 0, TextBetween(LogEntry, ': ', ']'),
       		null()) as [Web Site],
    if(index(LogEntry, 'LAN access') > 0, TextBetween(LogEntry, ':', ' to '),
       		null()) as [Port Ext],
    if(index(LogEntry, 'LAN access') > 0, TextBetween(LogEntry, ':', ',', 2),
       		null()) as [Port Int]
    // Don't load empty log entries or ones we have seen before - as single entry can appear in multiple emails
    WHERE LogEntry <> '' AND (NOT EXISTS (LogEntry))
    ;
LOAD DISTINCT
	// Split the text of the email into separate lines based on carriage return characters
	SubField(text, chr(10)) as LogEntry
RESIDENT tmpRouterLogs
;

// Drop any un-required tables and fields
DROP TABLE tmpRouterLogs;
DROP FIELD tmpMessageBody;

// If keeping the table of email headings drop fields from it we don't need
//DROP FIELDS text;