Splunk Query

[rahul2gupta]

Hi @gcusello ,

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

[venkatasri]

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.

[rahul2gupta]

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.

[gcusello]

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

[rahul2gupta]

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