Date and Time fields from the string

[bigll]

I have a "myfiled" for the last update in format 2020-11-25T11:40:42.001198Z.
I want to create two new fields UpdateDate and UpdateTime

I used "eval" + "substr" 
--------

| eval UpdateDate=substr("myfield",1,10)
| eval UpdateTime=substr("myfield",12,10)

--------
But in the table  UpdateDate and UpdateTime are empty. while "myfield" has value as shown above.

Any suggestions?

Thank you.

[bigll]

Thank you for an update.
Looks like I am missing something.
Eval statements do not produce the results
My SPL statement

--Query------
Index=xyz 
| eval evTime=strptime(agent.status.policy_refresh_at,"%Y-%m-%dT%H:%M:%S.%6NZ")
| eval UpdateDate=strftime(evTime,"%Y-%m-%d")
| eval UpdateTime=strftime(evTime,"%H:%M:%S.%1N")
| table agent.status.policy_refresh_at, evTime, UpdateDate, UpdateTime, hostname
-----------------

agent.status.policy_refresh_at evTime UpdateDate UpdateTime          hostname

2024-01-04T10:31:35.529752Z

CN*******

2024-01-04T10:31:51.654448Z

CN*******

2023-11-26T05:57:47.775675Z

gb********

2024-01-04T10:32:14.416359Z

cn********

2024-01-04T10:30:32.998086Z

cn*******

[yuanliu]

To ask a good question, you really want to tell people what is the desired output.  Illustrate with a table (anonymize as needed), not just code, not a screenshot with output that you think is wrong. (Screenshots are usually less useful anyway.)

For example,

agent.status.policy_refresh_at	UpdateDate	UpdateTime	host
2024-01-04T10:31:35.529752Z	??	???	blah

Without your actual description, volunteers can speculate UpdateDate (per customary denotation) as 2024-01-04.  But what about UpdateTime?  Do you want 10:31:35.529752Z?  Do you want 10:31:35.529752?  Do you want 10:31:35.5 as your initial code would have suggested? (Why truncate to 10 characters?  Is there a desired precision?)

You also want to let people know your intention with UpdateData and UpdateTime.  Are these for display only?  Do you intend to perform numerical comparison after this table is established?  If not, there is no benefit to convert agent.status.policy_refresh_at to epoch value.

If you want UpdateTime to include time zone (the trailing "Z" is a valid timezone, not an idle letter), this should suffice

index = xyz
| eval agent.status.policy_refresh_at = split('agent.status.policy_refresh_at', "T")
| eval UpdateDate = mvindex('agent.status.policy_refresh_at', 0)
| eval UpdateTime = mvindex('agent.status.policy_refresh_at', 1)

Your sample data will give

UpdateDate	UpdateTime	agent.status.policy_refresh_at	host
2024-01-04	10:31:35.529752Z	2024-01-04 10:31:35.529752Z	CN****
2024-01-04	10:31:51.654448Z	2024-01-04 10:31:51.654448Z	CN****
2023-11-26	05:57:47.775675Z	2023-11-26 05:57:47.775675Z	gb****
2024-01-04	10:32:14.416359Z	2024-01-04 10:32:14.416359Z	cn****
2024-01-04	10:30:32.998086Z	2024-01-04 10:30:32.998086Z	cn****

If you do not wish timezone to be included (not sure why that is desirable), you can strip it, like

index = xyz
| eval agent.status.policy_refresh_at = split('agent.status.policy_refresh_at', "T")
| eval UpdateDate = mvindex('agent.status.policy_refresh_at', 0)
| eval UpdateTime = replace(mvindex('agent.status.policy_refresh_at', 1), "\D$", "")

If you want to control precision, you can also limit number of decimals, etc.

Here is an emulation you can play with and compare with real data

| makeresults format=csv data="agent.status.policy_refresh_at,host
2024-01-04T10:31:35.529752Z,CN****
2024-01-04T10:31:51.654448Z,CN****
2023-11-26T05:57:47.775675Z,gb****
2024-01-04T10:32:14.416359Z,cn****
2024-01-04T10:30:32.998086Z,cn****"
``` data emulation above, equivalent to
index = xyz
```

[bigll]

Thank you for the reply.

The end goal of the exercise is to build report for agents that are not updated in last 24 hours and mot updated in last 7 days.
That is why I try to extract last time of update and last date of update. 

[yuanliu]

Once again, let me ask:

1. Please draw a table to illustrate the output you desire.  Without it, volunteers are wasting time reading mind.
2. For agent.status.policy_refresh_at 2024-01-04T10:31:35.529752Z, should UpdateTime be 10:31:35.529752Z?  Do you want 10:31:35.529752?  Do you want 10:31:35.5 as your initial code would have suggested?  Or do you want something totally different?
3. Is the sample output I posted based on your mock data what you expect (save potential difference in format, precision, etc.)?
4. Do you intend to perform numerical comparison with UpdateTime/UpdateDate after this table is established?

All these were asked in the previous post.

The "report" that you vaguely allude to (again, precise, specific requirement makes good question) suggests (fainly) to me that you will want some numeric calculation after separating UpdateTime from agent.status.policy_refresh_at. (Question 4.)  If so, it also implies that you really need to preserve time zone and not lose precision. (Question 2.)  If my posted output is what you expect (Question 3), one way to achieve this is to apply strptime against this text UpdateTime using a fixed date such as 1970-01-01.  However, Splunk is full of gems like timewrap which I only recently learned from this forum.  It may work a lot better for your use case, but the search will be rather different.  It all depends on the exact output you desire.

The moral is: Ask questions that volunteers can meaningfully help.  A good question begins with accurate description/illustration of (anonymized or mock) input/data, precise illustration of desired output, and sufficient explanation of logic (how to do it on paper) between data and desired output.

[bigll]

Hello and thank you everyone for the help.

What i try to get out the existing data (2024-01-08T04:53:13.028149Z) :
UdateDate - YYYY-MM-DD i.e. 2021-08-02
UpdateTime - HH:MM i.e. 13:36

[yuanliu]

You answered questions about data format, i.e., Question 2, also Question 1 to some extent. (It would always be more useful for you to construct a mock results table than using words.)  You did not indicate any intention to use UpdateTime in any numeric comparison downstream, negating part of what you implied earlier.   I will assume that the answer to Question 4 is "no".  As to Question 3, your update implies a "yes".  The only change you want is precision.  And by specifying HH:MM without any other condition, I deduce that you trust that raw agent.status.policy_refresh_at all bear the same timezone.

If the above is correct, you can use pure string manipulation to achieve what you wanted:

| eval agent.status.policy_refresh_at = split('agent.status.policy_refresh_at', "T") ``` separate date from time of day ```
| eval UpdateDate = mvindex('agent.status.policy_refresh_at', 0)
| eval UpdateTime = split(mvindex('agent.status.policy_refresh_at', 1), ":") ``` break time of day by colon ```
| eval UpdateTime = mvjoin(mvindex(UpdateTime, 0, 1), ":") ``` reconstruct with first two elements only ```

Using the same emulation I constructed from your mock data, the output shoud be

UpdateDate	UpdateTime	agent.status.policy_refresh_at	host
2024-01-04	10:31	2024-01-04 10:31:35.529752Z	CN****
2024-01-04	10:31	2024-01-04 10:31:51.654448Z	CN****
2023-11-26	05:57	2023-11-26 05:57:47.775675Z	gb****
2024-01-04	10:32	2024-01-04 10:32:14.416359Z	cn****
2024-01-04	10:30	2024-01-04 10:30:32.998086Z	cn****

Hope this helps

[dtburrows3]

If a fieldname has special characters in it, i.e. (".", "{", "}", ...) Then it may require to be wrapped in single quotes when used in an eval function.
Example:

index=xyz
    | eval
        evTime=strptime('agent.status.policy_refresh_at',"%Y-%m-%dT%H:%M:%S.%6NZ"),
        UpdateDate=strftime(evTime,"%Y-%m-%d"),
        UpdateTime=strftime(evTime,"%H:%M:%S.%1N")
    | table agent.status.policy_refresh_at, evTime, UpdateDate, UpdateTime, hostname

Output with sim data on my local instance.

[bigll]

Table format get changed
Please see picture instead

[madhav_dholakia]

Hi @bigll - I would use strptime() and strftime() for any changes with Date and/or Time Format.

| makeresults
| eval myfield="2020-11-25T11:40:42.001198Z"
| eval myfieldepoch=strptime(myfield,"%Y-%m-%dT%H:%M:%S.%6NZ")
| eval UpdateDate=strftime(myfieldepoch,"%Y-%m-%d")
| eval UpdateTime=strftime(myfieldepoch,"%H:%M:%S.%1N")
| table _time myfield myfieldepoch UpdateDate UpdateTime

Thank you.

[dtburrows3]

Removing the double quotes from around the myfield in the substr() function I think should fix it.
The substr() is acting on the "myfield" as a literal string instead of a placeholder for a field value.
Example:

| makeresults
    | eval
        myfield="2020-11-25T11:40:42.001198Z"
    | eval
        UpdateDate=substr(myfield,1,10),
        UpdateTime=substr(myfield,12,10)

Screenshot: