Splunk Search

Why does the convert command not work in my search?

hjaramillo
New Member

alt text

0 Karma
1 Solution

niketn
Legend

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

niketn
Legend

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!!!"

hjaramillo
New Member

alt text

alt text

0 Karma

hjaramillo
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

sbbadri
Motivator

can you paste value of TimeMax.

0 Karma

hjaramillo
New Member

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

alt text

0 Karma

sbbadri
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

hjaramillo
New Member

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

0 Karma

sbbadri
Motivator

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

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 ...