Tracking exchange white space values over time (parsing values in message from event 1221)

[jayrodef]

Hello, I'm new to Splunk and I'm using it to track several things that are looking really good. One thing I was curious to get working is a search to track the white white space by datastore on our exchange server. Here's an example of one of the events:

20110215060000.000000 Category=6 CategoryString=General ComputerName=EXSVR01 EventCode=1221 EventIdentifier=1074136261 EventType=3 Logfile=Application RecordNumber=2444633 SourceName=MSExchangeIS Mailbox Store TimeGenerated=20110215060000.000000-300 TimeWritten=20110215060000.000000-300 Type=Information User=NULL wmi_type=WinEventLog:Application Message=The database "NWRK\MB1" has 475 megabytes of free space after online defragmentation has terminated.

For more information, click http://www.microsoft.com/contentredirect.asp.

---

So in the Message area, I'd like to extract the database (NWRK\MB1) and the 475 value for megabytes. Sometimes I get multiple events for the same database in one day, so I'd need to ensure the values are distinct per database and track it over time. This is the search I had so far, and it tells me the total across all databases for the day, however it doesn't filter out the duplicate entries:

sourcetype="WMI:WinEventLog:Application" EventCode="1221" host="EXSVR01" | rex "has (?<mbs>\d+) megabytes of free" | timechart per_day(mbs)

Any ideas would be greatly appreciated, thanks so much!

Regards, Jared

[David · Accepted solution]

What I gather you're looking to do is track your free exchange database space over time, per database. What I do for this report is run the following search:

SearchCriteria | rex field=_raw "The database \"(?<EXDB>.*?)\" has (?<FreeMB>\d*) megabytes of free" 
               | timechart last(FreeMB) by EXDB

Doing a timechart last(FreeMB) You need to provide some function for Timechart to run on FreeMB, in the event it has to summarize. (E.g., if you were running it over the last 7 days, it wouldn't summarize at all. But if you were running it over 2 years, it would probably summarize it into months, or two month periods, or what have you.) Depending on your needs, you might want to go with avg() or min() or max() -- last() just shows the most recent event, which is fine since I run this report over a relatively small period of time, and I have a pretty small amount of change in my databases.

Notably, with this approach, it doesn't matter how many events you get per day. It will automatically summarize the number of events per database, and only show you a single value.

Does that all make sense?

[David]

Ah, I was flipping first and last. Good to know.

[David]

You should only get one entry per time period, per host. Maybe some events have the database name capitalized or not? If that's the case, then you could add an
| eval EXDB = upper(EXDB)
before the timechart.

If I'm misreading and you only want a single value overall (e.g., "the most recent free space"), you could always replace the timechart with just
| stats last(FreeMB) by EXDB
(or avg(), etc.) That would summarize all the values to one number per EXDB, but wouldn't let you see trends.

If you're still seeing duplicates, you can paste the results and the query, and I can take a look.

[gkanapathy]

if you use timechart span=1d first(FreeMB) by EXDB, it will only select the first (i.e. most recent) value of FreeMB it encounters for each distinct EXDB value.

[jayrodef]

Awesome, that works great, I think I'm still getting the sum of duplicate entries (i.e if there are two events for "NWRK\MB1" database), however I'll try to work through that, thanks for the help.

Jared

[Ron_Naken]

How do you determine a duplicate entry? By the EventIdentifier? Host? Use dedup to remove the duplicate values:

... | dedup EventIdentifier | timechart ...

[jayrodef]

I guess I'd like to dedup on the Datastore, so there's only one entry for datastore 'NWRK\MB1'. I tried somethings like:

| rex "The database (?.*)" | dedup (database) |

but that didn't seem to work.

Thanks for the reply.