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?
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
Is there any answer for this questions ?
what do the raw events look like?
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
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)
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"
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?