Timechart Field Name Change

[IRHM73]

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

I've put together the query below.

index=main auditSource=frontend auditType=ExitSurvey | timechart span=1W count(eventId) by detail.manageClient | addtotals label=Total | rename 1 as "Very Good", 2 as "Good", 3 as "Neutral", 4 as "Bad", 5 as "Very Bad"

Could someone tell me please how I can change the row field from the time value to the field name i.e detail.manageClient

Many thanks and kind regards

Chris

[woodcock · Accepted solution]

Based on your clarification, you need the contingency command to build a contingency table (you are really going to like this!)

If you have or can create a field called "question" which has either {detail.manageClient, detail.Payment, detail.Recommend}, then you can do it like this:

... | contingency tags.path question

If not, you should be able to do it like this:

 index=main auditSource=frontend auditType=ExitSurvey | contingency tags.path detail.manageClient | append
 [search index=main auditSource=frontend auditType=ExitSurvey | contingency tags.path detail.Payment] | append
 [search index=main auditSource=frontend auditType=ExitSurvey | contingency tags.path detail.Recommend]

[IRHM73]

Hi @woodcock, I really appreciate you coming back to me with this.

The problem I now have is rather than producing one horizontal 'Overall' total at the end of the questions, there is a total line under each question.

Could you tell me please is there a way to overcome this?

And yes you are correct I do like this!

Many thanks and kind regards

[woodcock]

Just add this to the original solution:

| where tags.path detail.manageClient!="TOTAL" AND tags.path detail.Payment!="TOTAL" AND tags.path detail.Recommend!="TOTAL" | fillnull value=0 | addtotals col=t | fillnull value="TOTAL"

[woodcock]

Try this (fixed a typo):

index=main auditSource=frontend auditType=ExitSurvey| where $detail.manageClient$!="TOTAL" AND $detail.Payment!$="TOTAL" AND $detail.Recommend$!="TOTAL" | fillnull value=0 | addtotals col=t | fillnull value="TOTAL"

[IRHM73]

Hi @woodcock, thank you for taking the time to come back to me with this but unfortunately this doesn't populate the table.

I think what I'm going to do, because this is feeding a dashboard is create three separate panels rather than having all three questions on one.

Many thanks and kind regards

Chris

[IRHM73]

Hi @ woodcock I re-visited this and with some alteration I was able to make this work by using the following:

index=main auditSource=frontend auditType=ExitSurvey |replace/survey With "Manage List"| contingency tags.path detail.manageList | append
  [search index=main auditSource=frontend auditType=ExitSurvey |replace /survey With "View Payments" |contingency tags.path detail.viewPayments ]  |append
  [search index=main auditSource=frontend auditType=ExitSurvey |replace /survey With "What Do You Think" |contingency tags.path detail.whatDoYouThink] | rename tags.path TO "Question" | fields Question, 1 2, 3, 4, 5 | fields - TOTAL | rename 1 TO "1 (HS)", 2 TO " 2 (HS)", 3 TO "3 (HS)", 4 TO "4 (HS)", 5 TO "5 (HS)"

Many thanks for your help and kind regards

Chris

[IRHM73]

Hi @woodcock, it's good to hear from you again.

I've used the query you kindly posted but unfortunately I receive the following error:

Error in 'where' command: The operator
at 'detail.manageClient!="TOTAL" AND
tags.path detail.Payment!="TOTAL" AND
tags.path detail.Recommend!="TOTAL" '
is invalid.

Many thanks and kind regards

Chris

[woodcock]

Try this:

| where $tags.path detail.manageClient$!="TOTAL" AND $tags.path detail.Payment!$="TOTAL" AND $tags.path detail.Recommend$!="TOTAL" | fillnull value=0 | addtotals col=t | fillnull value="TOTAL"

[IRHM73]

Hi @woodcock, thank you for coming back to me with this, it really is greatly appreciated.

Unfortunately though this doesn't work, because although I know there are returnable results, the query doesn't produce any.

Many thanks and kind regards

[woodcock]

Fine, assuming these are mutually-exclusive, let's combine those before the contingency command like this:

index=main auditSource=frontend auditType=ExitSurvey | eval action=coalesce(detail.manageClient, detail.Payment, detail.Recommend) | contingency tags.path action

[IRHM73]

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

Unfortunately the query you kindly provide doesn't work with the job returning the error: Unable to find row 'tags.path' and/or col 'action' fields in the results.

I feel that I've wasted enough of your time on this, so please don't spend anymore time on this, but thank you very much for all the help you've kindly provided.

Many thanks and kindest regards

Chris

[woodcock]

Back up and ditch timechart and use xyseries instead; then you should be able to make it work on your own (if not, comment on my answer):

https://answers.splunk.com/answers/93327/xyseries-vs-chart-over-by.html

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Xyseries

[IRHM73]

Hi @woodcock, thank you for taking the time to come back to me with this.

I've had a look at the links you provided plus a whole host of posts around multiple data series, and to be honest I'm still struggling with this.

I couldn't understand how to set the xyseries command for multiple data series, so I've come up with the following:

index=main auditSource=frontend auditType=ExitSurvey detail.manageClientList="*"| chart count over tags.path by detail.manageClient |replace /agent/survey With "Manage Client Ratings Totals" |  rename tags.path AS "GDS Rating" |addtotals label=Total

Unfortunately, although this displays the figures for the first of three questions i.e. "Manage Client" I'm still unable to add the two other questions so that they fall under the same columns as the first.

Many thanks and kind regards

Chris

[IRHM73]

Hi Keith, thank you very much for coming back to me with this.

I'll try to explain a little better than I have already.

I have three questions (detail.ManageClient, detail.Payment and detail.Recommend) which all have a rating of 1 to 5.

What I'd like to do is have the three questions one under neath each other as row headings.

I'd then like to have as my columns headings 1, 2, 3, 4 and 5.

Then the data in the table would be the total for each question under each rating.

I hope this helps and my apologies for not being clearer in my original post.

Many thanks and kind regards

Chris

[kmugglet]

Hi Chris,
Just wanted to clarify what you wanted to do, as timechart will always output the rows with the time as the first column (it aggregates the data into the timespans specified by the span command.)
If you wanted to just have the weeks horizontally and the values by detail.manageClient as the rows, try the transpose command.

[IRHM73]

Hi @kmugglet, thank you for taking the time to reply to my post, and my apologies for not being as clear as I should have been.

To be honest I don't really need the time column at all, but using the 'timechart' was the only way I could manage to have the feedback results i.e "Very Good" as the column headings.

I have looked at the 'transpose' command, but unfortunately this doesn't work because it now adds rows for span and span days?

Many thanks and kind regards

Chris

[kmugglet]

Hi Chris,

If you just wanted to have no time column you could just add

 | fields - _time

to the end

However this might be more what you're looking for.

index=main auditSource=frontend auditType=ExitSurvey  | bin span=1W _time | eval date=strftime(_time,"%F") | chart count(eventId) over detail.manageClient by date | addtotals label=Total |  eval detail_ManageClient = case(detail_ManageClient==1,"Very Good",detail_ManageClient==2,"Good", detail_ManageClient==3,"Neutral", detail_ManageClient==4,"Bad", detail_ManageClient==5,"Very Bad")

That will spin your results around, does the 1w span actually matter? Is it a grouping level you need?

Cheers, Keith