Splunk Search

show two different times for object per date

dpatiladobe
Explorer

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

alt text

Tags (1)
0 Karma
1 Solution

to4kawa
Ultra Champion
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.

View solution in original post

0 Karma

to4kawa
Ultra Champion
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.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...