How to compare 2 search results from different indexes and create a column that lists unique values?

[fabrice]

I tried the following query:

index=alldata Application="AZ"
|eval Date=strftime(_time,"%m/%d/%Y %H:%M:%S %Z")
|table Date user username |rename user as User, username as id |dedup id
|appendcols [search index=infor |fields disName userPrin |table disName userPrin |rename disName as Name userPrin as Mail |dedup Mail ]
|fields Date User id Mail Name
|eval "Login Status"=case(id==Mail, "Logged in", id!=Mail, "Never Logged in")
|eval Result=if(id=Mail, "Mail", "NULL")

I would like to create a column in the table that compares values in column id and Mail  and lists unique values (non duplicate).

[fabrice]

Thank you ITWhisperer! it worked

[somesoni2]

Give this a try

index=alldata Application="AZ"
| stats max(_time) as _time by user username
|eval Date=strftime(_time,"%m/%d/%Y %H:%M:%S %Z")
|table Date user username |rename user as User, username as id 
| append [
    search index=infor |stats latest(disName) as Name by userPrin |rename userPrin as id 
]
| stats values(Date) as Date values(User) as User values(Name) as Name by id
|eval "Login Status"=case(isnotnull(Date), "Logged in", id!=Mail, "Never Logged in")
|eval Result=if(isnotnull(Date), "Mail", "NULL")

[ITWhisperer]

There is no guarantee that the events returned by the two searches have id and Mail in the same order or even the same number of events, and appendcols takes no account of any matches, it just added the first event from the second search to the first event of the first search, the second event of the second search to the second event of the first search and so on.

In order to get some sort of matching, you could use join (although this is not recommended for large data sets) or use stats with a by clause specifying the "join" field (you would have to rename one of the fields from one of the searches so it matches the name from the other search)

[fabrice]

Hi ITWhisperer,

Thank you for following up!

The id and Mail fields both have email adresses. the id field has active email and the Mail field list all email addresses (active and non active). I am looking to add a column in the table to list all non active emails.

[ITWhisperer]

Try something like this

index=alldata Application="AZ"
| eval Date=strftime(_time,"%m/%d/%Y %H:%M:%S %Z")
| rename user as User, username as id 
| dedup id
| eval status="Logged in"
| append [search index=infor 
  | fields disName userPrin
  | rename disName as Name userPrin as id
  | dedup id]
| stats values(Date) as Date values(User) as User values(Name) as Name values(status) as status by id