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
. Here is the main query:
="(^/data/abc.*) ................"
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
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
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
yes it is the same query right. So it will give the duplicate userid only.
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.
Try to run this query to better check this:
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 | stats count by userid
No Hope. Still same result. Data is not a problem I think.
is it possible to put two duplicate set you are getting while running the above command?
I am using following query to get the data for user status = yes
host="hostname" sourcetype="source_type" |search userid!="-"
|search url="/data/a.jsp" | eval user_status="yes" | dedup userid
| lookup main_data userid OUTPUT userid, first_name,last_name
| table userid, first_name, last_name, user_status, url
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
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, url
Result:
userid = sam01
first_name=sam
last_name=Rogers
user_status=no
url=/data/*
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
Thanks woodcock. But still I am getting the duplicate values.
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
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.