Splunk Search

Improve efficiency of search

davidcraven02
Communicator

I want to compare the mailbox size from today to last week but my search is very slow and I am not sure how best to make it more efficient

index=msexchange source=exchangemailboxinventory samAccountName=testuser1
| eval mailboxSize = totalItemSize+totalDeletedItemSize 
| eval MB = round(mailboxSize/1024) 
| eval mailboxGB = round(MB/1024,2) 
| eval archiveMailboxSize = archiveTotalItemSize+archiveTotalDeletedItemSize 
| eval archiveMB = round(archiveMailboxSize/1024) 
| eval archiveMailboxGB = round(archiveMB/1024,2) 
| table samAccountName, totalItemSize, totalDeletedItemSize, mailboxGB, archiveTotalItemSize, archiveTotalDeletedItemSize, archiveMailboxGB 
| dedup samAccountName 
| join type=left samAccountName
    [ search index=msexchange source=exchangemailboxinventory samAccountName=testuser1 latest=-1week 
    | eval mailboxSizeNew = totalItemSize+totalDeletedItemSize 
    | eval MBnew = round(mailboxSizeNew/1024) 
    | eval mailboxGBOld = round(MBnew/1024,2) 
    | table samAccountName, mailboxGBOld   
    | dedup samAccountName] 
    | eval mailboxGrowth = (mailboxGB - mailboxGBOld) , growth= (mailboxGrowth/mailboxGBOld)*100
    | table samAccountName, totalItemSize, totalDeletedItemSize, mailboxGB,mailboxGBOld, mailboxGrowth, growth
| sort -mailboxGB

alt text

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

Give this a try

index=msexchange source=exchangemailboxinventory samAccountName=testuser1 (earliest=@d latest=now) OR (earliest=-7d@d latest=-6d@d)
| eval mailboxSize = totalItemSize+totalDeletedItemSize 
 | eval MB = round(mailboxSize/1024) 
 | eval mailboxGB = round(MB/1024,2)
 | eval archiveMailboxSize = archiveTotalItemSize+archiveTotalDeletedItemSize 
 | eval archiveMB = round(archiveMailboxSize/1024) 
 | eval archiveMailboxGB = round(archiveMB/1024,2) 
 | eval Period=if(_time>=relative_time(now9),"@d"),"Today","SameDayLastWeek")
 | eval mailboxGBOld=if(Period="SameDayLastWeek",ailboxGB,null())
 | table samAccountName, Period totalItemSize, totalDeletedItemSize, mailboxGB, archiveTotalItemSize, archiveTotalDeletedItemSize, archiveMailboxGB, mailboxGBOld
 | dedup samAccountName Period
 | eventstats values(mailboxGBOld) as mailboxGBOld 
 | where Perio="Today"
 | eval mailboxGrowth = (mailboxGB - mailboxGBOld) , growth= (mailboxGrowth/mailboxGBOld)*100
 | table samAccountName, totalItemSize, totalDeletedItemSize, mailboxGB,mailboxGBOld, mailboxGrowth, growth
 | sort -mailboxGB
0 Karma

somesoni2
SplunkTrust
SplunkTrust

The best optimization would be to get rid of join command. What's the time range applied to both searches? (e.g. are you comparing today vs same day last week?)

0 Karma

davidcraven02
Communicator

Yes basically comparing todays value of mailboxGB to 7 days ago.

0 Karma

koshyk
Super Champion

The aim here is to have single query. Collect required fields with dates for last 8 days. then compare the difference on the stats value rather than individual events

 index=msexchange source=exchangemailboxinventory testuser1 samAccountName=testuser1 earliest=-8d
| timechart span=1d sum(totalItemSize) as totalItemSize, sum(totalDeletedItemSize) as totalDeletedItemSize 
| rename _time as date| eval date=strftime(date,"%F")
| eval yesterday=strftime(relative_time(now(),"-86400s"),"%F")
| eval lastWeekday=strftime(relative_time(now(),"-604800s"),"%F")
| where (date=lastWeekday OR date=yesterday)

You can then add the relevant evaluations/sum/differences etc. between "yesterday" and "lastWeekday"

PS: You can make this query simpler if you use timewrap command, but it is available from 6.5.x only

0 Karma

davidcraven02
Communicator

Thank you but its today that I want not yesterday. We do have splunk 7.0.1 installed. How would timewrap work?

0 Karma

koshyk
Super Champion

Today is same only. I just did because yesterday vs -8d is better than today vs -7d. But upto you. So instead of 86400 you can put 0s.

timewrap has to be after timechart, and it will split the rows to columns kinda, which makes comparision in one-line.

 index=msexchange source=exchangemailboxinventory testuser1 samAccountName=testuser1 earliest=-8d
 | timechart span=1d sum(totalItemSize) as totalItemSize, sum(totalDeletedItemSize) as totalDeletedItemSize 
 | timewrap 1week

You need to try tuning these

0 Karma

skoelpin
SplunkTrust
SplunkTrust

On line 8, change table to fields +

Also, why are you joining to the same search? I think using relative_time would be a better choice

| eval one_week_ago=relative_time(now(),-1w)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...