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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...