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

[mztopp]

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!

[PradReddy]

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:

Thanks,
Pradeep Reddy

[mztopp]

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?

[PradReddy · Accepted solution]

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:

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

Thanks,
Pradeep Reddy