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?