topic Re: How to compare two log files in Splunk Search

How to compare two log files

isedrof — Tue, 07 Jul 2015 08:51:18 GMT

Hello everybody,

I'm working on two log files. The first one 'Collab.csv' seems to be like:

user_name       company    position
bob make        C1         Eng
Alice nelly     C2         Eng
Ashely gerard   C3         HR

And the second one "logapp.csv" has this form:

user_name       user_id    application   sing_in_date   account_validity
bob make        bob.m      app1          10/06/2014     01/05/2015
Alice nelly     alice.n    app2          5/01/2015      01/05/2017
Ashely gerard   ashely.g   app3          07/04/2014     01/05/2016

So the aim here is to retrieve all "users" that exists in the file "logapp.csv" that DO NOT exist in the first file "Collab.csv".
What I have done before is put the first one as a lookup file, and the second one I uploaded via "Add data". I tried all combinations to get results after search, but I was not successful. I'm new to Splunk and I need your help please.

Thank you

Re: How to compare two log files

MuS — Tue, 07 Jul 2015 09:11:21 GMT

Hi isedrof,

a quick and dirty approach is this:

| inputlookup logapp.csv | search NOT [ search source=Collab.csv | dedup user_name | table user_name ]

This will read the log app.csv and searches for all user_names that are not returned by the sub search on the other cvs file.

Hope that helps ...

cheers, MuS

Re: How to compare two log files

isedrof — Tue, 07 Jul 2015 09:28:18 GMT

Thank you for ur answer, just one more question which one i put as a lookup file and a data (index) ?

Re: How to compare two log files

MuS — Tue, 07 Jul 2015 09:41:35 GMT

Basically you could setup both cvs files as lookups and use them with inputlookup or index both cvs files to some index and use this index in your search.

Re: How to compare two log files

isedrof — Tue, 07 Jul 2015 09:46:56 GMT

i'm sorry to bother you with much questions but i'm new in splunk. so i'll say to you what i did until now.
I added the first file "Collab.csv" into data (i guess this is what we call index in splunk language). after that i added the second file into Lookups (Lookups file and definition). and i'm trying now your approach by typing it on the search bar. it's correct what i have done until now ?

Re: How to compare two log files

MuS — Tue, 07 Jul 2015 09:59:51 GMT

Try this:

| inputlookup logapp.csv | search NOT [ search index=* source=Collab.csv | dedup user_name | table user_name ]

Re: How to compare two log files

isedrof — Tue, 07 Jul 2015 10:43:21 GMT

Thank your very much it works.

Re: How to compare two log files

isedrof — Tue, 29 Sep 2020 06:36:51 GMT

Hey,
Please, to make a filter on the "user_name" So that we extarct those that does not start with "WS" for example, and filter "sing_in_date<08/07/2015 " .. how we can do that ?
Thanks again.

Re: How to compare two log files

MuS — Wed, 08 Jul 2015 19:57:20 GMT

Regarding the user_name simply but another NOT user_name=ws* somewhere in the search pipe like this:

| inputlookup logapp.csv | search NOT [ search index=* source=Collab.csv NOT user_name=WS* | dedup user_name | table user_name ]

Regarding the sign_in_date this is tricky, because you will need to transform it first into a date value so Splunk can use it an numeric value and not as string. Maybe this will do the trick?

 | inputlookup logapp.csv | search NOT [ search index=* source=Collab.csv NOT user_name=WS* | dedup user_name | table user_name ] | eval sign_in_date=strptime(sign_in_date, "%m/%d/%Y") | search sing_in_date<08/07/2015

This is untested!

Re: How to compare two log files

isedrof — Thu, 16 Jul 2015 08:00:18 GMT

Hey,
If we want to search in 2 lookups files in the same time instead of searching in one file.
thank you.

Re: How to compare two log files

MuS — Tue, 21 Jul 2015 22:53:02 GMT

do it like this:
| inputlookup one | inputlookup append=t two | ...