Splunk Search

reformat the _time

Path Finder

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.

alt text

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?

0 Karma

Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Contributor

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.

0 Karma

Path Finder

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".

0 Karma

Contributor

Also I have edited the date format a bit convert timeformat="%b/%Y" ctime(_time). Let me know if that's working.

0 Karma

Contributor

You can try placing the code after your timechart.

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma

Path Finder

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".

0 Karma

SplunkTrust
SplunkTrust

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 
0 Karma

Path Finder

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.

0 Karma

SplunkTrust
SplunkTrust

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 
0 Karma

Contributor

Hi,

Can you try using eval Time = strftime(_time, "%b/%Y")?

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!