topic Re: Compare 2 CSV files in Dashboards & Visualizations

Compare 2 CSV files

nomarja1 — Thu, 02 Dec 2021 16:29:55 GMT

I have two CSV files. One files has the name of the accounts and servers where the accounts are added.

The second CSV file I have a lookup breaking down the groups members.

The field name is in common with both CSV files.

e.g: Accounts01.CSV

Class	Domain	Hostname	Name
User	robotic	ROB-SVR01	Administrator
Group	robotic	ROB-SVR01	Advanced_users_IT
Group	robotic	ROB-SVR01	Advanced_users_HR

e.g: GroupMembers.CSV

Name	member
Advanced_users_IT	user_IT_01 user_IT_02 user_IT_03
Advanced_users_HR	user_HR_01 user_HR_02 user_IT_01

Is there any way to combine both files to match the names and adding a new column showing the members, so the result can be like this?

Class	Domain	Hostname	Name	Members
User	robotic	ROB-SVR01	Administrator	User Account
Group	robotic	ROB-SVR01	Advanced_users_IT	user_IT_01 user_IT_02 user_IT_03
Group	robotic	ROB-SVR01	Advanced_users_HR	user_HR_01 user_HR_02 user_IT_01

Re: Compare 2 CSV files

ITWhisperer — Thu, 02 Dec 2021 17:33:19 GMT

Re: Compare 2 CSV files

nomarja1 — Thu, 02 Dec 2021 19:42:43 GMT

@ITWhisperer Thanks for replying, but unfortunately, that didn't work, it is showing the member fields "User Account" where it should show the member of the group

Re: Compare 2 CSV files

ITWhisperer — Thu, 02 Dec 2021 19:48:14 GMT

Your example had "User Account" in that field - what value do you want in the field and where does it come from?

Re: Compare 2 CSV files

nomarja1 — Thu, 02 Dec 2021 19:55:08 GMT

I have 2 CSV in the same format as I described.

I want to fill the add the member field displaying the members from the CSV GroupMembers.

I managed to get that done, using JOIN type=left max=0, which is bringing the result I want. I know is not the best but I am going to use that for now.

Re: Compare 2 CSV files

ITWhisperer — Thu, 02 Dec 2021 20:16:38 GMT

Sorry, too many fields in the by clause - try it this way

| makeresults | eval _raw="Class Domain Hostname Name User robotic ROB-SVR01 Administrator Group robotic ROB-SVR01 Advanced_users_IT Group robotic ROB-SVR01 Advanced_users_HR" | multikv forceheader=1 | table Class Domain Hostname Name | append [| makeresults | eval _raw="Name member Advanced_users_IT user_IT_01,user_IT_02,user_IT_03 Advanced_users_HR user_HR_01,user_HR_02,user_IT_01" | multikv forceheader=1 | eval member=split(member,",") | table Name member] | stats values(*) as * values(member) as Members by Name | fillnull value="User Account" Members

Not sure if you need the fillnull or something else if the Name has no entry in the GroupMembers.csv

Re: Compare 2 CSV files

nomarja1 — Thu, 02 Dec 2021 20:32:36 GMT

That's perfect @ITWhisperer , I updated my search with your solution and it worked like a charm. The join command was working but made my search a bit slow.

I appreciate your help. thanks again.