How do I change timestamp field with a different time format?

[purvak2525]

I am trying to write a search query to change time format here and make it to simple MM-DD-YY , can anyone help me writing a query? I tried with convert function and other functions but may be i am missing something in my query. Kindly see attached image. Also, once I convert it then I want to sum up number of jobs failed(Flase/True) and then want to show X-axis as time and Y-axis as number of jobs succeed/failed kind of analytics with charts. Is it possible to do with search query?

[somesoni2 · Accepted solution]

Your field created is in string format so your conversion fails using strftime function (which takes an epoch timestamp and converts it to string). Also, the field name is has wrong case in the fieldformat command (field names are case-sensitive). Try something like this

index="ansible_tower" | table created job failed | sort created + desc | dedup job | eval create=strftime(strptime(created,"%Y-%m-%dT%H:%M:%S.%3N%Z"),"%d-%m-%y")

For your chart, you can do something like this

 index="ansible_tower" | table created job failed | sort created + desc | dedup job | eval _time=strptime(created,"%Y-%m-%dT%H:%M:%S.%3N%Z") | timechart span=1d count by failed | rename False as Succeed True as Failed

[purvak2525]

Thanks. I did make changes and worked for me.

I have data of index="ansible_tower" of last two months, in terms of extracting those all data with timechart as mentioned in your search query, how do I change the span for this for two months? I believe with span=1d only collects data of a week per the result i am seeing of below query.

index="ansible_tower" | table created job failed | sort created + desc | dedup job | eval _time=strptime(created,"%Y-%m-%dT%H:%M:%S.%3N%Z") | timechart span=1d count by failed | rename False as Succeed True as Failed

[purvak2525]

Thank you. I updated the query per your inputs.

Here is the search query which is giving me data about number of jobs run failed/succeed per day with every minute update on daily basis.

index="ansible_tower" | table created job failed | sort created + desc | dedup job | eval _time=strptime(created,"%Y-%m-%dT%H:%M:%S.%3N%Z") | timechart span=1d count by failed | rename false as Succeed true as Failed

[somesoni2]

The span=1d means, the count is calculated for one day period in the selected time range. If your time range is 1 week, you'd see 7 rows in the result, one for each day of that week. If your time range is 1 month, you'd see one row for each day of that month. So, if you select time range as 2 months, you'd see as many entries as the number of days in those 2 months. You can change the span to 1w (one week) or any other suitable value per your need. See this for all span options: http://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Timechart#Span_options