topic Transpose and Timechart in Splunk Search

Transpose and Timechart

IRHM73 — Thu, 26 Apr 2018 12:25:31 GMT

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

Re: Transpose and Timechart

skoelpin — Thu, 26 Apr 2018 12:40:05 GMT

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?

Re: Transpose and Timechart

IRHM73 — Thu, 26 Apr 2018 12:54:11 GMT

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

Re: Transpose and Timechart

kmaron — Thu, 26 Apr 2018 12:55:07 GMT

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

Re: Transpose and Timechart

skoelpin — Thu, 26 Apr 2018 12:59:49 GMT

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

Re: Transpose and Timechart

IRHM73 — Fri, 27 Apr 2018 06:24:24 GMT

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

Re: Transpose and Timechart

IRHM73 — Fri, 27 Apr 2018 06:25:31 GMT

Hi @kmaron, yes thank you for this.

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

Kind Regards

Chris