Splunk Search

Compare 2 fields from different Index

rnikam1412
Loves-to-Learn Everything

I am trying to look for accounts which are not active anywhere in network.

(index=network user=*) OR (index=okta SamAccountName=*) | eval InActive_Accounts=if(user==SamAccountName, "Active" , "NotActive") | table user, SamAccountName, InActive_Accounts

I tried it with coalesce as well but not getting any result.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

From your description, I understand you saying 

Find me all users who are seen in network index, but NOT in okta index and show me those users

then this may work for you

(index=network user=*) OR (index=okta SamAccountName=*) 
| eval userId=coalesce(user, SamAccountName)
| stats values(SamAccountName) as SamAccountName by userId
| where isnull(SamAccountName)

which is saying

  • Get me the data from both indexes
  • Create a new field that contains either the value of user or SamAccountName
  • Aggregate all the values of SamAccountName for that new field
  • Filter out only those fields where there has been no SamAccountName seen

which should tell you all users in the network index, not in the okta index.

If you are looking for all your users (known by some other means) and are trying to determine if they are present in either index, then you will first need a list of the users you expect, e.g. in a lookup file, and then you will need to do something like this, where your_list_of_users.csv contains a column called userId

(index=network user=*) OR (index=okta SamAccountName=*) 
| eval userId=coalesce(user, SamAccountName)
| stats count by userId
| append [ 
  | inputlookup your_list_of_users.csv
  | eval count=0
]
| stats max(count) as count by userId
| where count=0

Hope this helps

 

0 Karma

rnikam1412
Loves-to-Learn Everything

Actually, 

user - field in index=network which has username as value

SamAccountName - field in index=okta which has username as value

I am trying to search SamAccountName against user field in index=network and get the SamAccountName list which are not present in index=network

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Can’t Make It to Boston? Stream .conf25 and Learn with Haya Husain

Boston may be buzzing this September with Splunk University and .conf25, but you don’t have to pack a bag to ...

Splunk Lantern’s Guide to The Most Popular .conf25 Sessions

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

Unlock What’s Next: The Splunk Cloud Platform at .conf25

In just a few days, Boston will be buzzing as the Splunk team and thousands of community members come together ...