Splunk Search

## How can I compare the results of two weeks ago with one week ago, with several fields?

New Member

I have the following issue:
1- Two weeks ago I have 10 results of my entity with 3 fields;
2- One week ago I have 12 results of my entity with the same 3 fields;

3- After analysing the results I have found that 10 results in the "one week ago" are equal to the "two weeks ago"...so how can I only show in the "one week ago" just the two different results? The difference is based in the content of just two fields (see example)

Example with just 3 results:
Two weeks ago:
Field1 | Field 2 | Field 3
1 2 3
4 5 6
7 8 9

One Week ago:
Field1 | Field 2 | Field 3
1 2 3
4 5 6
4 6 6
7 8 9
11 12 13
14 15 16

Result that I expect:
4 6 6
11 12 13
14 15 16

Magnificent result showing the two type of results:
Difference:
4 6 6
11 12 13
14 15 16

Equals:
1 2 3
4 5 6
7 8 9

The target is that I can have the "all diference" of results, and/or if I want, then can use some specific fields (field1 and/or field2) to compare the results.

Thanks,

Best regards,

Tags (1)
1 Solution
Champion

Based on the clarification in the comments, I'm going to refine your requirements:

You will have two time periods you are comparing. Sometimes it will be "two weeks ago" vs "one week ago". Sometimes it will be "yesterday" vs "today". In either case, you want the "Difference" table to contain entries where [field1 field2 field3] appears only in the more recent window (e.g. "one week ago" or "today" in the two cases described above) and not in the older window. You do not want it to contain entries where [field1 field2 field3] appears only in the older window.

To find the "Difference" table as described, you should run your base search over the last two weeks, thus collecting all events. Then use `stats` to count similar events and compare their earliest seen time to the earliest date of the more recent window, and finally filter based on the number of times a duplicate event was seen and when it was first seen. In the following example, I will compare events for the last 2 weeks.

``````your base search that collects all events over the last two weeks
| stats earliest(_time) AS _time count by field1, field2, field3
| where count=1 AND _time>=relative_time(now(), "-14d@d")
| fields - count _time
``````

You can use this same approach to find the events for the "Equals" table but this time filter for events `| where count>1` and also compare both the earliest seen time and the latest seen time to ensure there was an event int each of the two time windows:

``````your base search that collects all events over the last two weeks
| stats earliest(_time) AS earliest_time latest(_time) AS latest_time count by field1, field2, field3
| where count>1 AND earliest_time<=relative_time(now(), "-14d@d") AND latest_time>relative_time(now(), "-14d@d")
| fields - count earliest_time latest_time
``````

If you want to compare only yesterday and today, you can replace instances of `relative_time(now(), "-14d@d")` with `relative_time(now(), "@d")` to specify the breaking point between "yesterday" and "today".

New Member

If you can share the purpose of your search, it will be easier to help you with the search query.
From what you are explaining, the best way to do it will be to use the below search query:

Run for the last 2 weeks, the below search will compare by IP field2 and field3.

index=xxx | table IP field1 field2 | stats count by IP field1 field2 | where count = 1 | fields - count

Explanation:

you are counting the number of rows with the same fields IP, field1 and field2.
Let me take you step by step:

index=xxx | table IP field1 field2
IP | Field 2 | Field 3
1.1.1.1 | 2 | 3
4.4.4.4 | 5 | 6
7.7.7.7 | 8 | 9
1.1.1.1 | 2 | 3
4.4.4.4 | 5 | 6
4.4.4.4 | 6 | 6
7.7.7.7 | 8 | 9
11.11.11.11 | 12 | 13
14.14.14.14 | 15 | 16

index=xxx | table IP field1 field2 | stats count by IP field1 field2

IP | Field 2 | Field 3 | count
1.1.1.1 | 2 | 3 | 2
4.4.4.4 | 5 | 6 | 2
7.7.7.7 | 8 | 9 | 2
4.4.4.4 | 6 | 6 | 1
11.11.11.11 | 12 | 13 | 1
14.14.14.14 | 15 | 16 | 1

index=xxx | table IP field1 field2 | stats count by IP field1 field2 | where count = 1

IP | Field 2 | Field 3 | count
4.4.4.4 | 6 | 6 | 1
11.11.11.11 | 12 | 13 | 1
14.14.14.14 | 15 | 16 | 1

index=xxx | table IP field1 field2 | stats count by IP field1 field2 | where count = 1 | fields - count ( what you are expecting as a result)

IP | Field 2 | Field 3
4.4.4.4 | 6 | 6
11.11.11.11 | 12 | 13
14.14.14.14 | 15 | 16

If you are searching to analyze the results with the same IP, use the below query:

index=xxx | table IP field1 field2 | stats count by IP field1 field2 | where count > 1 |stats list(field2) list(field3) by IP

Champion

Based on the clarification in the comments, I'm going to refine your requirements:

You will have two time periods you are comparing. Sometimes it will be "two weeks ago" vs "one week ago". Sometimes it will be "yesterday" vs "today". In either case, you want the "Difference" table to contain entries where [field1 field2 field3] appears only in the more recent window (e.g. "one week ago" or "today" in the two cases described above) and not in the older window. You do not want it to contain entries where [field1 field2 field3] appears only in the older window.

To find the "Difference" table as described, you should run your base search over the last two weeks, thus collecting all events. Then use `stats` to count similar events and compare their earliest seen time to the earliest date of the more recent window, and finally filter based on the number of times a duplicate event was seen and when it was first seen. In the following example, I will compare events for the last 2 weeks.

``````your base search that collects all events over the last two weeks
| stats earliest(_time) AS _time count by field1, field2, field3
| where count=1 AND _time>=relative_time(now(), "-14d@d")
| fields - count _time
``````

You can use this same approach to find the events for the "Equals" table but this time filter for events `| where count>1` and also compare both the earliest seen time and the latest seen time to ensure there was an event int each of the two time windows:

``````your base search that collects all events over the last two weeks
| stats earliest(_time) AS earliest_time latest(_time) AS latest_time count by field1, field2, field3
| where count>1 AND earliest_time<=relative_time(now(), "-14d@d") AND latest_time>relative_time(now(), "-14d@d")
| fields - count earliest_time latest_time
``````

If you want to compare only yesterday and today, you can replace instances of `relative_time(now(), "-14d@d")` with `relative_time(now(), "@d")` to specify the breaking point between "yesterday" and "today".

New Member

Hi guys, sorry for the delay in answer.

Both responses work for me.
Thanks very much for the help.

All Splunkers rocks!!

Champion

To find the "Difference" table as described, you should run your base search over the last two weeks, thus collecting all events. Then use `eventstats` to count similar events, and finally filter based on the number of times a duplicate event was seen.

``````your base search that collects all events
| eventstats count by field1, field2, field3
| where count=1
| fields - count
``````

You can use this same approach to find the events for the "Equals" table but this time filter for events `| where count>1`, as these events had duplicates:

``````your base search that collects all events
| eventstats count by field1, field2, field3
| where count>1
| fields - count
``````
New Member

Sorry for the delay in answer.
Didn' t resolve my issue...

New Member

My issue is comparing the IP from yesterday with the IP of today. If it is present today and not yesterday I should received in the output. If the IP is present yesterday and not today, I should not receive any output.

Thanks.

Champion

I'm sorry, but I don't really follow. You didn't mention any IP addresses in the post. Is one of the fields in your post meant to represent an IP address? Which one? And should I ignore the others, or are they relevant to the comparison, too?

New Member

Sorry for the misunderstood.

I didn't refer the field IP, because I had assumed that a field is a field and it' s not important the content of the field.

Assume this:
Two weeks ago:
IP | Field 2 | Field 3
1.1.1.1 | 2 | 3
4.4.4.4 | 5 | 6
7.7.7.7 | 8 | 9

One Week ago:
Field1 | Field 2 | Field 3
1.1.1.1 | 2 | 3
4.4.4.4 | 5 | 6
4.4.4.4 | 6 | 6
7.7.7.7 | 8 | 9
11.11.11.11 | 12 | 13
14.14.14.14 | 15 | 16

Result that I expect:
4.4.4.4 | 6 | 6
11.11.11.11 | 12 | 13
14.14.14.14 | 15 | 16

My main field is the IP, the others fields are only important if the IP is equal.
ex: if I have:
IP | Field2 | Field3
4.4.4.4 | 5 | 6
4.4.4.4 | 7 | 8
Then I see that the IP is equal, I should add the Field2 to compare.

Just to resume:
- If I can compare by IP, GREAT;
- If I can compare by IP, and then for field2, AWESOME;

Thanks for all the help.

New Member

If you can share the purpose of your search, it will be easier to help you with the search query.
From what you are explaining, the best way to do it will be to use the below search query:

Run for the last 2 weeks, the below search will compare by IP field2 and field3.

index=xxx | table IP field1 field2 | stats count by IP field1 field2 | where count = 1 | fields - count

Explanation:

you are counting the number of rows with the same fields IP, field1 and field2.
Let me take you step by step:

index=xxx | table IP field1 field2
IP | Field 2 | Field 3
1.1.1.1 | 2 | 3
4.4.4.4 | 5 | 6
7.7.7.7 | 8 | 9
1.1.1.1 | 2 | 3
4.4.4.4 | 5 | 6
4.4.4.4 | 6 | 6
7.7.7.7 | 8 | 9
11.11.11.11 | 12 | 13
14.14.14.14 | 15 | 16

index=xxx | table IP field1 field2 | stats count by IP field1 field2

IP | Field 2 | Field 3 | count
1.1.1.1 | 2 | 3 | 2
4.4.4.4 | 5 | 6 | 2
7.7.7.7 | 8 | 9 | 2
4.4.4.4 | 6 | 6 | 1
11.11.11.11 | 12 | 13 | 1
14.14.14.14 | 15 | 16 | 1

index=xxx | table IP field1 field2 | stats count by IP field1 field2 | where count = 1

IP | Field 2 | Field 3 | count
4.4.4.4 | 6 | 6 | 1
11.11.11.11 | 12 | 13 | 1
14.14.14.14 | 15 | 16 | 1

index=xxx | table IP field1 field2 | stats count by IP field1 field2 | where count = 1 | fields - count ( what you are expecting as a result)

IP | Field 2 | Field 3
4.4.4.4 | 6 | 6
11.11.11.11 | 12 | 13

## 14.14.14.14 | 15 | 16

If you are searching to analyze the results with the same IP, use the below query:

index=xxx | table IP field1 field2 | stats count by IP field1 field2 | where count > 1 |stats list(field2) list(field3) by IP

Let me know if this will solve your issue.

Get Updates on the Splunk Community!

#### There's No Place Like Chrome and the Splunk Platform

Watch On DemandMalware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...

#### The Great Resilience Quest: 5th Leaderboard Update

The fifth leaderboard update for The Great Resilience Quest is out &gt;&gt; &#x1f3c6; Check out the ...

#### Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...