Removing unwanted records for a single output?

[smanojkumar]

Hi There,

    These results are for a particular serial number, we do have many results like this for several serial number, 
   Row 1 are the results from index

   Row 2 are the results from lookup file,

The objective is remove the unwanted data, When the src_name consists of "aap", then its unwanted data, by removing app in src_name field in row1(which is result of index), the same src_name should not be considered, which in the sense, these both records having same src_name of a particular number, these both recors should be excluded in results.

Eg. Input

item	email_new	item_model	in_inventory	is_apple	src_name	src_name_concat	serial_number
5cg01233	hello@company.com	HP	1	0	aap-5cg01233	aap-5cg01233	5cg01233
s102910	hai@company.com	1	0	5cg0233	5cg01233
5cg1435	yess@company.com	Dell	1	0	5cg1435	5cg1435	5cg1435
s109525	no@company.com		1	0	5cg1435	5cg1435

Output 
-since only row is not consists of aap, so considering

item	email_new	item_model	in_inventory	is_apple	src_name	src_name_concat	serial_number
5cg1435	yess@company.com	Dell	1	0	5cg1435	5cg1435	5cg1435

[ITWhisperer]

Your algorithm is unclear, for example, why has row 4 also been excluded from the output?

[smanojkumar]

I had removed 4th row because the src_name for row 3 (data from index) and row 4 (data from lookup file) are having same src_name, so we removed duplicate.

[ITWhisperer]

OK, so why remove row 2?

Also, is there an indication in the event that it can from the lookup? If not, how do you know to remove row 4 and not row 3?

[smanojkumar]

If src_name consists of "aap" in it, which means, it and its duplicates should not be taken as values, 
 if we remove "aap" in src_name field of row 1, which is nothing but row 2 src_name, so its duplicates, removing that

[ITWhisperer]

Row 1 src_name is "aap-5cg0233", without the app- it is "5cg0233". Row 2 src_name is "5cg01233" - these are not the same

[smanojkumar]

I'm so sorry, It was my mistake! I had updated now!

[ITWhisperer]

Thanks. You still haven't explained how you know row 4 is to be removed rather than row 3?

[smanojkumar]

The reason that we are taking row 3 is.., 
Row 1 from index

Row 2 from lookup

Row 3 from index

Row 4 from lookup, Data from index is taken into consideration rather than taking from lookup, also we can say item should be equal to src_name, when the data is comming from index, where in row 1, app should be removed and then compared with item, so we can come to conclusion its from index, 

[ITWhisperer]

| eval aap=if(match(src_name,"^aap-"),1,null())
| eval real_src_name=ltrim(src_name,"aap-")
| eventstats values(aap) as aap by real_src_name
| where isnull(aap) AND item=src_name
| fields - aap real_src_name

[yuanliu]

Your illustrated data don't fit your description unless src_name in the first row is aap-5cg01233 instead of aap-5cg0233.  With this assumption, you can use something like

| eval bad_src=if(match(src_name, "^aap-"), replace(src_name, "^aap-", ""), null())
| eventstats values(bad_src) as bad_src
| where replace(src_name, "^aap-", "") != bad_src

You can use the following data emulation for testing and compare with real data.

| makeresults
| eval _raw = "item,email_new,item_model,in_inventory,is_apple,src_name,src_name_concat,serial_number  
5cg01233,hello@company.com,HP,1,0,aap-5cg01233,aap-5cg01233,5cg01233
s102910,hai@company.com,1,0,5cg0233,5cg01233
5cg1435,yess@company.com,Dell,1,0,5cg1435,5cg1435,5cg1435
s109525,no@company.com, ,1,0,5cg1435,5cg1435,"
| multikv
``` data emulation above ```