topic Re: Can you help me build a query that shows users who have not connected to a VPN in the last 7 days? in Splunk Search

Can you help me build a query that shows users who have not connected to a VPN in the last 7 days?

bluecollar — Tue, 02 Apr 2019 16:15:59 GMT

New to Splunk and I am learning as much as I can. I am trying to build on a query I have that shows the users who have connected via VPN for the last 7 days; the use case goal is to show users that have NOT connected to the VPN within the last 7 days.

Here is the query that shows the users for the last 7 days:

index="gw_pfsense" authenticated | rex field=_raw "user \'(?<user>.*)\' auth" | stats latest(_time) as Time by user | eval Time=strftime(Time, "%m/%d/%Y %H:%M:%S") | dedup user | sort user | table user, Time

I believe the best way might be a comparison against a list of users not seen in this report. I'm not sure how to do this. Looking for some guidance or tips

thanks

Re: Can you help me build a query that shows users who have not connected to a VPN in the last 7 days?

lakshman239 — Tue, 02 Apr 2019 22:01:32 GMT

One way would be to have a list of users in a lookuptable, say vpn_users.csv with 2 columns - user, status. The search can then be modified to something like

 index="gw_pfsense" authenticated | rex field=_raw "user \'(?<user>.*)\' auth" |inputlookup vpn_users.csv user OUTPUT status | where isnull(status)

So, if you find users who haven't authenticated in the last 7 days, you would get isnull(status) to true. You can then update rest of your searches as per your needs

Re: Can you help me build a query that shows users who have not connected to a VPN in the last 7 days?

bluecollar — Tue, 29 Sep 2020 23:57:19 GMT

Thank you for the recommendation, I like that angle, however Im getting an error.. < Error in 'inputlookup' command: Invalid argument: 'user' >

Here is my transforms.conf entry
[vpn_users]
filename = vpn_users.csv

and my props.conf entry attempts..

[vpn_users]
INPUTLOOKUP OUTPUT status

tried this also

LOOKUP-vpn_users = my_lookup user OUTPUT status

Any further help is appreciated

Re: Can you help me build a query that shows users who have not connected to a VPN in the last 7 days?

lakshman239 — Wed, 30 Sep 2020 00:02:19 GMT

Your props.conf should have something like:

[your_sourcetype]
EXTRACT-user = "user \'(?.*)\' auth"

LOOKUP-matchVPNusers = vpn_users user OUTPUT status

Note: you need an EXTRACT or REPORT to extract 'user' which can be used as input for the lookup transforms, as you are using automatic lookup extractions. If you don't need automatic lookup, you need not define LOOKUP in the props.conf

Your search will then have index="gw_pfsense" authenticated , giving status field for matching records.

Re: Can you help me build a query that shows users who have not connected to a VPN in the last 7 days?

bluecollar — Wed, 30 Sep 2020 00:09:12 GMT

This now works like a champ, giving me users who have authenticated during the last 7 days. How would I capture the inverse of this, i.e. those who HAVE NOT authenticated during the last 7 days?

index="gw_pfsense" authenticated | rex field=_raw "user \'(?.*)\' auth" | lookup vpn_users.csv user OUTPUT status | where isnotnull(status)

Did not work, which I believe I understand, how would I get the inverse of the status against the list, i.e those that have not authenticated

Re: Can you help me build a query that shows users who have not connected to a VPN in the last 7 days?

lakshman239 — Fri, 12 Apr 2019 14:33:26 GMT

try where isnull(status)

Re: Can you help me build a query that shows users who have not connected to a VPN in the last 7 days?

bluecollar — Fri, 12 Apr 2019 15:03:09 GMT

The where isnull(status) gives me all of the users who have authenticated. I've verified this by comparing user list with logs. I am trying to find the inverse, those who would not have a status of null.

Re: Can you help me build a query that shows users who have not connected to a VPN in the last 7 days?

Vijeta — Fri, 12 Apr 2019 15:17:11 GMT

@bluecollar- Try below-

  |inputlookup vpn_users.csv | join type=outer user[|search index="gw_pfsense" authenticated | rex field=_raw "user \'(?<user>.*)\' auth" | dedup user|fields index  user]| where NOT index="gw_pfsense"