responses.kql

// comments
// logs are stored in UTC time, so we need to convert between UTC and EST
// 
// changelog
// date       author        message
// 5/26/2020  kyle & grace  fix perf, fix startDate timezone, add change log
// 6/03/2020  kyle & grace  replace extract with substring for perf

// set start time for query
let startDate_EST = todatetime("2020-05-26T09:30:00");
let startDate_UTC = datetime_add("hour", 4, startDate_EST);
//
// bad convo ids
let wrongIDs = traces
| where customDimensions.Question contains "This did NOT answer my question" 
| project substring(itemId, 0, 7);
//
// get all q/a pairs
traces
| where timestamp > startDate_UTC
    and message contains "GenerateAnswer"
| extend input = tostring(customDimensions.Question),
        answer = tostring(customDimensions.Answer),
        scoreInt = toint(round(todouble(customDimensions.Score))),
        convoId = substring(itemId, 0, 7)
| where input  !contains "This did NOT answer my question"
| extend score = iif(convoId in (wrongIDs), -1, scoreInt), // separate bad vs good
         EST = datetime_add("hour", -4, timestamp)         // convert UTC to EST
| order by timestamp desc 
| project itemId,
          input,
          faq = extract("^\\*\\*(.*)\\*\\*", 1, answer),
          type = case(score == -1, "wrong",
                      score == 0, "no answer",
                      "answered"),
          score,
          timestamp = format_datetime(EST, "MM/dd/yyyy hh:mm tt")