OpenAI.kql

OpenAI.kql

//external
#connect cluster('kvc0wyc7zw8bafmkdc1bzv.southcentralus').database('MyDatabase')

.set-or-append async OpenAIQnA <|
let d= dynamic(
{
    "statusCode": 200,
    "headers": {
        "Transfer-Encoding": "chunked",
        "Vary": "Accept-Encoding",
        "Strict-Transport-Security": "max-age=31536000",
        "request-id": "e953f39a-96ae-4df7-aa1b-34affd1e3c8d",
        "client-request-id": "e953f39a-96ae-4df7-aa1b-34affd1e3c8d",
        "x-ms-ags-diagnostic": "{\"ServerInfo\":{\"DataCenter\":\"Central US\",\"Slice\":\"E\",\"Ring\":\"2\",\"ScaleUnit\":\"002\",\"RoleInstance\":\"DS3PEPF00006B3B\"}}",
        "OData-Version": "4.0",
        "Timing-Allow-Origin": "*",
        "x-ms-apihub-cached-response": "true",
        "x-ms-apihub-obo": "false",
        "Date": "Fri, 05 May 2023 23:49:07 GMT",
        "Content-Type": "application/json",
        "Content-Length": "1617"
    },
    "body": {
        "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#chats('19%3Ameeting_NDNhZjNiOWItYjRjNi00YjAwLThiZTctNTI0YzhhODhkODZh%40thread.v2')/messages/$entity",
        "id": "1683330546879",
        "replyToId": "1683330546879",
        "etag": "1683330546879",
        "messageType": "message",
        "createdDateTime": "2023-05-05T23:49:06.879Z",
        "lastModifiedDateTime": "2023-05-05T23:49:06.879Z",
        "lastEditedDateTime": null,
        "deletedDateTime": null,
        "subject": null,
        "summary": null,
        "chatId": "19:meeting_NDNhZjNiOWItYjRjNi00YjAwLThiZTctNTI0YzhhODhkODZh@thread.v2",
        "importance": "normal",
        "locale": "en-us",
        "webUrl": null,
        "channelIdentity": null,
        "policyViolation": null,
        "eventDetail": null,
        "from": {
            "application": null,
            "device": null,
            "user": {
                "@odata.type": "#microsoft.graph.teamworkUserIdentity",
                "id": "3ad4d799-f62a-4c2f-9074-12a513b27c39",
                "displayName": "Hiram Fleitas",
                "userIdentityType": "aadUser",
                "tenantId": "cb6db03a-9620-49aa-9171-8732bd70440b"
            }
        },
        "body": {
            "contentType": "html",
            "content": "<p>genie, do you know what is Power BI Fabric?</p>",
            "plainTextContent": "genie, do you know what is Power BI Fabric?"
        },
        "attachments": [],
        "mentions": [],
        "reactions": [],
        "messageLink": "https://teams.microsoft.com/l/message/19:meeting_NDNhZjNiOWItYjRjNi00YjAwLThiZTctNTI0YzhhODhkODZh@thread.v2/1683330546879?context=%7B%22contextType%22:%22chat%22%7D",
        "threadType": "chat",
        "conversationId": "19:meeting_NDNhZjNiOWItYjRjNi00YjAwLThiZTctNTI0YzhhODhkODZh@thread.v2"
    }
});
print EnquedTime=now(), BodyContent=d;

.drop table OpenAIQnA ifexists 



.show operations
| where StartedOn >ago(5m)

.set-or-append async OpenAIQnA<|
let q=dynamic('<p>genie, what is your favorite song?</p>');
let a=dynamic([{"id":"chatcmpl-7DJHdzCnYPbobeiwRuXzVIaDjNXKQ","object":"chat.completion","created":1683406745,"model":"gpt-35-turbo","choices":[{"index":0,"finish_reason":"stop","message":{"role":"assistant","content":"As an AI language model, I don't have personal preferences or emotions like humans do, so I don't have a favorite song. However, I can help you find some great songs based on your preferences if you'd like! What kind of music do you enjoy?"}}],"usage":{"completion_tokens":54,"prompt_tokens":32,"total_tokens":86}}]);
print t=now(), q=q, a=a


.set-or-append async OpenAIQnA<|

let q=tostring('<p style="margin-bottom:0; margin-left:0">&nbsp;</p><p>genie, what kind of music do you enjoy?</p>');
let a=dynamic([{"id":"chatcmpl-7DJQI6hWMdSE67DMwKXjrP84kOJPe","object":"chat.completion","created":1683407282,"model":"gpt-35-turbo","choices":[{"index":0,"finish_reason":"stop","message":{"role":"assistant","content":"As an AI language model, I don't have a particular taste in music. However, I can provide you with information about any music genre or artist you are interested in."}}],"usage":{"completion_tokens":35,"prompt_tokens":51,"total_tokens":86}}]);
print t=now(), q=q, a=a

.set-or-append async UserMessage with(folder = "OpenAI") <|
let cid=tostring('19:meeting_NDNhZjNiOWItYjRjNi00YjAwLThiZTctNTI0YzhhODhkODZh@thread.v2');
let mid=tolong(1683411718315);
let cdt=datetime(2023-05-06T22:21:58.315Z);
let bc=tostring('<p>genie, What is KQL?</p>');
print ConversationId=cid, MessageId=mid, CreatedDateTime=cdt, BodyContent=bc

.create table SamplePowerRequirementHistorizedData (timestamp: datetime, twinId: string, modelId: string, name: string, value: real, relationshipTarget: dynamic, relationshipId: dynamic) with (docstring = "Contains historized property values over a one month period",folder = "IoT")


.drop table UserMessage ifexists 

.drop table OpenAIQnA ifexists 


.set-or-append OpenAIQnA with(folder="OpenAI") <|
let cid=tostring('19:meeting_NDNhZjNiOWItYjRjNi00YjAwLThiZTctNTI0YzhhODhkODZh@thread.v2');
let mid=toint(1683412967389);
let cdt=datetime(2023-05-06T22:42:47.389Z);
let b=dynamic({"contentType":"html","content":"<p>copilot, can you give me one advanced KQL query sample?</p>","plainTextContent":"copilot, can you give me one advanced KQL query sample?"});
let hb=dynamic({"id":"chatcmpl-7DKu5zvTcITioZDMmLKErrG749dQB","object":"chat.completion","created":1683412973,"model":"gpt-35-turbo","choices":[{"index":0,"finish_reason":"stop","message":{"role":"assistant","content":"Sure! Here's an advanced KQL query sample:\n\n```\nSecurityEvent\n| where TimeGenerated > ago(1d)\n| where AccountType == \"User\"\n| where AccountName != \"ANONYMOUS LOGON\"\n| where EventID in (4624, 4625, 4768, 4769, 4776, 4779)\n| where LogonType == 3 or LogonType == 10 or LogonType == 11\n| extend LogonTypeDescription = case(LogonType == 3, \"Network\", LogonType == 10, \"Remote Interactive\", LogonType == 11, \"Cached Interactive\", \"Other\")\n| project TimeGenerated, Computer, AccountName, LogonTypeDescription, SourceIP, TargetUserName, TargetDomainName, LogonProcessName, AuthenticationPackageName\n```\n\nThis query looks for user logon events in the Security Event log for the past day, excluding anonymous logons. It filters for specific Event IDs and logon types, and then adds a description to each logon type. Finally, it projects a few relevant columns for analysis."}}],"usage":{"completion_tokens":233,"prompt_tokens":37,"total_tokens":270}});
print ConversationId=cid, MessageId=mid, CreatedDateTime=cdt, Body=b, HttpBody=hb


OpenAIQnA
| take 10
| mv-expand Question=Body.content, Answer=HttpBody.choices[0].message.content, HttpBody.created
| project QuestionTime=CreatedDateTime, Question=replace(@'\</?\w+\>', '',tostring(Question)), Answer, AnswerTime=unixtime_seconds_todatetime(toint(HttpBody.created))
| extend ms=datetime_diff('millisecond',AnswerTime, QuestionTime)
//max(ms) = slowest response?
//max/min Addressed to?
//maxmin tokens?
//most common question?
//most common answer?




let removeHtmlTags = (str: string) {
  replace(@'\</?\w+\>', '', str)
};
datatable(input:string) ["<p>Some text</p>"] 
| extend output = removeHtmlTags(input)


//hallucinations 
OpenAIQnA
| where HttpBody has 'interpolation'
| take 10



//3RD
OpenAIQnA
| take 10

| mv-expand Question=Body.content, Answer=HttpBody.choices[0].message.content, HttpBody.created
| project QuestionTime=CreatedDateTime, Question=replace(@'\</?\w+\>', '',tostring(Question)), Answer, AnswerTime=unixtime_seconds_todatetime(toint(HttpBody.created))
| extend ms=datetime_diff('millisecond',AnswerTime, QuestionTime)
| sort by QuestionTime desc 
//max(ms) = slowest response? -done
//max/min Addressed to? -done
//maxmin tokens?
//most common question?
//most common answer?
//hallucinations OpenAIQnA| where HttpBody has 'interpolation'| take 10
| where Question has 'image'

.delete table OpenAIQnA records <| OpenAIQnA
| where HttpBody.choices[0].message.content contains_cs ' send email' and Body contains_cs "not sure what you mean by"

.delete table OpenAIQnA records <| 
OpenAIQnA
| where CreatedDateTime == '2023-05-09T19:57:13.307Z'
| extend ms=datetime_diff('millisecond', unixtime_seconds_todatetime(toint(HttpBody.created)), CreatedDateTime)
| where ms >4000

.delete table OpenAIQnA records <| 
OpenAIQnA
| where CreatedDateTime == '2023-05-09T20:22:53.249Z'
| extend ms=datetime_diff('millisecond', unixtime_seconds_todatetime(toint(HttpBody.created)), CreatedDateTime)
| where ms>0


//microsoft
#connect cluster('kvc43f0ee6600e24ef2b0e.southcentralus').database('MyDatabase')

.set-or-append OpenAIQnA with(folder="OpenAI") <| 
cluster('kvc0wyc7zw8bafmkdc1bzv.southcentralus').database('MyDatabase').OpenAIQnA 

OpenAIQnA | count 

.set-or-append UserMessage with(folder="OpenAI") <| 
cluster('kvc0wyc7zw8bafmkdc1bzv.southcentralus').database('MyDatabase').UserMessage 

UserMessage | count

.delete table OpenAIQnA records <| OpenAIQnA
| extend ms=datetime_diff('millisecond',unixtime_seconds_todatetime(toint(HttpBody.created)), CreatedDateTime)
| where CreatedDateTime > ago(1d) and ms > 100000