Splunk Search

Splunk Query - how to get sum of count for a specific field

shashankk
Communicator

I am having a below query and the sample output shown:

index=<search_string> earliest=-30d@d
| timechart span=1m aligntime=earliest count(eval(searchmatch("from"))) as HotCount by TestMQ
| where tonumber(strftime(_time, "%H")) >= 2 AND tonumber(strftime(_time, "%H")) < 4

 

_time TestMQ1 TestMQ2 TestMQ3
2023-07-04 02:00:00 20 30 45
2023-07-04 02:01:00 30 80 20
2023-07-04 02:02:00 50 20 25
and so on.... .. .. ..


My requirement is to get the Sum of these HotCount and show it as TotalHotCount in a Day wise columns.

I have tried modifying this query to get total sum and store the results in day wise column as below:

index=<search_string> earliest=-30d@d
| timechart span=1m aligntime=earliest count(eval(searchmatch("from"))) as HotCount by TestMQ
| where tonumber(strftime(_time, "%H")) >= 2 AND tonumber(strftime(_time, "%H")) < 4
| eval Day=strftime(_time, "%Y-%m-%d")
| stats sum(HotCount) as TotalHotCount by Day


But, this is not giving me any results (blank output). What is missing here and how it can be modified to achieve the below expected results?

Kindly suggest.

Day TestMQ1 TestMQ2 TestMQ3
2023-07-04 120 170 210
2023-07-05  90 180 120
2023-07-06  150 120 125
and so on.... .. .. ..



Labels (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Use addtotals (you don't have a field called HotCount)

shashankk
Communicator

Thank you for kind your response @ITWhisperer 

I have tried the suggested approach as below:

index=<search_string> earliest=-30d@d
| timechart span=1m aligntime=earliest count(eval(searchmatch("from"))) as HotCount by TestMQ
| where tonumber(strftime(_time, "%H")) >= 2 AND tonumber(strftime(_time, "%H")) < 4
| eval Day=strftime(_time, "%Y-%m-%d")
| addtotals fieldname=TotalHotCount label="Total" labelfield=TestMQ


It gives me result in below format. It is doing the Row level of Sum
Additionally I am looking for Merging the column results in Day wise total. (i.e. the total of the given time range should be added as a Day wise output)

_timeTestMQ1TestMQ2TestMQ3DayTotalHotCount
2023-07-04 02:00:001001502002023-07-04450
2023-07-04 02:01:00501001502023-07-04 300
2023-07-04 02:02:001501503002023-07-04 600
and so on....for 30 days..........

 

Expected output as below, please suggest what is missing here and how it can be achieved?

Kindly suggest.

Day WiseTestMQ1TestMQ2TestMQ3TotalHotCount
2023-07-04120180200500
2023-07-05 90110100300
2023-07-06 150100120370
and so on............
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

index=<search_string> earliest=-30d@d
| where tonumber(strftime(_time, "%H")) >= 2 AND tonumber(strftime(_time, "%H")) < 4
| timechart span=1d aligntime=earliest count(eval(searchmatch("from"))) as HotCount by TestMQ
| addtotals fieldname=TotalHotCount label="Total" labelfield=TestMQ
| eval Day=strftime(_time, "%Y-%m-%d")
Get Updates on the Splunk Community!

Your Guide to SPL2 at .conf24!

So, you’re headed to .conf24? You’re in for a good time. Las Vegas weather is just *chef’s kiss* beautiful in ...

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...