Splunk Search

Grouping Activity By Hour Across Months

xbudahx
Explorer

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

Tags (2)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

xbudahx
Explorer

Specifically, I made the change to reductionFigure you suggested.

Thanks!

0 Karma

xbudahx
Explorer

Thank you.

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

xbudahx
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...