Splunk Search

adding the result of 2 "stats count" queries

ruiaires
Path Finder

I'm using Summary indexing to calculate daily and hourly counts of events and feed the totals to a fast dashboard gauge. I have 2 scheduled searches that run every hour (hourlysearch) and every day (dailysearch) to compute the totals

Then I have 2 summary index queries: - index=summary source="dailysearch" | stats count as TotalA ( - index=summary source="hourlysearch" | stats count as TotalB

These queries run for different time ranges: - the first runs from "start date" until "@d" (today at 00h00) - the second runs from @d (today at 00h00) until "now"

The ideia is to have, at all times, the global running total - run both queries - eval(TotalA + TotalB)

I've seen several answers here with techinques to run 2 queries (using OR and JOIN) but since I'm using different time ranges, it seems they don't apply to this ?

1 Solution

David
Splunk Employee
Splunk Employee

I would go with an appendcols here, which is similar to a join. The way you can get around the time issue is by overriding the time for the second search:

 index=summary source="dailysearch" earliest=-7d@d latest=@d 
         | stats count as TotalA
         | appendcols 
             [search index=summary source="hourlysearch" earliest=@d latest=now
                   | stats count as TotalB]
         | eval Total=TotalA+TotalB
         | fields Total

The only complication here is that you'll need to specify an earliest in the first search. At least in the test I just ran, specifying only a latest overrode both the latest and the earliest. If you're trying to put this in a dashboard with a timepicker, that might not work for you. One way around this would be to code up a custom time picker, a la this answers, but you'd lose the dynamic (and standardized) power of timepicker that way.

You could also get around it this way:

index=summary source="dailysearch" 
            | eval ItemA=if(_time < relative_time(now(), "@d"),1,0) 
            | stats sum(ItemA) as TotalA 
            | appendcols [search index=summary source="hourlysearch" earliest=@d latest=now  
                               | stats count as TotalB] 
            | eval Total=TotalA+TotalB

But this would add the inefficiency of running the dailysearch from @d to now. Based on the notion of it being for summary indexes, I'd imagine that's not going to have a significant performance impact for you.

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

It happens that because your two time ranges are adjacent, you could do something simpler, though it's only marginally so. And it less efficient if the time ranges have a large gap between them:

index=summary source=dailysearch OR source=hourlysearch earliest=-7d@d latest=now
    | stats count(eval(source=="dailysearch")) as dailysum
            count(eval(source=="hourlysearch")) as hourlysum

However, I would also note that it's a good idea to store different densities of summary (e.g. daily vs hourly) into different indexes, because you'd then be able to archive/delete, e.g., all the hourlies after one month, while keeping all the dailies for two years, and save on disk space.

David
Splunk Employee
Splunk Employee

I would go with an appendcols here, which is similar to a join. The way you can get around the time issue is by overriding the time for the second search:

 index=summary source="dailysearch" earliest=-7d@d latest=@d 
         | stats count as TotalA
         | appendcols 
             [search index=summary source="hourlysearch" earliest=@d latest=now
                   | stats count as TotalB]
         | eval Total=TotalA+TotalB
         | fields Total

The only complication here is that you'll need to specify an earliest in the first search. At least in the test I just ran, specifying only a latest overrode both the latest and the earliest. If you're trying to put this in a dashboard with a timepicker, that might not work for you. One way around this would be to code up a custom time picker, a la this answers, but you'd lose the dynamic (and standardized) power of timepicker that way.

You could also get around it this way:

index=summary source="dailysearch" 
            | eval ItemA=if(_time < relative_time(now(), "@d"),1,0) 
            | stats sum(ItemA) as TotalA 
            | appendcols [search index=summary source="hourlysearch" earliest=@d latest=now  
                               | stats count as TotalB] 
            | eval Total=TotalA+TotalB

But this would add the inefficiency of running the dailysearch from @d to now. Based on the notion of it being for summary indexes, I'd imagine that's not going to have a significant performance impact for you.

ruiaires
Path Finder

Thanks...it worked 🙂
And I have a fixed eartiest in the first search so, that won't be a problem...

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 ...