Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Compare values of 2 columns in a table

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

ramonnegronvz

New Member

05-01-2020
12:24 PM

I have a table that has **2 columns** with Transaction ID's shown by a stats values() as below:

*| stats values(E-TransactionID) as E-TransactionID values(R-TransactionID) as R-TransactionID*

I'd like to compare the values of both columns and only show the Transaction ID's from **R-TransactionID** that does **NOT** appear in the **E-TransactionID** column.

I've made the following attempts after the stats values() with no luck. Any help is **GREATLY** appreciated.

**Attempt 1** (had to try this anyway):

| table R-TransactionID E-TransactionID

| where R-TransactionID != E-TransactionID

**Attempt 2**:

| eval match=if(R-TransactionID=E-TransactionID, "EQUAL", R-TransactionID)

| stats values(match) as TransactionID

**Attempt 3**:

| foreach R-TransactionID

[eval match=if(R-TransactionID!=E-TransactionID, R-TransactionID, "MATCH")]

| stats values(R-TransactionID) as R-TransactionID values(E-TransactionID) as E-TransactionID values(match) as TransactionID

**Attempt 4** (*similar to previous, but with table instead*😞

| foreach R-TransactionID

[eval match=if(R-TransactionID!=E-TransactionID, R-TransactionID, "MATCH")]

| stats values(R-TransactionID) as R-TransactionID values(E-TransactionID) as E-TransactionID values(match) as TransactionID

1 Solution

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Compare values of 2 columns in a table

richgalloway

SplunkTrust

05-01-2020
01:12 PM

One problem is the `values`

function returns a multi-value field which does not work well with other commands and functions. Usually, `mvexpand`

helps, but you have two multi-value fields you're trying to match up.

Perhaps you could take a step back and describe the original problem you want to solve.

---

If this reply helps you, an upvote would be appreciated.

If this reply helps you, an upvote would be appreciated.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Compare values of 2 columns in a table

ramonnegronvz

New Member

05-01-2020
01:31 PM

Thanks @richgalloway

Within the same index/sourcetype, there are 3 messages which I need to track in order to come up with the Transaction ID's that "matter". In the below query, I'm correlating 2 of the strings within the log events to come up with the E-TransactionID's (from the values of transid). I'm doing another search (string_3) to come up with R-TransactionID, which tells me about the Transaction ID's that "matter", but I want to "ignore" them if the Transaction ID matches E-TransactionID. In other words, my goal is to find the Transaction ID's that appear on R-TransactionID but NOT on E-TransactionID.

From your comment, do you think if I change values to table, I'll be able to accomplish what I need?

Here's a "dummy-down" search query I'm running to come up with both E-TransactionID and R-TransactionID:

index=index*p sourcetype=st*p message="string*1"
| join transid
[ search index=index*p sourcetype=st

| table transid]

| stats values(transid) as E-TransactionID

| append

[search index=index

| stats values(transid) as R-TransactionID]

| stats values(E-TransactionID) as E-TransactionID values(R-TransactionID) as R-TransactionID

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

to4kawa

Ultra Champion

05-01-2020
02:49 PM

sample:

```
| makeresults count=20
| streamstats count
| stats values(count) as R
| eval E=split("1,4,6",",")
| eval tmp=mvsort(mvappend(E,R))
| stats count by tmp
| where count < 2
```

try `mvappend`

and `stats by`

but,

```
index=index_p sourcetype=st_p message="string_1" OR message="string_2"
| stats dc(message) as flag count(eval(message="string_1")) as message1 by transid
| where flag < 2 AND message1 > 0
| table transid
```

Your query does not need them.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Compare values of 2 columns in a table

ramonnegronvz

New Member

05-01-2020
03:18 PM

Thanks @to4kawa

Let me see how can I attempt using mvappend and stats to fit my need here.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Compare values of 2 columns in a table

to4kawa

Ultra Champion

05-01-2020
03:49 PM

Hi @ramonnegronvz

my answer is updated.

Highlighted
##

I must admit, that at the beginning I was trying to figure out how would this help, but after trying it out, and testing it out, it is perfect! Thanks so much @to4kawa!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Compare values of 2 columns in a table

ramonnegronvz

New Member

05-03-2020
03:27 PM