I would like to find the difference of time where based on the value of field "disposion", I choose the time value form another field.
for start idisposion="ANSWERED", I use field answer
for stop disposion="NO ANSWER", I use field cr-date
I have tried this:
based search | eval start =
if(disposion="ANSWERED",strptime(answer,"%Y-%m-%d
%H:%M:%S"),0) |eval stop =
if(disposion="NO
ANSWER",strptime(cr_date,"%Y-%m-%d
%H:%M:%S"),0) |eval results =
stop-start |table results
And this:
base search | dedup disposion | eval
start = strptime(answer,"%Y-%m-%d
%H:%M:%S") | where
disposion="ANSWERED" |eval stop =
strptime(cr_date,"%Y-%m-%d %H:%M:%S")
|where disposion="NO ANSWER" |eval
results = stop-start |table results
Any ideas? Thanks
@fmatera
Can you please try below search?
based search | eval start = if(disposion="ANSWERED",strptime(answer,"%Y-%m-%d %H:%M:%S"),NULL)
| eval stop = if(disposion="NO ANSWER",strptime(cr_date,"%Y-%m-%d %H:%M:%S"),NULL)
| stats values(start) as start values(stop) as stop
| eval results = stop - start
| table results
If you have any unique id in event, like UNIQUE_ID then
based search | eval start = if(disposion="ANSWERED",strptime(answer,"%Y-%m-%d %H:%M:%S"),NULL)
| eval stop = if(disposion="NO ANSWER",strptime(cr_date,"%Y-%m-%d %H:%M:%S"),NULL)
| stats values(start) as start values(stop) as stop by UNIQUE_ID
| eval results = stop - start
| table UNIQUE_ID results
My Sample Search:
| makeresults
| eval disposion="ANSWERED",answer="2019-01-01 00:00:00"
| append
[| makeresults
| eval disposion="NO ANSWER",cr_date="2019-01-01 00:05:00" ]
| eval start = if(disposion="ANSWERED",strptime(answer,"%Y-%m-%d %H:%M:%S"),NULL)
| eval stop = if(disposion="NO ANSWER",strptime(cr_date,"%Y-%m-%d %H:%M:%S"),NULL)
| stats values(start) as start values(stop) as stop
| eval results = stop - start
| table results
Thanks
It's not clear - do you want to have both start and stop calculated within the same event, or do you group your events somehow and then one of the events in the group should provide a 'start' while another yields 'stop'? Can you show at least a small extract of the relevant fields of your data and explain what you are trying to achieve?
@fmatera
Can you please try below search?
based search | eval start = if(disposion="ANSWERED",strptime(answer,"%Y-%m-%d %H:%M:%S"),NULL)
| eval stop = if(disposion="NO ANSWER",strptime(cr_date,"%Y-%m-%d %H:%M:%S"),NULL)
| stats values(start) as start values(stop) as stop
| eval results = stop - start
| table results
If you have any unique id in event, like UNIQUE_ID then
based search | eval start = if(disposion="ANSWERED",strptime(answer,"%Y-%m-%d %H:%M:%S"),NULL)
| eval stop = if(disposion="NO ANSWER",strptime(cr_date,"%Y-%m-%d %H:%M:%S"),NULL)
| stats values(start) as start values(stop) as stop by UNIQUE_ID
| eval results = stop - start
| table UNIQUE_ID results
My Sample Search:
| makeresults
| eval disposion="ANSWERED",answer="2019-01-01 00:00:00"
| append
[| makeresults
| eval disposion="NO ANSWER",cr_date="2019-01-01 00:05:00" ]
| eval start = if(disposion="ANSWERED",strptime(answer,"%Y-%m-%d %H:%M:%S"),NULL)
| eval stop = if(disposion="NO ANSWER",strptime(cr_date,"%Y-%m-%d %H:%M:%S"),NULL)
| stats values(start) as start values(stop) as stop
| eval results = stop - start
| table results
Thanks
Thanks. Worked perfectly!
Can you please provide sample data?