Why is my eval search returning empty field results?

[ryanmcdermott12]

Hello,

I have searched some of the previous questions, but none seem to pertain to my problem. I am running the below search:

 | jirarest jqlsearch "type = *(typename)* AND \"Environment Type\" = *(environmenttype)* AND (\"Environment Name\" in (*(environmentname1)*, *(environmentname2)*, *(environmentname3)*) OR \"Environment Name\" is EMPTY) AND createdDate >= startOfMonth()" | eval Created=strptime(Created, "%d:%m") | table Created

The search returns table rows as if it is finding results, but all of the rows are blank. The field I am evaluating is a date/time field, but it has more data than I need, and I am also trying to present it in a more easily readable format.

Any insight anyone may have will be greatly appreciated. Thank You.

[somesoni2]

Share some sample values that comes under field Created from below query.

 | jirarest jqlsearch "type = *(typename)* AND \"Environment Type\" = *(environmenttype)* AND (\"Environment Name\" in (*(environmentname1)*, *(environmentname2)*, *(environmentname3)*) OR \"Environment Name\" is EMPTY) AND createdDate >= startOfMonth()" | table Created

[ryanmcdermott12]

Thank you for your reply. Here is an example of some of the output of the Created Field:

2017-01-03T13:01:03.000+0000
2017-01-11T17:06:49.000+0000
2017-01-12T00:51:57.000+0000
2017-01-04T17:30:10.000+0000
2017-01-03T17:43:37.000+0000
2017-01-04T08:10:10.000+0000

I am trying to reformat this field to show something like DD-MON-YYYY or DD-MM-YYYY.

[GregZillgitt · Accepted solution]

What do you see if you leave off the eval and table commands? How about just leaving off the eval? Sounds to me like the strptime function isn't matching the values in "Created", which should have strings looking like "24:01". If this is true then the effect of the eval command would be to set the Created field null, which you are then putting into a table. Test this:
...eval Created2=strptime(Created, "%d:%m") | eval Created2=coalesce(Created2,"FOOBAR!")|table Created Created2

[ryanmcdermott12]

Thank you Greg. I tried your suggestion, and here is a sample of the output:

2017-01-03T13:01:03.000+0000 FOOBAR!
2017-01-11T17:06:49.000+0000 FOOBAR!
2017-01-12T00:51:57.000+0000 FOOBAR!
2017-01-04T17:30:10.000+0000 FOOBAR!
2017-01-03T17:43:37.000+0000 FOOBAR!
2017-01-04T08:10:10.000+0000 FOOBAR!

The problem is the format of "Created". I'm trying to present the date in a format more like DD-MON-YYYY. The reasoning behind it is that;
1. It would be easier for people to read.
2. I plan to eventually display the output in a chart.

The I cannot use the _time value from Splunk to build the chart, so I need to use the Created field as my X-axis.

Thank you for your help.

[GregZillgitt]

Try this:

| jirarest jqlsearch "type = (typename) AND \"Environment Type\" = (environmenttype) AND (\"Environment Name\" in ((environmentname1), (environmentname2), (environmentname3)) OR \"Environment Name\" is EMPTY) AND createdDate >= startOfMonth()" | eval Created=strptime(Created,"%Y-%m-%dT%H:%M:%S.%3N%z") | eval _time=Created

That'll take care of charting. To get from _time to your preferred display format:

strftime(_time,"%d-%b-%Y")

[ryanmcdermott12]

Ahhhhh. You're awesome! That worked! Thank you so much for the help.

I'm curious, what is causing the field not to be directly formatted with just the strptime command?

[GregZillgitt]

If I understand your question:

strptime() creates a time object from a string. For output or string conversion, you format time objects via strftime() or the convert command. So...

Created=strptime(Created,"%Y-%m-%dT%H:%M:%S.%3N%z")

... basically takes the current value of "Created", which is a string, and replaces that field with a time object. The time object doesn't have a "format" per se.

Here are a few example conversions:

eval dayofweek=strftime(_time,"%w")
eval hour=strftime(_time,"%H") 
eval yymmdd=strftime(tstamp,"%y%m%d")
eval minute=strftime(_time,"%y%m%d%h%M")

truncate time to the current minute, then convert to epoch format string:

eval trunc_minute=strftime(strptime(strftime(_time,"%y%m%d%H%M"),"%y%m%d%H%M"),"%s")

the more succinct way:

eval trunc_minute=strftime(relative_time(_time, "@m"),"%s")