how to compare the two fields from different sourcetype, check the existence and display Yes or NO ?

[pgadhari]

I have users in different groups and that has to be compared with master_userlist, to show the permissions in the dashboard panel. I want to compare each group field with master_list. If user in master_list is there in Group1, it should show "Yes" or "correct tickmark" and if it is not there, it should show "No" or "Wrong tickmark", similary for group2, group3 and group4. I have to create a single query for that. Please help.

My scenario:

master_list --> user1, user2, user3, user4, user5, user6, user7, user8, user9, user10
group1 --> user1,user2,user3
group2 --> user1,user4,user5
group3 --> user6,user7,user8,user2,
group4 --> user9,user10,user1,user3

I want a create a tabular display of the above permissions showing the fields like below :

master_list       group1    group2     group3    group4

user1              Yes            Yes              X              Yes
user2              Yes             X              Yes
user3              Yes             X                                 Yes
user4                 X             Yes
user5                 X             Yes
user6                 X                            Yes
user7                 X                             Yes
user8                 X                             Yes
user9                 X                                              Yes
user10               X                                               Yes

Thanks
PG

[pgadhari]

I was able to write and query and resolve the issue. I will close this question.

[niketn]

@pgadhari post your query and accept the same to mark this question as answered. If it is @kamlesh_vaghela 's comments that has led you to finding the solution do accept his answer or upvote as needed.

[pgadhari]

Ya sure will do that. I am verifying certain things.

[kamlesh_vaghela]

@pgadhari

Can you please sample events of master_list and group_list?

[pgadhari]

master_list is nothing but list of all users i.e. 10 users, and group_list is nothing but who have access to that group which will be 3 or 4 users. It is just name of the users in those list. I have given the details in my query above in "My Scenario". Hope you got it ?

[kamlesh_vaghela]

Agree, but master_list and group_list are fields or coming from any lookup. How you are accessing these list in splunk?? Is that any query for that?

[pgadhari]

Active Directory is exporting that list to a csv file in the name of each group as well as master_list, using forwarder I am indexing that csv file and each group is created as a seperate sourcetype in inputs.conf. Even master_list is a group with a different sourcetype. So, consider every column is a different sourcetype. Hope you got it ?

[kamlesh_vaghela]

@pgadhari

I've tried to create a search for you please check it. You can use the same logic to design your search.

I have created master_list event using | makeresults | eval master_list=" user1, user2, user3, user4, user5, user6, user7, user8, user9, user10". You can find this search in main search also.

I have created 'groupevents using| makeresults | eval group="group1|group2|group3|group4", users="user1,user2,user3|user1,user4,user5|user6,user7,user8,user2|user9,user10,user1,user3",group=split(group,"|"),users=split(users,"|") | eval data=mvzip(group,users,"|") | mvexpand data | eval group=mvindex(split(data,"|"),0),users=mvindex(split(data,"|"),1) | table group users` .

And below is my main search.

| makeresults | eval master_list="user1,user2,user3,user4,user5,user6,user7,user8,user9,user10" 
| eval master_list=split(master_list,",") 
| mvexpand master_list | table master_list 
| join max=0 [
| makeresults | eval group="group1|group2|group3|group4", users="user1,user2,user3|user1,user4,user5|user6,user7,user8,user2|user9,user10,user1,user3",group=split(group,"|"),users=split(users,"|") | eval data=mvzip(group,users,"|") | mvexpand data 
| eval group=mvindex(split(data,"|"),0),users=mvindex(split(data,"|"),1) 
| table group users | eval users=split(users,",")] 
| eval group=if(isnotnull(mvfind(users,master_list)),group,"Extra") 
| chart count over master_list by group | fields - Extra

This search will gives you 1 and 0 against the user and group. Means yes and no respectivily. Try and let me know it is worked for you or not.

Thanks

[pgadhari]

Thanks @kamlesh_vaghela for your help and efforts. Appreciate it.

[pgadhari]

same type of question is asked in this below splunk question. Only difference is I am looking for user comparison.

https://answers.splunk.com/answers/690868/how-do-you-compare-values-in-two-columns-created-u.html

[pgadhari]

@kamlesh_vaghela - did you check on this issue ?

[pgadhari]

I think your query is bit different. Actually, I already have a master_list and group list in splunk in a table format. I have below query, which display 2 columns and their respective usernames as the values. Now I want to check if user is present in "user" and "group1" then it should show "Yes" or "righ tickmark symbol" in Group1 column, if not then it should show "No" or "Wrong tickmark symbol". Similarly I have to append other groups as appended column. Actually, this table will show user permission for my Splunk apps. Actually, each group is a Splunk app or dashboard.

User Group1

index=test sourcetype=masterlist | rename "Display Name" as User | sort User | table User | appendcols [search index=test sourcetype=group1 | rename "Display Name" as group1 | sort group1 | table group1] ......

( I have to add more groups as columns)... I have given example above.

I think we will have to do some if condition using match parameter, but I am not sure on how to get this work ?

[kamlesh_vaghela]

Can you please share OP from below search?

index=test sourcetype=masterlist | rename "Display Name" as User | dedup User | sort User | table User

index=test sourcetype=group1 | rename "Display Name" as group1 | dedup group1 | sort group1 | table group1

[pgadhari]

Due to security constraints. I cant share all the output, as they contain the actual usernames. My master list has 60 users, while Group1 user list is 30 users, similarly group2 has some 25 users, similarly group3 has 15 users and so on.

Actually the groupname is nothing but the splunk app name. For e.g. Wifi app - for this the ad group name is splunk_wifi and the users in that group has access to that app, similarly we have more apps and for each app we have created group in Active Directory and added user to that group to give access to that App/Dashboard.

I have added a image on this site. Please check and I will remove it then.

[pgadhari]

https://imgur.com/MKwk0Qy - link for the image

[pgadhari]

There are cases wherein the values of user and group1 are not in the same row, they are jumbled i.e. up and down. So user2 in "user" column will be at row 2 and the same user2, will be at row 1 in "Group1" column and so on.

[pgadhari]

Actually I want to check whether value in User column, exists in "Group1" column, then the result should be "Yes" or it should show "No". I have tried various match options, but it check for field to field row-wise which will not be always matching the users. Hence, I have check the existence of user value across data set ?

[pgadhari]

May be we need to use foreach on user column (masterlist) and check whether that user exists in "Group1" column, then display "Yes" or display "No" ???