Heavy query

[spillo491]

Hi,

I have a problem with this query executed on 3 months

index="app" host="sl0920*" source="/home/java/jboss-eap-6.2/app/log/teller-web.log" OR source="/home/java/jboss-eap-6.2/app/log/desktop-web.log" priority="FATAL" category="AUDIT*" message="{Invoking*" | eval date = strftime(_time, "%Y-%m-%d") | stats count as Contatore by message,correlationId,date | where Contatore > 1 | eval tot = Contatore/2 | chart sum(tot) as tot by date

Is it possible rewrite the same query with a query with better performances please ?

[DalJeanis]

I don't see anything obviously bad about the code. When possible, you want to avoid reformatting data at the event level before the data is summarized, and a numeric field (or epoch time field) is more efficient for summary than a display field, but you at least need to bin the _time at the 1d level, so we can't avoid a reformat completely.

Try this, and see what happens. There might be a marginal improvement.

 index="app" host="sl0920*" (source="/home/java/jboss-eap-6.2/app/log/teller-web.log" OR source="/home/java/jboss-eap-6.2/app/log/desktop-web.log") priority="FATAL" category="AUDIT*" message="{Invoking*" 
| table _time, message, correlationId
| bin _time as date  span=1d
| stats count as Contatore by message, correlationId, date 
| where Contatore > 1 
| eval tot = Contatore/2
| eval date = strftime(_time, "%Y-%m-%d")  
| chart sum(tot) as tot by date

I'm tempted to try a timechart version, but without your data, I have no way of knowing if it would get better results.

[spillo491]

Sorry but this query doesn't extract any results

[niketnilay]

@spillo491... do you get any result when you run 1st 4 lines of DalJeanis' code?

[spillo491]

Yes, I get results !

[niketnilay]

On a different thought... If you have too many events to be handled in 30 days... You can actually try summary index.