topic Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro in Splunk Search

group threefields and get the latest timestamp record and retrieve additional column value corresponding to that group

NathanAsh — Sat, 27 Apr 2024 03:33:58 GMT

I have a vast data set with a sample as below. Need to group the data based on three columns latest timestamp data and get the fourth column value against the latest timestamp found for that grouped data.

Deployed_Data_time	env	app	version
4/16/2024 15:29	axe1	app1	v-228
4/16/2024 15:29	axe1	app1	v-228
9/15/2023 8:12	axe1	app1	v-131
9/15/2023 8:05	axe2	app1	v-120
9/12/2023 1:19	axe2	app1	v-128
4/16/2024 15:29	axe2	app2	v-628
4/16/2024 15:26	axe2	app2	v-626
9/15/2023 8:12	axe2	app2	v-531
9/15/2023 8:05	axe1	app2	v-530
9/12/2023 1:19	axe1	app2	v-528

and I need the output as

app	axe1	axe2
app1	v-228	v-120
app2	v-530	v-628

And I tried something as below but output is not as expected.

Please help me achieve this .

Thanks

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

bowesmana — Sat, 27 Apr 2024 04:06:32 GMT

This won't work as you want

| stats latest(Deployed_Data_time) AS Deployed_Data_time values(env) AS env max(latestVersion) AS latestVersion BY app

latest() function is based on the _time field, so if you want Deployed_Data_time to be _time then you need to evaluate it

| eval _time=strptime(Deployed_Data_time,"%m/%d/%Y %H:%M")

but you also cannot do max(latestVersion) as that is simply doing a numeric comparison on the date, which is a string, so 4/16/2024 is LESS than 9/15/2023 - 4 is less than 9.

If you ever want to do string based date comparisons, you need them to be ISO8601, i.e. YYYY-MM-DD-HH:MM:SS

So, using your example data, is this what you want?

| makeresults format=csv data="Deployed_Data_time,env,app,version 4/16/2024 15:29,axe1,app1,v-228 4/16/2024 15:29,axe1,app1,v-228 9/15/2023 8:12,axe1,app1,v-131 9/15/2023 8:05,axe2,app1,v-120 9/12/2023 1:19,axe2,app1, v-128 4/16/2024 15:29,axe2,app2,v-628 4/16/2024 15:26,axe2,app2,v-626 9/15/2023 8:12,axe2,app2,v-531 9/15/2023 8:05,axe1,app2,v-530 9/12/2023 1:19,axe1,app2, v-528" | rex field=version "v-(?<v>\d+)" | stats max(v) AS version BY app env | table app,version,env | chart values(version) by app, env limit=0 | fillnull value="Not Deployed"

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

gcusello — Sat, 27 Apr 2024 05:25:17 GMT

Hi @NathanAsh ,

did you tried the OVER clause in the chart command?

for more infos see at https://docs.splunk.com/Documentation/Splunk/9.2.1/SearchReference/Chart

Ciao.

Giuseppe

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

NathanAsh — Sat, 27 Apr 2024 11:29:32 GMT

No results displayed, but table returns the same value as my try

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

NathanAsh — Sat, 27 Apr 2024 11:34:52 GMT

No , app1 in axe2 value should be 120 not 128, (latest deployed version as per the date timestamp)

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

NathanAsh — Sat, 27 Apr 2024 12:07:30 GMT

Sorry, displays data but output is same as my try earlier, latest version is displayed along all env, not specific to that env is displayed.

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

NathanAsh — Sun, 28 Apr 2024 18:26:38 GMT

anything in that line of thoughts be helpful to achieve this https://community.splunk.com/t5/Splunk-Search/How-to-convert-rows-to-columns/m-p/398009

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

bowesmana — Sun, 28 Apr 2024 23:47:38 GMT

@NathanAsh You're right! Then use the strptime() example I mentioned and the latest() function. You don't seem to need _time so just convert Deployed_data_time to _time and you can use latest(version)

| makeresults format=csv data="Deployed_Data_time,env,app,version 4/16/2024 15:29,axe1,app1,v-228 4/16/2024 15:29,axe1,app1,v-228 9/15/2023 8:12,axe1,app1,v-131 9/15/2023 8:05,axe2,app1,v-120 9/12/2023 1:19,axe2,app1, v-128 4/16/2024 15:29,axe2,app2,v-628 4/16/2024 15:26,axe2,app2,v-626 9/15/2023 8:12,axe2,app2,v-531 9/15/2023 8:05,axe1,app2,v-530 9/12/2023 1:19,axe1,app2, v-528" | eval _time=strptime(Deployed_Data_time, "%m/%d/%Y %H:%M") | stats latest(version) AS version BY app env | table app,version,env | chart values(version) by app, env limit=0 | fillnull value="Not Deployed"

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

NathanAsh — Mon, 29 Apr 2024 00:48:50 GMT

Thanks, it works for the sample data which I have given, but the actual data I pushed in splunk is not as per the Deployed date timestamp, I have pushed old data (2023 year) lately and new data (2024 ) first. Hence for some columns the results are coming as per the data pushed in to splunk time. Any work around can be applied?

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

NathanAsh — Mon, 29 Apr 2024 01:43:29 GMT

Got it remediated by including gcusello suggestion of | eval latestDeployed_version=Deployed_Data_time."|".version and used that field in your stats statement as max value instead of latest. This worked well and validated to be fine. Thanks a lot to both

Re: group threefields and get the latest timestamp record and retrieve additional column value corresponding to that gro

bowesmana — Mon, 29 Apr 2024 01:44:52 GMT

Mmm, that's odd because I use that technique to manipulate _time - If you could find a simple example of _raw data where that is the case - perhaps by limiting the search just to pick up an event of each type - I'd be really interested to see.

If the date format for the 2023 data is not as per the strptime format syntax that would cause a problem as it would be later - that would be my suspicion.

If you can do a simple search for that 2023 data and do this

| eval orig_time=strftime(_time, "%F %T.%Q") | eval _time=strptime(...) | table _time orig_time

that may show the difference