Grouping Activity By Hour Across Months

[xbudahx]

I am trying to display a line chart on a dashboard which shows activity of a service by time of day. I need to show this over several months to determine what time of day is busiest.

This would be easy, would it not be for a need to eliminate the service calls made by a monitor. These are made once every ten minutes and need to be left out of the results.

I'm using message_id for the count, since I want the call and response (2 separate log entries) to count only once. My issue is that my results are not correct, it seems as if they are being truncated or if my math is somehow off.

The search I'm using is below and any help is much appreciated.

index="platform_osb" sourcetype="OSB" SingleSignOn SVC_ACCT earliest=10/08/2013:0:0:0 latest=@d

| rex field=_raw "message-id:\s+(?P[^,]+)"

| eval searchStartTime=strptime("10/08/2013", "%m/%d/%Y")

| eval reductionFigure=(floor((now()-searchStartTime)/60/60/24)-1)*6

| stats count(message_id) as Count1 By date_hour reductionFigure

| eval Count=Count1-reductionFigure

| table date_hour Count1 Count | fields - Count1

[somesoni2]

The problems that I see with your queries is:
Call and Response are two separate entry and if they both have message-id, then by using "|stats count(message_id), you are counting them both.
If message-id field is unique for every set of Call and response events, they you should use "|stats dc(message_id)".
Try it and let me know if it helps.

[xbudahx]

Specifically, I made the change to reductionFigure you suggested.

Thanks!

[xbudahx]

Thank you.

I did make the change you suggested, however my results remain unchanged.

[somesoni2]

Ohh my bad, another issue that I see with the search with reductionFigure calculation. The time range for search is "10/08/2013:0:0:0" to "@d"(for today it'll be 12/05/2013:0:0:0". But while calculating reductionFigure, you are considering "10/08/2013:0:0:0" to "now()" (which will be "12/05/2013:13:10:59" by now), so ultimately you are reducing more 13*6 count, which is not correct.
To resolve this either change your latest to "now()" or change the eval command for reductionFigure to "|eval reductionFigure=(floor((relative_time(now(),"@d")-searchStartTime)/60/60/24)-1)*6.

[somesoni2]

Ohk, my bad. Another issue that I see is with time range. The time range for the search is from "10/08/2013:0:0:0" to "@d" (which is "Current Date(M/D/Y) 00:00:00". But when you're reductionFigure is calculated from "10/08/2013:0:0:0" to "now", which means if you are executing the search at, say 9:00 AM, you're reducing the count by extra 9*6. So in the reductionFigure eval command, instead of "now()", use "relative_time(now(), "@d")"

[xbudahx]

Thank you, although I think I misspoke, the call is the only one with SVC_ACCT in it so searching for that is keeping my count distinct.