topic Re: How to use join to compare results from a csv file? in Splunk Search

How to use join to compare results from a csv file?

queryboy — Mon, 10 Oct 2022 20:15:49 GMT

this query shows all employees in the company:

I need to compare with a CSV that shows all of the people who did their training, this CSV contains the field Submitted

How would you structure the join to compare if xyz employee is in the CSV of people who completed their training?

Re: How to use join to compare results from a csv file?

richgalloway — Mon, 10 Oct 2022 20:27:47 GMT

Does the CSV contain only the Submitted field? If so, what are the values of that field (names, email addresses, etc.)? If the CSV contains other fields, what are they? Something is needed to correlate the CSV to the indexed data.

Re: How to use join to compare results from a csv file?

yuanliu — Mon, 10 Oct 2022 20:28:18 GMT

Set up your CSV as a lookup. (You can do this via Splunk Web.) The search command for matching is lookup. If your CSV contains a column called primaryWorkEmail, it is as simple as

| lookup mylookup pimaryWorkEmail

If, on the other hand, the corresponding column in lookup is eMail, for example, do

| lookup mylookup pimaryWorkEmail as eMail

Hope this helps

Re: How to use join to compare results from a csv file?

queryboy — Mon, 10 Oct 2022 22:48:23 GMT

I uploaded the CSV to splunk this is the query:

| inputlookup trainingoct10.csv
| dedup Applicant_Email
| table Applicant_First_Name, Applicant_Last_Name, Applicant_Email, County_Searched, Training_Status

how can I structure my query so it will check for each of the names inside this CSV against my main query? and be able to add a field saying "in list" or "not in list"

sorry I am new to splunk and this is getting difficult for me.
@yuanliu @richgalloway

Re: How to use join to compare results from a csv file?

yuanliu — Mon, 10 Oct 2022 22:58:59 GMT

how can I structure my query so it will check for each of the names inside this CSV against my main query? and be able to add a field saying "in list" or "not in list"

Can you explain what "not in list"? Specifically, if a user is in main search, but not found in CSV. Is this the definition of "not in list"?

If so, you shouldn't need inputlookup. The command is lookup.

index=EmployeeData AND sourcetype=Directory* earliest=@d | search NOT Hire_Date IN ("2021-11-11","2021-05-22","2022-08-02", "2021-11-11") | search Worker !="Level05" Termination_Date="" Training_Performed="" | table First, Last, primaryWorkEmail, Training_Performed | lookup trainingoct10.csv Applicant_Email AS primaryWorkEmail | eval in_list = if(isnull(Training_Status), "no", "yes")

Re: How to use join to compare results from a csv file?

queryboy — Tue, 11 Oct 2022 16:45:46 GMT

Thank you for this, do you know how would you structure the query to if any of the Applicant_Email(employe email) is in any of the rows of the primaryWorkEmail(employee email in directory) and basically have a new column saying "in_list" for the ones that have a match and "not in list" for the ones that doesn't have a match? @yuanliu you are helping me a lot is just that one piece I need to get straight

Re: How to use join to compare results from a csv file?

yuanliu — Wed, 12 Oct 2022 07:22:37 GMT

I am not sure what is being asked. My previous code already added a column named "in_list". It takes values of either "yes" or "no". If you want to change the display string, just change those values.

Re: How to use join to compare results from a csv file?

queryboy — Wed, 12 Oct 2022 12:19:52 GMT

@yuanliu it gives me all results as not in list, I did this with excel and there are some names that are in list

| eval in_list = if(isnull(Training_Status), "not in list", "in list")

is there a way to do a eval and check not for training status but only if emails in the lookup(csv) are in the main search?

like : | eval in_list = if(Applicant_Email in primaryWorkEmail), "in list", "not in list")

Thank you for your help

Re: How to use join to compare results from a csv file?

yuanliu — Wed, 12 Oct 2022 19:13:00 GMT

@queryboy The way lookup works is that if the lookup field(s) - in my sample code, Applicant_Email AS primaryWorkEmail, match, the command will output other fields of the matching record. Therefore, presence of other field(s) is used to detect a match. Consequentially, the absence of other field(s) indicates absence of a match.

My sample code assumed that every trainee in that CSV file has a Training_Status (that is not zero length). If this assumption is not true, pick another field that is always populated. For example, if Applicant_Last_Name is always populated,

In short, your CSV must contain at least one more column (other than Applicant_Email) that is fully populated. If you are uncertain of such a condition in real data, you should add a dummy column to CSV that has a non-zero-length value in every row. (That's what I had done in one extreme condition.) Then, use | eval in_list = if(isnull(dummy), "not in list", "in list").

Re: How to use join to compare results from a csv file?

queryboy — Wed, 12 Oct 2022 20:26:33 GMT

@yuanliu you are awesome! I tested with Applicant_Email for the Eval as you mentioned and is working just fine! Thank you!