Hi, I wonder whether someone may be able to help me please with something that I just don't understand.
I'm using the query below with the date range of 'Yesterday' which returns the figure of 2699.
index=main auditSource=preferences auditType=TxSucceeded | dedup detail.input-ur | search "detail.input-preference-tal"=true |stats count(detail.input-ur) AS "Number of unique URs that opted in"
I then have tried using the following with the same date range:
index=main auditSource=preferences auditType=TxSucceeded | search "detail.input-preference-tal"=true | dedup detail.input-ur|stats count(detail.input-ur) AS "Number of unique URs that opted in"
But this returns a figure of 2703 and I don't understand why.
Could someone possibly look at this please and explain the logic behind the different values?
Many thanks and kind regards
Chris
Consider following sample data
detail.input-preference-tal detail.input-ur
FALSE A
TRUE B
FALSE A
FALSE C
TRUE A
TRUE B
FALSE D
FALSE C
Running query in following order (first dedup and then filter)
| dedup detail.input-ur | search detail.input-preference-tal=true
Output - 1 row (the dedup will give 4 rows with latest value for each detail.input-ur then only keep detail.input-preference-tal=true)
detail.input-preference-tal detail.input-ur
FALSE A ***will be removed by search filter
TRUE B
FALSE C ***will be removed by search filter
FALSE D ***will be removed by search filter
Running query in following order (first filster and then dedup)
| search detail.input-preference-tal=true | dedup detail.input-ur
Output - 2 rows (the search will just keep the events with detail.input-preference-tal=true and then keep the latest for each detail.input-ur)
detail.input-preference-tal detail.input-ur
TRUE B
TRUE A
TRUE B will be removed by dedup
You can see based on data, order of dedup and filter can change the count of rows and possibly that's what's happening here.
Consider following sample data
detail.input-preference-tal detail.input-ur
FALSE A
TRUE B
FALSE A
FALSE C
TRUE A
TRUE B
FALSE D
FALSE C
Running query in following order (first dedup and then filter)
| dedup detail.input-ur | search detail.input-preference-tal=true
Output - 1 row (the dedup will give 4 rows with latest value for each detail.input-ur then only keep detail.input-preference-tal=true)
detail.input-preference-tal detail.input-ur
FALSE A ***will be removed by search filter
TRUE B
FALSE C ***will be removed by search filter
FALSE D ***will be removed by search filter
Running query in following order (first filster and then dedup)
| search detail.input-preference-tal=true | dedup detail.input-ur
Output - 2 rows (the search will just keep the events with detail.input-preference-tal=true and then keep the latest for each detail.input-ur)
detail.input-preference-tal detail.input-ur
TRUE B
TRUE A
TRUE B will be removed by dedup
You can see based on data, order of dedup and filter can change the count of rows and possibly that's what's happening here.
Hi @somesoni2 thank you very much for taking the time to reply to my post and for putting the answer together it's greatly appreciated.
I also understand your logic and seems a very reasonable explanation.
Once many thanks and kind regards
Chris
it is | dedup detail.input-ur
command because it Remove duplicates of results with the same detail.input-ur value.
for more information see how to use the dedup command in doc
Hi thank you for coming back to me with this. If you look at both queries, they both contain the 'dedup' command which is why I'm a little confused as to why their is a difference in the results.
Kind regards
Chris