Eval On Field With Multiple Values

[jr_arzuaga]

I'm trying to get the time difference of two dates on a table but when my user has multiple values for the end_date and start_dates my time_diff is not calculated. See below:

User end_date start_date time_diff
User1 2011-11-04T13:17Z 2011-11-04 09:18:20 14320
User2 2011-11-04T21:08Z 2011-11-04 14:08:28
2011-11-04T15:16Z 2011-11-04 16:38:16
User3 2011-11-04T15:15Z 2011-11-04 10:18:12
2011-11-04T21:25Z 2011-11-04 14:26:40
2011-11-04T13:35Z 2011-11-04 09:36:45
User4 2011-11-04T20:01Z 2011-11-04 16:03:02 13889

Search:

source="/Users/user/unit_pretest_.json" | lookup UserSessionsLookup user_id as userId | rename username as "User Name" | eval itime=strptime(start_date,"%Y-%m-%d %H:%M:%S") | eval ptime=strptime(end_date,"%Y-%m-%d %H:%M:%S") | eval TimeDiff=ptime-itime | table "User Name", start_date, end_date, TimeDiff

Anyone knows what's wrong here?

[mrobichaud_splu]

Most eval functions balk with multi-value fields. I'm skipping over strptime in these examples, but know that you need a single value field for strptime.

If you just need to work with the first or last values, you can put them into new fields before working with them.

| eval start_date_first=mvindex(start_date, 0)
| eval start_date_last=mvindex(start_date, -1)
| eval end_date_first=mvindex(end_date, 0)
| eval end_date_last=mvindex(end_date, -1)

Another option is to create a separate row for each users start and end date:

| eval periods=mvzip(start_date, end_date)  // create multi-value field for with pairs of comma separated dates
| mvexpand periods                          // separate each pair into separate events
| makemv periods delim=","                  // separate the pair into a multi-value
| eval start_date=mvindex(periods, 0)       // set the first value to start_date
| eval end_date=mvindex(periods, -1)        // set the last value to end_date

I've broken this down a little granularly than necessary. Many of these could be combined, but remember you often need to cast mvindex() output into a type with tostring() or tonumber(). For intance:

| eval foo= mvindex(bar, 0) * 2             // always throws error
| eval foo= tonumber(mvindex(bar, 0)) * 2   // works

[luthfi49]

Is there any answer for this questions ?

[kristian_kolb]

what do the raw events look like?

[jr_arzuaga]

UPDATE:

This doesn't work either

source="/Users/user/unit_pretest_.json" | lookup UserSessionsLookup user_id as userId | rename username as "User Name" | stats sum(strptime(end_date,"%Y-%m-%d %H:%M:%S")-strptime(start_date,"%Y-%m-%d %H:%M:%S")) as TimeDiff by "User Name" | table "User Name", TimeDiff

[jr_arzuaga]

UPDATE:

This is the new search I'm trying:

source="/Users/user/unit_pretest_.json" lookup UserSessionsLookup user_id as userId | rename username as "User Name" | eval itime=strptime(start_date,"%Y-%m-%d %H:%M:%S") | eval ptime=strptime(end_date,"%Y-%m-%d %H:%M:%S") | eval TimeDiff=ptime-itime | stats sum(TimeDiff) as Diff by "User Name" | table "User Name", start_date, end_date, Diff

I get the same thing, data for users with only one row of sessions data (e.g. User1 and User4 above)

[aneeshkjm123]

Can You try this out with stats command. I am not sure.. Just try it out. It may work !

stats eval(ptime-itime) AS TimeDiff by "User Name"

[jr_arzuaga]

Not working. It won't allow me to do eval(ptime-itime). It seems like what I need to do is compute the difference and then group by the "User Name" but the syntax above doesn't work. Any thoughts?