Splunk Search

Why does the convert command not work in my search?

New Member

alt text

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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.

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

View solution in original post

SplunkTrust
SplunkTrust

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.

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

View solution in original post

New Member

alt text

alt text

0 Karma

New Member

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

0 Karma

Motivator

can you paste value of TimeMax.

0 Karma

New Member

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

alt text

0 Karma

Motivator

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

0 Karma

New Member

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

0 Karma

Motivator

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

0 Karma