LVP SQL

LogViewPlus has a custom SQL engine which executes against the parsed log entries in memory.  This ensures queries can be executed quickly and without writing your log entries to another data store. We call our custom SQL implementation LogViewPlus SQL or LVP SQL.

SQL statements in LogViewPlus are based on Transact-SQL, or T-SQL, which is Microsoft's version of SQL used extensively in SQL Server. Our SQL engine is tested against a SQL Server back-end to ensure SQL statement compatibility and accuracy of results. 

If you're writing a SQL statement and unsure of the syntax, it can be extremely helpful to search the web for "how to X in SQL Server", the resulting answer should also be supported in LogViewPlus.  We have also added a "Copy AI Prompt" command to the context menu on all of our SQL edit dialogs.  This command will look at the schema for the current view and copy a prompt to your clipboard which can be used with an AI Chatbot to help build your query.

LogViewPlus only supports SQL SELECT statements.  Other SQL statements such as INSERT or CREATE TABLE are not supported.

LogViewPlus only supports one table called CurrentView which can be aliased with the abbreviation CV.  Therefore, the FROM target of your SELECT statement should always be CurrentView or CV as there is only one view.  Data from the current view can be enriched with JOIN conditions and functions such as LOADCSV (discussed below).

The CurrentView data matches the data which is currently visible in the Log Entry Grid.  The data returned by your SQL statement will change automatically as the data in the current view changes - such as when you navigate between filters or log files.  The data columns available to your SQL query will also change based on the current selection.  For example, when using a message parser.  In some scenarios, this may cause an existing SQL statement to move between states of being valid and invalid.  This behaviour is normal and expected.

Changes to the CurrentView will impact the data available to a Dashboard.  This allows filters to work as WHERE clause conditions that dynamically manage dashboard queries.

The following keywords and functions are supported.

Keywords

Keyword
Description
The SELECT command is used to select data from a database. The data returned is stored in a result table, called the result set.
The WHERE command filters a result set to include only records that fulfill a specified condition.
The AND command is used with WHERE to only include rows where both conditions is true.
The NOT command is used with WHERE to only include rows where a condition is not true.
The GROUP BY command is used to group the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG).
The ORDER BY command is used to sort the result set in ascending or descending order.
BY
See ORDER or GROUP.
The HAVING command is used instead of WHERE with aggregate functions.
The AS command is used to rename a column or table with an alias.
The SELECT DISTINCT command returns only distinct (different) values in the result set.
The TOP command is used to specify the number of records to return starting at the top of the dataset.
BOTTOM
The BOTTOM command is used to specify the number of records to return starting at the top of the dataset if the set is ordered.  Otherwise, the return value will start at the N rows before the final record.
Selects the first X percent of the records matched by the query.
The FROM command is used to specify which table to select or delete data from.
See INNER JOIN.
Selects records that have matching values in both tables.
Returns all records from the left table, and the matching records from the right table.
IS
A field with a NULL value is a field with no value.  This is distinct from a field with an empty or default value.
GO
Used to indicate end of statement.  Currently allowed but ignored.
The CASE command is used is to create different output based on conditions.
WHEN
See CASE.
THEN
See CASE.
ELSE
See CASE.
END
See CASE.
The ASC command is used to sort the data returned in ascending order.
The DESC command is used to sort the data returned in descending order.


String Functions

Function
Description
The CHAR() function returns the character based on the ASCII code.
The CHARINDEX() function searches for a substring in a string, and returns the position.
The CONCAT() function adds two or more strings together.
CONTAINS
Determines if one string contains a another.  Similar to simple forms of the SQL Server CONTAINS function. 
The LEFT() function extracts a number of characters from a string (starting from left).
The LEN() function returns the length of a string.
The LOWER() function converts a string to lower-case.
The LTRIM() function removes leading spaces from a string.
The NCHAR() function returns the Unicode character based on the number code.
The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.
The REVERSE() function reverses a string and returns the result.
The RIGHT() function extracts a number of characters from a string (starting from right).
The RTRIM() function removes trailing spaces from a string.
The STR() function returns a number as a string.
The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position.
The SUBSTRING() function extracts some characters from a string.
The UPPER() function converts a string to upper-case.
The FORMAT() function formats a value with the specified format and culture.


Date Functions

Function
Description
The DATEPART() function returns a specified part of a date.
The GETDATE() function returns the current database system date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format.
The GETUTCDATE() function returns the current database system UTC date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format.
DATE
The DATE() function extracts the date part from a date time expression.
The DATEDIFF() function returns the difference between two dates.
The DATEADD() function adds a date time interval to a date and then returns the date.
The DATETRUNC function returns an input date truncated to a specified datepart.
The DAY() function returns the day of the month (from 1 to 31) for a specified date.
The MONTH() function returns the month part for a specified date (a number from 1 to 12).
The YEAR() function returns the year part for a specified date.


Math Functions

Function
Description
The ABS() function returns the absolute value of a number.
The CEILING() function returns the smallest integer value that is larger than or equal to a number.
The FLOOR() function returns the largest integer value that is smaller than or equal to a number.
The RAND() function returns a random number between 0 (inclusive) and 1 (exclusive).
The ROUND() function rounds a number to a specified number of decimal places.


Conversion Functions

Function
Description
The CAST() function converts a value (of any type) into a specified datatype.
The COALESCE() function returns the first non-null value in a list.
The CONVERT() function converts a value (of any type) into a specified datatype.
The ISNULL() function returns a specified value if the expression is NULL.
The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first expression.


Aggregation Functions

The DISTINCT keyword can be used with all aggregate functions.  LVP SQL does not currently support the OVER clause.

Function
Description
The AVG() function returns the average value of an expression.
The COUNT() function returns the number of records returned by a select query.
The MAX() function returns the maximum value in a set of values.
The MIN() function returns the minimum value in a set of values.
The SUM() function calculates the sum of a set of values.
Returns the statistical variance of all values in the specified expression.  Our implementation does not support the OVER syntax.
Returns the statistical variance for the population for all values in the specified expression.
Returns the statistical standard deviation of all values in the specified expression.
Returns the statistical standard deviation for the population for all values in the specified expression.
Concatenates the values of string expressions and places separator values between them.  Our implementation does not currently support the order_clause syntax.


System Functions

Function
Description
Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.


Custom Functions

The following functions are specific to LogViewPlus and enhance the application's ability to work with data commonly found in log files.

Function
Description
PIVOT
The PIVOT function transforms rows into columns, creating a summary cross-column report from detailed data. It restructures the data by using the value found in PivotColumn (see below) to create an array of new columns.
 
When using the PIVOT function, data should be pre-grouped in a source table, typically provided by a subquery.
 
The PIVOT function takes 3 parameters:
 
GroupColumn: Specifies the column used to group the output, organizing the data vertically.
 
PivotColumn: Determines the headers of the new columns, each unique value in this column generates a corresponding column in the output.
 
ValueColumn: Contains the data that populates the body of the pivot table, under the headers specified by the PivotColumn.
 
Example:
SELECT PIVOT(Date, Logger, Entries) FROM (
  SELECT Date, Logger, COUNT(*) AS Entries
  FROM CV
  WHERE Logger IN (SELECT TOP 5 Logger FROM CV)
  GROUP BY Logger, Date
)
LOADCSV
Given a file name or full file path to a CSV file, this function will parse the file and provide the contents to the query as a data table.  CSV columns will be automatically scanned to determine an appropriate data type.  The provided CSV file must have column headers.
 
If a file name is provided without a path, the following directories will be searched recursively on a first match basis:
%AppData%\LogViewPlus\LookupTables
%ProgramData%\LogViewPlus\LookupTables
 
Files loaded by LOADCSV are assumed to be static.  They will be cached in memory until the application is closed.
 
Example:
SELECT * FROM LoadCsv('myfile.csv')
GETFILENAME
Given a Unix or Windows file path, this function will return the name of the file, or the last value in the path.  Query string values, if any, will be removed.
 
Example:
GetFileName('c:\myfile.txt')
 
Returns:
myfile.txt
GETFILEEXTENSION
Given a Unix or Windows file path, this function will return the file extension.  If the file does not have an extension, an empty string will be returned.
 
Example:
GetFileExtension('c:\myfile.txt')
 
Returns:
.txt
SPLITPART
This will convert a string into an array using a given character as the delimiter.  The function will then return the value at the array index position.
 
Example:
SplitPart('hello,world', ',', 1)
 
Returns:
world
HEX
Converts a given value (such as a string, integer) into its hexadecimal representation.  Only integers and string data types are supported.
 
Example:
Hex(30)
 
Returns:
1E
UNHEX
Takes a hexadecimal string and converts it back into the original data type (such as integer or string).  An optional second argument can be provided to specify the target data type.  If no type is provided, an integer will be assumed.
 
Example:
UnHex('68656C6C-6F20776F726C64', varchar)
 
Returns:
hello world
QUERYSTRINGVALUE
This function will parse a query string and return a named value.
 
Example:
QueryStringValue('TopicID=569&PageIndex=1', 'TopicID')
 
Returns:
569
REVERSEDNS
This function will execute a reverse DNS lookup on an IP address.  Given an IP address, it will return the domain name for the IP.
 
WARNING: This function is extremely slow when used for a large number of IP addresses.  We decided to maintain support for the functionality only because it is extremely useful in some scenarios.
 
Example:
ReverseDns('157.240.240.35')
 
Returns:
edge-star-mini-shv-01-lcy1.facebook.com
LASTCHARINDEX
Starting from the end of a string, this function will search for a substring in a string, and return the position.  This function is similar to CHARINDEX, but it works in reverse.
 
Example:
LastCharIndex('Hello', 'l')
 
Returns:
4
WIN32ERRORDESC
Given a Win32 error code, this function will return the error description. 
 
Example:
Win32ErrorDesc(13)
 
Returns:
The data is invalid
HTTPSTATUSCODEDESC
Given an HTTP status code, this function will return a brief description of the status code.  For example, given the status code 404, the function will return "Not Found".  Only common status codes are supported.
 
Example:
HttpStatusCodeDesc(504)
 
Returns:
Gateway Timeout


Add On Functions

The following functions are available only through the use of Add Ons which must be downloaded and installed separately.

Function
Description
CountryFromIP
Given an IP address, this function will return the name of the country that is associated with the address. 
 
Example:
CountryFromIP('157.240.240.35')
 
Returns:
United Kingdom
CountryCodeFromIP
Given an IP address, this function will return the two letter country code that is associated with the address. 
 
Example:
CountryCodeFromIP('157.240.240.35')
 
Returns::
GB


< >