display results based on a condition

[kranthimutyala]

I have a query which shows the details of Users and VPN host which they are connected.For suppose if a user has connected to vpn_bom in the 24 hours I don't want to see his details in the results.I want to display the results of all the users who haven't connected to vpn_bom in the last 24hrs at least once.

Thank you for the help as always.

These are the results im getting when i execute the above query , but I don't want to display chrispar details as he has connected to vpn_bom at least once.

I want only those people who have not connected to vpn_bom and connected to other vpns(sbala,jeffp in thi case)

Results:

User	User_Country	Target_VPN
chirspar	India	vpn_dub
chirspar	India	vpn_bom
chirspar	India	vpn_sin
sbala	India	vpn_sin
sbala	India	vpn_phx
jeffp	India	vpn_fra
jeffp	India	vpn_ash

Query:

index=vpn  Cisco_ASA_message_id=722051 OR Cisco_ASA_message_id=113019 NOT "AnyConnect-Parent"
| transaction user endswith="Duration:" keepevicted=true
| eval full_duration = duration_hour."h".duration_minute."m".duration_second."s"
| eval bytesMB=round(((bytes/1024)/1024),2), bytes_inMB=round(((bytes_in/1024)/1024),2), bytes_outMB=round(((bytes_out/1024)/1024),2)
| eval Start_time=strftime(_time,"%Y/%m/%d %H:%M:%S"), End_time=(strftime(_time + duration,"%Y/%m/%d %H:%M:%S")), Total_time=if(isnull(full_duration), Start_time." --> current session",Start_time." --> ".End_time)
| mvexpand src
| iplocation src | eval LocationIP=City.", ".Country
| stats values(host) as vpn_host values(Total_time) as "Session Time" values(src) as "PublicIP" values(LocationIP) as LocationIP values(assigned_ip) as "Assigned IP" values(reason) as "Termination Reason" values(bytesMB) as bytesMB values(bytes_inMB) as bytes_inMB values(bytes_outMB) as bytes_outMB values(full_duration) as Duration by _time, user|rename LocationIP as User_Location |eval temp=split(User_Location,",") | eval User_Country=mvindex(temp,1)| fields - temp
|rename user as User vpn_host as Target_VPN| table User User_Country Target_VPN |search User_Country=*India*

[ITWhisperer]

| eval connectedToBom = if(Target_VPN="vpn_bom",1,0)
| fillnull value=0 connectedToBom
| eventstats sum(connectedToBom) as BomConnects by User
| where BomConnects = 0

[kranthimutyala]

@ITWhisperer  Thanks for the answer.Sorry I missed to mention it early I have two different vpn_bom hosts one is vpn_bom_a and other is vpn_bom_a , how to use OR in eval if.I tried this but its not working as expected.

eval connectedToBom = if((Target_VPN="vpn_bom_a") OR (Target_VPN="vpn_bom_b"),1,0)

can you please correct the query.Thanks 

[ITWhisperer · Accepted solution]

You could try something like this

| eval connectedToBom = if(Target_VPN="vpn_bom_a",1,if(Target_VPN="vpn_bom_b",1,0))