Having append issues in joining searches and combining the results

[gopiven]

Hello Experts

Actually I am trying to join the results of two searches.

There are 3 indexes 1a,2b, and 3c with many source types.
In index=1a the field ( say "ClientId" which I required is directly there I am doing the lookup against the file. ( since in the index 1a, both userid and clientId fields are there I can evaluate the Userid and then join the ClientId through the lookup.
But in index=2b, index= 3c I have to evaluate the field "Userid" from different sourcetypes and do input lookup and join the "ClientId" from the same input lookup.
But when I am charting the results with index 2b and 2c , the values for index 1a is not showing however it is showing the huge volume.

Inputlookup Filename: UserId.csv
Inputlookup file format:
Userid Clientid
User1 Client1
User2 Client2

index= "1a"  OR index="2b"  OR index="3c"
|  eval 
Platform = case(
index="1a", "Online",
index="2b", "Mobile",
index="3c", "OtherPlatforms")

[ search sourcetype="onlineindex" AND CATEGORY="{signin}" [inputlookup UserId.csv] ] | append [
 | eval Userid=case(sourcetype=="type1",user,sourcetype=="type2",userids,sourcetype=="type3",useridvalue)
 | lookup Userid.csv Userid AS Userid | join ClientId [inputlookup UserId.csv] ]

 | Stats dc(clientId) as total_clients by date_hour,date_wday,Platform | chart avg(ClientId) over date_hour by platform

ANOTHER METHOD:

As mentioned earlier, for the index-="1a" both userid and clientId fields are there I Can evaluate the Userid and then join the ClientId through the lookup ( instead I am looking for direct Clientid field in the events)
In the below search I am evaluating the clienId like other sourcetypes and joining "ClientId" thru input lookup.

index= "1a"  OR index="2b"  OR index="3c"

|  eval 
Platform = case(
index="1a", "Online",
index="2b", "Mobile",
index="3c", "OtherPlatforms")

| eval Userid=case(sourcetype="onlineindex" AND CATEGORY="{signin}",Userid, sourcetype=="type1",user,sourcetype=="type2",userids,sourcetype=="type3",useridvalue)

| lookup Userid.csv Userid AS Userid | join ClientId [inputlookup UserId.csv] ]

 | Stats dc(clientId) as total_clients by date_hour,date_wday,Platform | chart avg(ClientId) over date_hour by platform

In the above searches I am suspecting only the sourcetype="onlineindex" AND CATEGORY="{signin}" , the AND operator is giving issue. Please assist me.
Is there any better way to redefine the search?
Multisearch also I tried but it is giving some sublimi search error beacuse I am pulling millions of records.

[gcusello]

Hi gopiven,
probably it isn't clear for me the relation between your request and the search you used because there are some problems in your search and I think that it cannot run (or maybe you didn't correctly display the search: use the Code Sample button to display code):

• at first when you use the inputlookup command in a subsearch, put the pipe char "|" in the beginning of the search,
• probably (if I correctly understood) you could use lookup instead of inputlookup,
• you don't need to use join when you have a lookup, because the lookup command is already a join,
• then use always index in your searches because they are more performant,
• then when you use inputlookup command, use the fields command to say which fields to use as keys (in your search it seems that you use both Userid and Clientid as keys),
• after append command, there isn't a search but evals and it isn't correct.

So try something like this:

index= "1a" OR index="2b" OR index="3c" sourcetype="onlineindex" AND CATEGORY="{signin}"
| eval Platform = case(index="1a", "Online",index="2b", "Mobile",index="3c", "OtherPlatforms")
| lookup UserId.csv UserId OUTPUT Clientid 
| eval Userid=case(sourcetype="type1", user, sourcetype="type2", userids, sourcetype="type3", useridvalue)
| Stats dc(clientId) as total_clients by date_hour,date_wday,Platform 
| chart avg(ClientId) over date_hour by platform

surely it isn't correct, but you can understand the approach to follow.

Bye.
Giuseppe

[richgalloway]

The SPL | lookup Userid.csv Userid AS Userid | join ClientId [inputlookup UserId.csv] makes no sense. Once you've done the lookup, joining the same lookup table adds nothing.