Excel Logic
Use Case: Complicated SIEM LOGS
You might have a list of logs and need to see if both/or conditions are true. Here are two examples:
This AND That
More than 1 condition needs to be true:
=AND(B1 = "192.168.1.1", C1 = "Unauthorized Access")
| Row | IP Address | Event Type | Result |
|---|---|---|---|
| 1 | 192.168.1.1 | Unauthorized Access | =AND(B2 = “192.168.1.1”, C2 = “Unauthorized Access”) |
| 2 | 192.168.1.1 | Authorized Access | =AND(B3 = “192.168.1.1”, C3 = “Unauthorized Access”) |
| 3 | 10.0.0.5 | Unauthorized Access | =AND(B4 = “10.0.0.5”, C4 = “Unauthorized Access”) |
| 4 | 172.16.0.1 | Unauthorized Access | =AND(B5 = “172.16.0.1”, C5 = “Unauthorized Access”) |
would render:
| Row | IP Address | Event Type | Result |
|---|---|---|---|
| 1 | 192.168.1.1 | Unauthorized Access | TRUE |
| 2 | 192.168.1.1 | Authorized Access | FALSE |
| 3 | 10.0.0.5 | Unauthorized Access | FALSE |
| 4 | 172.16.0.1 | Unauthorized Access | FALSE |
This OR That
Only 1 condition needs to be true:
=OR(B1 = "192.168.1.1", B1 = "10.0.0.5")
Match List
If you had a sheet called “IOC” and that sheet had a list in column A of known IP addresses to look for, you could search your log entries in another tab like this:
=IF(ISNUMBER(MATCH(B1, IOC!A:A, 0)), "Match", "No Match")
| Timestamp | IP Address | Match Status |
|---|---|---|
| 12:00 | 192.168.1.1 | =IF(ISNUMBER(MATCH(B2, IOC!A:A, 0)), “Match”, “No Match”) |
| 12:01 | 10.0.0.5 | =IF(ISNUMBER(MATCH(B3, IOC!A:A, 0)), “Match”, “No Match”) |
| 12:02 | 172.16.0.1 | =IF(ISNUMBER(MATCH(B4, IOC!A:A, 0)), “Match”, “No Match”) |
Match List AND
To check an entire list and other conditions, we can use the IOC list in Column A on the IOC sheet to look at many log entries:
=IF(AND(ISNUMBER(MATCH(B2, IOC!A:A, 0)), C2 = "Unauthorized Access"), "Match", "No Match")
| Row | IP Address | Event Type | Result |
|---|---|---|---|
| 1 | 192.168.1.1 | Unauthorized Access | =IF(AND(ISNUMBER(MATCH(B2, IOC!A:A, 0)), C2 = “Unauthorized Access”), “Match”, “No Match”) |
| 2 | 192.168.1.1 | Authorized Access | =IF(AND(ISNUMBER(MATCH(B3, IOC!A:A, 0)), C3 = “Unauthorized Access”), “Match”, “No Match”) |
| 3 | 10.0.0.5 | Unauthorized Access | =IF(AND(ISNUMBER(MATCH(B4, IOC!A:A, 0)), C4 = “Unauthorized Access”), “Match”, “No Match”) |
| 4 | 172.16.0.1 | Unauthorized Access | =IF(AND(ISNUMBER(MATCH(B5, IOC!A:A, 0)), C5 = “Unauthorized Access”), “Match”, “No Match”) |
Other Options
- Power Query/Get & Transform (Data > Filter > Event Type > Keyword)
- Conditional Formatting:
=AND(ISNUMBER(MATCH($B2, IOC!$A$1:$A$100, 0)), $C2 = "Unauthorized Access")
The formulas are a bit cumbersome, but will work regardless of the data type and are easy to customize.