How to edit my search to list jobs in a table per user, per day?

[Svill321]

Hello,

One of my co-workers is using a search to make a table listing the days the events of interest took place, as well as the corresponding User IDs on those days and the Jobs that are associated with it. So this:

index=index_name date_wday=* User=* Job=*  | bucket span=1d _time |stats count by date_wday User Job _time | search count >25 | stats values(_time) as time list(User) as UserID  values(Job) as JobName list(count) as count  by date_wday | eval Time=strftime(time, "%m-%d-%Y") | fields - time | table Time, date_wday, UserID,JobName,count | sort - Time | rename date_wday as Day_of_week

Creates something like this:

07-19-2017   |     wednesday    |         User1      |         Job1                   |              34
             |                  |          User2      |         Job2                   |              45
             |                  |          User3      |         Job3                   |              34
             |                  |          User3      |         Job4                   |              32

This works fine, but I'm wondering if it is possible to set this up so that if a user is associated with multiple jobs, the jobs appear in one line, as opposed to another entry of the user. For example:

07-19-2017   |     wednesday    |         User1      |         Job1                   |              34
             |                  |          User2      |         Job2                   |              45
             |                  |          User3      |         Job3, Job4             |              34

Is this even possible using the search queries, though?

[somesoni2 · Accepted solution]

Give this a try (deriving date_wday from _time itself for more accuracy)

index=index_name User=* Job=*  | bucket span=1d _time 
|stats count by User Job _time | search count >25 
| stats sum(count) as count values(Job) as Job by _time User delim="," | nomv Job
| stats list(User) as UserID  list(Job) as JobName list(count) as count  by _time 
| eval Time=strftime(_time, "%m-%d-%Y") | eval Day_of_week=strftime(_time,"%A") 
| table Time, Day_of_week, UserID,JobName,count | sort - Time