topic Re: Date and Time fields from the string in Splunk Search

Date and Time fields from the string

bigll — Wed, 03 Jan 2024 16:59:46 GMT

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.

Re: Date and Time fields from the string

dtburrows3 — Wed, 03 Jan 2024 16:15:01 GMT

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:

Re: Date and Time fields from the string

madhav_dholakia — Wed, 03 Jan 2024 17:01:43 GMT

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.

Re: Date and Time fields from the string

bigll — Thu, 04 Jan 2024 16:19:38 GMT

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

Re: Date and Time fields from the string

bigll — Thu, 04 Jan 2024 16:21:09 GMT

Table format get changed
Please see picture instead

Re: Date and Time fields from the string

dtburrows3 — Thu, 04 Jan 2024 17:12:06 GMT

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.

Re: Date and Time fields from the string

yuanliu — Thu, 04 Jan 2024 23:57:49 GMT

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

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

Re: Date and Time fields from the string

bigll — Fri, 05 Jan 2024 14:41:20 GMT

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.

Re: Date and Time fields from the string

yuanliu — Sat, 06 Jan 2024 04:08:23 GMT

Once again, let me ask:

Please draw a table to illustrate the output you desire. Without it, volunteers are wasting time reading mind.
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?
Is the sample output I posted based on your mock data what you expect (save potential difference in format, precision, etc.)?
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.

Re: Date and Time fields from the string

bigll — Mon, 08 Jan 2024 12:42:17 GMT

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

Re: Date and Time fields from the string

yuanliu — Mon, 08 Jan 2024 20:18:29 GMT

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