Tranpose on specific structure events

[Laurent]

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

[Laurent]

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")

[ITWhisperer]

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

[Laurent]

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

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

[ITWhisperer]

Here is a runanywhere example showing it working

| makeresults
| eval guid=1
| eval property="start"
| eval value="2024-04-30T12:01:04.215Z"
| xyseries guid property value
| eval start=strftime(strptime(start, "%FT%T.%Q%Z"), "%F %T")

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

[ITWhisperer]

| xyseries guid property value

[Laurent]

Thanks it's work great.

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

[ITWhisperer · Accepted solution]

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

[Laurent]

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

[ITWhisperer]

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

[Laurent]

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

[ITWhisperer]

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?

[Laurent]

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.

[ITWhisperer]

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

[Laurent]

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

[ITWhisperer]

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.

[Laurent]

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

[ITWhisperer]

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

[Laurent]

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

[ITWhisperer]

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

[Laurent]

Hello

many thanks for the feedback now it's working perfectly, it was the story of the simple quote instead of the double 👍 thanks again