/////////////////////////////////////////////////////////////////////////////////////////// // 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;