Splunk Search

How to create a single query that can show Internal AND External users all in one table.

mztopp
Explorer

All users are located under POP_Address. If the POP_Address = 192.168.* or 172.16.*, etc, we consider them to be internal, if not, external. So how can I create a table that can show both: POP_Address=192.168.* (Internal) AND POP_Address!=192.168.* (External) and output the User_Name and a count of how many times this user connected?

Example Output:

User_Name           Internal/External               Count

 

Or something to this effect. In a nutshell, I just want to know how many times each user is connecting and whether it is an internal or external connection, all in one table using just POP_Address. Thanks!

Labels (5)
0 Karma
1 Solution

PradReddy
Path Finder

Hi mztopp,

Please check if this helps for alert

Query:

| makeresults
| eval User_Name="a",POP_Address="192.168.0.1"
| append
[| makeresults
| eval User_Name="a",POP_Address="172.16.0.1"]
| append
[| makeresults
| eval User_Name="b",POP_Address="193.168.0.1"]
| append
[| makeresults
| eval User_Name="c",POP_Address="172.16.0.1"]
| append
[| makeresults
| eval User_Name="b",POP_Address="194.168.0.1"]
| append
[| makeresults
| eval User_Name="a",POP_Address="194.168.0.1"]
| fields _time User_Name POP_Address
| eval Connection_Type=case(LIKE(POP_Address,"192.168%"),"Internal",LIKE(POP_Address,"172.16%"),"Internal",0=0,"External")
| stats sum(eval(Connection_Type="Internal")) as Internal sum(eval(Connection_Type="External")) as External count AS Total by User_Name
| fillnull value=0 Internal,External

Output:

Capture.PNG

Once you have defined condition when this alert needs to be triggered please user "WHERE" command

Example:

| where External>1

Alert gets triggered including users when External connections are greater than 1 like this

Capture.PNG

Thanks,
Pradeep Reddy

View solution in original post

0 Karma

PradReddy
Path Finder

Hi mztopp,

Please check if this helps 

Query:

| makeresults
| eval User_Name="a",POP_Address="192.168.0.1"
| append
[| makeresults
| eval User_Name="a",POP_Address="172.16.0.1"]
| append
[| makeresults
| eval User_Name="b",POP_Address="193.168.0.1"]
| append
[| makeresults
| eval User_Name="c",POP_Address="172.16.0.1"]
| append
[| makeresults
| eval User_Name="b",POP_Address="194.168.0.1"]
| append
[| makeresults
| eval User_Name="a",POP_Address="194.168.0.1"]
| fields _time User_Name POP_Address
| eval "Internal/External"=case(LIKE(POP_Address,"192.168%"),"Internal",LIKE(POP_Address,"172.16%"),"Internal",0=0,"External")
| stats count by User_Name "Internal/External"

Tabular Output:

Capture.PNG


Thanks,
Pradeep Reddy

0 Karma

mztopp
Explorer

AND just to add, if it's possible, if I can set an alert on this that can show the TOTAL of internal and external connections?

0 Karma

PradReddy
Path Finder

Hi mztopp,

Please check if this helps for alert

Query:

| makeresults
| eval User_Name="a",POP_Address="192.168.0.1"
| append
[| makeresults
| eval User_Name="a",POP_Address="172.16.0.1"]
| append
[| makeresults
| eval User_Name="b",POP_Address="193.168.0.1"]
| append
[| makeresults
| eval User_Name="c",POP_Address="172.16.0.1"]
| append
[| makeresults
| eval User_Name="b",POP_Address="194.168.0.1"]
| append
[| makeresults
| eval User_Name="a",POP_Address="194.168.0.1"]
| fields _time User_Name POP_Address
| eval Connection_Type=case(LIKE(POP_Address,"192.168%"),"Internal",LIKE(POP_Address,"172.16%"),"Internal",0=0,"External")
| stats sum(eval(Connection_Type="Internal")) as Internal sum(eval(Connection_Type="External")) as External count AS Total by User_Name
| fillnull value=0 Internal,External

Output:

Capture.PNG

Once you have defined condition when this alert needs to be triggered please user "WHERE" command

Example:

| where External>1

Alert gets triggered including users when External connections are greater than 1 like this

Capture.PNG

Thanks,
Pradeep Reddy

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...