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
Revered Legend

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
Revered Legend

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!

Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...