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