Getting Data In

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

purvak2525
New Member

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?

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

0 Karma

somesoni2
Revered Legend

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

View solution in original post

0 Karma

purvak2525
New Member

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

0 Karma

purvak2525
New Member

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

0 Karma

somesoni2
Revered Legend

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

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.