Dashboards & Visualizations

Compare 2 CSV files

nomarja1
Explorer

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

ClassDomainHostnameName
UserroboticROB-SVR01Administrator
GrouproboticROB-SVR01Advanced_users_IT
GrouproboticROB-SVR01Advanced_users_HR

 

e.g: GroupMembers.CSV

Namemember
Advanced_users_ITuser_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?

 

ClassDomainHostnameNameMembers
UserroboticROB-SVR01AdministratorUser Account
GrouproboticROB-SVR01Advanced_users_ITuser_IT_01
user_IT_02
user_IT_03
GrouproboticROB-SVR01Advanced_users_HRuser_HR_01
user_HR_02
user_IT_01
Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| inputlookup Accounts01.CSV
| append
  [| inputlookup GroupMembers.CSV]
| stats values(member) as Members by Class Domain Hostname Name
| fillnull value="User Account" Members
0 Karma

nomarja1
Explorer

@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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

nomarja1
Explorer

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.

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

nomarja1
Explorer

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.

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...