topic Re: Tranpose on specific structure events in Splunk Search

Tranpose on specific structure events

Laurent — Fri, 19 Apr 2024 14:58:58 GMT

hello i have a list of events structured with the following fields :

guid (uniqueid), property (name of a property ), value ( value link to the property name).

i have 4 specific properties that I received separately on different events and the key is the guid to consolidate the information property/value by guid

i make a search => search xxx | table guid , property , value

i m able to have all the events in a table in this way

guid property value
1 start 1
1 end 2
1 duration 1
1 status OK
2 start 1
2 end 3
2 duration 2
2 status KO

I try to transpose the result in this way => search xxx | table guid , property , value | transpose 0 header_field="property"
tho have a result like this :

guid start end duration status
1 1 2 1 OK
2 1 3 2 KO

but the result is not good, is there a way to easily search and display in a readable table this kind of structured events?
Other need, how to simply display by guid the status and duration ?
Thanks for your help
regards
Laurent

Re: Tranpose on specific structure events

ITWhisperer — Fri, 19 Apr 2024 15:25:45 GMT

| xyseries guid property value

Re: Tranpose on specific structure events

Laurent — Mon, 22 Apr 2024 08:07:05 GMT

Thanks it's work great.

Is there a way to order the value of the column property?

Re: Tranpose on specific structure events

ITWhisperer — Mon, 22 Apr 2024 08:13:49 GMT

They are already ordered - they are sorted lexicographically (alphabetically) - perhaps not the order you wanted? Try adding this to the end

| fields guid start end duration status

Re: Tranpose on specific structure events

Laurent — Tue, 30 Apr 2024 08:44:02 GMT

Hello ,

| fields guid start end duration status

is there way to reformat a field for exemple here the start? i want to apply the format done by that :

| eval start=strftime(strptime(start, "%FT%T.%Q%Z"), "%F %T")

Thanks

LAurent

Re: Tranpose on specific structure events

ITWhisperer — Tue, 30 Apr 2024 09:30:48 GMT

Firstly, this seems to be a different question. Secondly, haven't you already received and accepted a solution here

Re: Tranpose on specific structure events

Laurent — Tue, 30 Apr 2024 09:55:08 GMT

Yes but is it to apply the result of the date reformating provided into a fields of this answer :). But i can open a new topic if necessary

Re: Tranpose on specific structure events

ITWhisperer — Tue, 30 Apr 2024 10:01:53 GMT

OK assuming your start and end fields match the timestamp format you are using to parse, then this should work for both fields (but your example data doesn't show it as such). Have you tried it?

Re: Tranpose on specific structure events

Laurent — Tue, 30 Apr 2024 10:13:20 GMT

i have test the format directly to the value it's work.

my concerne is to apply it after the xseries on => | fields guid start end duration status .

On the result of the field start if i put the eval at the end it doesn't work.

Re: Tranpose on specific structure events

ITWhisperer — Tue, 30 Apr 2024 10:48:03 GMT

Exactly what have you tried and exactly what doesn't work? What results / errors messages do you get?

Re: Tranpose on specific structure events

Laurent — Tue, 30 Apr 2024 12:28:53 GMT

thanks, i have tried that to reformat the field start but it result in an empty field.

| xyseries guid property value | fields guid start end duration status | eval start=strftime(strptime(start, "%FT%T.%Q%Z"), "%F %T")

Re: Tranpose on specific structure events

ITWhisperer — Tue, 30 Apr 2024 12:58:36 GMT

| makeresults | eval start="2024-04-30T11:59:24.123Z" | eval start=strftime(strptime(start, "%FT%T.%Q%Z"), "%F %T")

This works which would seem to suggest that the values you have in start (and end) are not in this format. Please can you share some examples which aren't working?

Re: Tranpose on specific structure events

Laurent — Tue, 30 Apr 2024 15:53:12 GMT

The date transformation is working, i test it. My concerned is how(where) to insert it in my search query . When i add the transfo at the end of the query nothing happen.

Here my full request which display a table well structured but with the date end and start date in this format 2024-04-30T12:01:04.215Z and not in this one 2024-04-30 14:01:04

Re: Tranpose on specific structure events

ITWhisperer — Tue, 30 Apr 2024 14:30:14 GMT

Here is a runanywhere example showing it working

Please share some actual examples (anonymised of course) where this technique does not work

Re: Tranpose on specific structure events

Laurent — Tue, 21 May 2024 12:13:31 GMT

Hello, thanks for answering

there is no errors the time formating is not take into account here my query:

<query>index="xxx" earliest=$timerange.earliest$ latest=$timerange.latest$ | table guid, "parameter", "value"| xyseries "guid", "parameter", "value" | fields "guid" "type" "Start Time" "End Time" "Duration" | eval fields "Start Time" = strftime("Start Time", "%d/%m/%y %H:%M:%S") | sort 0 - "Start Time" </query>

as i explained start time is not reformated as expected it's on orignal format!

guid type start time end time duration

2024-05-20T04:00:53.536Z

incremental

2024-05-20T04:00:53.847Z

2024-05-20T04:01:05.815Z

00:00:12

thanks

Laurent

Re: Tranpose on specific structure events

ITWhisperer — Tue, 21 May 2024 16:20:46 GMT

Is "Start Time" an epoch time?

If not, you need to parse it to an epoch time before formatting to a different format. Since you are sorting, you should parse, sort, then format.

Re: Tranpose on specific structure events

Laurent — Wed, 22 May 2024 15:45:33 GMT

Hello

no start time is in this format 2024-05-20T04:00:53.847Z and after the eval the result is the same 2024-05-20T04:00:53.847Z ! how to put on epoch time and transform it?

thanks

Laurent

Re: Tranpose on specific structure events

ITWhisperer — Wed, 22 May 2024 16:33:43 GMT

You need to use strptime() to parse the time string into an epoch time.

Re: Tranpose on specific structure events

Laurent — Thu, 23 May 2024 14:33:40 GMT

Hello

i have done the following query but this is not affecting the result of the column Start Time on the result!

eval fields "Start Time" = strftime(strptime("Start Time", "%FT%T.%Q%Z"), "%F %T") | reverse

When i try directly on the value of this fields whith a query it's works!

index="xxx" x | table "Interface" "Status"| stats latest("Status") as latest_time by "Interface"| eval latest_time=strftime(strptime(latest_time, "%FT%T.%Q%Z"), "%F %T") | sort 0 - latest_time

i think it's more a problem with the position of the eval on the query which doesn't affect the display!

thanks

Laurent

Re: Tranpose on specific structure events

ITWhisperer — Thu, 23 May 2024 15:18:59 GMT

When you use a field name with embedded spaces on the right hand side of an assignment, it should be in single quotes (not double quotes)

eval "Start Time" = strftime(strptime('Start Time', "%FT%T.%Q%Z"), "%F %T") | reverse