Splunk Search

reformat the _time

jvmerilla
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

niketn
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

MousumiChowdhur
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

jvmerilla
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

MousumiChowdhur
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

MousumiChowdhur
Contributor

You can try placing the code after your timechart.

0 Karma

kamlesh_vaghela
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

jvmerilla
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

kamlesh_vaghela
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

jvmerilla
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

kamlesh_vaghela
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

MousumiChowdhur
Contributor

Hi,

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...