Dashboards & Visualizations

display results based on a condition

kranthimutyala
Path Finder

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:

UserUser_CountryTarget_VPN
chirsparIndiavpn_dub
chirsparIndiavpn_bom
chirsparIndiavpn_sin
sbalaIndiavpn_sin
sbalaIndiavpn_phx
jeffpIndiavpn_fra
jeffpIndiavpn_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*

 

 

 

 

Labels (4)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

You could try something like this

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

View solution in original post

0 Karma

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

kranthimutyala
Path Finder

@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 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You could try something like this

| eval connectedToBom = if(Target_VPN="vpn_bom_a",1,if(Target_VPN="vpn_bom_b",1,0))
0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...