index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) | foreach USERID_X USERID_Y USERID_Z [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>) | eval "IP OF <<FIELD>>" = IP] | stats values(USER) as USER values(USERID_X) as USERID_X values(eval('IP OF USERID_X')) as "IP OF USERID_X" values(USERID_Y) as USERID_Y values(eval('IP OF USERID_Y')) as "IP OF USERID_Y" values(USERID_Z) as USERID_Z values(eval('IP OF USERID_Z')) as "IP OF USERID_Z" by transactionID This is the only query which is close to what I require. I only need it to pick the IP address of the user where the USERID_X is not the same as USERID_Y and USERID_Z  ... View more

@yuanliu  USERID and IP Adderess belongs to sourcetype A USERID_X, USERID_Y, USERID_Z and TransactionID belong to sourcetype B. USERID and USERID_X, Y and Z is what is common between both sourcetypes.  USERID_X, Y and Z depict Makers, Checkers and Verifiers where for example Pamela can be a maker and a checker but not verifier i.e UserID_X and Y or she can be a checker and a verifier but not a maker i.e USERID_Y and Z or she can be a maker and a verifier but not a checker i.e USERID_X and Z. What is happening with my query currently, is that it is picking the users correctly, it is also picking IP addresses but it picks IP address for users that are the same.  So if Pamela is a maker and a checker, it will pick her IP and Arthur is the verifier but it instead of picking Arthur's IP it will pick Pamela's IP address because we have used FOREACH and grouped by USER in the query.  Is there a way in which I can give the conditions and say that where Splunk sees USERID_X and USERID_Y is the same, it should pick it as one user and give me the IP address and and it should also display USERID_Z and give me the IP address?  ... View more

@yuanliu I have re-run the query, see below for sample results: SOURCETYPE _TIME USER_ID DEVICE_ID TRANSACTION_ID USERID_X USERID_Y USERID_Z sourcetypeA 2022-08-24 08:14:56 DANIEL 10.10.10.1         sourcetypeA 2022-08-24 08:12:27 DAVID 10.10.50.2         sourcetypeB 2022-08-23 16:59:02     017235 PAMELA PAMELA AMY sourcetypeB 2022-08-23 17:28:07     017550 JOHN STANLEY STANLEY   ... View more

I have re-run the query, see below for sample results: SOURCETYPE _TIME USER_ID DEVICE_ID TRANSACTION_ID USERID_X USERID_Y USERID_Z sourcetypeA 2022-08-24 08:14:56 DANIEL 10.10.10.1         sourcetypeA 2022-08-24 08:12:27 DAVID 10.10.50.2         sourcetypeB 2022-08-23 16:59:02     017235 PAMELA PAMELA AMY sourcetypeB 2022-08-23 17:28:07     017550 JOHN STANLEY STANLEY ... View more

@yuanliu, or anybody else, please assist. How can I display IP addresses for the users? ... View more

I have run what you had requested for and the below are the sample results: SOURCETYPE _TIME TRANSACTION_ID USERID_X USERID_Y USERID_Z sourcetypeB 2022-08-20 12:08:12 012434 PAMELA AMY AMY sourcetypeB 2022-08-20 12:08:12 012434 PAMELA AMY AMY sourcetypeB 2022-08-20 13:16:10 013435 DIANA ARTHUR ARTHUR sourcetypeB 2022-08-20 13:16:10 013435 DIANA ARTHUR ARTHUR   ... View more

TransactionID is in one source type and IP address is in the other source type. So, this use case is for a maker-checker-verifier where somebody can be a maker and checker but not a verifier or someone can be a checker and verifier but not a maker or someone can be a maker and verifier but not a checker. Those are the USERID_X, USERID_Y and USERID_Z respectively. Their IP addresses need to be different as well. If one person is a maker and checker it is okay for their IP to be 10.10.10.1 but the verifier needs to have a different IP address e.g. 10.10.10.2 and so on ... This is where I am stuck. As the IP address of the same individual appears but that of the one who has a different IP address, is not appearing in my results. I am grouping by USER and I think Splunk is taking USERID, USERID_X, USERID_Y and USERID_Z and thinking that the information belongs to one individual and I have realized that this is not the  case because of the below explanation: The information of users, can only be the same for: USER, USERID_X and USERID_Y or USER, USERID_Y and USERID_Z or USER, USERID_X and USERID_Z At no given point are all 4 of these i.e. USER, X, Y and  Z picking the same users at the same time. ... View more

@yuanliu , TransactionID is not present in both source types as well. The common thing in both source types is the USERID, however, they are renamed differently in both source types. Hence, we had to create a field USER to put all the users inside that field. Common things in both source types are USERID, USERCODES, REMARKS and all 3 of these fields are renamed differently in both source types. Other than the above mentioned fields, there is nothing common in both the source types. I need results to be close to this format: index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) | foreach USERID_X USERID_Y USERID_Z [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>) | eval "IP OF <<FIELD>>" = IP] | stats values(USER) as USER values(USERID_X) as USERID_X values(eval('IP OF USERID_X')) as "IP OF USERID_X" values(USERID_Y) as USERID_Y values(eval('IP OF USERID_Y')) as "IP OF USERID_Y" values(USERID_Z) as USERID_Z values(eval('IP OF USERID_Z')) as "IP OF USERID_Z" by transactionID But I need information to be picked correctly for each user. What is happening with the above query is as below: USER USERID_X IP OF USERID_X USERID_Y IP OF USERID_Y USERID_Z IP OF USERID_Z BEATRICE   10.10.10.1   10.10.10.1   10.10.10.1 ARTHUR JANE 10.10.10.3 ARTHUR 10.10.10.3 ARTHUR 10.10.10.3   Beatrice's IP is correct. Now, ideally, I should see the IP address of Jane under IP OF USERID_X, which should be 10.10.10.4, but it is picking the IP OF USERID_Y who is Arthur and whose IP is 10.10.10.3 instead, because I have grouped BY USER  and not BY TRANSACTIONID as it was not common in both source types. When I analyze my data and look for Jane and Arthur separately, the users do not have the same IP address and Jane has never used Arthur's IP address. Correct me if I am wrong, when we use the command | foreach  USER_ID, USERID_X, USERID_Y, USERID_Z are we trying to combine the information for all these into 1 one common field called USER? (I have never used the | foreach command before, hence, the question) Something I've realized is that the information of users, can only be the same for: USER, USERID_X and USERID_Y or USER, USERID_Y and USERID_Z or USER, USERID_X and USERID_Z At no given point are all 4 of these i.e. USER, X, Y and  Z picking the same users at the same time. Maybe that's why the IP address is only picked for the user where the information is the same and leaves the IP address of the one where the user is different?  ... View more

@yuanliu Correct me if I am wrong. When we use the command | foreach  USER_ID, USERID_X, USERID_Y, USERID_Z are we trying to combine the information for all these into 1 USER? Something I've realized is that the information can only be the same for: USER, USERID_X and USERID_Y or USER, USERID_Y and USERID_Z or USER, USERID_X and USERID_Z At no given point are USER, X, Y, Z the same at the same time. Maybe that's why the IP address is only picked for the user where the information is the same and leaves the IP address of the one where the user is different? ... View more

I need results to be close to this format: index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) | foreach USERID_X USERID_Y USERID_Z [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>) | eval "IP OF <<FIELD>>" = IP] | stats values(USER) as USER values(USERID_X) as USERID_X values(eval('IP OF USERID_X')) as "IP OF USERID_X" values(USERID_Y) as USERID_Y values(eval('IP OF USERID_Y')) as "IP OF USERID_Y" values(USERID_Z) as USERID_Z values(eval('IP OF USERID_Z')) as "IP OF USERID_Z" by transactionID But I need information be picked correctly for each user. ... View more

When I run this: index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) | foreach USERID_X USERID_Y USERID_Z [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>)] | stats values(USER) as USER by IP transactionID | where mvcount(USER) > 1 It brings something like this: IP transactionID USER 10.10.4.1 some ID JOSEPH JACINTA The issue is that it is picking JOSEPH's IP address only and not JACINTA's and both users are using different IP's when I check individually for each user.   When I run this: index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) | foreach USERID_X USERID_Y USERID_Z [eval USER = if(isnull(<<FIELD>>), USER, <<FIELD>>) | eval "IP OF <<FIELD>>" = IP] | stats values(USER) as USER values(USERID_X) as USERID_X values(eval('IP OF USERID_X')) as "IP OF USERID_X" values(USERID_Y) as USERID_Y values(eval('IP OF USERID_Y')) as "IP OF USERID_Y" values(USERID_Z) as USERID_Z values(eval('IP OF USERID_Z')) as "IP OF USERID_Z" by transactionID I get something close to this: USER USERID_X IP OF USERID_X USERID_Y IP OF USERID_Y USERID_Z IP OF USERID_Z MARY MARY JANE MARY 10.10.1.1 MARY 10.10.1.1 JANE 10.10.1.2 BEATRICE ARTHUR ARTHUR BEATRICE 10.10.2.1 ARTHUR 10.10.2.3 ARTHUR 10.10.2.3 AMY DIANA BELLA AMY 10.10.3.1 DIANA 10.10.3.2 BELLA 10.10.3.6 JOSEPH JACINTA JACINTA JOSEPH 10.10.4.1 JACINTA 10.10.4.1 JACINTA 10.10.4.1 The issue here is that when I use BY Transaction_ID, the IP addresses disappear and I am only left with the fields USER, USERID_X, USERID_Y and USERID_Z. Removing the line BY TRANSACTION_ID populates the whole table but the issue is that IP addresses duplicate across the fields for USER, USERID_X, USERID_Y and USERID_Z and are not correctly mapped to the correct user.  So I see something like: USER USERID_X IP OF USERID_X USERID_Y IP OF USERID_Y USERID_Z IP OF USERID_Z Jane Jane Martha Jane 10.10.1.1 Jane 10.10.1.1 Martha 10.10.1.1 And I know that the IP addresses assigned to Martha and Jane are in-correct  because I have run searches for each individual user to see their individual IP addresses. ... View more

Yes.  ... View more

Transactions being conducted by them. The problem is some data is one sourcetype and IP address is in another sourcetype.  So picking IP addresses of the people is a challenge. ... View more

I require something like the below: USER USERID_X IP OF USERID_X USERID_Y IP OF USERID_Y USERID_Z IP OF USERID_Z MARY MARY JANE MARY 10.10.1.1 MARY 10.10.1.1 JANE 10.10.1.2 BEATRICE ARTHUR ARTHUR BEATRICE 10.10.2.1 ARTHUR 10.10.2.3 ARTHUR 10.10.2.3 AMY DIANA BELLA AMY 10.10.3.1 DIANA 10.10.3.2 BELLA 10.10.3.6 JOSEPH JACINTA JACINTA JOSEPH 10.10.4.1 JACINTA 10.10.4.1 JACINTA 10.10.4.1   Where the row highlighted in bold needs to be fired as an alert as all the IP's are the same. How can I achieve something like the table above? ... View more

I have run the below query as advised. I get no results when I change the values to X,Y and Z respectively. However removing the line USERID_X=* generates results of IP addresses. The users are different in every row, but the IP addresses being picked are for all IP's in a subnet and all subnets and not the specific users that are part of the row. Results are something as below: USER USERID_X USERID_Y USERID_Z IPs Mary Mary Jane Mary Mary Jane 10.10.1.1 10.10.1.2 10.10.1.3 10.10.2.1 10.10.2.2 10.10.2.3 10.10.3.1 10.10.3.2 10.10.3.3 .... .... ....   How can I find out which IP belongs to Mary and which IP belongs to Jane from the long list of IPs? ... View more

I have run the below test query changing the USERID to X, Y and Z respectively: index=myindex (sourcetype="mysourcetypeA" OR sourcetype=mysouretypeB) USERID_X=* | eval USER = mvappend(USERID_A,USERID_X,USERID_Y,USERID_Z) Each time the USERID changes to X,Y or Z, results are populated for USER, USERID_X, USERID_Y and USERID_Z. What I've noticed is that either values in USER, USERID_X and USERID_Y are the same or values in USER, USERID_Y and USERID_Z are the same. ... View more

I am using the below query: index=myindex (sourcetype="mysourcetype" OR sourcetype=mysouretype) |  eval USER = mvappend(USERID_A,USERID_X,USERID_Y,USERID_Z) |  stats dc(IP) as IP_COUNT values(IP) as IP values(USERID_X) values(USERID_Y) values(USERID_Z) BY USER The reason behind usage of mvappend is because the usernames in both source types have different titles.  The results I get with the above query are depicted in the table below: USER IP_COUNT IP USERID_X USERID_Y USERID_Z               The IP that is currently being picked is for "USER" and "USERID_X"  but I require IP addresses of  USERID_Y and USERID_Z as well, as in some situations they vary. After that I need to put in the IF statement to compare IP addresses to make sure that they are not the same. ... View more

Hi Giuseppe, I have tried this, but it still doesn't bring the IP addresses of both Person A and Person B. The query picks the IP address of only Person A. I need both IP addresses to be displayed. ... View more

Hi Giuseppe, Yes. IP address of Person A and Person B are in one source type and the tasks being performed by Person A and Person B are in the other source type and both source types belong to the same index. ... View more

Hi Rich, So I've managed to combine both the indexes but now the challenge I face is that results come in two separate rows instead of one.  One row picks information for AD and the other picks for Access Control. I think this is arising due to naming conventions being slightly different in both indexes. Is there a way we can tell Splunk that person Apple Banana is the same person as Apple Cabbage because "Apple" is the common thing in both indexes? And also have this result in one row instead of two? ... View more