Require Splunk query to assign field value to another field as value

[Abhineet]

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

[ITWhisperer]

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

[Abhineet]

I have highlighted currentuser count for AUSTDPVPN1 which is sum of both cluster device current user login count. 

We want output like below mentioned.

Region	Device	Service	Leased License	Reserved License	Maximum License	Current Users
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

[ITWhisperer]

How do you know that the devices are part of the same cluster? Is it specific to just AUSTDPVPN1 and AUSTDPVPN2?

[Abhineet]

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.

[ITWhisperer]

So how would identify from the events which devices are part of the same cluster (apart from hardcoding the names)?

[Abhineet]

yes we have to hardcode that.

AUSTDPVPN1 and AUSTDPVPN2 is one cluster

AUSTMPVPN1 and AUSTMPVPN2 is another cluster

AUSTPPVPN1 and AUSTPPVPN2 is another cluster

[Abhineet]

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:

device	max(currUser)
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

[Abhineet]

below mentioned is query output

Region	Device	Service	Leased License	Reserved License	Maximum License	Current Users
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

[Abhineet]

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"