Transpose and Timechart

[IRHM73]

Hi, I wonder whether someone may be able to help me please.

I've put together the following query which works but I'm having trouble adding the 'timechart' command.

 (wso2_wmf(RequestCompleted)) OR (`auth_wmf(RequestReceived)`)
  | spath output=afin input=detail.responseMessage path=affinityGroup
  | eval requestid=coalesce('request.tags.X-Request-ID', 'tags.X-Request-ID')
  | stats values(afin) as afin, values(request.detail.apiContext) as api by requestid
  | search (api="benefits" OR api="employment" OR api="income")
  | stats count by afin, api
  | transpose 0
 | fields - column

I'd like the time to be show in the far left column, but as soon as I incorporate the 'timechart' and 'transpose' commands, the format of the table is thrown out.

i just wondered whether someone may be able to look at this please and offer some guidance on how I can incorporate the 'timechart' with a span of 1d.

Many thanks and kind regards

Chris

[kmaron]

isn't your stats command dropping _time since it's not included?

[IRHM73]

Hi @kmaron, yes thank you for this.

I've now included the _time but I have issues over the Transpose.

Kind Regards

Chris

[skoelpin]

When you use transpose your turning your _time column into a row and timechart is attempting to use time on the x-axis and it can't. I also noticed your query is using stats and not passing time. You need to add your _time to the stats

Also, you can keep your stats, but you would need to add | bin _time span=1d before the stats command.

Why are you using transpose anyways?

[IRHM73]

Hi @skoelpin, thank you for replying. I can certainly make the changes you suggest but the reason I'm using transpose, is so I can change the row values into column headers.

I'm not sure whether there is a more efficient way?

Kind Regards

Chris

[skoelpin]

I've updated the query for you. After running this, what's your desired result?

   (wso2_wmf(RequestCompleted)) OR (`auth_wmf(RequestReceived)`)
       | spath output=afin input=detail.responseMessage path=affinityGroup
       | eval requestid=coalesce('request.tags.X-Request-ID', 'tags.X-Request-ID')
       | bin _time span=1d
       | stats values(afin) as afin, values(request.detail.apiContext) as api by _time, requestid
       | search (api="benefits" OR api="employment" OR api="income")
       | stats count by _time, afin, api

[IRHM73]

Hi @skoelpin, thank you for coming back to me with this.

The table does produce the date along the data, but I'm not sure whether you picked up on my last comment about the transpose.

The api field value need to be the column values, and as soon as I add the transpose command the time is also transposed but I still need the _time as a row value. This is where I have the issue.

Kind Regards

Chris