Security

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

fabrice
New Member

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

0 Karma

fabrice
New Member

Thank you ITWhisperer! it worked

0 Karma

somesoni2
Revered Legend

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")
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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)

0 Karma

fabrice
New Member

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.

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...