How can I join a search to a lookup to see where data is missing?

[andrewtrobec]

Hello,
I am writing a search to figure out which users haven't loggedtheir hours. For a list of all users I have a lookup:

Name,Id,Team
Name1,id1,Team1
Name2,Id2,Team1
Name3,Id3,Team2
Name4,Id4,Team2

The data for their hours is in the following format:

Id,Date,Task,Hours
Id1,2016-01-01,Task1,3
Id1,2016-01-01,Task2,4
Id1,2016-01-01,Task3,1
Id2,2016-01-01,Task2,6
Id2,2016-01-01,Task4,2
Id3,2016-01-01,Task4,4
Id1,2016-01-02,Task2,4
Id1,2016-01-02,Task3,5
Id2,2016-01-02,Task1,5
Id2,2016-01-02,Task2,2
Id3,2016-01-02,Task1,4
Id3,2016-01-02,Task2,1
Id3,2016-01-02,Task3,2

What I'm trying to create is a chart that tells me the total hours a user has logged per day, regardless of task. In order to get the full list of users, I start with the lookup (in the above example user Name4 has not logged any hours, but I need the chart to tell me that). No matter what I use, I cannot get the entire list of hours by day for each user. I can only get the latest one:

| inputlookup resource_list.csv
| join type=outer Id [ search index="hours"
 | stats sum(Hours) as Hours by Id, Date ]
| table Name, Team, Hours, Date
| sort Team, Name

I have also tried using mvzipto create a multivalue that I expand afterwards, but this doesn't work either. I'm looking to get output like:

Name,Team,2016-01-01,2016-01-02...
Name1,Team1,8,9...
Name2,Team1,8.7...
Name3,Team2,4,9...
Name4,Team2,0,0...

Is this possible? Any help would be greatly appreciated!

Thank you and best regards,

Andrew

[sundareshr]

Try this

index="hours" 
| eval Name=Name."#".Team
| chart sum(Hours) as Hours over Name by Date
| rex field=Name "(?<Name>[^#]+)#(?<Team>.*)"
| append [| inputlookup resource_list.csv | table Name Team]
| fillnull value=0
| dedup Name Team

[andrewtrobec]

Hello,
I don't think this will work since the "Name" and "Team" fields are part of the lookup, not the index. Also, would this not limit the final output to only the resources that have charged hours?
Thank you for taking the time to consider my issue!
Best regards,
Andrew

[gokadroid]

How about you try this to get the entire list of hours by day for each user, if the lookup definition for your csv is called resource_list_def :

index="hours"
| lookup resource_list_def Id as Id OUTPUT Name as Name
| stats sum(Hours) as Hours by Id, Name, Date