topic Re: need help in order to compare 2 columns and display one related field in Splunk Search

need help in order to compare 2 columns and display one related field

maz38 — Thu, 29 Oct 2020 08:52:14 GMT

Hi, I'm Alex from France
as almost everyone here, I need some splunk guru ^^

fields computer and user are in index1, computer2 is in index2
I need a table with computer and related user fields, but only computers which are not in computer2
I can't get my table, please help me!

Re: need help in order to compare 2 columns and display one related field

renjith_nair — Thu, 29 Oct 2020 09:23:54 GMT

Assuming that computer and computer2 have similar values but different field names.

Try

index="index1" OR index="index2" |eval comp_name=coalesce(computer,computer2) |stats list(user) as user , dc(index) as dc_index by comp_name |where dc_index < 2

You may add other interesting fields to stats

Re: need help in order to compare 2 columns and display one related field

maz38 — Thu, 29 Oct 2020 09:50:25 GMT

Hi, thank you for your quick answer

I think it is not working, I have some computers in both fields that are displayed...

I'm waiting 12 results, got 25...

Re: need help in order to compare 2 columns and display one related field

renjith_nair — Thu, 29 Oct 2020 09:58:06 GMT

Do you have some sample data from both dataset?

Can you try this and check whether the output is right

index="index1" OR index="index2" |eval comp_name=if(isnull(computer),computer2,computer) |eventstats dc(index) as dc_index by comp_name |table index,comp_name,computer,computer2,dc_index

comp_name should have values either from index1 or index2 and dc_index should have count 2 for matching records of computer and computer2

Re: need help in order to compare 2 columns and display one related field

maz38 — Thu, 29 Oct 2020 10:02:29 GMT

OK, I think I need to explain more...

a computer can be multiple times in computer1, and a computer2 item is always present in computer1, but not the over way around

Re: need help in order to compare 2 columns and display one related field

renjith_nair — Thu, 29 Oct 2020 10:19:35 GMT

Ok, here is the sample data , I am trying

index1 ==== commputer="computer1", user="user1" commputer="computer1", user="user1" commputer="computer2", user="user2" commputer="computer2", user="user2" commputer="computer3", user="user3" commputer="computer4", user="user4" commputer="computer4", user="user4" commputer="computer4", user="user4" commputer="computer4", user="user4"

index2 == computer2="computer1", user="user1" computer2="computer2", user="user2"

I get 2 index for common records computer1 and computer2 since they are in both

Let me know what's different from the dataset and expected output

Re: need help in order to compare 2 columns and display one related field

maz38 — Thu, 29 Oct 2020 11:02:52 GMT

"comp_name should have values either from index1 or index2 and dc_index should have count 2 for matching records of computer and computer2"

I think it's not the good way, because an item can be multiple times in computer1, but there is no empty fields, computer1 and computer2 are always not null

or maybe with some dedup on fields?

Re: need help in order to compare 2 columns and display one related field

maz38 — Thu, 29 Oct 2020 11:03:51 GMT

I can't give you any data, my company is working in cybersecurity.