hi! I have to create an area chart where it shows the actual and the target part count of the machine. I am using timechart function to display results every hour, but the _time function is displaying a different time. how can I correct this?
this is my search:
|savedsearch rename1
|fields Date_Time Username Green Yellow Red
|rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}\:\d{2}\:\d{2})"
|sort 0 _time Username Green Yellow Red
|streamstats window=1 current=f list(_time) as prevTime list(Green) as RUN
|stats count(Green) as Green sum(RUN) as Count1 by Date
|appendcols[|savedsearch rename2
|fields Date_Time Username Green Yellow Red
|rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}\:\d{2}\:\d{2})"
|sort 0 _time Username Green Yellow Red
|streamstats window=1 current=f list(_time) as prevTime list(Green) as RUN2
|stats count(Green) as Green sum(RUN2) as Count2 by Date]
|appendcols[|savedsearch rename3
|fields Date_Time Username Green Yellow Red
|rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}\:\d{2}\:\d{2})"
|sort 0 _time Username Green Yellow Red
|streamstats window=1 current=f list(_time) as prevTime list(Green) as RUN3
|stats count(Green) as Green sum(RUN3) as Count3 by Date]
|appendcols[|savedsearch rename4
|fields Date_Time Username Green Yellow Red
|rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}\:\d{2}\:\d{2})"
|sort 0 _time Username Green Yellow Red
|streamstats window=1 current=f list(_time) as prevTime list(Green) as RUN4
|stats count(Green) as Green sum(RUN4) as Count4 by Date]
|eval PC = Count1+Count2+Count3+Count4
|accum PC
|eval Green = PC*3
|accum Green
|stats values(Green) as Target values(PC) as Actual by Date
here is the screenshot of my chart
Try this:
| savedsearch rename1
| fields Date_Time Username Green Yellow Red
| sort 0 _time Username Green Yellow Red
| streamstats window=1 current=f list(_time) AS prevTime list(Green) AS RUN
| eval _time = strptime(Date_Time, "%Y/%m/%d %H:%M:%S")
| bin _time span=1h
| stats count(Green) AS Green sum(RUN) AS Count1 BY _time
| appendcols[|savedsearch rename2
| fields Date_Time Username Green Yellow Red
| sort 0 _time Username Green Yellow Red
| streamstats window=1 current=f list(_time) AS prevTime list(Green) AS RUN2
| eval _time = strptime(Date_Time, "%Y/%m/%d %H:%M:%S")
| bin _time span=1h
| stats count(Green) AS Green sum(RUN2) AS Count2 BY _time]
| appendcols[|savedsearch rename3
| fields Date_Time Username Green Yellow Red
| sort 0 _time Username Green Yellow Red
| streamstats window=1 current=f list(_time) AS prevTime list(Green) AS RUN3
| eval _time = strptime(Date_Time, "%Y/%m/%d %H:%M:%S")
| bin _time span=1h
| stats count(Green) AS Green sum(RUN3) AS Count3 BY _time]
| appendcols[|savedsearch rename4
| fields Date_Time Username Green Yellow Red
| sort 0 _time Username Green Yellow Red
| streamstats window=1 current=f list(_time) AS prevTime list(Green) AS RUN4
| eval _time = strptime(Date_Time, "%Y/%m/%d %H:%M:%S")
| bin _time span=1h
| stats count(Green) AS Green sum(RUN4) AS Count4 BY _time]
| eval PC = Count1+Count2+Count3+Count4
| accum PC
| eval Green = PC*3
| accum Green
| timechart span=1h values(Green) AS Target values(PC) AS Actual
You probably should move the strptime
line farther up (but I did not do that because it will change your data).
You say you're using the timechart
function, but I don't see "timechart" anywhere in your query. Please clarify.
Hi @mdmaala,
Step one is to simplify your problem. Run only one of the four parts of your search and see if the time displayed is the one from _time. Let me know what you get so we can work this out.
Cheers,
David