Splunk Search

Subtract Results from Two Different Searches/Charts

genesiusj
Builder

Hello,
Happy Easter, Passover, and holiday to all you Splunkers. I pray that you and your families are safe and healthy during this pandemic.

SessionType (Start, Connect, Stop). Each SessionType generates a SessionID. The same SessionID is used throughout the life of the session (Start, Connect, Stop). Each SessionType also has an OfficeLocation associated with it.

We need to calculate the number of active connections at any point in time. This is the sum(Start) + sum(Connect) - sum(Stop). This is after a dedup of all SessionID. This will give us the total active sessions. Here is the code.

index=syslog
    AND sourcetype=syslog
    AND (SessionType =Start OR SessionType =Connect) 
| dedup SessionID
| stats count(SessionID) AS actSess 
| appendcols 
    [ search index= syslog
        AND sourcetype=syslog
        AND (SessionType =Stop) 
    | stats count(SessionID) AS inactSess ] 
| eval totActSess=actSess-inactSess 
| table totActSess

The results will be displayed in a pie chart.

We also need the total active sessions broken down by OfficeLocation. This calculation is a bit more complex. Based on the OfficeLocation we need to determine if the SessionID has a SessionType of Stop associated with it.

I feel like I might be overthinking this.
How do you subtract two tables or charts from each other? I'm figuring something like this.
sum(Start) BY OfficeLocation + sum(Connect) BY OfficeLocation - sum(Stop) BY OfficeLocation

Thanks and God bless,
Genesius

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Your search should work, although it doesn't make sense to display a single value in a pie chart.

Including OfficeLocation changes things. We can't use appendcols because the two searches may not have the exact same set of locations. Perhaps this will help.

index=syslog sourcetype=syslog (SessionType =Start OR SessionType =Connect) 
| dedup SessionID
| stats count(SessionID) AS actSess by OfficeLocation
| append
    [ search index= syslog sourcetype=syslog (SessionType =Stop) 
    | stats count(SessionID) AS inactSess by OfficeLocation ] 
| sort 0 OfficeLocation
| fillnull value = 0
| eval totActSess=actSess-inactSess 
| table OfficeLocation totActSess
---
If this reply helps you, Karma would be appreciated.
0 Karma

genesiusj
Builder

Thanks @richgalloway
The SPL I provided is for a single value for the total of all active sessions.
The difference (active sessions by OfficeLocation) is going to be a pie chart.
Thanks and God bless,
Genesius

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