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
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
@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"
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
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
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.
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
try where isnull(status)
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.