topic Why does the convert command not work in my search? in Splunk Search

Why does the convert command not work in my search?

hjaramillo — Thu, 27 Jul 2017 17:08:40 GMT

Re: Why does the convert command not work in my search?

sbbadri — Thu, 27 Jul 2017 18:21:04 GMT

can you paste value of TimeMax.

Re: Why does the convert command not work in my search?

hjaramillo — Thu, 27 Jul 2017 18:33:46 GMT

The format of TimeMax is "%m/%d/%Y %H:%M:%S" and i need this "%H:%M:%S"

Re: Why does the convert command not work in my search?

sbbadri — Thu, 27 Jul 2017 18:51:26 GMT

try like below
rest of your search | eval temp=strptime(TimeMax,"%m/%d/%Y %H:%M:%S") | convert timeformat="%H:%M:%S" ctime(temp) AS Hora

Re: Why does the convert command not work in my search?

niketn — Thu, 27 Jul 2017 19:00:33 GMT

There are several performance issues with your current query, besides the issue you have posted here.

1) Filter results in base query (i.e. before first pipe) :where IDVariable="(207011004)" should be removed and moved as filter to base query.

 <YourBaseSearch With Index and Sourcetype> IDVariable="(207011004)" | <your remaining query>

2) You are performing reverse and then tail 1. You can instead perform a single | head 1 command.
3) Create table after head 1 command.

Refer to Splunk Documentation on Query optimization for your referenc: http://docs.splunk.com/Documentation/Splunk/latest/Search/Quicktipsforoptimization

Coming to your issue, you should convert string timestamp to epoch using strptime() function and then use fieldformat command to use the Display format (String Time) as required while retaining the underlying epoch time value. Following is a run anywhere search:

| makeresults
| eval TimeMax=strptime("04/01/2017 05:34:26","%m/%d/%Y %H:%M:%S")
| fieldformat TimeMax=strftime(TimeMax,"%H:%M:%S")

Your query after optimization and fix should look like the following:

index=sqlserver source=reportesco sourcetype=reportesco IDVariable="(2017011004)"
| head 1
| stats dc(Value) as "OK" values(Value) as "Ultimo Valor 24 hrs" values(TimeMax) as "Hora"
| eval Hora=strptime(Hora,"%m/%d/%Y %H:%M:%S")
| fieldformat Hora=strftime(Hora,"%H:%M:%S")

Please try out an confirm if it is working as expected.

Re: Why does the convert command not work in my search?

hjaramillo — Thu, 27 Jul 2017 21:33:34 GMT

Using:

index=sqlserver source=reportesco sourcetype=reportesco IDVariable="(2017011004)"
| head 1
| stats dc(Value) as "OK" values(Value) as "Ultimo Valor 24 hrs" values(TimeMax) as "Hora"
| eval Hora=strptime(Hora,"%m/%d/%Y %H:%M:%S")
| fieldformat Hora=strftime(Hora,"%H:%M:%S")

Not results, the tables are empty.

And

index=sqlserver source=reportescso sourcetype=reportescso
| head 1
| stats dc(Value) as "OK" values(Value) as "Ultimo Valor 24 hrs" values(TimeMax) as "Hora"
| eval temp=strptime(TimeMax,"%Y/%m/%d %H:%M:%S")
| convert timeformat="%H:%M:%S" ctime(temp) as Hora
| fieldformat Hora=strftime(Hora,"%H:%M:%S")

Missing the value "hour"

My Splunk is 6.5.3

Re: Why does the convert command not work in my search?

hjaramillo — Thu, 27 Jul 2017 21:35:20 GMT

Re: Why does the convert command not work in my search?

hjaramillo — Thu, 27 Jul 2017 21:45:27 GMT

It does not give me result of the table "hour"

Re: Why does the convert command not work in my search?

sbbadri — Thu, 27 Jul 2017 23:47:51 GMT

| convert timeformat="%H:%M:%S" ctime(temp) AS Hour