topic Re: Splunk Query in Splunk Search

Splunk Query

rahul2gupta — Tue, 22 Jun 2021 09:06:28 GMT

Can you please help me to design a Splunk query to show whether a particular user has been coming into the office at Mascot (and/ or Erskine Park), or otherwise working from home (or elsewhere).

I'd like to structure the results to show a table listing logon time and IP address - like this:

 Workstation    Last Login          User

 10.11.12.13    15-11-01 10:00:00   user1

 10.12.13.14    15-11-01 15:34:02

Regards,

Rahul

Re: Splunk Query

gcusello — Tue, 22 Jun 2021 09:21:24 GMT

Hi @rahul2gupta,

I'd like to help you, but youshould explain how can we define the location of a user, the only way I can see (tell me if there's something helpful else ) is the IP address:

e.g.

if IP address is 10.11.*.* the location is Mascot

if IP address is 10.12.*.* the location is Erskine Park,

otherwise is outside

can this condition to be correct?

if this is the condition to define the user location, you can use a simple if or case condition in an eval command, something like this:

your_search | eval location=if(substr(Workstation,1,5)="10.11","Mascot",if(substr(Workstation,1,5)="10.12","Erskine Park","Outside")), "Last Login"=strftime(_time,"%Y-%m-%d %H:%M:%S") | table Workstation "Last Login" User

Ciao.

Giuseppe

Re: Splunk Query

rahul2gupta — Tue, 29 Jun 2021 02:08:13 GMT

Hi @gcusello ,

You mentioned Your_search in the query that you provided but I'm not sure what to use in my search as user has just asked to show a user’s working location. Can you please me on this.

I used index = security sourcetype=history* but it didn't helped me.

Regards,

Rahul Gupta

Re: Splunk Query

venkatasri — Tue, 29 Jun 2021 02:17:40 GMT

Hi @rahul2gupta

As @gcusello mentioned there should be some data in your logs associated to User being already logged in from a place. You have to dig through logs under the index you mentioned to find the location/address/IP from the user logged in. Alternatively you can provide the sample events how they looks like as we do not have insights into your data.

The data could be sensitive to your company confidential then you have to mask/alter it before you share here for your confidentiality.

Re: Splunk Query

rahul2gupta — Tue, 29 Jun 2021 06:53:45 GMT

Hi @venkatasri ,

Just found the logs associated to User.

index=main sourcetype="activedirectory". I want to add IP Address column as well but couldn't find in interesting fields. please help us to do this.

We can only fetch last 14 days events but I checked the configuration of index=main and the retention period was 6 months . Also, please help me to understand this.