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
@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
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
@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
@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?
@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
@SathyaNarayanan ,
Try
"your search"
|eval tmp=TYPE."_".Status."_"."Time"|table Place,tmp,_time
|eval {tmp}=_time|fields - tmp,_time|fillnull value="-"
@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
@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
Also can u explain wat happen when u use | eval {tmp}=_time in it ?
WOW @renjith.nair
It worked for me, thank u very much.
@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
@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.