### Calculate Detection Rules for SubTechnique in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md Calculates the total detection rules for sub-techniques under a main technique. It checks if the current row has sub-techniques (based on the 'No. of Sub Techniques' column). If so, it sums the 'Detection Rules for Technique' values for all rows sharing the same main technique, then subtracts the 'Detection Rules for Technique' value of the current row (if it's a main technique) to get the sum for sub-techniques. ```Excel Formula =IF(IF(ISNUMBER([@[No. of Sub Techniques]]),[@[No. of Sub Techniques]]>0,FALSE),SUM(IF([@Technique]=[Technique],[Detection Rules for Technique],0))-IF(ISNUMBER([@[Detection Rules for Technique]]),[@[Detection Rules for Technique]],0),"") ``` -------------------------------- ### Get String Length (Excel LEN) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md The LEN function returns the number of characters in a text string, including spaces and punctuation. ```Excel Formula LEN(E2) ``` -------------------------------- ### Get Length of String Without Commas (Excel) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md This part calculates the length of the string after all commas have been removed using the SUBSTITUTE function. ```Excel Formula LEN(SUBSTITUTE(E2,",","")) ``` -------------------------------- ### Lookup Coverage Value by Technique Name (Excel) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md This part of a formula uses MATCH to find the row number of a technique name (from A2) in the 'Techniques[Name]' column and then uses INDEX to retrieve the corresponding value from the 'Techniques[Coverage]' column. ```Excel Formula INDEX(Techniques[Coverage],MATCH(A2,Techniques[Name],0)) ``` -------------------------------- ### Calculate Technique Coverage in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md This formula checks if a technique name exists in a list and retrieves its coverage value. If the coverage is greater than 0, it returns the value; otherwise, it returns an empty string. It also ensures an empty string is returned if the input cell (A2) is empty. ```Excel Formula =IF(A2<>"",IF(INDEX(Techniques[Coverage],MATCH(A2,Techniques[Name],0))>0,INDEX(Techniques[Coverage],MATCH(A2,Techniques[Name],0)),""),"") ``` -------------------------------- ### Calculate Detection Coverage Percentage in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md Calculates the detection coverage for the technique or sub-technique. If the total 'Detection Rules' meet or exceed the 'Expected Detection Rules', coverage is 1 (100%). Otherwise, it's the ratio of 'Detection Rules' to 'Expected Detection Rules'. ```Excel Formula =IF([@[Detection Rules]]>=[@[Expected Detection Rules]],1,[@[Detection Rules]]/[@[Expected Detection Rules]]) ``` -------------------------------- ### Perform Error Checks for Detection Rules in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md Performs various checks for potential configuration errors. It flags if expected rules are negative, if actual rules exceed expected rules, or if a technique with sub-techniques has a 'Detection Rules for Technique' value that isn't greater than its 'Detection Rules Modifier', suggesting the modifier might need adjustment. ```Excel Formula =IF([@[Expected Detection Rules]]<0,"expected detection rules negative!",IF([@[Detection Rules]]>[@[Expected Detection Rules]],"more detection rules than expected!",IF(AND(IF(ISNUMBER([@[Detection Rules for Technique]]),[@[Detection Rules for Technique]]>0,FALSE),IF(ISNUMBER([@[No. of Sub Techniques]]),[@[No. of Sub Techniques]]>0,FALSE)),IF([@[Detection Rules for Technique]] > IF(ISNUMBER([@[Detection Rules Modifier]]),[@[Detection Rules Modifier]],0), "modifier should be increased for the technique", "")))) ``` -------------------------------- ### Determine Minimum Required Detection Rules in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md Determines the minimum number of detection rules expected. If the row represents a technique with more than one sub-technique, the minimum is the number of sub-techniques. Otherwise (for techniques with 0 or 1 sub-technique, or sub-techniques themselves), the minimum is 1. ```Excel Formula =@IF(AND(ISNUMBER([@[No. of Sub Techniques]]),[@[No. of Sub Techniques]]>1),[No. of Sub Techniques],1) ``` -------------------------------- ### Count Available Data Sources for Technique (Excel) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md This complex formula counts how many data sources listed in a cell ([@[Data Sources]]) are also present in the 'Sources' table and marked as 'yes' in the 'Available' column of the 'Sources' table. ```Excel Formula =SUM(IF(ISERROR(FIND(Sources[Data Source],[@[Data Sources]])),0,1)*IF(Sources[Available]="yes",1,0)) ``` -------------------------------- ### Determine Active Detection Rules for Technique in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md Checks if there are any active detection rules linked to the current technique row (identified by cell C2). It sums up instances where the technique matches a rule in the 'Detections' table and that rule is marked as "yes" in the 'Is Active' column. If the sum is greater than 0, it returns the sum; otherwise, it returns an empty string. ```Excel Formula =IF(SUM(IF($C2=Detections,IF(Detections[Is Active]="yes",1,0),0))>0,SUM(IF($C2=Detections,IF(Detections[Is Active]="yes",1,0),0)),"") ``` -------------------------------- ### Calculate Subtechnique Count in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md Calculates the number of sub-techniques associated with a given technique row. It checks if the current row's Technique matches its ID (indicating it's a main technique) and then counts rows where the ID matches the Technique column, subtracting 1 to exclude the main technique itself. ```Excel Formula =IF([@Technique]=[@ID],SUM(IF([@ID]=[Technique],1,0))-1,"") ``` -------------------------------- ### Calculate Expected Total Detection Rules in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md Calculates the total expected detection rules by adding the minimum required rules ('Min. Detection Rules') to any applicable 'Detection Rules Modifier'. The modifier is summed for all rows sharing the same main technique if the current row has sub-techniques, otherwise it uses the modifier from the current row. ```Excel Formula =[@[Min. Detection Rules]]+IF(IF(ISNUMBER([@[No. of Sub Techniques]]),[@[No. of Sub Techniques]]>0,FALSE),SUM(IF([@Technique]=[Technique],[Detection Rules Modifier],0)),[@[Detection Rules Modifier]]) ``` -------------------------------- ### Determine Technique/SubTechnique Status in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md Assigns a status based on expected rules, available data sources, and actual detection rules. Status can be "disabled" (if expected rules <= 0), "detect" (if sources > 0 and rules > 0), "no detect" (if sources > 0 and rules = 0), "inconsistent" (if sources = 0 and rules > 0), or "no sources" (if sources = 0 and rules = 0). ```Excel Formula =IF([@[Expected Detection Rules]]<=0,"disabled",IF([@[Data Sources Available]]>0, IF([@[Detection Rules]]>0,"detect","no detect"),IF([@[Detection Rules]]>0,"inconsistent","no sources"))) ``` -------------------------------- ### Sum Total Detection Rules for Technique/SubTechnique in Excel Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md Calculates the total count of detection rules for the current row by summing the 'Detection Rules for Technique' and 'Detection Rules for SubTech' columns. It treats non-numeric values in these columns as 0. ```Excel Formula =IF(ISNUMBER([@[Detection Rules for Technique]]),[@[Detection Rules for Technique]],0)+IF(ISNUMBER([@[Detection Rules for SubTech]]),[@[Detection Rules for SubTech]],0) ``` -------------------------------- ### Return Result or Empty String Based on A2 (Excel) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md This is the outer IF structure ensuring that the entire formula results in an empty string if the initial input cell (A2) was empty, preventing calculations on empty cells. ```Excel Formula =IF(A2<>"", IF(...), "") ``` -------------------------------- ### Check if Coverage Value is Greater Than Zero (Excel) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md This part of a formula checks if a retrieved coverage value is greater than 0. If true, it returns the coverage value; otherwise, it returns an empty string. ```Excel Formula IF(INDEX(...) > 0, INDEX(...), "") ``` -------------------------------- ### Check if Cell A2 is Non-Empty (Excel) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md This is the initial part of a larger formula that checks if cell A2 contains any text. If A2 is empty, the formula branch returns an empty string; otherwise, it proceeds to the next part. ```Excel Formula =IF(A2<>"", ...) ``` -------------------------------- ### Replace Commas in String (Excel SUBSTITUTE) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md The SUBSTITUTE function replaces all occurrences of a specified text (comma) within a string (E2) with another text (empty string), effectively removing the commas. ```Excel Formula SUBSTITUTE(E2,",","") ``` -------------------------------- ### Calculate Number of Commas (Excel) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md This expression calculates the difference between the original string length and the length after removing commas, which equals the number of commas in the original string. ```Excel Formula LEN(E2) - LEN(SUBSTITUTE(E2,",","")) ``` -------------------------------- ### Count Comma-Separated Items in Cell (Excel) Source: https://github.com/quitehacker/mitre-attack-enterprise-matrix-in-excel-for-soc/blob/main/legends.md This formula counts the number of items in a cell (E2) that are separated by commas. It works by subtracting the length of the string without commas from the length of the original string, and then adding 1. ```Excel Formula =LEN(E2)-LEN(SUBSTITUTE(E2,",",""))+1 ``` === COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.