I am trying to extract 2 different time from extend event logs
1. Processing time taken by Server. ( "Finished processing the" OR EventClass=COMMAND_END )
2. Time taken by Seleect query ("TextData=*SELECT" )
First Query
index=xxx sourcetype=extendedevent ( "Finished processing the" OR EventClass=COMMAND_END ) DatabaseName="DbName" | eval ObjectName=if(isnull(ObjectName), "Full_Processing_time",ObjectName) | eval Date=strftime(_time,"%d/%m/%y") | eval Duration=round(tonumber(Duration)/1000/60,2) |chart limit=0 max(Duration) AS D BY ObjectName Date
Second Query
index=xxx sourcetype=extendedevent ( "TextData=*SELECT") DatabaseName="DbName" | eval Date=strftime(_time,"%d/%m/%y") | eval Duration=round(tonumber(Duration)/1000/60,2) |chart limit=0 max(Duration) AS D BY ObjectName Date
I want to show these 2 timings per object per day (7 or 30 day's of report.) I am not able to combine and show
What i have tried is
index=xxx sourcetype=extendedevent ( "Finished processing the" OR EventClass=COMMAND_END ) DatabaseName="DbName" | eval ObjectName=if(isnull(ObjectName), "Full_Processing_time",ObjectName) | eval Date=strftime(_time,"%d/%m/%y") | eval SSAS_Processing=round(tonumber(Duration)/1000/60,2) | append [ search index=xxx sourcetype=extendedevent ( "TextData=*SELECT") DatabaseName="DbName" | eval Date=strftime(_time,"%d/%m/%y") | eval Select_Query=round(tonumber(Duration)/1000/60,2) ] | chart max(SSAS_Processing) AS SSAS_Processing, max(Select_Query) AS Select_Query BY ObjectName ,Date
index=xxx (earliest=1579014000 latest=1579100400 ) OR (earliest=1579100400 latest=1579186800)
AND sourcetype=extendedevent DatabaseName="DbName"
AND (( "Finished processing the" OR EventClass=COMMAND_END ) OR "TextData=*SELECT")
| eval ObjectName=if(isnull(ObjectName), "Full_Processing_time",ObjectName)
| eval Date=strftime(_time,"%d/%m/%y")
| eval Select_Query=if(searchmatch("TextData=*SELECT"),round(tonumber(Duration)/1000/60,2),NULL)
| eval SSAS_Processing=if(NOT searchmatch("TextData=*SELECT"),round(tonumber(Duration)/1000/60,2),NULL)
| chart limit=0 max(SSAS_Processing) AS SSAS_Processing, max(Select_Query) AS Select_Query BY ObjectName Date
append
is searching twice, so I don't use it.
maybe, it works
Reordering field names can be difficult.
index=xxx (earliest=1579014000 latest=1579100400 ) OR (earliest=1579100400 latest=1579186800)
AND sourcetype=extendedevent DatabaseName="DbName"
AND (( "Finished processing the" OR EventClass=COMMAND_END ) OR "TextData=*SELECT")
| eval ObjectName=if(isnull(ObjectName), "Full_Processing_time",ObjectName)
| eval Date=strftime(_time,"%d/%m/%y")
| eval Select_Query=if(searchmatch("TextData=*SELECT"),round(tonumber(Duration)/1000/60,2),NULL)
| eval SSAS_Processing=if(NOT searchmatch("TextData=*SELECT"),round(tonumber(Duration)/1000/60,2),NULL)
| chart limit=0 max(SSAS_Processing) AS SSAS_Processing, max(Select_Query) AS Select_Query BY ObjectName Date
append
is searching twice, so I don't use it.
maybe, it works
Reordering field names can be difficult.