topic rename field with numeric date eg 2024-06-10 to today in Splunk Search

rename field with numeric date eg 2024-06-10 to today

CMEOGNAD — Mon, 10 Jun 2024 10:37:22 GMT

Hi Community,

actual i have a cron job, thats get every day values for today and tomorrow.
How to extract for "today" or "tomorrow" the value?

This SPL doesn´t work, and don´t rename my field to get a fix fieldname...

| eval today=strftime(_time,"%Y-%m-%d") | rename "result."+'today' AS "result_today" | stats list(result_today)

Here my RAW...

Re: rename field with numeric date eg 2024-06-10 to today

KendallW — Tue, 11 Jun 2024 04:05:05 GMT

Hi @CMEOGNAD

Assuming that today's date will always be the first element of result and tomorrow the second, you can do this:

```adding sample data``` | makeresults | eval _raw="{ \"result\" : [ {\"2024-06-10\" : 1338}, {\"2024-06-11\" : 1715} ] }" ```using spath to extract values``` | spath output=today path=result{0}. | rex field=today "\{\"(?<todayDate>[^\"]+)\"\s\:\s(?<todayResult>\d+)" | spath output=tomorrow path=result{1}. | rex field=today "\{\"(?<tomorrowDate>[^\"]+)\"\s\:\s(?<tomorrowResult>\d+)"

This gets you the todayResult and tomorrowResult values extracted with regex.

Ideally, you could extract the values directly with spath, but it seems it's not possible to use a variable for the path in spath, e.g.

| eval today=tostring(strftime(_time,"%Y-%m-%d")) | spath output=today path=result{0}. | spath input=today output=today path='today'

hope this helps.

Re: rename field with numeric date eg 2024-06-10 to today

yuanliu — Tue, 11 Jun 2024 07:04:29 GMT

Iterator command foreach is your friend. (I do not recommend treating structured data as text.) One strategy could be iterate on field name:

| foreach result.* [eval today = mvappend(today, if(strftime(now(), "%F") == "<<MATCHSTR>>", '<<FIELD>>', null())), tomorrow = mvappend(tomorrow, if(strftime(now(), "%F") != "<<MATCHSTR>>", '<<FIELD>>', null()))]

BTW, illustrate raw JSON in text, do not use screenshot and do not use Splunk's contracted format. Using @KendallW's makeresults emulation, you get

_time	result.2024-06-10	result.2024-06-11	today	tomorrow
2024-06-10 23:59:39	1338	1715	1338	1715

Re: rename field with numeric date eg 2024-06-10 to today

bowesmana — Tue, 11 Jun 2024 07:09:25 GMT

You won't really be able to rename the fields unless you transpose data, which is probably not the right approach in your use case.

Here are a couple of other examples to give you ways to manipulate data

This one gets the result object and sorts the dates to make sure they are in date order

| eval dates=mvsort(json_array_to_mv(json_extract(_raw, "result"))) | eval result_today=replace(mvindex(dates, 0), "[^:]*:\s*(\d+)\}", "\1") | eval result_tomorrow=replace(mvindex(dates, 1), "[^:]*:\s*(\d+)\}", "\1")

This one extracts the fields and then uses the wildcarding technique with foreach to make the field assignments.

| spath | foreach result{}.* [ eval result_today=if("<<MATCHSTR>>"=strftime(_time, "%F"), '<<FIELD>>', result_today), result_tomorrow=if("<<MATCHSTR>>"=strftime(_time, "%F"), result_tomorrow, '<<FIELD>>')]

Re: rename field with numeric date eg 2024-06-10 to today

CMEOGNAD — Mon, 22 Jul 2024 13:51:17 GMT

The solution from yuanliu works, but not for the full json file from https://forecast.solar/
The best way was to use regex field extractor, but...

...next step to get timecharts from this format wont work by regex

{ "result": { "watts": { "2019-06-22 05:15:00": 17, "2019-06-22 05:30:00": 22, "2019-06-22 05:45:00": 27, ... "2019-06-29 20:15:00": 14, "2019-06-29 20:30:00": 11, "2019-06-29 20:45:00": 7 }, "watt_hours": { "2019-06-22 05:15:00": 0, "2019-06-22 05:30:00": 6, "2019-06-22 05:45:00": 12, ... "2019-06-29 20:15:00": 2545, "2019-06-29 20:30:00": 2548, "2019-06-29 20:45:00": 2550 }, "watt_hours_day": { "2019-06-22": 2626, "2019-06-23": 2918, "2019-06-24": 2526, "2019-06-25": 2866, "2019-06-26": 2892, "2019-06-27": 1900, "2019-06-28": 2199, "2019-06-29": 2550 } }, "message": { "type": "success", "code": 0, "text": "" } }

Re: rename field with numeric date eg 2024-06-10 to today

yuanliu — Mon, 22 Jul 2024 17:15:17 GMT

As we always say in this forum, illustration of raw input (in text format) is critical for the question to be answerable. Thank you for finally getting to data. My previous answer was based on KendallW's emulation. This latest illustration is not only different from that emulation, but also different from your initial screenshot. One fundamental difference is that this data includes multiple days potentially in the future. It seems that the input is from a prediction of sorts.

This said, I also realized that JSON keys themselves can be utilized to simply solution if you are using Splunk 8.1 or later. Again, regex is NOT the correct tool for structured data.

Here is the code you can try:

| eval today = strftime(now(), "%F"), tomorrow = strftime(relative_time(now(), "+1d"), "%F") | eval today = json_extract(_raw, "result.watt_hours_day." . today) | eval tomorrow = json_extract(_raw, "result.watt_hours_day." . tomorrow)

Here is an emulation for you to play with and compare with real data. Because your illustrated data is way in the past, I randomly pick 2019-06-26 as search time and establishes a "fake_now" field instead of using now() function. (As a result, "tomorrow" corresponds to 2019-06-27.)

| makeresults | eval _raw="{ \"result\": { \"watts\": { \"2019-06-22 05:15:00\": 17, \"2019-06-22 05:30:00\": 22, \"2019-06-22 05:45:00\": 27, \"2019-06-29 20:15:00\": 14, \"2019-06-29 20:30:00\": 11, \"2019-06-29 20:45:00\": 7 }, \"watt_hours\": { \"2019-06-22 05:15:00\": 0, \"2019-06-22 05:30:00\": 6, \"2019-06-22 05:45:00\": 12, \"2019-06-29 20:15:00\": 2545, \"2019-06-29 20:30:00\": 2548, \"2019-06-29 20:45:00\": 2550 }, \"watt_hours_day\": { \"2019-06-22\": 2626, \"2019-06-23\": 2918, \"2019-06-24\": 2526, \"2019-06-25\": 2866, \"2019-06-26\": 2892, \"2019-06-27\": 1900, \"2019-06-28\": 2199, \"2019-06-29\": 2550 } }, \"message\": { \"type\": \"success\", \"code\": 0, \"text\": \"\" } }" | spath | eval fake_now = strptime("2019-06-26 18:15:06", "%F %T") | eval today = strftime(fake_now, "%F"), tomorrow = strftime(relative_time(fake_now, "+1d"), "%F") | eval today = json_extract(_raw, "result.watt_hours_day." . today) | eval tomorrow = json_extract(_raw, "result.watt_hours_day." . tomorrow) | fields result.watt_hours_day.2019-06-26 result.watt_hours_day.2019-06-27 today tomorrow

Output is

today	tomorrow	result.watt_hours_day.2019-06-26	result.watt_hours_day.2019-06-27	_raw
2892	1900	2892	1900	{ "result": { "watts": { "2019-06-22 05:15:00": 17, "2019-06-22 05:30:00": 22, "2019-06-22 05:45:00": 27, "2019-06-29 20:15:00": 14, "2019-06-29 20:30:00": 11, "2019-06-29 20:45:00": 7 }, "watt_hours": { "2019-06-22 05:15:00": 0, "2019-06-22 05:30:00": 6, "2019-06-22 05:45:00": 12, "2019-06-29 20:15:00": 2545, "2019-06-29 20:30:00": 2548, "2019-06-29 20:45:00": 2550 }, "watt_hours_day": { "2019-06-22": 2626, "2019-06-23": 2918, "2019-06-24": 2526, "2019-06-25": 2866, "2019-06-26": 2892, "2019-06-27": 1900, "2019-06-28": 2199, "2019-06-29": 2550 } }, "message": { "type": "success", "code": 0, "text": "" } }