topic Re: Why is my subquery returning duplicate values? in Getting Data In

Why is my subquery returning duplicate values?

abouttathagata — Wed, 30 Jan 2019 17:07:53 GMT

Hello,

I am trying to run a query, which will give me the results not returning by the inner query. Basically any userid can have url="/data/a.jsp" and also url="^/data/abc.* ". I want userids having url="/data/a.jsp" to not appear in the search for url ="(^/data/abc.*) ................". Here is the main query:

host="hostname" sourcetype="source_type" NOT 
[search  host="hostname" sourcetype="source_type" | search url = "/data/a.jsp" | fields userid] | 
search userid!="-" | regex url="(^/data/abc.*) |(^/data/def.*)|(^/data/ghi.*)|(^/data/klm.*)" |
dedup url | eval user_status = "no" | dedup userid| 
lookup main_data userid OUTPUT userid, first_name,last_name| table userid, first_name, last_name, user_status

I tried several ways, but still the duplicate userids are coming. Please help me out. Thanks in advance.

Regards,
Arka

Re: Why is my subquery returning duplicate values?

richgalloway — Wed, 30 Jan 2019 17:58:55 GMT

Try this to filter userids in the subsearch.

host="hostname" sourcetype="source_type" NOT 
[ search host="hostname" sourcetype="source_type" | search url = "/data/a.jsp" userid!="-" | stats count by userid | fields userid | format ]
| regex url="(^\/data\/abc\.)|(^\/data\/def\.)|(^\/data\/ghi\.)|(^\/data\/klm\.)" 
| dedup userid | eval user_status = "no"
| lookup main_data userid OUTPUT userid, first_name,last_name
| table userid, first_name, last_name, user_status

Re: Why is my subquery returning duplicate values?

abouttathagata — Wed, 30 Jan 2019 18:27:16 GMT

Thanks for your quick response. But still the same. The userid present in search url = "/data/a.jsp" still appearing. I am not sure but looks like the inner query not returning anything. If I run it individually it is running fine though.

Re: Why is my subquery returning duplicate values?

woodcock — Wed, 30 Jan 2019 19:41:31 GMT

Try this:

index=YouShoulAlwaysSpeciryIndexValues host="hostname" sourcetype="source_type" userid!="-"
NOT [search  host="hostname" sourcetype="source_type" url = "/data/a.jsp" | stats count BY userid | table userid]
| regex url="(^/data/abc.*) |(^/data/def.*)|(^/data/ghi.*)|(^/data/klm.*)" 
| dedup userid
| lookup main_data userid OUTPUT userid first_name last_name
| eval user_status = "no" 
| table userid first_name last_name user_status

Re: Why is my subquery returning duplicate values?

abouttathagata — Thu, 31 Jan 2019 06:04:54 GMT

Thanks woodcock. But still I am getting the duplicate values.

Re: Why is my subquery returning duplicate values?

vishaltaneja070 — Thu, 31 Jan 2019 06:28:23 GMT

Hello @abouttathagata

Output of this query is also having duplicate userid:

 host="hostname" sourcetype="source_type" NOT 
 [search  host="hostname" sourcetype="source_type" | search url = "/data/a.jsp" | fields userid] | 
 search userid!="-" | regex url="(^/data/abc.*) |(^/data/def.*)|(^/data/ghi.*)|(^/data/klm.*)" |
 dedup url | eval user_status = "no" | dedup userid

Re: Why is my subquery returning duplicate values?

abouttathagata — Thu, 31 Jan 2019 08:19:42 GMT

yes it is the same query right. So it will give the duplicate userid only.

Re: Why is my subquery returning duplicate values?

vishaltaneja070 — Tue, 29 Sep 2020 23:01:40 GMT

Hello @abouttathagata

If at the end of query, dedup userid is mentioned and still you are able to see duplicate userid, then i think the issue is with data. Same userid has either different case or having extra space in the value etc.

Re: Why is my subquery returning duplicate values?

abouttathagata — Thu, 31 Jan 2019 10:21:02 GMT

No Hope. Still same result. Data is not a problem I think.

Re: Why is my subquery returning duplicate values?

vishaltaneja070 — Thu, 31 Jan 2019 10:31:47 GMT

is it possible to put two duplicate set you are getting while running the above command?

Re: Why is my subquery returning duplicate values?

abouttathagata — Tue, 29 Sep 2020 23:01:45 GMT

I am using following query to get the data for user status = yes

Result:

userid = sam01
first_name=sam
last_name=Rogers
user_status=yes
url=/data/a.jsp

following query to get the data for user status = no

Result:

userid = sam01
first_name=sam
last_name=Rogers
user_status=no
url=/data/*

Re: Why is my subquery returning duplicate values?

woodcock — Tue, 12 Feb 2019 16:32:19 GMT

The problem is this line:

 | lookup main_data userid OUTPUT userid first_name last_name

Because that file contains duplicate userid values AND because you are outputting userid again (which is pretty silly), it is doing exactly what you are telling it to do and outputting them all on each line. First, fix your lookup file like this:

| inputlookup main_data
| dedup userid
| outputlookup main_data