diff options
Diffstat (limited to 'Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data')
3 files changed, 213 insertions, 7 deletions
diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/planner_prompt.txt b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/planner_prompt.txt index c8557a41f..b68b05755 100644 --- a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/planner_prompt.txt +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/planner_prompt.txt @@ -23,24 +23,29 @@ SCHEMA USE - If you are joining and want to project two columns with the same name append '1' to the end of the second table name. (e.g: SitesTable | join kind=inner (OrganizationsTable) on $left.ORGANIZATION_GUID == $right.GUID | project Site = NAME, Organization = NAME1). - To get machine's organization name example: MachinesTable | where SERIAL_NUMBER == '30001' | join kind=inner (OrganizationsTable) on $left.ORGANIZATION_GUID == $right.GUID | project OrganizationName = NAME1. - To get machine's site name example: MachinesTable | where SERIAL_NUMBER == '30001' | join kind=inner (SitesTable) on $left.SITE_GUID == $right.GUID | project SiteName = NAME1. +- Use the LogsTable where Category == "Error" to identify machine errors. +- When asked to generate a chart or graph, produce KQL that returns ploty output as a result, similar to the provided ploty examples, and classify as "ploty". +- Use the provided ploty examples as a reference on how to properly design the query and ploty output. ROUTING - Classify into exactly one: - "data": requires querying telemetry via KQL (counts, trends, top-N, rates, timelines, lookups). + - "ploty": user asked to generate a chart or graph. - "docs": architectural/how-to/design/definitions; no live data needed. - "none": small-talk (“thanks”, “hi”), meta-questions about how you chose a query, or requests to rephrase without needing data. - Use conversational context from prior turns. If the current question refers to a previous result (“that”, “the same one”, “which organization is that?”), KEEP the same assistant as the previous turn unless the user clearly asks for documentation. OUTPUT FORMAT — RAW JSON ONLY (no code fences) Return a single JSON object with these fields: -- assistant: "data" | "docs" | "none" -- kql: string ("" when assistant ≠ "data") +- assistant: "data" | "ploty" | "docs" | "none" +- kql: string ("" when assistant ≠ "data" or "ploty") - parameters: object with any needed parameters ({} when assistant ≠ "data"). Use fromDate/toDate when applicable. - parameterTypes (optional): object (e.g., {"fromDate":"datetime","toDate":"datetime"}) - assumptions: array of short strings (may be empty) - why: short natural-language rationale (string) - conversation: string * When assistant == "none": put a SHORT friendly reply (1–3 sentences) that addresses the user directly without KQL. + * When assistant == "ploty": put a title and a description of the chat/graph. * Otherwise: set to "". EXAMPLES diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/ploty_sample.txt b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/ploty_sample.txt new file mode 100644 index 000000000..d4c4e0c42 --- /dev/null +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/ploty_sample.txt @@ -0,0 +1,126 @@ +--Ploty Examples-- + +Pie Chart of Job Statuses: + +let data = +JobRunsTable + | summarize Completed = countif(Status == "Completed"), Failed = countif(Status == "Failed"), Aborted = countif(Status == "Aborted"); +data +| extend TotalJobs = Completed + Failed + Aborted +| extend ploty = strcat( + '{"data":[{"type":"pie",', + '"labels":["Completed","Failed","Aborted"],', + '"values":[', tostring(Completed), ',', tostring(Failed), ',', tostring(Aborted), '],', + '"marker":{"colors":["green","red","orange"]},', + '"textinfo":"label+value",', + '"hoverinfo":"label+value"}],', + '"layout":{"title":"Job Status Distribution (', tostring(TotalJobs), ')",', + '"showlegend":false}}' +) +| project ploty + + +Ink Consumption by Color (L) + +JobRunsTable +| summarize + Cyan = sum(OutputCyan), + Magenta = sum(OutputMagenta), + Yellow = sum(OutputYellow), + Black = sum(OutputBlack), + LightCyan = sum(OutputLightCyan), + LightMagenta = sum(OutputLightMagenta), + LightYellow = sum(OutputLightYellow), + Blue = sum(OutputBlue), + LightBlue = sum(OutputLightBlue), + Orange = sum(OutputOrange), + LightOrange = sum(OutputLightOrange), + Rubine = sum(OutputRubine), + LightRubine = sum(OutputLightRubine), + Navy = sum(OutputNavy), + Violet = sum(OutputViolet), + Transparent = sum(OutputTransparent) +| extend keys = pack_array("Cyan","Magenta","Yellow","Black","Light Cyan","Light Magenta","Light Yellow","Blue","Light Blue","Orange","Light Orange","Rubine","Light Rubine","Navy","Violet","Transparent") +| extend values = pack_array( + toreal(Cyan)/1000000000.0, + toreal(Magenta)/1000000000.0, + toreal(Yellow)/1000000000.0, + toreal(Black)/1000000000.0, + toreal(LightCyan)/1000000000.0, + toreal(LightMagenta)/1000000000.0, + toreal(LightYellow)/1000000000.0, + toreal(Blue)/1000000000.0, + toreal(LightBlue)/1000000000.0, + toreal(Orange)/1000000000.0, + toreal(LightOrange)/1000000000.0, + toreal(Rubine)/1000000000.0, + toreal(LightRubine)/1000000000.0, + toreal(Navy)/1000000000.0, + toreal(Violet)/1000000000.0, + toreal(Transparent)/1000000000.0 +) +| extend colors = pack_array("#00FFFF", "#FF00FF", "#FFFF00", "#000000", "#E0FFFF", "#FFB6C1", "#FFFFE0", "#0000FF", "#ADD8E6", "#FFA500", "#FFE4B5", "#D2042D", "#FF69B4", "#000080", "#8A2BE2", "#FFFFFF") +| extend ploty = strcat( + '{"data":[{"type":"bar",', + '"x":', tostring(keys), ',', + '"y":', tostring(values), ',', + '"marker":{"color":', tostring(colors), '}', + '}],', + '"layout":{"title":"Ink Consumption by Color (L)","xaxis":{"title":"Color"},"yaxis":{"title":"Total Ink (L)"}}}' +) +| project ploty + + +Failing Machines (TOP 10): + +let data = JobRunsTable +| where Status == "Failed" +| summarize FailedCount = count() by SerialNumber, Site +| top 10 by FailedCount desc +| order by FailedCount desc +| extend SerialNumberWithSite = strcat(SerialNumber, iif(isnull(Site), "", strcat(" (", Site, ")"))); +let x = toscalar(data | summarize make_list(SerialNumberWithSite)); +let y = toscalar(data | summarize make_list(FailedCount)); +let colors = dynamic([ + "#B30000", "#CC0000", "#E60000", "#FF0000", "#FF2A2A", + "#FF3333", "#FF4D4D", "#FF6666", "#FF9999", "#FFCCCC" +]); +print ploty = tostring(strcat( + '{"data":[', + '{"x":', tostring(x), + ',"y":', tostring(y), + ',"type":"bar",', + '"marker":{"color":', tostring(colors), '}', + '}],', + '"layout":{"title":"",', + '"xaxis":{"title":"Machine (Site)","automargin":true},', + '"yaxis":{"title":"Failure Count"},', + '"margin":{"b":150}}}' +)) + + +Resume Stats: + +let r1="Continuous request message 'JobRequest' had failed to provide a response for a period of 10 seconds and has timed out."; +let r2="Transporter disconnected."; +let ids_with_disconnect=JobRunsTable +| where isnotempty(FailureReason) +| where FailureReason endswith r1 or FailureReason endswith r2 +| distinct ID; +let latest_per_id=JobRunsTable +| summarize arg_max(CreatedTime, *) by ID +| extend LatestIsDisconnect=isnotempty(FailureReason) and (FailureReason endswith r1 or FailureReason endswith r2); +let totals=latest_per_id +| where ID in (ids_with_disconnect) +| summarize Resumed=countif(not(LatestIsDisconnect)), NotResumed=countif(LatestIsDisconnect); +totals +| extend ["Job Runs Recovered By Resume"]=Resumed, ["Job Runs Could not Resume"]=NotResumed, ["Total Disconnections"]=Resumed+NotResumed, ["Success Rate"]=round(100.0*todouble(Resumed)/todouble(Resumed+NotResumed),2) +| project-away Resumed, NotResumed +| extend keys=pack_array("Job Runs Recovered By Resume","Job Runs Could not Resume","Total Disconnections","Success Rate"), + values=pack_array(toreal(['Job Runs Recovered By Resume']),toreal(['Job Runs Could not Resume']),toreal(['Total Disconnections']),toreal(['Success Rate'])), + colors=pack_array("#28a745","#dc3545","#6c757d","#007bff") +| extend ploty=strcat('{"data":[{"type":"bar","x":', tostring(keys), ',"y":', tostring(values), ',"marker":{"color":', tostring(colors), '}}],"layout":{"title":"Job Resume Analysis","xaxis":{"title":""},"yaxis":{"title":"Value"},"showlegend":false}}') + + + +--Ploty Examples--
\ No newline at end of file diff --git a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/schema.json b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/schema.json index 41da6e63a..dba20ffb8 100644 --- a/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/schema.json +++ b/Software/Visual_Studio_22/Tango.Portal.Chat.Web/Data/schema.json @@ -136,17 +136,91 @@ } ] }, - "JobStatusTable": { + "MachineStatusesTable": { "columns": { "SerialNumber": "string", + "MachineType": "string", + "Environment": "string", + "Organization": "string", + "Site": "string", "CreatedTime": "datetime", + "UploadTime": "datetime", "State": "string", "OverallTemperature": "real", "Status": "dynamic" }, + + "primaryTimeColumn": "CreatedTime", + "fallbackTimeColumns": [ "UploadTime" ], + "defaultTimeWindowDays": 1, + + "keys": { + "machineKey": [ "SerialNumber" ] + }, + + "enums": { + "State": [ + "PowerUp", + "Ready", + "Sleep", + "Error", + "ShuttingDown", + "PreparingJob", + "RunningJob", + "PowerDown" + ] + }, + "notes": [ - "High-volume time series of machine status snapshots. Short retention." - ] + "One row per machine status snapshot. Use CreatedTime for analysis; UploadTime is the ingestion/upload timestamp.", + "Status is a dynamic JSON object mirroring the on-device status payload (e.g., State, OverallTemperature, SpoolState, IDSPacksLevels[], error flags).", + "OverallTemperature is also duplicated at the top level for quick filtering/aggregation; the authoritative nested copy is Status.OverallTemperature.", + "Common expansions: Status.State (string), Status.SpoolState (string), Status.IDSPacksLevels (array of per-pack objects with MidTankLevel, DispenserLevel, etc.).", + "Example: latest state per machine (last 24h): MachineStatusesTable | where CreatedTime > ago(1d) | summarize arg_max(CreatedTime, *) by SerialNumber | project SerialNumber, Time=CreatedTime, State, OverallTemperature", + "Example: detect low MidTankLevel on any pack: MachineStatusesTable | where CreatedTime > ago(1d) | mv-expand pack=Status.IDSPacksLevels | project SerialNumber, CreatedTime, Index=todouble(pack.Index), MidTankLevel=toreal(pack.MidTankLevel) | where MidTankLevel < 2 | summarize min(MidTankLevel), any(CreatedTime) by SerialNumber, Index", + "Example: error intervals: MachineStatusesTable | where CreatedTime > ago(7d) | summarize Start=min(CreatedTime), End=max(CreatedTime), Count=count() by SerialNumber, State | where State == \"Error\" | order by Count desc", + "Example: Get the overall temperature during the last job for machine 30001: let LastJob = JobRunsTable | where SerialNumber == \"30001\" | summarize arg_max(EndTime, StartTime, EndTime) by SerialNumber; MachineStatusesTable | where SerialNumber == \"30001\" | join kind=inner (LastJob) on SerialNumber | where CreatedTime between (StartTime .. EndTime)" + ], + + "guardrails": { + "requireTimeFilter": true, + "maxRowsSuggested": 500, + "encourageSummarizeOrTop": true + }, + + "joinHints": [ + { + "table": "JobRunsTable", + "on": [ "SerialNumber" ], + "why": "Correlate state transitions (PreparingJob/RunningJob/Error) with job windows." + }, + { + "table": "MachinesTable", + "on": [ "SerialNumber" ], + "why": "Enrich with machine metadata (organization/site/version)." + }, + { + "table": "LogsTable", + "on": [ "SerialNumber" ], + "why": "Investigate logs around status changes using a time window." + } + ], + + "display": { + "rename": { + "CreatedTime": "Time" + }, + "suggestedProjections": [ + "SerialNumber", + "Time", + "State", + "OverallTemperature", + "Status.SpoolState", + "Status.IDSPacksLevels" + ] + } + + }, "MachinesTable": { "columns": { @@ -266,7 +340,8 @@ "Application logs emitted by services/apps running on or about the machines.", "Use CreatedTime for time filters; UploadTime is the ingestion/upload timestamp.", "Category is limited to Info, Warning, Error, Critical, Debug.", - "Source can be either Application or Firmware where Firmware represents logs generated by the embedded device and Application is everything else." + "Source can be either Application or Firmware where Firmware represents logs generated by the embedded device and Application is everything else.", + "Example to get error logs associated with a specific job: let LastJob = JobRunsTable | where SerialNumber == \"30001\" | summarize arg_max(EndTime, StartTime, EndTime) by SerialNumber; LogsTable | join kind=inner (LastJob) on SerialNumber | where Category == \"Error\" | where CreatedTime between (StartTime .. EndTime) | order by CreatedTime asc | project Time = format_datetime(CreatedTime, 'HH:mm:ss.fff'), Message" ], "guardrails": { @@ -461,4 +536,4 @@ ] } } -}
\ No newline at end of file +} |