TIMS Functions

Now that you have a basic understanding of how instructions are built, including an understanding of Methods and Modules, we will explore some functions that will allow us to build complex Instructions using TIMS. TIMS uses a proprietary language, however it is based heavily on SQLite. 

SQLite is a software library that provides a relational database management system. The lite in SQLite means light weight in terms of setup, database administration, and required resource. SQLite is self-contained meaning it requires minimal support from the operating system or external library. This makes it quite usable across platforms like Windows, Linux and Android. SQLite is capable of creating in-memory databases which are very fast to work with. In this lab, we will work with some common functions. Once we have a strong understanding of these functions, we can use them to develop complex Instructions.

The Basics

In this exercise we will start with very basic SQLite commands such as SELECT, FROM, LIKE and WHERE.

SELECT is arguably the most common SQL statement used. It is at the core of extracting data from a database. You select certain values you want to extract from a database by using the SELECT statement. Before we can start using SQL statements, we must input data into the SQL Lite database. This is done by creating a temporary table in the database. This is done quite easily simply by defining a variable/table using @ in front of a variable name.

Basic SQL

1. If TIMS is already open, close it and relaunch it from the desktop
2. In the Instruction window, run the following query

Network.GetIpAddresses();

3. Note the output. This Method will bring back all IP Addresses for the machine. In our case, we have two, an IPv4 and an IPv6 address.
4. Clear the existing code in TIMS and input the following. Click Run

@IPAddresses = Network.GetIpAddresses();

5. Note the output is the same. However, we have now created a temp table with the output data. We can now manage that data using SELECT statements.
6. Beneath the existing code, input the following and click Run

SELECT * from @IPAddresses;

7. Note the output is the same again. We are simply selecting all from the table. The * means select everything.
8. Modify the above line to read as follows. Click Run

SELECT * from @IPAddresses WHERE IpVersion="IPv6";

Note the output now. We are selecting only the row where the IpVersion is a specific value. This is database querying at work. This is a very vital part of creating instructions. The ability to capture different datasets into different temp tables will allow you to create complex instructions that bring together more than just data returned from a single Module/Method.
9. Modify the above line to read as follows. Click Run

SELECT * from @IPAddresses WHERE SubnetMask LIKE "%255%";

Note the result here. We are again querying off a specific column, this time SubnetMask. However, we are using the LIKE statement to return any row that contains a value of 255 in the SubnetMask column (LIKE "%255%"). We can use the % on either side of the value we're looking for, or both. If we have it only at the beginning, it will wildcard everything to start with. If only at the end, only the back part. If on both sides, we will evaluate anything that has the value inside the %%.

String Manipulation

Now that we have worked with some SELECT statements and the like, we will look at some of the more advanced functionality in TIMS. When you are working with actual data that is return you will need to change the formatting that is returned in order to be able to compare two values or your query may return a 16 character string but you only need to evaulate the last 4 characters. The next exercises will teach you some commands to use for string manipulation.

Splitlines

Splitlines is a Method within the Utilities Method that allows us to take a data set and split it up into individual lines. There is a lot of value in doing so if we need to execute something against each value in that list, such as in a loop.

10. Clear the TIMS query window (either use file → new or select all and delete)
11. In the Instruction window, run the following query

Utilities.SplitLines(Text: "ABC,DEF,XYZ", Delimiter: ",");

12. Note the output. We've got each three-letter string on a separate row. Formatting data in this form allows for a variety of different functionality on the dataset.

SUBSTR

The SUBSTR function returns a substring from a string starting at a specified position with a predefined length. The starting position is defined by the start argument, while the length is determined by the length argument as follows: Substr(string, start, length)

13. Clear the TIMS query window. In the Instruction window, run the following query

@file = FileSystem.GetFile(FileName: "c:\\windows\\notepad.exe");

14. Note the output. In this scenario, we will return only everything after the *c:* from the filename using SUBSTR.
15. Input the following below the above line in TIMS and click Run

SELECT SUBSTR(FileName, 12) as filename from @file;

16. Note the output. Using the SUBSTR function, giving it a value of 12 with the FileName parameter, we are chopping off the first 12 characters of our string, and outputting the rest.
17. Clear the TIMS window and input the following. Click Run

@file = FileSystem.GetFile(FileName: "c:\\windows\\notepad.exe");
SELECT SUBSTR(Filename, 12, 7) as filename from @file;

18. Note the change in output. Here, we are inputting two values into the function. The first value defines how many characters we want to chop off at the front, while the second defines how many characters we want to keep from the new starting position. This can be very useful to format data strings brought back by different methods so they can be parsed into other methods.

INSTR

The INSTR function searches a substring in a string and returns an integer that indicates the position of the substring, which is the first character of the substring.
If the substring does not appear in the string, the INSTR function returns 0. In case either string or substring is NULL, the INSTR function returns a NULL value.

19. Clear the TIMS query window
20. In the Instruction window, run the following query

SELECT INSTR('ABCDEFGHI', 'D') as D;

Note the output. As D is the fourth character in our string, we get a return value of 4.
21. Clear the TIMS query window
22. In the Instruction window, run the following query

SELECT INSTR('ABCDEFGHI', 'd') as d;

Note the output now. We replaced the capital D with a lower-case d, and the return value is 0. The function is case sensitive.
23. Clear the TIMS query window
24. In the Instruction window, run the following query

SELECT INSTR('TIMS es mi hombre','hombre') as PositionNumber;

Note the output here. INSTR works with strings the same as individual characters. In this example we are searching for a substring "hombre" in a string "TIMS es mi hombre" and it will return the position number of the first character of the substring in the string (including spaces) – so we get 12

Regular Expression

There are three REGEX functions in TIMS. The REGEX functions allow us to evaluate strings and perform different functions on them. Here we will work with all three functions. There is a lot of value in using these functions when you are trying to compare different data sets.

REGEXP

The REGEXP function determines if a string matches a regular expression.

25. Clear the TIMS query window
26. In the Instruction window, run the following query

@a = Utilities.SplitLines(Text: "This will pass,This will pass not, This gonna pass?", Delimiter: ",");

27. Note the output. We are simply outputting some text and using SplitLines to put them on separate rows with the "," being our delimiter.
28. Beneath the existing code, input the following and click Run

SELECT Output, REGEXP("This .* pass", Output) AS [Match] FROM @a;

29. Note the output here. We are using REGEXP to dictate the expression we are looking to evaluate. In this example, we're using "This . pass"* The .* is a wildcard, meaning anything in between is acceptable. But we need the string to start with This and end with pass. Based on the 3 rows of text we have, only one passes, hence we get a match of 1 for that row, and 0 for the other two.
30. Delete the last line of code and input the following. Click Run

SELECT Output FROM @a WHERE Output REGEXP "This .* pass";

31. Note the output here. We are not asking for validation via the Match column like the previous example, rather we are only returning the string that matches here.

REGEXCAPTURE

REGEXCAPTURE can be used to capture a subset of a string. This can be very useful when looking for specific verbiage in different datasets or parsing out other pieces of a string to end up with only the specific text you want, which you can then use in different ways.

32. Clear the TIMS query window
33. In the Instruction window, run the following query and click Run

SELECT REGEXPCAPTURE("I (.*) your", "Luke I am your father") AS Capture;

34. Note the output. Here we are defining what we want to capture. Everything starting from I and ending with your. Thus, we are cutting off anything before I and after your. If the capture definition you input doesn't match the string being evaluated, you will get a blank output returned. This is a simple example; however you can use the different REGEX functions in FOREACH loops, cycling through rows of data returned via a Method.

REGEXREPLACE

REGEXREPLACE is used to replace a specific string. It works much in the same way as the other REGEX functions, in that you define what you want to replace, and it will evaluate the string in question and replace the string.

35. Clear the TIMS query window
36. In the Instruction window, run the following query and click Run

SELECT REGEXPREPLACE("(Twinkle) (Winkle)", "Twinkle Winkle little star", "$2 $1") as output;

37. Note the output. We have swapped Twinkle and winkle from the string. Here, we define "Twinkle" and "Winkle" as the strings we want to replace, then the target string, then $2 $1, which define which parts of the string we want to replace (here $2 is the second submatch string (Winkle) and $1 is the first submatch string (Twinkle).
38. Clear the TIMS window and input the following

SELECT REGEXPREPLACE("(Twinkle) (Winkle)", "Twinkle Winkle little star", "$2") as output;

39. Note the output here. We lose the Twinkle because we did not define a second parameter for replacement. Try it replacing $2 with $1 and see the results. These are simple examples; however, you can use the different REGEX functions in FOREACH loops inputting data captured using different methods, cycling through rows of data returned from a query.

Time Formatting

Quite often, different Methods will return data in different formats, depending on where the data is being retrieved from. Conversely, we might need that data in a very specific format to parse into something else as the Instruction builds. In such cases, we need to be able to format that data differently. We have seen the SUBSTR and INSTR functions which will allow for string manipulation, now we will see how to format time.

40. Clear the TIMS query window
41. In the Instruction window, run the following query

@now = SELECT STRFTIME('%s','now') AS TS;

42. Note the output here. This is the current time, in a specific format. This is the number of seconds elapsed since January 1^st, 1970.
43. Copy the following code beneath the existing line and click Run

@now = SELECT STRFTIME('%S','now') AS TS;

44. Note the output here. The capital S will bring back the second reference of the current minute. Run it again and watch the number change, from anywhere between 0 and 59. Try replacing the %S with %M, %m. Each parameter will return the current time in different formats. Try and guess what each value reflects, based on the current time/date.
45. Clear the TIMS query window
46. In the Instruction window, run the following query

@now = SELECT STRFTIME('%s','now') AS TS;
SELECT TS,
EPOCHTOJSON(TS) AS TS_EpochToJson,
DATETIME(TS, "unixepoch") as TS_datetime,
DATETIME(TS, "unixepoch", "localtime") as TS_localtime,
STRFTIME("%Y-%m-%dT%H:%M:%SZ",DATETIME(TS, "unixepoch")) as TS_strftime
FROM @now;

47. Note the different formats the data is presented in. The EpochToJson is the most common format used across multiple platforms, as it is an ISO standard. Next is the UNIXEpoch time, which is a format that counts the number of seconds elapsed since January 1, 1970 UTC. The third column is also UNIXEpoch, but it is presenting the local time based on the timezone, as opposed to the UTC time. Lastly, we are using STRFTIME to define the output in a specific format.

Other Functions

Here we will learn about some other functions that are commonly used in creating Instructions.

FOREACH Loop

Quite often, we want to cycle through a data set and execute something on each value within the dataset. This can be accomplished using a FOREACH loop. A FOREACH loop will cycle through each piece of data in a dataset and execute whatever it is you are defining for execution. Here we will make use of the FOREACH loop, and in the process also demonstrate the use of LIMIT.

48. Clear the TIMS query window
49. In the Instruction window, run the following query

@folders = SELECT "c:\\windows\\,c:\\tools\\" AS FolderList;
@folders = Utilities.SplitLines(Text: @folders.FolderList, Delimiter: ",");

50. Note the output. We are inputting two different folders in the first line, and then using the SplitLines function to output them onto individual rows using a comma delimiter.
51. Beneath the existing two lines, input the following and click Run

@files = FOREACH @f IN @folders
DO
    // Build our command-line
    @cmd = SELECT "cmd.exe /c dir " || @f.Output || " /b" AS CommandLine FROM @f;
   NativeServices.RunCommand(CommandLine: @cmd.CommandLine, SplitLines: true);
DONE;

52. Note the output here. We are capturing all the files in the folders we specified. The other columns are the default output for the command we are running.
There are a few things happening here. First, we are opening up a FOREACH loop. Below the first line is what we want to execute within the FOREACH loop. First, we are building a commandline. This is also a very useful tool, as often you will want to execute something on your clients via a command line. Our command line starts with cmd.exe /c dir. Then we are piping in the individual lines from or original output, ending it with a /b. So the commandline will look like cmd.exe /c dir c:\windows /b on the first loop. The second loop will replace c:\windows with c:\tools and the loop will cycle through each row that is in our dataset.
53. Input the following code at the bottom of the existing code and click Run

SELECT Output FROM @files ORDER BY Output LIMIT 5;

54. Note the output here. Firstly, we are only selecting the Output column, so the other 2 columns are no longer displayed. Next, we are ordering the dataset by Output, thus it will alphabetically order the data set. Lastly, we are limiting the output to 5 columns using the LIMIT function. Thus, only the top 5 rows are displayed. Change the LIMIT value to a different number to see the results. You can also type in DESC after Output in the last line and it will sort in descending order.

UNION

Sometimes you need to combine data from multiple tables into a complete result set. To combine rows from two or more queries into a single result set, you use the UNION clause. There are two variations of UNION: UNION and UNION ALL.

55. Clear the TIMS query window
56. In the Instruction window, run the following query

@list = SELECT "Andy" as name
UNION
SELECT "Dandy" as name
UNION
SELECT "Candy" as name
UNION
SELECT "Dandy" as name;

57. Note the output. Only 3 rows are returned. As Dandy is a duplicate, it is omitted.
58. Clear the TIMS query window
59. In the Instruction window, run the following query

@list = SELECT "Andy" as name
UNION ALL
SELECT "Dandy" as name
UNION ALL
SELECT "Candy" as name
UNION ALL
SELECT "Dandy" as name;

60. Note the difference in output. Here we are getting all 4 rows, as UNION ALL will not omit duplicates.
61. Clear the TIMS query window
62. In the Instruction window, run the following query

@windows = NativeServices.RunCommand(CommandLine: "cmd.exe /c dir c:\\windows /b", SplitLines: true);

63. Note the output. We have an Output column with all the content of c:\windows. There are two other columns which are outputted when the SplitLines function is used.
64. In the Instruction window, clear the existing text and run the following query

@tools = NativeServices.RunCommand(CommandLine: "cmd.exe /c dir c:\\tools /b", SplitLines: true);

65. Here we are outputting the contents of c:\tools. Now we will bring the data from these two data sets together using UNION.
66. In the Instruction window, clear the existing text and run the following query

@windows = NativeServices.RunCommand(CommandLine: "cmd.exe /c dir c:\\windows /b", SplitLines: true);
@tools = NativeServices.RunCommand(CommandLine: "cmd.exe /c dir c:\\tools /b", SplitLines: true);
SELECT "Windows" as Folder, Output from @windows
UNION
SELECT "Tools" as Folder, Output from @tools;

67. Note the output here. We captured two different data sets from two different folders and brought the data together. We can also run the function without the "X" as Folder part in the select statement to get the same results without the folders column. In that instance, if we had duplicate entries in the windows and tools folder, UNION ALL would bring back everything, whereas UNION would omit the duplicates. Try running the query different ways to see how the data is presented.

File Path

A key part of Tachyon functionality is to evaluate files on a client, Windows or otherwise. There are many different methods that bring back different data in different formats. Often you will need to format the data to your liking for use with other data sets. Here we will work with a few TIMS functions that allow for the formatting of file data.

68. Clear the TIMS query window
69. In the Instruction window, run the following query

@file = FileSystem.GetFile (FileName: "c:\\windows\\notepad.exe");

70. Note the data returned in the 2 columns. Specifically, the FileName field.
71. Under the existing query, input the following and click Run

SELECT @file.FileName,
      DIRECTORYFROMPATH(@file.FileName) AS JustTheDirectory
FROM @file;

72. Note the output. Here we are selecting the filename we captured from the GetFile method, and also outputting the DIRECTORYFROMPATH.
73. Clear the TIMS query window and input the following. Click Run

@file = FileSystem.GetFile (FileName: "c:\\windows\\notepad.exe");
SELECT @file.FileName,
      DIRECTORYFROMPATH(@file.FileName) AS JustTheDirectory,
      BASENAMEFROMPATH(@file.FileName) AS JustTheFileName,
      COMBINEPATHS(DIRECTORYFROMPATH(@file.FileName), BASENAMEFROMPATH(@file.FileName)) AS Recombined
FROM   @file;

74. Note the output here. We have 4 columns. The original filename as retrieved by the FileSystem.GetFile method, JustTheDirectory from the DIRECTORYFROMPATH function, JustTheFileName from the BASENAMEFROMPATH function, and Recombined using the COMBINEPATHS function.

CASE

The SQLite CASE expression evaluates a list of conditions and returns an expression based on the result of the evaluation. The CASE expression is like the IF-THEN-ELSE statement in other programming languages. You can use the CASE statement in any clause or statement that accepts a valid expression.

75. Clear the TIMS query window
76. In the Instruction window, run the following query

@arp = Network.GetArpEntries();

77. Note the output here. At least one of the rows has a blank for the AdapterType.
78. Clear the TIMS query window and input the following. Click Run

@arp = Network.GetArpEntries();
@arp = SELECT case when length(AdapterType) >1 then AdapterType else "Unknown" end as AdapterType, IpAddress, Subnet, MACAddress from @arp;

79. Note the results here. For the row that had a blank for the AdapterType, we have inputted 'Unknown'.
Here, we are using a simple CASE statement. First, we are capturing all the ARP entries on the machine. Next, we are using the CASE statement to define what we want our output to look like. For any entries in the AdapterType field that are blank, we are replacing the blank with Unknown. If it is not a blank, we are simply outputting whatever the initial query brought back. This can be used on multiple columns as well.
As an added bonus, we have also learned how to use the length() function.
80. Clear the TIMS query window and enter the following. Click Run

@arp = Network.GetArpEntries();
@arp = SELECT case when length(AdapterType) >1 then AdapterType else "Unknown" end as AdapterType, IpAddress,
case when length(AssignedBy) >1 then AssignedBy else "meow" end as AssignedBy
from @arp;

81. Note we have now replaced the blank AssignedBy with meow, along with the CASE statement replacing the blank AdapterType to unknown.

REPLACE

The replace function allows us to replace a string with a replacement of our choice. There is a lot of value in doing this when getting data that is not of the exact format you might require.

82. Clear the TIMS query window
83. In the Instruction window, run the following query

FileSystem.GetDigitalSignature(FileName: "c:\\windows\\notepad.exe");

84. Note the output. Only the FileName and SignatureStatus fields return values. We will now replace the SignatureStatus value.
85. Clear the TIMS query window. In the Instruction window, run the following query

@sig= FileSystem.GetDigitalSignature(FileName: "c:\\windows\\notepad.exe");
@sig = select FileName, Replace(SignatureStatus,"Un","") as SigStatus from @sig;

86. Note the output. We have replaced the SignatureStatus from UnSigned to signed by replacing the Un with a blank.

EVALUATE

Evaluate is an IF/THEN function that allows you to exit out of the code if conditions are not met. This is quite useful in instances where you do not want to do anything more if a specific condition on your dataset is not met.

87. Clear the TIMS query window
88. Launch Internet Explorer and confirm the home page is loaded to MSN.com
89. In the Instruction window, run the following query

@conns = Network.GetConnections();

90. Note the output. We have a few different connections on different remote IP Addresses.
91. Input the following line under the existing query and click Run

SELECT ProcessName FROM @conns WHERE ProcessName = "Schmoopy" OR RemoteAddress = "40.101.40.220";

92. Note the output here. Since we do not have a process named "Schmoopy" or a connection to a remote address of "40.101.40.220", the query returns Success (no content), meaning nothing matched our criteria.
93. Input the following under the existing code and click Run

EVALUATE;
// Otherwise add an entry to the hosts file to point to the new application server server
FileContent.FindAndAppend(
    FileName: "c:\\windows\\system32\\drivers\\etc\\hosts",    
    FindString: "40.101.40.220 applicationserver.acme.local",
    AppendString: "40.101.40.220 applicationserver.acme.local",
    Backup: true, 
    CaseSensitive: false);

94. Note we get the same output as before. Since our conditions are not being met, the EVALUATE statement causes the code to exit out at that very spot, not executing anything below.
95. In the second line of the code, replace "Schmoopy" with "(system)" and click Run
We now have some output. Since (system) is a process that is returned via the Network.GetConnections method, our criteria is being met. Thus, EVALUATE allows for the code to proceed. What we are doing next is appending some text to a file we have specified, in this case the hosts file.
96. Navigate to c:\windows\system32\drivers\etc. Right-click the hosts file and Edit with Notepad++
97. Note at the bottom we have appended the file with our host entry with an associated IP. Also note a hosts.bak file has been created. In learning about EVALUATE, we have also learned how to append text to a file.

String Escaping

Strings should be enclosed in double-quotes ("). Single quotes have been deprecated but are still available in this version of Tachyon. Even though they are permitted in SQLite they may stop working in the future, so it is best to move to double-quotes only. The escape character in SCALE is a backslash (\). If you require a literal double-quote in a string, then you must use an escape character. Using an escape character allows the device to interpret the character (that has been "escaped") differently than it would without the escape character. Since SCALE uses double-quotes to denote a string, if you want the quotes to be part of the string you must use the escape character just before the " symbol.

98. Clear the TIMS window
99. In the Instruction window, run the following query

// Result: The Tachyon Agent log is found in "C:\ProgramData\1E\Client\" by default.
SELECT "The Tachyon Agent log is found in \"C:\\ProgramData\\1E\\Client\\\" by default." AS Tip;

100. Notice the output we have our string displayed. Notice the backslash before the "c:\
101. Remove the backslash (\) in front of "c:\\ and run the instruction again
102. Notice the error. The escape allows Tachyon to evaluate the double-quotes as a character in the string. Notice we have a backslash in front of the first (") and in front of the last one also.

String Concatenation

Putting strings together is called concatenation. We achieve this in SCALE by using a double pipe (||).

103. Clear the TIMS window
104. In the Instruction window, run the following query

@breakfast = SELECT "sausages" AS choice; 

105. Notice the output. We have choice defined as sausages
106. Input the following under the existing code and click Run

SELECT "James says \"I would like " || choice || " today\" every morning." AS greeting FROM @breakfast; 

107. Notice the output. Since our strings are words, we want to add spaces between each word. Notice our code block there is a space after like and before the ", there is also a space between " and today. Delete the space between like and ("). Click Run again.
108. Notice the output. There will be times when you want to concatenate strings that you run together and times when you will want to add spaces. It will depend on the actual data that you are working with.

Lab Summary

In this lab, we learned some basic and not so basic functions that can be used in TIMS. Most of it is SQLite specific, thus a strong understanding of SQL is required to create complex instructions. There are many other functions that we did not cover here, but you will see in different instructions. The quickest way to learn how to create complex Instructions, beyond attending this course, is to reverse engineer existing Instructions.

You will find a large collection of Instructions on the Tachyon Exchange. Download the Instructions and open the XML files in TIMS to review the different steps and logic employed to get to the end result.