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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...