Could someone help on transpose command?

[jip31]

hello

I transpose events like this

| eval time=strftime(_time,"%H:%M") 
| sort time 
| fields - _time _span _origtime _events 
| transpose 0 header_field=time column_name=KPI include_empty=true 
| rename "row1" as "7:00"
| sort KPI

 But I have a problem with my header_field

Sometimes it works well because time field is well displayed : 7:00, 8:00, 9:00.....

But sometimes (between 7:00 and 9:00 most of the times and I dont no why because after it works well), instead time fields, I have row1, row2, row3....

Is anybody have an idea concerning this issue

I try a workaround with the rename of row1, row2...., but the rename doesn't works

Could you help please?

[ITWhisperer · Accepted solution]

Your issue is that your first search is not finding any events

Try setting your search time:

<earliest>@d+7h</earliest>
<latest>@d+19h</latest>

and start your search like this:

| makeresults
| timechart span=1h count
| fields - count
| appendcols 
    [ search `index_mesurescontinues` sourcetype="ezoptima:signaler" type=* earliest=@d+7h latest=@d+19h 
    | fields signaler_id 
    | timechart span=1h dc(signaler_id) as "0. Signalements Utilisateur (ezOptima)" 
    | appendpipe 
        [ stats count as _events 
        | where _events = 0 
        | eval "0. Signalements Utilisateur (ezOptima)" = 0 ]
        ] 
| appendcols 
    [ search 

[jip31]

Many thanks ITWhisperer, it works fine!

[bowesmana]

If you don't have a field 'time' in your data then it will create 'row ...'.

What is the table before your transpose command?

[jip31]

but I have a field time....

And like I said it works normaly after a little time so it's very strange...

[bowesmana]

Please post an image of the search results before the transpose

[jip31]

https://www.cjoint.com/c/LDzpRnxcFYj

[bowesmana]

That was the search, not the results.

What do your results look like if you run the search but without the transpose command?

[jip31]

[bowesmana]

So, there is no time field, but there is a _time field. 

If that is the data right before the transpose then that is why you get 'row X...'

To diagnose this, you will need to run that mammoth search and gradually remove the subsearches from the bottom to find out why of the appendcols is causing the problem.

Your transpose will ONLY work if the table you are converting has the correct structure.

The fact that you have extra rows is an indication that there is some search problem ABOVE the transpose

[jip31]

Ok I am going to do this but i dont understand why most of the time it works well with the same search....

[bowesmana]

When you work backwards, you will understand why ...

I think your problem is that all your timechart statements are expected to return a table like this

_time	24. Portail SIBP / Espace CO - Utilisateurs ayant au moins 2 erreurs
2022-04-28 07:00	4
2022-04-28 08:00	4
2022-04-28 09:00	4
2022-04-28 10:00	4
2022-04-28 11:00	5
2022-04-28 12:00	5
2022-04-28 13:00	4
2022-04-28 14:00	4
2022-04-28 15:00	4
2022-04-28 16:00	4
2022-04-28 17:00	4
2022-04-28 18:00	0

 However, as soon as any one of those searches gets 0 events, then the appendcols is doing this

_time	24. Portail SIBP / Espace CO - Utilisateurs ayant au moins 2 erreurs
 	0

so in that case, _time is null and you only have a single row, not 12 as you would get from the timechart.

For things to work properly, all of those searches must generate the same number of rows with _time values being the correlating row identifier.

In the above scenario, if your FIRST search returns 0 results then the _time column will always be null.

See this example search modelled on yours that exhibits the possible problem

You should be able to run this

index=_audit earliest=@d+7h latest=@d+19h user=xXXX
    | eval web_url=mvindex(split("/VP_/,/VPI/,/VPC/", ","), random() % 3)
    | fields web_url user _time
    | rename user as sam
    | eval sam=lower(sam) 
    | bin _time span=1h 
    | eval url =upper(web_url) 
    | eval SIBP=case(
        match(url,"/VP_/"),"Portail SIBP historique", match(url,"/VPI/"),"IHM porail Espace CO", match(url,"/VPC/"),"Portail Guichet Espace Co"
        ) 
    | search SIBP=* 
    | stats count as PbPerf by sam _time 
    | search PbPerf > 1 
    | timechart span=1h count as "NO USERS" 
    | appendpipe 
        [ stats count as _events 
        | where _events = 0 
        | eval "NO USERS" = 0 ]
| appendcols [ search index=_audit earliest=@d+7h latest=@d+19h user=*
    | eval web_url=mvindex(split("/VP_/,/VPI/,/VPC/", ","), random() % 3)
    | fields web_url user _time
    | rename user as sam
    | eval sam=lower(sam) 
    | bin _time span=1h 
    | eval url =upper(web_url) 
    | eval SIBP=case(
        match(url,"/VP_/"),"Portail SIBP historique", match(url,"/VPI/"),"IHM porail Espace CO", match(url,"/VPC/"),"Portail Guichet Espace Co"
        ) 
    | search SIBP=* 
    | stats count as PbPerf by sam _time 
    | search PbPerf > 1 
    | timechart span=1h count as "ALL USERS" 
    | appendpipe 
        [ stats count as _events 
        | where _events = 0 
        | eval "ALL USERS" = 0 ]
        ]

[jip31]

Sorry but its not clearing for me

Do i have to double all my searches with 2 différents conditions?

    | timechart span=1h count as "NO USERS" 

    | timechart span=1h count as "ALL USERS" 

Could you please give me an example from my original search?