On this page:

Creating an Instruction: Critical Services

In this lab, we will create a complex instruction from scratch. Now that we understand the different functions available for use in TIMS, we can employ the methods in conjunction with the functions to create an instruction using complex logic.

In this lab, we will create an Instruction which will check the status of a set of 'Critical' services, and based on parameters, either auto-remediate services that are not installed or started, or log a ticket, or both. We will dissect the instruction into small pieces and evaluate how the logic works, as well as the syntax required to get the desired output.

Logged on User

As a best practice, it is always prudent to capture the logged-on user for any Instruction you are writing. Often, the logged-on user is something we want to filter on for any given Instruction. In this exercise, we will create a module that can be reused in any Instruction to capture the logged-on user.

Capture Logged on User

1ETRNW73
  1. If TIMS is already open, close it and relaunch it from the desktop. This will clear the persistent storage and allow us to have a fresh TIMS environment.
  2. In the Instruction window, run the following query
  3. Users.GetLoggedOnUsers();
  4. Note the results that come back. You should see 1 row with 1ETRN\Tachyon_AdminG as the account, and a few other columns of data related to the session. You may also see other rows if other accounts have logged onto that machine. Make note of the 'SessionState' column.
  5. Clear the query window. Run the following to put the logged-on user information into a table
  6. @u = Users.GetLoggedOnUsers();
  7. Input the following below the existing line to get only the logged-on user into a variable, stripping away the other columns from the previous query. Click Run
  8. @u = SELECT Account as CurrentUser from @u where SessionState='Active' 
    UNION SELECT 'No currently logged in user' as CurrentUser limit 1;
    Here we are taking the 'Account' column from the original output and naming it 'CurrentUser'. We are also using 'where' logic to only bring back results where the 'SessionState' is 'Active. If no one is logged into the machine, we are outputting 'No Current logged in user' into the field, using the union function.
  9. In the Instruction Definition pane, input the following:
  10. Description: Get logged on user 
    Name: 1ETRN-GetLoggedonUser
    Readable Payload: Get logged on user
  11. Click on the Schema button in the top ribbon, and click Yes to the popup
  12. In the Edit Schema GUI, click OK accepting the default schema
  13. Go to File>Save as. Click OK on the Validation page
  14. Save the Instruction as C:\Tools\1ETRN-GetLoggedonUser.xml
  15. Although this is simply the first part of our Instruction, we have now saved this as a standalone instruction which returns the logged-on user. We can use this directly in Tachyon, or we can reuse the 3 lines of code in other Instructions. Remember, it is best practice to add logged on user to Instructions. The more data we pull, the more we can query against, and logged on user is a very important piece of data.
    Make sure you leave the logged-on user section in TIMS at the top, as we will use it in the instruction we are building in this lab!

Defining Critical Services

In this exercise, we will begin with defining what our critical services are. We will define a friendly name for the services, as well as the actual name of the services as defined in Windows. We will then join the two for output.

Define Critical Services

1ETRNW73
  1. In TIMS, input the following below the GetloggedOnUsers section
  2. @svc=OperatingSystem.GetServiceInfo();
  3. Highlight just the line above and click Run
  4. Note the output provides information about all the services installed on the computer. Pay particular attention to the Name column as we will be making use of it in this Instruction. Also note we are inputting the data into the @svc table.
  5. Input the following in TIMS below the existing lines
  6. @services = Utilities.SplitLines(Text: "BITS,WMI,Windows Server Service,RPC Server,SCCM Client,Nomad Client,Application Virtualization Client,Windows Defender ATP", Delimiter: ",");
  7. Highlight just this line and click Run
  8. Note the output column. Here, we are using the Utilities.SplitLines function to create a table with one value per line, delimited by a comma.
  9. Input the following into TIMS below the existing lines
  10. @names = Utilities.SplitLines(Text: "BITS,Winmgmt,LanmanServer,RpcSs,CcmExec,NomadBranch,sftlist,WinDefend", Delimiter: ",");
    @list = SELECT S.Output AS Service, N.Output AS Name FROM @services AS S INNER JOIN @names AS N ON S.RowId = N.RowId;
  11. Select the bottom 3 lines as shown below, and click RUN
  12. @services = Utilities.SplitLines(Text: "BITS,WMI,Windows Server Service,RPC Server,SCCM Client,Nomad Client,Aplication Virtualization CLient,Windows Defender ATP", Delimiter: ",");
    @names = Utilities.SplitLines(Text: "BITS,Winmgmt,LanmanServer,RpcSs,CcmExec,NomadBranch,sftlist,WinDefend", Delimiter: ",");
    @list = SELECT S.Output AS Service, N.Output AS Name FROM @services AS S INNER JOIN @names AS N ON S.RowId = N.RowId;
  13. Note the output. We have 2 columns now, one with the friendly name, the other with the service name. The last line in the query is joining the two, and renaming the generic Output header with Name and Service using AS.

Defining Parameters

Parameters are variables that allow the operator to input data into an Instruction at runtime. This is a very important aspect of Tachyon, as it allows for far more dynamic Instructions to be created. For example, you might want to run an Instruction to find out which computers are running a specific process.

You can hard code this process into the Instruction, or you can expose it to the operator using a parameter, thus allowing the operator to input the specific process. This makes the Instruction far more dynamic than one where the process is hard coded into the Instruction itself. In this exercise, we will define 2 parameters for our Instruction.

Define Parameters

1ETRNW73
  1. From the top ribbon in TIMS, click on the Add parameter button
  2. In the Name field, input Escalate
  3. Note the Pattern autofill with the name with % around it.
  4. Click on the Data Type drop down to review the different types of data. Keep the data type as String
  5. Depending on the data type you choose, the parameter will be limited to the type of data which can be inputted. Tachyon will also interpret the parameter in a specific way based on the data type.
  6. In the Hint Text box, input Do you want to raise a ticket when devices are non-compliant?
  7. In the Default Value box, input Log a ticket
  8. We do not need to define a default value. However, this allows for a value to be automatically selected if one isn't explicitly chosen.
  9. In the Control section, click on the dropdown for Type and review the different values. Select Value Picker
  10. Here we have different options for what the parameter type can be. We are selecting Value Picker, which means we must define values, and the Instruction will provide the options from which the operator must pick.
  11. In the Allowed Values in the Validation section, input Log a ticket,Do not log a ticket
  12. Here we are defining the values from which the operator must pick, based on the Control type. Since we choose Log a ticket as the default value, that must be one of the values inputted here. We are also defining Do not log a ticket as a second value.
  13. Click OK to save the parameter
  14. Repeat steps above to create a second parameter with the following values and click OK to save
  15. Name: ServiceAutoStart
    Hint Text: If a critical service is not running, start the service and set it to automatic start
    Data Type: String
    Default Value: Auto-remediate stopped services
    Type: Value Picker
    Allowed Values: Auto-remediate stopped services, do not auto-remediate stopped services
  16. Note the 2 parameters now shown in the middle pane in TIMS. Click on either and select Edit to review the values inputted previously. Click OK to exit out

Determining State of Critical Services and taking action based on Parameters

In the previous exercise, we defined 2 parameters, and each parameter has 2 possible values. When we run the Instruction, we will be asked to define the value for each parameter. Thus, we have 4 possible outcomes. If the 2 values for the first parameter are A and B, and the 2 values for the second parameter are C and D, we end up with the following 4 combinations:

A-C | A-D | B-C | B-D

In this exercise, we will run a FOREACH loop that outputs different data based on which combination of values are selected for the 2 parameters. The data outputted will be based on the critical services we have defined in our Instruction, and what state each service is in.

Determine the State

1ETRNW73
  1. In TIMS, copy the following code below the existing code
  2. @output = FOREACH @i in @list DO
    		SELECT @i.Service as CriticalService, @i.Name,
    CASE 
    			WHEN State LIKE 'STOP%' and '%ServiceAutoStart%' = 'Auto-remediate stopped services' and '%Escalate%' = 'Log a ticket'
    			THEN 'Service Startup set to Automatic, State set to Running. Closed ticket recorded.'
    			WHEN State LIKE 'STOP%' and '%ServiceAutoStart%' = 'Do not auto-remediate stopped services' and '%Escalate%' = 'Log a ticket'
    			THEN 'No Auto Start remediation performed. Open ticket raised.'
    			WHEN State LIKE 'STOP%' and '%ServiceAutoStart%' = 'Do not auto-remediate stopped services' and '%Escalate%' = 'Do not log a ticket'
    			THEN 'No Auto Start remediation performed. No ticket raised.'
    			WHEN State LIKE 'STOP%' and '%ServiceAutoStart%' = 'Auto-remediate stopped services' and '%Escalate%' = 'Do not log a ticket'
    			THEN 'Service Startup set to Automatic, State set to Running. No ticket raised.'
    		ELSE 'No action taken.' 
    	END as Action,
    CASE 
    			WHEN State = 'Running'
    			THEN 'Critical Service Installed and Running'
    			WHEN State LIKE 'STOP%' then 'Critical Service Not Running'
    	END as StatusDescription, 
    CASE 
    			WHEN State = 'Running' 
    			THEN 'Passed'
    			WHEN State LIKE 'STOP%' then 'Failed'
    	END as Status, @u.CurrentUser
    	from @svc, @i, @u where @svc.Name = @i.Name
    UNION
    	SELECT @i.Service as CriticalService, @i.Name,
    		CASE
    			WHEN '%Escalate%' = 'Do not log a ticket'
    			THEN 'Service Not Installed. No action taken. No ticket raised.'
    			WHEN '%Escalate%' = 'Log a ticket'
    			THEN 'Service Not Installed. Open ticket raised.'
    	END as Action,
    		'Not Installed' as StatusDescription, 'Failed' as Status, @u.CurrentUser from @i, @u
    		WHERE (select count(*) from @svc where @svc.Name = @i.Name)=0;
    DONE;
    We will go through the code, following the logic line by line to understand what the for loop is doing in a minute.
  3. Run the entire Instruction in TIMS, selecting Log a ticket, and Auto-remediate stopped services as the parameters' values

Note the output. It displays 6 columns. All the services other than NomadBranch are installed and running, thus no action is taken. For the NomadBranch service, we output Service Not Installed. Open ticket raised. for the Action.

  1. Rerun the Instruction, this time selecting Do not log a ticket as the value for the Escalate Parameter
  2. Note the output, it is exactly the same as the previous run, except for the Action on the NomadBranch service. Here, it outputted Service Not Installed. No action taken. No ticket raised.
  3. Open the services applet from the start menu by typing in services.msc, and stop the SMS Agent Host service
  4. This is the SMS Agent Host service, which is one of our critical services defined in the Instruction.
  5. Return to TIMS and run the Instruction. Select Log a ticket and Auto-remediate stopped services as the values for the respective parameters

Note the output. For the CcmExec service, we have an action of Service Startup set to Automatic, State set to Running. Closed ticket recorded. We will get to this in a second as we dissect this block of code.

  1. Capture this output for future reference. We will continue to reference it as we get further into the instruction.
  2. Return to the services applet and refresh the view
  3. Note the status of the Ccmexec service. Even though the output states that we've started the service, we actually have not gotten to the module that does this. Right now we are just working with what is being outputted from the Instruction. We will get to the actual service action in the next block.
  4. Rerun the Instruction, this time changing the parameter values to Do not log a ticket and Do not auto-remediate stopped services
  5. Note the output. Both for the NomadBranch service as well as the CcmExec service, we are neither remediating, nor raising a ticket.

For loop for each of the 4 scenarios

We just ran the Instruction using different values for the different parameters, as well as running it after stopping one of the services. In each scenario, the NomadBranch service is not installed. We saw the different output for each of the scenarios. We will now dissect the block of code to follow the logic and see how exactly things are being evaluated, and subsequently outputted.

Let's begin with the first line of the code:

DO NOT copy any more code from the above exercise into TIMS. The sections below are simply reviewing the code we have already inputted. The next bit of code that will be copied at the bottom of the existing code will be in Remediate Exercise.

@output = FOREACH @i in @list DO

This line starts a for each loop and puts the output of the block into the variable @output. We are looping through the output from @list, which we previously evaluated.

For each FOREACH loop, we must have a DONE at the end to close out the loop. If you look at this block of code, you will see the DONE at the end of it.
1ETRNW73
  1. Select all lines from the top to the line which starts with @list and click Run
  2. Click OK on the Parameters page. Note the output which is our @list table
  3. This is the list that is now being looped, with the following lines being executed for each row in @list.

Select statement using CASE

Now that we have established our FOREACH loop, we need to execute something inside said loop. In this scenario, we are taking the Service and Name values from @list and running if/then logic using the CASE expression

@output = FOREACH @i IN @list DO
SELECT @i.Service AS CriticalService, @i.Name,

The second line in this code is selecting Service and Name from @list. It is labelling Service as CriticalService which we will see in the output.

The following code is defining what output we will show based on the 4 possible combinations we can have based on what values are selected for the 2 parameters, and define a value for Action.

CASE 
	WHEN State LIKE 'STOP%' AND '%ServiceAutoStart%' = 'Auto-remediate stopped services' AND '%Escalate%' = 'Log a ticket'
	THEN 'Service Startup set to Automatic, State set to Running. Closed ticket recorded.'
	WHEN State LIKE 'STOP%' AND '%ServiceAutoStart%' = 'Do not auto-remediate stopped services' AND '%Escalate%' = 'Log a ticket'
	THEN 'No Auto Start remediation performed. Open ticket raised.'
	WHEN State LIKE 'STOP%' AND '%ServiceAutoStart%' = 'Do not auto-remediate stopped services' AND '%Escalate%' = 'Do not log a ticket'
	THEN 'No Auto Start remediation performed. No ticket raised.'
	WHEN State LIKE 'STOP%' AND '%ServiceAutoStart%' = 'Auto-remediate stopped services' AND '%Escalate%' = 'Do not log a ticket'
	THEN 'Service Startup set to Automatic, State set to Running. No ticket raised.'
			ELSE 'No action taken.' 
END AS Action,

The first WHEN Statement in the case loop will execute if a service state is like %STOP% and the parameters are set to:

WHEN State LIKE 'STOP%' AND '%ServiceAutoStart%' = 'Auto-remediate stopped services' AND '%Escalate%' = 'Log a ticket'


ServiceAutoStart="Auto-remediate stopped services"
Escalate="Log a ticket"


The second WHEN statement will execute if the service state is like %STOP% and the parameters are set to:

WHEN State LIKE 'STOP%' AND '%ServiceAutoStart%' = 'Do not auto-remediate stopped services' AND '%Escalate%' = 'Log a ticket'


ServiceAutoStart="Do not auto-remediate stopped services"
Escalate="Log a ticket"

  1. Read the next 2 lines, you should understand what is being executed here based on the 2 Parameters. Ask your instructor if it is not clear.

The last line in this CASE statement is END AS Action. This defines the Action value of our output for each scenario in the CASE loop

Next we have CASE statements that define the StatusDescription column of our output

CASE 
			WHEN State = 'Running' 
			THEN 'Critical Service Installed and Running'
			WHEN State LIKE 'Stop%' 
			THEN 'Critical Service Not Running'
END AS StatusDescription,


Here, we have 2 scenarios within the case loop. Service status = Running or like %STOP%. Based on the state, we will output specific verbiage into the StatusDescription field as defined above.

The service can be in a stopped state, or a stopping state, hence we are using like %STOP% to cover both scenarios, as opposed to using equal for Running, because there are no variants of Running for the state of a service. We do, however, need to ensure that this is case sensitive. Optionally, we can use like %% for Running as well to bypass the case sensitivity.
We also have a case loop that defines what we will output into the Status column of our output:

CASE 
			WHEN State = 'Running' 
			THEN 'Passed'
			WHEN State LIKE 'Stop%' 
			THEN 'Failed'
END as Status, 

Here, if the state of the service is Running we set the status to Passed. If the status is like %STOP% we set the status to Failed.

Remember the first block of code, where we collected the logged on user. We are now making use of that data we collected.
Lastly, we have a FROM statement, which defines where we're getting the data from.

FROM @svc, @i, @u WHERE @svc.Name = @i.Name

Using UNION for Services not installed

For services in our Critical Services that are not installed, the previous select statements will bring back a NULL or a blank. Thus, we will not be able to output anything against those services.

To remediate this, we use the UNION expression to output to the columns which we defined previously for each service. (Union removes duplicate rows from a result set, if you want to see all rows, then use UNION ALL).

UNION
SELECT @i.Service AS CriticalService, @i.Name,
CASE
WHEN '%Escalate%' = 'Do not log a ticket'
THEN 'Service Not Installed. No action taken. No ticket raised.'
WHEN '%Escalate%' = 'Log a ticket'
THEN 'Service Not Installed. Open ticket raised.'
END AS Action,
'Not Installed' AS StatusDescription, 'Failed' AS Status, @u.CurrentUser FROM @i, @u
WHERE (select count(*) from @svc where @svc.Name = @i.Name)=0;
DONE; 

Here, we are once again selecting Service and Name from @i, same as the last SELECT statement. From there, we are using CASE to output the Action column based on the Escalate parameter. We do not need to evaluate the ServiceAutoStart parameter for services that are not installed, as there is no auto remediation scenario involved here.

Thus, if %Escalate% is set to Log a ticket, we output Service Not Installed into the Action column. Open ticket raised. If it is set to Do not log a ticket we set the Action value to Service Not Installed. No action taken. No ticket raised.


1ETRNW73
  1. Rerun the entire Instruction using whatever combination of parameters you desire, and review the output
  2. The CcmExec service should still be stopped, thus giving you output with services that are started, one service which is not started, and one service which is not installed. Trace the output back to the logic in the code we have in the Instruction.

Remediate

Now that we have all the information we need regarding our critical services, it is time to remediate. If we have set the %ServiceAutoStart% parameter to Auto-remediate stopped services, the Instruction will start any stopped services and set their startup to Automatic. Further, if we have the Escalate parameter set to Log a ticket, we will create a ticket, depending on what the status is of any particular service.

Start Service and set it to Automatic

1ETRNW73
  1. In TIMS, input the following code below the existing code
  2. @remediate = SELECT Name 
    			FROM @output 
    			WHERE Action LIKE 'Service startup set to Automatic%' 
    				AND '%ServiceAutoStart%' = 'Auto-remediate stopped services'; 
    FOREACH @action IN @remediate DO
    		OperatingSystem.ControlService(Service:@action.Name, Action:"Start", Startup:"Automatic");
    DONE;
  3. Note we are taking the output from the existing code, @output as our starting point, and running a FOREACH loop to cycle through each service in our list of critical services. Inside the FOREACH loop, we are checking the Action field for each service. If the field has the text 'Service startup set to Automatic' in it, we will start the service and set the service startup to Automatic.
  4. Rerun the Instruction without this last block and select Auto-remediate stopped services for the ServiceAutoStart parameter
  5. Note the Action field for the CcmExec service. Now rerun the Instruction setting the ServiceAutoStart parameter to Do not auto-remediate stopped services and note the action field for CcmExec.
  6. If we have set the ServiceAutoStart parameter to Auto-remediate stopped services, the Action field will return with Service Startup set to Automatic, State set to Running. No ticket raised. That will trigger the remediate action inside the FOREACH loop, which will invoke the OperatingSytem.ControlService method to start the service and set the startup to Automatic.
  7. Ensure the CcmExec service is still stopped. Stop it if it is running
  8. Run the entire Instruction with Auto-remediate stopped services selected for the ServiceAutoStart parameter
  9. Note the results. We are not actually outputting anything specific here, so we get a default output based on what is being executed in the last block. Now go to the services applet and check the status of the CcmExec service. Note that it has started.

Get the FQDN of the Computer

We will now capture the FQDN of the machine, for the purpose of inputting that into a file to open a ticket. We are using a simple scenario of creating a CSV file which will be used to trigger a ticket opening in an external system. There are many ways integration can work with other systems.

1ETRNW73

  1. Input the following code beneath the existing code in TIMS
  2. @host = Device.GetSummary();
  3. Highlight just the line and click Run using any combination of parameters
  4. Note the output returned using the Device.GetSummary method. We are not interested in all the information, though any of it can be used in creating a ticket. For our example, we simply want the FQDN of the machine.
  5. Input the following line beneath the existing code
  6. @host = SELECT Fqdn AS Hostname FROM @host;
  7. Highlight the last 2 lines as shown below and click Run
  8. @host = Device.GetSummary();
    @host = SELECT Fqdn AS Hostname FROM @host;
  9. Note the output. We are running the Device.GetSummary method to get a set of data back from the machine, and then selecting only the FQDN field and labelling it Hostname inside the @host table.

Creating a File With Information For Opening a Ticket

We are now at the last stage of the Instruction, which is creating a ticket. Our Escalate parameter has two options, Log a ticket or Do not Log a ticket. If we have the parameter set to Do not log a ticket, nothing will happen in this block. However, if we have set the parameter to Log a ticket, information will be piped into a CSV file depending on what state a service is in.


1ETRNW73
  1. Input the following code beneath the existing code in TIMS
  2. NativeServices.RunCommand(CommandLine:"cmd /c echo Short_Description, Description, Status >>%TEMP%\\out.csv"); 
    @escalate = SELECT * FROM @output WHERE Action LIKE '%ticket%' AND '%Escalate%' = 'Log a ticket'; 
    @ticket = FOREACH @action IN @escalate DO
    	@tick = SELECT CASE 
    			WHEN Action LIKE '%Installed%Open%' 
    			THEN 'Service ' || CriticalService || ' not installed' 
    			ELSE 'Service ' || CriticalService || ' not running' 
    		END AS Short_Description,
    	CASE 
    			WHEN Action like '%Open%' 
    			THEN 'Open' 
    			ELSE 'Closed' 
    		END AS Status,
    	CASE 
    			WHEN StatusDescription LIKE '%Not Installed%'
    			THEN 'Compliance check failed. Critical service ' || CriticalService || ' is not installed on device: ' || Hostname ||'. Please remediate manually.'
    			when Action like '%Automatic%' and StatusDescription like '%Not Running%'
    			THEN 'Auto Remediation of failed compliance check. Tachyon started critical service ' || CriticalService || ' on device: ' || Hostname || '. Startup Type was set to Automatic.'
    			WHEN Action like '%No Auto%' and StatusDescription like '%Not Running%'
    			THEN 'Compliance check failed. Critical service ' || CriticalService || ' is not running on device: ' || Hostname ||'. Tachyon has not attempted to start the service. Please remediate manually.'
    		END as Description 
    	from @action, @host; 
    @cmdRun = SELECT "cmd /c echo " ||Short_Description ||','|| Description ||','|| Status || '>> %TEMP%\\out.csv' from @tick;
    NativeServices.RunCommand(CommandLine:@cmdRun);
    DONE;
    SELECT * from @output;

The full code should look like:

@u = Users.GetLoggedOnUsers();
@u = SELECT Account as CurrentUser from @u where SessionState='Active'
UNION SELECT 'No currently logged in user' as CurrentUser limit 1;
@svc=OperatingSystem.GetServiceInfo();
@services = Utilities.SplitLines(Text: "BITS,WMI,Windows Server Service,RPC Server,SCCM Client,Nomad Client,Application Virtualization Client,Windows Defender ATP", Delimiter: ",");
@names = Utilities.SplitLines(Text: "BITS,Winmgmt,LanmanServer,RpcSs,CcmExec,NomadBranch,sftlist,WinDefend", Delimiter: ",");
@list = SELECT S.Output AS Service, N.Output AS Name FROM @services AS S INNER JOIN @names AS N ON S.RowId = N.RowId;
@output = FOREACH @i in @list DO
        SELECT @i.Service as CriticalService, @i.Name,
CASE
            WHEN State LIKE 'STOP%' and '%ServiceAutoStart%' = 'Auto-remediate stopped services' and '%Escalate%' = 'Log a ticket'
            THEN 'Service Startup set to Automatic, State set to Running. Closed ticket recorded.'
            WHEN State LIKE 'STOP%' and '%ServiceAutoStart%' = 'Do not auto-remediate stopped services' and '%Escalate%' = 'Log a ticket'
            THEN 'No Auto Start remediation performed. Open ticket raised.'
            WHEN State LIKE 'STOP%' and '%ServiceAutoStart%' = 'Do not auto-remediate stopped services' and '%Escalate%' = 'Do not log a ticket'
            THEN 'No Auto Start remediation performed. No ticket raised.'
            WHEN State LIKE 'STOP%' and '%ServiceAutoStart%' = 'Auto-remediate stopped services' and '%Escalate%' = 'Do not log a ticket'
           THEN 'Service Startup set to Automatic, State set to Running. No ticket raised.'
        ELSE 'No action taken.'
    END as Action,
CASE
            WHEN State = 'Running'
            THEN 'Critical Service Installed and Running'
            WHEN State LIKE 'STOP%' then 'Critical Service Not Running'
    END as StatusDescription,
CASE
            WHEN State = 'Running'
            THEN 'Passed'
            WHEN State LIKE 'STOP%' then 'Failed'
    END as Status, @u.CurrentUser
    from @svc, @i, @u where @svc.Name = @i.Name
UNION
    SELECT @i.Service as CriticalService, @i.Name,
        CASE
            WHEN '%Escalate%' = 'Do not log a ticket'
            THEN 'Service Not Installed. No action taken. No ticket raised.'
            WHEN '%Escalate%' = 'Log a ticket'
            THEN 'Service Not Installed. Open ticket raised.'
    END as Action,
        'Not Installed' as StatusDescription, 'Failed' as Status, @u.CurrentUser from @i, @u
        WHERE (select count(*) from @svc where @svc.Name = @i.Name)=0;
DONE;
@remediate = SELECT Name
            FROM @output
            WHERE Action LIKE 'Service startup set to Automatic%'
                AND '%ServiceAutoStart%' = 'Auto-remediate stopped services';
FOREACH @action IN @remediate DO
        OperatingSystem.ControlService(Service:@action.Name, Action:"Start", Startup:"Automatic");
DONE;
@host = Device.GetSummary();
@host = SELECT fqdn AS Hostname FROM @host;
NativeServices.RunCommand(CommandLine:"cmd /c echo Short_Description, Description, Status >>%TEMP%\\out.csv");
@escalate = SELECT * FROM @output WHERE Action LIKE '%ticket%' AND '%Escalate%' = 'Log a ticket';
@ticket = FOREACH @action IN @escalate DO
    @tick = SELECT CASE
            WHEN Action LIKE '%Installed%Open%'
            THEN 'Service ' || CriticalService || ' not installed'
            ELSE 'Service ' || CriticalService || ' not running'
        END AS Short_Description,
    CASE
            WHEN Action like '%Open%'
            THEN 'Open'
            ELSE 'Closed'
        END AS Status,
    CASE
            WHEN StatusDescription LIKE '%Not Installed%'
            THEN 'Compliance check failed. Critical service ' || CriticalService || ' is not installed on device: ' || Hostname ||'. Please remediate manually.'
            when Action like '%Automatic%' and StatusDescription like '%Not Running%'
            THEN 'Auto Remediation of failed compliance check. Tachyon started critical service ' || CriticalService || ' on device: ' || Hostname || '. Startup Type was set to Automatic.'
            WHEN Action like '%No Auto%' and StatusDescription like '%Not Running%'
            THEN 'Compliance check failed. Critical service ' || CriticalService || ' is not running on device: ' || Hostname ||'. Tachyon has not attempted to start the service. Please remediate manually.'
        END as Description
    from @action, @host;
@cmdRun = SELECT "cmd /c echo " ||Short_Description ||','|| Description ||','|| Status || '>> %TEMP%\\out.csv' from @tick;
NativeServices.RunCommand(CommandLine:@cmdRun);
DONE;
SELECT * from @output;

Let us look at the first line:

NativeServices.RunCommand(CommandLine:"cmd /c echo Short_Description, Description, Status >>%TEMP%\\out.csv");

Here, we are simply piping 3 things into a CSV file: Short Description, Description, and Status. If the file does not exist, this command will create it. In our example, the file is going to %TEMP%, however, we can send this file anywhere we want.

For example, we can load it to a share which is being monitored by a service, and as soon as a file appears there or is updated, the service will capture information from the file to open a ticket. For now, we are simply piping headers into the file, comma delimited, and if the file doesn't exist it will be created.

Next, we will evaluate the block of code which will input information into the CSV file. This is the rest of the code block

@escalate = SELECT * from @output where Action like '%ticket%' and '%Escalate%' = 'Log a ticket'; 
@ticket = FOREACH @action IN @escalate DO
@tick = SELECT case 
			WHEN Action like '%Installed%Open%' 
			THEN 'Service ' || CriticalService || ' not installed' 
			ELSE 'Service ' || CriticalService || ' not running' 
	END as Short_Description,
CASE 
			WHEN Action like '%Open%' 
			THEN 'Open' 
			ELSE 'Closed' 
	END as Status,
CASE 
			WHEN StatusDescription like '%Not Installed%'
			THEN 'Compliance check failed. Critical service ' || CriticalService || ' is not installed on device: ' || Hostname ||'. Please remediate manually.'
			WHEN Action like '%Automatic%' and StatusDescription like '%Not Running%'
			THEN 'Auto Remediation of failed compliance check. Tachyon started critical service ' || CriticalService || ' on device: ' || Hostname || '. Startup Type was set to Automatic.'
			WHEN Action like '%No Auto%' and StatusDescription like '%Not Running%'
			THEN 'Compliance check failed. Critical service ' || CriticalService || ' is not running on device: ' || Hostname ||'. Tachyon has not attempted to start the service. Please remediate manually.'
	END as Description 
from @action, @host; 
@cmdRun = SELECT "cmd /c echo " ||Short_Description ||','|| Description ||','|| Status || '>> %TEMP%\\out.csv' from @tick;
NativeServices.RunCommand(CommandLine:@cmdRun);
DONE;
SELECT * from @output;


In this block, first we are creating a table, @escalate. We are inputting every row from @output where the Action field is like %ticket% and the Escalate parameter is set to Log a ticket.

If the Escalate parameter is set to Do not log a ticket, this entire block would not execute, as we would not be opening any tickets.

Next, we have 3 Case statements, each one for the 3 variables we want to pipe into the CSV file, Short Description, Status and Description.

The first case statement deals with the Short Description. If the Action field is like %Installed%Open%, we are piping 'Service %CriticalService% not installed'

Else we are piping 'Service % CriticalService% not running'

CASE 
			WHEN Action like '%Installed%Open%' 
			THEN 'Service ' || CriticalService || ' not installed' 
			ELSE 'Service ' || CriticalService || ' not running' 
END as Short_Description,

  1. Refer back to our output where Escalate is set to Open a Ticket, and CcmExec is not running. Review the Action field for each service and tie it back to the logic employed here.
The next case statement deals with the Status field being piped to the CSV. There is very simple logic employed here.

CASE 
			WHEN Action like '%Open%' 
			THEN 'Open' 
			ELSE 'Closed' 
END as Status,

  1. Refer back to our output where Escalate is set to Open a Ticket. Note the NomadBranch service which is not installed. The Action field has the word Open in it. The CcmExec service which is not running does not, and also has the word Closed in it. Thus this block could have been written inversely as well, keying off the word Closed, and using else 'Open' instead.

The last block is a bit more complex. Here we are piping into the Description field in the CSV.

CASE 
			WHEN StatusDescription like '%Not Installed%'
			THEN 'Compliance check failed. Critical service ' || CriticalService || ' is not installed on device: ' || Hostname ||'. Please remediate manually.'
			WHEN Action like '%Automatic%' and StatusDescription like '%Not Running%'
			THEN 'Auto Remediation of failed compliance check. Tachyon started critical service ' || CriticalService || ' on device: ' || Hostname || '. Startup Type was set to Automatic.'
			WHEN Action like '%No Auto%' and StatusDescription like '%Not Running%'
			THEN 'Compliance check failed. Critical service ' || CriticalService || ' is not running on device: ' || Hostname ||'. Tachyon has not attempted to start the service. Please remediate manually.'
END as Description 

First logic is keying off the StatusDescription from our @output data set. 

If it's like %Not Installed% then we're piping 'Compliance check failed. Critical service % CriticalService% is not installed on device: %Hostname%. Please remediate manually.' Into the Description field.

If the Action field is like %Automatic% and the StatusDescription is like %Not Running%, we're piping 'Auto Remediation of failed compliance check. Tachyon started critical service %CriticalService% on device: %Hostname%. Startup Type was set to Automatic. into the Description field.

If the Action field is like %No Auto% and $StatusDescription% like %Not Running% we're piping 'Compliance check failed. Critical service %CriticalService% is not running on device: %Hostname%. Tachyon has not attempted to start the service. Please remediate manually.' Into the Description field.  

Finally, we're taking whatever data set any given service qualifies for and piping it into the csv file using an echo command. And we're returning @output as our final output which would be displayed in Tachyon.

@cmdRun = SELECT "cmd /c echo " ||Short_Description ||','|| Description ||','|| Status || '>> %TEMP%\\out.csv' from @tick;
NativeServices.RunCommand(CommandLine:@cmdRun);
DONE;
select * from @output;

  1. Stop the SMS Agent Host service
  2. Run the entire instruction, selecting Log a ticket and Auto-remediate stopped services parameters
  3. Note the output displayed in TIMS. This is what would be displayed in Tachyon.
  4. Navigate to %temp% in explorer
  5. Note a file named out.csv. Open it with notepad and review the text within. 
  6. Note the two rows, one for the Ccmexec service, and the other for the NomadBranch service.
  7. Stop the SMS Agent Host service again, and run the instruction, this time selecting Log a ticket and Do not auto-remediate stopped services
  8. Return to the out.csv file, and note the verbiage in the last row.

Create an Instruction

Now that we have defined our instruction in TIMS, we want to formalize it into an actual Instruction which can be brought into Tachyon. In this exercise, we will define the different settings for the Instruction, save it as an XML, and run it on our clients.

Schema

  1. In TIMS, click on the Schema button in the top ribbon
  2. Note the default schema defined. Based on what we select for final output, TIMS will define a default schema for us. However, often we might want to reorganize the schema, or remove certain columns. In our scenario, we saved an Instruction for the last logged on user, so the default schema will display that.
  3. Click the Rebuild to get a default schema for the current instruction
  4. Click on the Name column and click the red X to remove the column. Click OK
  5. Click on the Action column and using the down arrow, move it all the way to the bottom. Click OK to save the schema

Aggregation

  1. Click on the Aggregation button in the top ribbon
  2. Check the Enable aggregation of response data for the instruction definition box
  3. Click on Status and click Group By
  4. Note that we are now aggregating our data grouped by Status.
  5. In the Aggregation Operation pane, drop down the Operation to Count. In the Name box, input Count. Click Add
  6. Click OK to close out the Aggregation window

Instruction Definition

  1. In the Instruction Definition pane on the top right, input the following values
  2. Description: Will check for service status and installation of critical services, optionally start stopped services and optionally log service desk tickets.
    Name: 1ETRN-CheckandEscalate
    Readable Payload: Checks for critical service status.%ServiceAutoStart%. %Escalate% if criteria not met.
    We will leave this instruction type as Question to bypass the workflow, including 2FA. This will speed up our execution of the Instruction.
  3. Click File-> Save. Click OK on the warning
  4. Click OK on the Validation page
  5. Note that we are being warned that the payload entered has exceeded 4000 characters. This is simply a warning about the payload max size and compression.
  6. Save the Instruction as C:\Tools\1ETRN-CheckandEscalate.xml
  7. Copy the file and launch the ConfigMgr Content Source shortcut from the desktop
  8. Paste the file you just created there

Running the Instruction

In this exercise, we will run the instruction through Tachyon on our clients. Ensure all the clients are started in the lab. We need to have at least Instruction Set Administrators role in Tachyon in order to be able to upload an instruction and move it into an instruction set. We will do this now

Uploading the Instruction

1ETRNAP
  1. Log into 1ETRNAP as 1ETRN\AppInstaller
  2. Launch the ConfigMgr Content Source shortcut from the desktop
  3. Copy the 1ETRN-CheckandEscalate.xml and paste it into C:\Temp
  4. If not already running, navigate to HTTPS://Tachyon.1ETRN.local/Tachyon to open the Tachyon Portal.
  5. Navigate to the Settings Application
  6. Navigate to Instructions-> Instruction sets
  7. Click Upload and select C:\Temp\1ETRN-CheckandEscalate.xml
  8. Ensure that the Instruction is uploaded successfully.
  9. Select the Unassigned Instruction Set and check the box next to the Check and Escalate Instruction in the middle pane and click Move
  10. Drop down to the 1ETRN Instruction Set and click Move

Stop the SMS Agent Host service

1ETRNW73
  1. From the services applet, stop the SMS Agent Host service
  2. Repeat on two other computers in the environment
  3. Select whichever computers you wish. Keep track of which ones you stopped the service on.

Run the Instruction

1ETRNW71
  1. Navigate to the Explorer Application from the Tachyon Portal or use the Switch app menu
  2. Type the word Check in the I want to know box and select the Check and Escalate Instruction
  3. Note the default values selected for the two parameters. We defined the default values in the parameters.
  4. Click Ask this question
  5. Note the results returned. We have grouped the results based on the count of the status of each service.
  6. Click on the Failed row to expand the results
  7. Note here we see the details of each service that has a status of failed. The data returned is what we defined in the schema of the Instruction.
  8. Click on the Passed row to review the results

Lab Summary

In this lab, we employed many of the functions we learned in an earlier to create a complex Instruction which evaluates a list of critical services we have defined, and takes different actions based on what values we define for the two parameters. This lab taught us the progressive nature of an Instruction, and the importance of temp tables.

Lastly, once the logic was fully defined, we defined the schema and aggregation for the instruction, and executed the instruction in Tachyon. We validated the results match what we defined for the schema and aggregation.

Next Page
Ex 5 - Tachyon v5.1 Advanced - Creating an Instruction that executes a Script