Currently I am running into an issue where if there is a person logs onto a server multiple times, it combines. Any ideas on how to split?
Here is sample data.
Currently I am using
| stats values(*) as * by Host Account_Name
From This:
Host | Account_Name | Duration | Session_End | Session_Start |
fdk-DC01 | jfrank | 1612536779 1612558813 | 1612536778 1612558812 | |
fdk-DC01 | ptom | 00:00:02 | 1612563697 | 1612563695 |
fdk-Host01 | jfrank | 00:00:05 | 1612539322 | 1612539317 |
fdk-Host03 | bhill | 1612540329 1612543822 | 1612540323 1612543816 |
To This:
Host | Account_Name | Duration | Session_End | Session_Start |
fdk-DC01 | jfrank | 00:00:03 | 1612536779 | 1612536778 |
fdk-DC01 | jfrank | 00:00:07 | 1612558813 | 1612558812 |
fdk-DC01 | ptom | 00:00:02 | 1612563697 | 1612563695 |
fdk-Host01 | jfrank | 00:00:05 | 1612539322 | 1612539317 |
fdk-Host03 | bhill | 00:00:09 | 1612540329 | 1612540323 |
fdk-Host03 | bhill | 00:00:010 | 1612543822 | 1612543816 |
Thank you for any pointers!
In addition to @saravanan90 comment, just be aware that when using stats values() the resultant values in the multi-value field will be sorted and duplicates removed, so hence if combining two columns using mvzip you need to know your data will be sensibly sorted to combined item 1+1 and 2+2 from each of the 2 fields.
In your time based case, this is likely, but if doing this with data that might sort alphabetically, this would not always yield the correct pairs.
This may help..
base query | fields - Duration | eval temp = mvzip(Session_Start, Session_End, ",") | mvexpand temp | makemv delim="," temp | eval Session_Start = mvindex(temp, 0),Session_End = mvindex(temp, 1) | fields - temp | eval Duration=tostring(Session_End-Session_Start,"duration")