Comparing two string values

[pmccomb]

I have email address' that are used as user names in two different source types in two different indices. I am trying to compare the two in order to find a list of matches and also the list of ones that do not match for each. I am doing something like this:

index="index1" OR index ="main" sourcetype="SessionCount" OR sourcetype="Identity" Userid=email | table Userid, email
(I just want to output matching fields with this search)

There are a lot of matches between the Userid and email -> I have run individual searches on each and compared the results. However, I receive no matches. Is there possibly an issue with the format of the strings or are there any time comparisons going on that may throw it off?

[somesoni2 · Accepted solution]

Try this

Common values

|set intersect [search index="index1" sourcetype="SessionCount" | stats count by Userid | fields - count ] [search index ="main" sourcetype="Identity" | stats count by email | rename email as UserId | fields - count]

Difference

|set diff [search index="index1" sourcetype="SessionCount" | stats count by Userid | fields - count ] [search index ="main" sourcetype="Identity" | stats count by email | rename email as UserId | fields - count]

[vranjith009]

My requirement was also same like this, but its not working. I have two files like this.
Name

abc
def
ghi

Name0
xyz
abc
ghi

I am expecting common values which are present on both files and difference values.
Eval / if - queries are not working due to values are misplaced in files. I was trying with below query for common values

|set intersect [|inputcsv scc.csv |stats values(Name) as sccm] [|inputcsv lan.csv | stats values(Name0) as Lan]

But its not giving any outputs tried by adding fields also, no luck. can any one help me on this.

[pmccomb]

This did it.. thank you! That was really helpful.

[lukejadamec]

Where do you learn this stuff?

[HiroshiSatoh]

This part is wrong?
「Userid=email」

For example if it is?

index="index1" OR index ="main" sourcetype="SessionCount" OR sourcetype="Identity" | stats values(sourcetype) by Userid, email

Userid, email,values(sourcetype)
001,a@a,SessionCount Identity
002,b@a,SessionCount
003,c@a,Identity

[pmccomb]

Not sure I see anywhere that this is matching the email/Userid values.

[asimagu]

your logic to compare is wrong, with Userid=email you are telling Splunk to look for events with the value "email" in the field Userid

I take Userid is a field and then email is another field, right?

If that's the case you would need something like this:

index="index1" OR index ="main" sourcetype="SessionCount" OR sourcetype="Identity" | table Userid email | where Userid=email  

[pmccomb]

This one seemed like it would work but the table did not return any results. Splunk said it found matching events... but the table returned noting and complained that there was some kind of issue with "| table Userid email | where Userid=email"