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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...