topic Re: help on transpose command in Splunk Search

Could someone help on transpose command?

jip31 — Thu, 28 Apr 2022 18:09:11 GMT

hello

I transpose events like this

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?

Re: help on transpose command

bowesmana — Thu, 28 Apr 2022 06:28:46 GMT

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

What is the table before your transpose command?

Re: help on transpose command

jip31 — Thu, 28 Apr 2022 06:32:06 GMT

but I have a field time....

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

Re: help on transpose command

bowesmana — Thu, 28 Apr 2022 06:31:29 GMT

Please post an image of the search results before the transpose

Re: help on transpose command

jip31 — Thu, 28 Apr 2022 06:35:34 GMT

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

Re: help on transpose command

bowesmana — Thu, 28 Apr 2022 06:40:56 GMT

That was the search, not the results.

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

Re: help on transpose command

jip31 — Thu, 28 Apr 2022 06:59:23 GMT

Re: help on transpose command

bowesmana — Thu, 28 Apr 2022 07:11:10 GMT

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

Re: help on transpose command

jip31 — Thu, 28 Apr 2022 07:18:12 GMT

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

Re: help on transpose command

bowesmana — Thu, 28 Apr 2022 07:42:29 GMT

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

Re: help on transpose command

jip31 — Thu, 28 Apr 2022 09:05:25 GMT

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?

Re: Could someone help on transpose command?

ITWhisperer — Sun, 01 May 2022 10:08:04 GMT

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

Try setting your search time:

and start your search like this:

Re: Could someone help on transpose command?

jip31 — Mon, 02 May 2022 05:54:17 GMT

Many thanks ITWhisperer, it works fine!