we have two device AUSTDPVPN1 and AUSTDPVPN2 and current user logged in count on device as 0 and 2867.
I want whenever AUSTDPVPN1 user login count is 0 it should get replace with login user count of AUSTDPVPAN2.
Below mentioned is query an their output
Query
index="pulse_secure_index" STS20641
| rex field=host (?<device>\w+).*
| rex field=msg "STS20641: Number of concurrent users logged in to the device: (?<currUser>\d+)"
| eval device=upper(device)
| search device=AUSTDPVPN*
| stats max(currUser) as currentUser BY device
OUTOUT
device | currentUser |
AUSTDPVPN1 | 0 |
AUSTDPVPN2 | 2867 |
Suggest how we can get below mentioned output
device | currentUser |
AUSTDPVPN1 | 2867 |
AUSTDPVPN2 | 2867 |
There are probably a number of ways to do this - here's one
index="pulse_secure_index" STS20641
| rex field=host (?<device>\w+).*
| rex field=msg "STS20641: Number of concurrent users logged in to the device: (?<currUser>\d+)"
| eval device=upper(device)
| search device=AUSTDPVPN*
| stats max(currUser) as currentUser BY device
| eventstats max(currentUser) as maxCurrentUser
| eval currentUser=if(currentUser=0,maxCurrentUser,currentUser)
| fields - maxCurrentUser
I have highlighted currentuser count for AUSTDPVPN1 which is sum of both cluster device current user login count.
We want output like below mentioned.
AUSTIN | AUSTDPVPN1 | DESKTOP | 4000 | 4000 | 6000 | 0+2867 |
AUSTIN | AUSTMPVPN1 | MOBILE | 400 | 400 | 800 | 19 |
AUSTIN | AUSTPPQAVPN1 | PORTAL-QA | 100 | 100 | 150 | 4 |
AUSTIN | AUSTPPVPN1 | PORTAL | 550 | 500 | 1000 | 355 |
AUSTIN | AUSTPPVPN3 | PORTAL | 150 | 150 | 500 | 30 |
CHINA | AMCDPVPN1 | DESKTOP | 1700 | 1700 | 2000 | 993 |
CHINA | AMCMPVPN1 | MOBILE | 100 | 100 | 300 | 7 |
How do you know that the devices are part of the same cluster? Is it specific to just AUSTDPVPN1 and AUSTDPVPN2?
It's mentioned by network team, that these are cluster device and any one device can be used for given time so if AUSTDPVPN1 is inactive user will use AUSTDPVPN2 license.
So how would identify from the events which devices are part of the same cluster (apart from hardcoding the names)?
yes we have to hardcode that.
AUSTDPVPN1 and AUSTDPVPN2 is one cluster
AUSTMPVPN1 and AUSTMPVPN2 is another cluster
AUSTPPVPN1 and AUSTPPVPN2 is another cluster
CURRENT USER QUERY:
index="pulse_secure_index" STS20641
| rex field=host (?<device>\w+).*
| rex field=msg "STS20641: Number of concurrent users logged in to the device: (?<currUser>\d+)"
| eval device=upper(device)
| stats max(currUser) BY device
OUTPUT:
AMCDPVPN1 | 993 |
AMCMPVPN1 | 7 |
AMEDPVPN1 | 727 |
AMEMPVPN1 | 3 |
AMILDPVPN1 | 130 |
AMILMPVPN1 | 126 |
AMINDDPVPN1 | 2110 |
AMINDMPVPN1 | 1791 |
AMJDPVPN1 | 561 |
AMJMPVPN1 | 1 |
AMKDPVPN1 | 586 |
AMKMPVPN1 | 6 |
AMSEAMPVPN1 | 12 |
AMTDPVPN1 | 411 |
AMTMPVPN1 | 192 |
AUSTDPVPN1 | 0 |
AUSTDPVPN2 | 2867 |
AUSTPPVPN1 | 355 |
AUSTPPVPN2 | 484 |
AUSTPPVPN3 | 30 |
SCLADPVPN1 | 3315 |
SCLAMPVPN1 | 0 |
below mentioned is query output
AUSTIN | AUSTDPVPN1 | DESKTOP | 4000 | 4000 | 6000 | 0 |
AUSTIN | AUSTMPVPN1 | MOBILE | 400 | 400 | 800 | 19 |
AUSTIN | AUSTPPQAVPN1 | PORTAL-QA | 100 | 100 | 150 | 4 |
AUSTIN | AUSTPPVPN1 | PORTAL | 550 | 500 | 1000 | 355 |
AUSTIN | AUSTPPVPN3 | PORTAL | 150 | 150 | 500 | 30 |
CHINA | AMCDPVPN1 | DESKTOP | 1700 | 1700 | 2000 | 993 |
CHINA | AMCMPVPN1 | MOBILE | 100 | 100 | 300 | 7 |
Thanks for the response. it really helps me.
I have another query related to that i am creating inner join of two query
query:
index="pulse_secure_index" sourcetype="pulse:connectsecure" LIC30503 "Concurrent Users"
| rex field=msg "\w+: \w+ (?<leased>\d+) [\w+\s]+'[\w+\s]+' \w+ (?<device>\w+) - \w+ (?<resLic>\d+) \w+ (?<maxLic>\d+) \w+ \w+ (?<incQuan>\d+)"
| join device
[| inputlookup LIC-DEVICE_MAP.csv
| eval device=DEVICE]
| dedup device
| table LOCATION device SERVICE leased resLic maxLic
| join type=inner device
[ search index="pulse_secure_index" STS20641
| rex field=host (?<device>\w+).*
| rex field=msg "STS20641: Number of concurrent users logged in to the device: (?<currUser>\d+)"
| eval device=upper(device)
| stats max(currUser) BY device]
| sort device
| eval leased = if(in(device,"AUSTDPVPN1","AUSTDPVPN2","AUSTMPVPN1","AUSTMPVPN2","AUSTPPVPN1","AUSTPPVPN2"),(2*leased),leased)
| eval resLic = if(in(device,"AUSTDPVPN1","AUSTDPVPN2","AUSTMPVPN1","AUSTMPVPN2","AUSTPPVPN1","AUSTPPVPN2"),(2*resLic),resLic)
| eval maxLic = if(in(device,"AUSTDPVPN1","AUSTDPVPN2","AUSTMPVPN1","AUSTMPVPN2","AUSTPPVPN1","AUSTPPVPN2"),(2*maxLic),maxLic)
| rename LOCATION AS Region device AS Device SERVICE AS Service leased AS "Leased License" resLic AS "Reserved License" maxLic AS "Maximum License" max(currUser) AS "Current Users"