topic Re: Eval On Field With Multiple Values in Splunk Search

Eval On Field With Multiple Values

jr_arzuaga — Mon, 28 Sep 2020 12:53:21 GMT

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:

Anyone knows what's wrong here?

Re: Eval On Field With Multiple Values

aneeshkjm123 — Fri, 30 Nov 2012 09:25:41 GMT

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"

Re: Eval On Field With Multiple Values

jr_arzuaga — Fri, 30 Nov 2012 14:41:19 GMT

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?

Re: Eval On Field With Multiple Values

jr_arzuaga — Mon, 28 Sep 2020 12:53:35 GMT

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)

Re: Eval On Field With Multiple Values

jr_arzuaga — Mon, 28 Sep 2020 12:53:37 GMT

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

Re: Eval On Field With Multiple Values

kristian_kolb — Fri, 30 Nov 2012 22:04:48 GMT

what do the raw events look like?

Re: Eval On Field With Multiple Values

luthfi49 — Sat, 21 Sep 2013 20:35:44 GMT

Is there any answer for this questions ?

Re: Eval On Field With Multiple Values

mrobichaud_splu — Thu, 04 Jun 2015 18:10:02 GMT

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