Tachyon Activity Record

Tachyon Activity Record, also referred to as Tachyon Agent Historic Data Capture is a mechanism through which the Tachyon Agent continuously captures details of certain events as they happen, in a similar way to Windows Task Manager or Perfmon. During startup, the Agent is also able to detect some events that occurred when it was not running. Data is regularly written into a local, compressed and encrypted persistent store, called an inventory. The inventory is accessible to the Agent language as SQL tables. The Agent periodically aggregates data in order to minimise the amount of storage required, so that each capture source has a live, hourly, daily and monthly table. The whole process is designed to minimise impact on device performance, storage and security.
The Agent has two key mechanisms of knowing when an event occurs that is of interest - event-based and polling-based.
Event-based relies on a source external to the Agent (normally the operating system) providing a notification to indicate that something has happened
Polling-based is where the Agent will periodically check a source of data and work out what has changed by looking at differences in the data returned. Polling intervals means some brief events that occur between polls can be missed.
In this Lab, we will use the historical data to identify what processes have run in the past 7 days that haven't run in the past 30 days. This instruction will allow us to identify possible exploits that may have entered an enterprise recently.

Reviewing the Historical Data

Review Historical Data

In this exercise, we will review some of the historical data captured by the Tachyon agent. This will allow us to get an understanding of how we can use said data to evaluate different things on our Tachyon clients.

1. Logged in as 1ETRN\Tachyon_AdminG, close TIMS and relaunch it as administrator from the desktop
2. Type the following in. Do not copy/paste

SELECT * FROM $Process_Daily;

3. IntelliSense should kick in and display a wide range values once you type in SELECT * from $. These are all the historical data values available for us to use. Note the data returned. These are the processes that have run in the past day.
4. Input the following below the existing line into TIMS and click Run

SELECT * FROM $DNS_Daily;

5. Note the data returned, including the LookupCount. This information relates to the anything that DNS resolves on the machine
6. Query for different historical data values by using the above SELECT statement
7. Typing the SELECT statement up to the $ will present you with all the different values available. Review the different data to get a strong understanding of what is available
8. Switch to the LOG view at the bottom on the TIMS window and switch the Logging Level to Trace
9. Wait for a few seconds and note a slew of DELETE or Insert statements scroll by. This is the Historical Data functionality operating
10. Switch Logging Level back to Info and switch back to the Results pane

Establishing processes delta

In this exercise, we are going to determine what processes have run in the past week compared to what processes have run in the past month. We are using these timelines, but you can use any timelines within a month period to write this instruction, or subsequently use parameters to allow the operator to input the timelines.

Processes run in the past 7 days

In this Task, we will find out which processes have run in the past 7 days. In addition, we will capture some information related to the executable for the given processes.

11. Clear TIMS. Input the following code and click Run

@thisweek = SELECT ExecutionCount, strftime("%Y-%m-%d", datetime(TS, "unixepoch")) as ExecutionDate, ExecutableName, ExecutableHash, ExecutablePath
FROM $Process_Daily 
WHERE ExecutionDate >= datetime(datetrunc(strftime("%s","%environment:now%"),"day"),"unixepoch","-7 day");

12. In this code, we are first establishing a temp table named @thisweek. Into this table we are selecting ExecutionCount, ExecutionDate, ExecutableHash, and ExecutablePath. The Where statement defines the timeframe we want to evaluate. We are evaluating everything between today, and 7 days ago.
Remember the time formatting from the earlier lab?

Summarize execution over past 7 days

In this task, we will summarize the execution of processes in the past 7 days, as opposed to having an individual row for each execution per day. We are not concerned with what ran on what day, rather just what has run in the past 7 days, and how many times.

13. Input the following into TIMS below the existing code from above and click Run

@thisweek = SELECT ExecutableHash, ExecutableName, ExecutablePath, SUM(ExecutionCount) as ExecutionCount
FROM @thisweek
group by ExecutableHash, ExecutableName, ExecutablePath;

14. Here, we are overwriting the @thisweek table with the ExecutableHash, ExecutableName, ExecutablePath, and ExecutionCount for each process we identified in the first query. The ExecutionCount is the sum of execution for each process run in the past 7 days
15. Note the output. We no longer have the execution date, as these are all the processes that have run in the past 7 days

Identify Processes that have run in the past month but not in the past 7 days

In this task, we will identify the processes that have run in the past month, minus what has run in the past week. This data set will give us the ability to compare what has run in the past week vs. what has run before that.

16. Input the following into TIMS below the existing code from above and click Run

@thismonth = SELECT ExecutableHash, datetime(datetrunc(TS,"day"),"unixepoch") as ExecutionDate
FROM $Process_Daily 
WHERE ExecutionDate <= datetime(datetrunc(strftime("%s","%environment:now%"),"day"),"unixepoch","-8 day");

17. This code is capturing all Processes run from 8 days out. By design, the daily historical data is held for the past 30 days, so here we get everything that is 8 days or older until 30 days
• Due to this being a lab environment, we may not have any processes showing from more than a week ago. This environment is spun up from a template, and thus the historical data will only go as far back as the start time of the environment at the beginning of this course. Change the -8 to -1 to see the processes that have run in the past day to validate the query.
• Note we are only capturing the ExecutableName here. This is because we simply need something to compare against. When we get the delta in the next task, we'll display all the other values we captured in the initial query.

Getting the delta between the two data sets

We now have data that shows us what has run in the past 7 days as well as what has run between day 8 and 30. We now need to bring the data together to determine what has run in the past 7 days that hadn't run in before that in the past 30 days.

18. Input the following into TIMS below the existing code from above and click Run

@delta = SELECT ExecutableHash, ExecutableName, ExecutablePath, ExecutionCount
FROM @thisweek WHERE ExecutableHash not in (SELECT ExecutableHash FROM @thismonth);

19. Here, we are getting the ExecutableHash, ExecutableName, ExecutablePath, and ExecutionCount values for every process in @thisweek which is not in @thismonth. This is the delta we are looking for
20. Review the dataset here it may be very similar to the @thisweek dataset, since we won't have a large delta due to having limited data in the environment

Getting more process details

Now that we have identified the delta in processes run this week vs. the past month, we will work on the final output of the data. We have 4 fields being returned currently, the ExecutableHash, ExecutableName, ExecutablePath, and ExecutionCount. We will now gather further information on the Executable associated with the process as well as format some of the data for final output.

FOREACH loop for each process in the list

Here, we will use the FOREACH loop function to loop through every process in the list we compiled in the last block. Once the loop has been established, we will execute different things to gather further information regarding each process.

21. Input the following into TIMS below the existing code from above

@fileout = FOREACH @process IN @delta DO

22. Note the last block which created a variable named @delta. Here we are taking the data from @delta and looping through each row. What we execute in each loop will be defined below
Did you click Run on the instruction? If you did, it will result in a syntax failure. Why? Because we haven't defined what we want to run within the loop yet

Changing the ExecutablePath to friendly name

The output from the previous queries we have run shows a \device\ExecutablePath. In this task, we will convert this to a c: format. Here we will demonstrate the use of a Replace function.

23. Input the following into TIMS below the existing code from above

@file = SELECT "C:" || substr(ExecutablePath,24) as FileName, ExecutableHash, ExecutionCount FROM @process;

Clicking run will again result in a syntax failure. Why? Because we haven't defined what we want our final output to be. All that is happening here is the change in the path. We have to wait for the rest of the steps in the instruction to be added before we can run it.

Here we are using the substr function to replace text from the ExecutablePath with C:. The current path has the following format: \device\harddiskvolume4\<folder>. Count the number of characters before the <folder>. There are 24 characters. So the substr function we're calling here is piped a value of 24, which will replace the first 24 characters of that value with C:.

FileSystem.GetFileDetails

Here we will gather further file details for the final output. Using the FileSystem.GetFileDetails method, we will gather the CreationDate, ModifiedDate, Owner and Size of the files in the @delta list which we are looping.

24. Input the following into TIMS below the existing code from above, highlight the single line and click Run

FileSystem.GetFileDetails(FilePath:"c:\\windows\\notepad.exe");

25. Note the output. Both the DateCreated and DateModified fields have a T in the middle and a Z at the end. We want to remove this to show a friendly format in our final output
26. Delete the above line from TIMS. Input the following below the existing code

@newfiledata = foreach @r in @file do
@filedata = FileSystem.GetFileDetails(FilePath:@r.FileName);
DONE;
@filedata = select * from @newfiledata;
@filedata = SELECT Attributes, replace(substr(DateCreated,0,20),"T"," ") as CreationDate, replace(substr(DateModified,0,20),"T"," ") as ModifiedDate,
FilePath as FileName, Owner, Size as ByteSize
FROM @filedata;

27. This code is gathering CreationDate, ModifiedDate, Owner and Size for each file in our loop. For the two dates, we are using a combination of replace and substr to remove the T and the Z from the date format outputted by the method

FileSystem.GetVersionInfo

Here we will gather further file details for the final output. Using the FileSystem.GetVersionInfo method, we will gather the ProductName, ProductVersion, FileVersion and OriginalFileName of the files in the @delta list which we are looping.

28. Input the following into TIMS below the existing code from above

@fileinfo = FileSystem.GetVersionInfo(FileName:@file.FileName);
@fileinfo = SELECT FileName, case when length(FileDescription) >1 then FileDescription else "Not provided" end as FileDescription, FileVersion, 
case when length(ProductName) > 1 then ProductName else "Not provided" end as ProductName, ProductVersion, OriginalFilename 
FROM @fileinfo;

29. Note the use of the CASE function here. There are many instances where the file description or the product name are null. For any files we encounter which have these null values, we are inputting 'Not provided' into the respective fields. The output will display it as such

FileSystem.GetDigitalSignature

Here we will gather further file details for the final output. Using the FileSystem.GetDigitalSignature method, we will gather the CertEffiectiveDate, CertExpiryDate, and CertThumbprint of the files in the @delta list which we are looping.

30. Input the following into TIMS below the existing code from above, select just the 1 line and click Run

FileSystem.GetDigitalSignature(FileName:"c:\\program files\\itunes\\itunes.exe");

Note the multiple entries returned. Every file won't have multiple entries. Review the different columns to see what is different in each. We are going to focus only on the signing cert. The CertificateType field defines what type of certificate it is
31. Delete the previous line, and input the following into TIMS below the existing code from above

@filecert = FileSystem.GetDigitalSignature(FileName:@file.FileName);
@filecert = SELECT FileName, replace(substr(EffectiveDate,0,20),"T"," ") as CertEffectiveDate, replace(substr(ExpiryDate,0,20),"T"," ") as CertExpiryDate, Thumbprint as CertThumbprint,
substr(Subject,instr(Subject,"CN=")+3) as CertSigner
FROM @filecert WHERE CertificateType='Signing'
order by ExpiryDate DESC limit 1;

32. Here, we are capturing the file name, the certificate effective and expiration dates, as well as the thumbprint of the signing certificate. We are also using the replace/substr combination used previously to remove the T and Z from the dates

Final Output

We have now gathered a wide variety of data for the processes we captured as our delta of new processes that have run in the past week which didn't run for the rest of the past 30 days. It is time to output the data as part of the instruction. We have captured different data in different variables, we now have to bring it together.

Final Output

33. Input the following into TIMS below the existing code from above and click Run

SELECT @file.FileName, @file.ExecutableHash, @file.ExecutionCount, @filedata.Attributes, @filedata.ByteSize, @filedata.CreationDate, @filedata.ModifiedDate, case when @filedata.Owner is null then "File no longer present on disk" else @filedata.Owner end as Owner, @filedata.ByteSize,
case when @fileinfo.FileDescription is null and ByteSize is null then "File no longer present on disk" else @fileinfo.FileDescription end as FileDescription, 
@fileinfo.FileVersion, @fileinfo.ProductName, @fileinfo.ProductVersion, @fileinfo.OriginalFilename,
@filecert.CertEffectiveDate, @filecert.CertExpiryDate, @filecert.CertThumbprint, 
case 
when ByteSize is null then "File no longer present on disk" 
when @filecert.CertSigner is null then "File not signed"
else @filecert.CertSigner end as CertSigner,
substr(Replace(@file.FileName,DIRECTORYFROMPATH(@file.FileName), ''),2) as File
FROM @file 
left join @fileinfo on @fileinfo.FileName = @file.FileName
left join @filecert on @filecert.FileName = @file.FileName
left join @filedata on @file.FileName = @filedata.FileName;
DONE;

34. Note the output. It shows all the different attributes we captured for the file associated with the processes we defined in @delta

The complete code block is below:

@thisweek = SELECT ExecutionCount, strftime("%Y-%m-%d", datetime(TS, "unixepoch")) as ExecutionDate, ExecutableName, ExecutableHash, ExecutablePath
FROM $Process_Daily
WHERE ExecutionDate >= datetime(datetrunc(strftime("%s","%environment:now%"),"day"),"unixepoch","-7 day");
@thisweek = SELECT ExecutableHash, ExecutableName, ExecutablePath, SUM(ExecutionCount) as ExecutionCount
FROM @thisweek
group by ExecutableHash, ExecutableName, ExecutablePath;
@thismonth = SELECT ExecutableHash, datetime(datetrunc(TS,"day"),"unixepoch") as ExecutionDate
FROM $Process_Daily
WHERE ExecutionDate <= datetime(datetrunc(strftime("%s","%environment:now%"),"day"),"unixepoch","-1 day");
@delta = SELECT ExecutableHash, ExecutableName, ExecutablePath, ExecutionCount
FROM @thisweek WHERE ExecutableHash not in (SELECT ExecutableHash FROM @thismonth);
@fileout = FOREACH @process IN @delta DO
@file = SELECT "C:\\" || substr(ExecutablePath,25) as FileName, ExecutableHash, ExecutionCount FROM @process;
@newfiledata = foreach @r in @file do
@filedata = FileSystem.GetFileDetails(FilePath:@r.FileName);
DONE;
@filedata = select * from @newfiledata;
@filedata = SELECT Attributes, replace(substr(DateCreated,0,20),"T"," ") as CreationDate, replace(substr(DateModified,0,20),"T"," ") as ModifiedDate,
FilePath as FileName, Owner, Size as ByteSize
FROM @filedata;
@fileinfo = FileSystem.GetVersionInfo(FileName:@file.FileName);
@fileinfo = SELECT FileName, case when length(FileDescription) >1 then FileDescription else "Not provided" end as FileDescription, FileVersion,
case when length(ProductName) > 1 then ProductName else "Not provided" end as ProductName, ProductVersion, OriginalFilename
FROM @fileinfo;
@filecert = FileSystem.GetDigitalSignature(FileName:@file.FileName);
@filecert = SELECT FileName, replace(substr(EffectiveDate,0,20),"T"," ") as CertEffectiveDate, replace(substr(ExpiryDate,0,20),"T"," ") as CertExpiryDate, Thumbprint as CertThumbprint,
substr(Subject,instr(Subject,"CN=")+3) as CertSigner
FROM @filecert WHERE CertificateType='Signing'
order by ExpiryDate DESC limit 1;
SELECT @file.FileName, @file.ExecutableHash, @file.ExecutionCount, @filedata.Attributes, @filedata.ByteSize, @filedata.CreationDate, @filedata.ModifiedDate, case when @filedata.Owner is null then "File no longer present on disk" else @filedata.Owner end as Owner, @filedata.ByteSize,
case when @fileinfo.FileDescription is null and ByteSize is null then "File no longer present on disk" else @fileinfo.FileDescription end as FileDescription,
@fileinfo.FileVersion, @fileinfo.ProductName, @fileinfo.ProductVersion, @fileinfo.OriginalFilename,
@filecert.CertEffectiveDate, @filecert.CertExpiryDate, @filecert.CertThumbprint,
case
when ByteSize is null then "File no longer present on disk"
when @filecert.CertSigner is null then "File not signed"
else @filecert.CertSigner end as CertSigner,
substr(Replace(@file.FileName,DIRECTORYFROMPATH(@file.FileName), ''),2) as File
FROM @file
left join @fileinfo on @fileinfo.FileName = @file.FileName
left join @filecert on @filecert.FileName = @file.FileName
left join @filedata on @file.FileName = @filedata.FileName;
DONE;

In this last block, we are defining what the output will look like based on the data we captured using the different methods in the previous exercise. We are pulling data from the 4 variables we established using the different methods, @file, @fileinfo, @filedata and @filecert. CASE function is being used to input 'File no longer present on disk' into FileDescription if either Owner or ByteSize are Null. Lastly, we are using join statements to join up the data captured in each of the different variables/tables.
35. Define the schema and aggregation for the Instruction based on what you learned in the previous lab and run the Instruction via Tachyon. Use different settings to see different outputs. When making changes to the Instruction in TIMS, you simply need to change the version in the Instruction Definition pane. You can then save the Instruction as the same XML file, and Tachyon will allow you to upload it as the new version

Lab Summary

In this lab, we worked with the Tachyon Activity Record. TAR is historical data, summarized hourly, daily and monthly. This is very important data, as it allows us to compare what is happening current state with any timeframe over the past month. We used different methods to capture different data and used many of the same functions we learned in earlier Labs.