Skip to main content

1E SDK

SELECTing Data

Once you have data in a @table (e.g. by capturing the output of a method invocation), that @table is available as a pseudo-table within SQLite, which can then be queried.

A @table assignment is straight-forward:

Example - @table assignment 

@MyTable = Network.GetConnections();
SELECT DISTINCT ProcessName FROM @MyTable;

Once created, a @table has "scope" for the entire instruction – in other words, it will exist until the instruction completes, and can be referenced as many times as required within the remainder of the instruction.

@tables can be re-assigned if required, for example:

Example - @table reassignment 

@MyTable = Network.GetConnections();
@MyTable = SELECT DISTINCT ProcessName FROM @MyTable;

In the example above, we assign @MyTable first to the output of querying for network connections, and to the result of SELECTing the DISTINCT (unique) process names from the previous data.

Limiting Data

From v3.2, the maximum number of output rows from a single SELECT_expression is limited to the value specified by the SelectRowsLimit configuration value. The range is 1 to 1000000000 (109), with a default of 100000. This is intended to prevent runaway expressions that generate a ridiculous number of rows, consuming gigabytes of memory and wasting CPU and time. No further rows are produced if this value is exceeded, but it is not considered to be an error situation although a warning is generated in the log file.

JOINing and GROUPing

A SELECT statement can of course reference more than one @table if required. The most common reason for doing this is to join data together; for example:

Example - JOINing and GROUPing 

@p = OperatingSystem.GetProcesses();
@c = Network.GetConnections();

SELECT     COUNT(1) AS ConnectionCount
,          @p.AccountName
,          @c.RemotePort
FROM       @p
INNER JOIN @c
  ON       @p.ProcessId = @c.ProcessId
GROUP BY   @p.AccountName
,          @c.RemotePort;

This simple example returns a count of active TCP connections per-port, per-user, by performing an INNER JOIN on the process and connection data using the common "ProcessId" field.

SELECTing "out of thin air"

Example - SELECTing without a table 

/* Note that omitting the FROM clause means we SELECT from a non-existent
   table which has exactly one row. This is similar to using the "dual"
   table in Oracle. */
@d = SELECT DATETIME() AS LocalDate;
More complex examples

SQLite provides huge flexibility and power in its SELECT syntax – the full syntax is available online.

Here are some more examples to get you started.

Get the top 5 processes with the most number of active TCP connections

Example - using LIMIT 

@c = Network.GetConnections();

SELECT   COUNT(1) AS ConnectionCount
,        ProcessName
FROM     @c
GROUP BY ProcessName
ORDER BY ConnectionCount DESC
LIMIT    5; /* Use LIMIT n to return the first n rows */
String concatenation

Example - basic string concatenation 

@p = OperatingSystem.GetProcesses();

SELECT Executable || " was run by " || AccountName AS Message
FROM @p;

Concatenation from multiple @tables:

Example - string concatenation 

@programData = NativeServices.RunCommand(CommandLine:"cmd /c echo %PROGRAMDATA%");
@tachyonFolder = SELECT "\\1E\\Tachyon" AS Path;

SELECT @programData.Output || @tachyonFolder.Path AS TachyonPath FROM @programData,@tachyonFolder
Using CASE to translate values

Example - SELECT CASE 

@c = Network.GetConnections();

SELECT CASE
 WHEN RemotePort = 443  THEN  "HTTPS"
 WHEN RemotePort = 80   THEN  "HTTP"
 WHEN RemotePort = 21   THEN  "FTP"
 WHEN RemotePort = 4000 THEN  "1E Tachyon"
 ELSE "Port #" || CAST(RemotePort AS STRING)
 END AS PortName,
 ProcessName,
 RemoteAddress
FROM @c;
Sub-SELECTs

Example - Sub-SELECT 

@bit = NativeServices.RunCommand(CommandLine:"manage-bde -status", SplitLines:true);
@baseLN = SELECT LineNumber FROM @bit WHERE output LIKE "Volume%";
SELECT 
(SELECT SUBSTR(output, 8, 2) FROM @bit WHERE @baseLN.LineNumber = @bit.LineNumber)  AS DriveLetter, 
(SELECT SUBSTR(output, 27) FROM @bit WHERE @baseLN.LineNumber = @bit.LineNumber -3) AS DiskSpace,
(SELECT SUBSTR(output, 27) FROM @bit WHERE @baseLN.LineNumber = @bit.LineNumber -4) AS BitlockerVersion,
(SELECT SUBSTR(output, 27) FROM @bit WHERE @baseLN.LineNumber = @bit.LineNumber -5) AS ConversionStatus,
(SELECT SUBSTR(output, 27) FROM @bit WHERE @baseLN.LineNumber = @bit.LineNumber -6) AS PercentageEncrypted,
(SELECT SUBSTR(output, 27) FROM @bit WHERE @baseLN.LineNumber = @bit.LineNumber -7) AS EncryptionMethod,
(SELECT SUBSTR(output, 27) FROM @bit WHERE @baseLN.LineNumber = @bit.LineNumber -8) AS ProtectionStatus
FROM @baseLN;
RECURSIVE CTEs

The following is an example of how the Agent language can use a SQLite CTE. The temp table can have any number of columns so long as they are matched by the same number of columns in each of the sub-selects.

Example - CTE 

@factorials = SELECT * FROM (
  WITH RECURSIVE
  temp(n, nfact) AS (
    SELECT 0, 1
    UNION ALL 
    SELECT n+1,
    (n+1)*nfact FROM temp WHERE n < 9)
  SELECT * FROM temp
);
In this section: