How to get the on time and off time over a category with place

[SathyaNarayanan]

Hi Splukers,

@niketnilay

I have table with 4 fields. I created the status with eval command with

index=XXX sourcetype=XXX  
| search (PLSO=false AND PLSA=true) OR (PLSO=true AND PLSA=false) 
| eval Status = if(PLSO=="false","ON","OFF")
| table _time Place Type Status

_time Place Type Status
2019-09-03 00:00:33 ABC 1 OFF
2019-09-03 00:01:51 ABC 2 ON
2019-09-03 02:34:21 ABC 3 OFF
2019-09-03 02:43:06 ABC 1 ON
2019-09-03 02:47:51 ABC 2 OFF
2019-09-03 02:49:51 ABC 2 ON
2019-09-03 03:00:51 ABC 3 ON
2019-09-03 03:57:53 ABC 3 OFF
2019-09-03 04:10:53 ABC 1 OFF

Now i need the result as

Place type1-On:time type1-Off Time type2 -On:time type2 Off Time type3 On:time type3 Off Time
ABC 02:43:06 04:10:53 00:01:51 02:47:51 03:00:51 03:57:53

[diogofgm]

Try this:

index=XXX sourcetype=XXX  
| search (PLSO=false AND PLSA=true) OR (PLSO=true AND PLSA=false) 
| eval Status = if(PLSO=="false","ON","OFF")
| table _time Place Type Status
| eval column = "type".Type."-".Status.":time"
| xyseries Place column _time

[renjith_nair · Accepted solution]

@SathyaNarayanan ,

Try

 index=XXX sourcetype=XXX  
| search (PLSO=false AND PLSA=true) OR (PLSO=true AND PLSA=false) 
| eval Status = if(PLSO=="false","ON","OFF")
| table _time Place Type Status
| sort _time     
| stats last(_time) as _time  by Type,Status,Place
| eval tmp="Type".Type."-".Status.":time"
| chart values(_time) as _time over Place by tmp

Updated as per comments:

  index=XXX sourcetype=XXX  
 | search (PLSO=false AND PLSA=true) OR (PLSO=true AND PLSA=false) 
 | eval Status = if(PLSO=="false","ON","OFF")
 | table _time Place Type Status
 | eval tmp=TYPE."_".Status."_"."Time"|streamstats count as group by Place,tmp
 | eval {tmp}=_time|stats values(*) as * by Place,group|fields - tmp,_time,group,Status,TYPE

[renjith_nair]

@SathyaNarayanan ,
As per your sample data and output in the question, it takes the latest time. Please provide the expected based on your sample data, for e.g. Type1 has two "OFF" records . How do you want to represent that?

[SathyaNarayanan]

@renjith.nair

i modified the query and got the below results.

_time   Status  Place   TYPE
 5:47:27    ON  A01 P_1
9:30    OFF A01 P_1
13:00   ON  A01 P_1
21:51   OFF A01 P_1
5:48    OFF A01 P_2
5:49    ON  A01 P_2
 3:33:38    ON  A02 P_1
12:35   ON  A02 P_1
 6:21:46    ON  A02 P_1
15:38   OFF A02 P_2
9:04    OFF A02 P_3

Now my final output should be below

Place   P_1_ON_Time P_1_OFF_Time    P_2_ON_Time P_2_OFF_Time    P_3_ON_Time P_3_OFF_Time
A01 03/09/2019 5:47 03/09/2019 9:30 03/09/2019 5:49 03/09/2019 9:47     
A01 03/09/2019 13:00    03/09/2019 21:51                
A02 03/09/2019 3:33 03/09/2019 9:43 03/09/2019 3:37 03/09/2019 15:38        
A02 03/09/2019 12:35    03/09/2019 16:06                
A02                 03/09/2019 3:33 03/09/2019 9:04

[renjith_nair]

@SathyaNarayanan ,
Try

"your search"
|eval tmp=TYPE."_".Status."_"."Time"|table Place,tmp,_time
|eval {tmp}=_time|fields - tmp,_time|fillnull value="-"

[SathyaNarayanan]

@renjith.nair.

I tried your query, the reuslts are coming but i want this in single line.

Below was the ouput came from your query.

The output came in each lines.

Place   PLB_1_ON__time  PLB_1_OFF__time PLB_2_ON__time  PLB_2_OFF__time PLB_3_ON__time  PLB_3_OFF__time
A01 5:47 AM                  
A01                               9:30 AM                
A01  1:00 PM                     
A01                                  9:51 PM                 
A01                                                                  5:49 AM             
A01                                                                                                 9:47 AM      
A01                                                                  1:02 PM             
A02 3:33 AM                  
A02                              9:43 AM                 
A02 12:35 PM                     
A02                                    4:06 PM               
A02 6:21 PM                  
A02                                                                    3:37 AM           
A02                                                                                               3:38 PM        
A02                                                                    6:25 PM           
A02                                                                                                                         3:33 AM  
A02                                                                                                                                                          9:04 AM

[renjith_nair]

@SathyaNarayanan ,

Alright, I thought you want like a matrix,

Below should work for you

|eval tmp=TYPE."_".Status."_"."Time"|streamstats count as group by Place,tmp
|eval {tmp}=_time|stats values(*) as * by Place,group|fields - tmp,_time,group,Status,TYPE

[SathyaNarayanan]

Also can u explain wat happen when u use | eval {tmp}=_time in it ?

[SathyaNarayanan]

WOW @renjith.nair

It worked for me, thank u very much.

[renjith_nair]

@SathyaNarayanan ,
Glad it worked. I will update as answer, please accept it .

eval {tmp}=_time - value of _tmp is used as the fieldname for _time.

Reference:
https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Eval#Field_names

[SathyaNarayanan]

@renjith.nair

when we do | stats last(_time) it takes the last on and off time per place. in my scenario. I might have many one and off.