Hi,
Is it possible to reformat the _time, for example, remove the day so only the month and the year will remain?
I have a query here:
index="sample_data" sourcetype="management_sampledata.csv"
| fields*
| search Status="*" "Ticket Type"="*" Priority="*" Ticket_No=*
| rename "Reported Date" as Reported_Date
| eval _time = strptime(Reported_Date, "%m/%d/%y") | timechart span=1mon count(eval(Status="Closed" OR Status="Cancelled")) as Closed/Cancelled, count as Inflow
| eval Backlog_Total = Inflow - 'Closed/Cancelled'
| accum Backlog_Total as Backlog_Total(acccumulation)
| fields - Backlog_Total
This code is good, but my problem with this is when I visualize it into a chart.
If you notice, if I hover over the chart, it shows the date, now what I want is for the month and the year to show only.
Is there any possible way to do this?
@jvmerilla, before we suggest you fixing time as %Y/%m which should not be difficult, can you please confirm whether you are performing Post Processing? If so ideally base search should always have transforming command and passing raw events via fields* is not recommended approach. Further the search filters should be imposed as early as possible, in your case in the base search itself. Refer to documentation for Post-processing Best Practices. Following two commands seem expensive to me.
| fields*
| search Status="*" "Ticket Type"="*" Priority="*" Ticket_No=*
In cases where you can not have base search with transforming command, you should check the performance with and without post-processing, as you may actually be better off without post-processing, and in case defined limits for post-processing is reached, you might also loose data.
Hi @jvmerilla,
Could you please try using convert timeformat="%Y-%m" ctime(_time)
at the end of query? This will format the _time
to display only month and year. Let me know if this can help you.
Hi @MousumiChowdhury,
I just want to clarify, where should I put the code? Is it before or after the "eval _time"?
I tried it and put it to the last part of my code but when I hover over my chart it shows "Invalid timestamp".
Also I have edited the date format a bit convert timeformat="%b/%Y" ctime(_time)
. Let me know if that's working.
You can try placing the code after your timechart.
Hi @jvmerilla,
Can you please try this?
index="sample_data" sourcetype="management_sampledata.csv"
| fields*
| search Status="*" "Ticket Type"="*" Priority="*" Ticket_No=*
| rename "Reported Date" as Reported_Date
| eval _time = strptime(Reported_Date, "%m/%d/%y")
| timechart span=1mon count(eval(Status="Closed" OR Status="Cancelled")) as "Closed/Cancelled", count as Inflow
| eval Backlog_Total = Inflow - 'Closed/Cancelled'
| sort _time
| eval Time = strftime(_time, "%m/%y")
| fields - _time
| chart sum("Closed/Cancelled") as "Closed/Cancelled" sum(Inflow) as Inflow sum(Backlog_Total) as Backlog_Total over Time
| accum Backlog_Total as "Backlog_Total(acccumulation)"
| fields - Backlog_Total
Hi @kamlesh_vaghela,
Thank you for your response.
I tried it but the problem is that it shows months in number like 05, 06, instead of the name like May, and June. I need the date to show like this "May 2017" and "Jun 2017".
Hi @jvmerilla,
Can you please try this?
index="sample_data" sourcetype="management_sampledata.csv"
| fields*
| search Status="*" "Ticket Type"="*" Priority="*" Ticket_No=*
| rename "Reported Date" as Reported_Date
| eval _time = strptime(Reported_Date, "%m/%d/%y")
| timechart span=1mon count(eval(Status="Closed" OR Status="Cancelled")) as "Closed/Cancelled", count as Inflow
| eval Backlog_Total = Inflow - 'Closed/Cancelled'
| sort _time
| eval Time = strftime(_time, "%b %y")
| fields - _time
| chart sum("Closed/Cancelled") as "Closed/Cancelled" sum(Inflow) as Inflow sum(Backlog_Total) as Backlog_Total over Time
| accum Backlog_Total as "Backlog_Total(acccumulation)"
| fields - Backlog_Total
Hi @kamlesh_vaghela,
I tried your code and I also tried something similar to this. The problem with this is that when I use the accum
command, it returns wrong values.
I'm not sure but I think it is because the time it is using is already in a human-readable format.
Hi @jvmerilla,
I have just shift accum up so Can you please try this?
index="sample_data" sourcetype="management_sampledata.csv"
| fields*
| search Status="*" "Ticket Type"="*" Priority="*" Ticket_No=*
| rename "Reported Date" as Reported_Date
| eval _time = strptime(Reported_Date, "%m/%d/%y")
| timechart span=1mon count(eval(Status="Closed" OR Status="Cancelled")) as "Closed/Cancelled", count as Inflow
| eval Backlog_Total = Inflow - 'Closed/Cancelled'
| accum Backlog_Total as "Backlog_Total(acccumulation)"
| sort _time
| eval Time = strftime(_time, "%b %y")
| fields - _time
| chart sum("Closed/Cancelled") as "Closed/Cancelled" sum(Inflow) as Inflow values("Backlog_Total(acccumulation)") as "Backlog_Total(acccumulation)" over Time
Hi,
Can you try using eval Time = strftime(_time, "%b/%Y")
?