Splunk Search

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

splunk_exercice
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)
0 Karma
1 Solution

elliotproebstel
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".

View solution in original post

0 Karma

phifa
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

0 Karma

elliotproebstel
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".

0 Karma

splunk_exercice
New Member

Hi guys, sorry for the delay in answer.

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

All Splunkers rocks!!

0 Karma

elliotproebstel
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
0 Karma

splunk_exercice
New Member

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

0 Karma

splunk_exercice
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.

0 Karma

elliotproebstel
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?

0 Karma

splunk_exercice
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.

0 Karma

phifa
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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...