Improve efficiency of search

[davidcraven02]

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

[somesoni2]

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

[somesoni2]

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?)

[davidcraven02]

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

[koshyk]

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

[davidcraven02]

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

[koshyk]

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

[skoelpin]

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)