Splunk Search
Highlighted

Merging to searches on specific field

New Member

Hey guys,

i can't figure out with my own google searches and forum searches how to merge two searches on a specific field.

So i've got the following by now:

 index="at0001_saas_centrify" extracted_EventType="Cloud.Core.Login"

this will give me kinda this

normalizeduser               |       _time         
user1                        |   01.01.2017, 08:25:20
user1                        |   02.01.2017, 07:20:00
user2                        |   02.03.2017, 04:24:50
user3                        |   04.05.2017, 09:21:22
user5                        |   01.05.2017, 06:27:25
user5                        |   02.05.2017, 08:00:20
user1                        |   04.05.2017, 09:21:22

and this one:

index="at0001_saas_centrify" extracted_EventType="Cloud.Saas.Application.AppLaunch" AND ( ApplicationID="6e6a5ea1-0886-4fe1-acbb-c01496ed20ff" OR ApplicationID="23af5793-e835-4d82-96f9-67c8cf51e0db" )

which will give me this :


applicationid | normalizeduser | _time

6e6a5ea1-0886-4fe1-acbb-c01496ed20ff | user1 | 01.01.2017, 08:25:20
6e6a5ea1-0886-4fe1-acbb-c01496ed20ff | user1 | 02.01.2017, 07:20:00
6e6a5ea1-0886-4fe1-acbb-c01496ed20ff | user2 | 02.03.2017, 04:24:50
6e6a5ea1-0886-4fe1-acbb-c01496ed20ff | user3 | 04.05.2017, 09:21:22
23af5793-e835-4d82-96f9-67c8cf51e0db | user5 | 01.05.2017, 06:27:25
23af5793-e835-4d82-96f9-67c8cf51e0db | user5 | 02.05.2017, 08:00:20
23af5793-e835-4d82-96f9-67c8cf51e0db | user1 | 04.05.2017, 09:21:22

So the thing is, if someone login, it'll the feed the first index for login information. And it'll feed the second index with the application dependent information. Both will get the identical timestamp.
So what we want is, to know which user successfully launched the dependent Applications through centrify login.
We cannot use the second index only because they might not have logged in in between and just launched something or did something else.
There is no application stored in the first index and no login information in the second index.

And if the ApplicationID is A , then display as "AppA" and if ApplicationID is B then display as "AppB"

So how can i merge them to achieve this.


User | Application | Timestamps
-------------------------------+-----------------------------+--------------------------------
user1 | AppA | 01.01.2017, 08:25:20
| | 02.01.2017, 07:20:00
-------------------------------+-----------------------------+--------------------------------
user1 | AppB | 04.05.2017, 09:21:22
-------------------------------+-----------------------------+--------------------------------
user2 | AppA | 02.03.2017, 04:24:50
-------------------------------+-----------------------------+--------------------------------
user3 | AppA | 04.05.2017, 09:21:22
-------------------------------+-----------------------------+--------------------------------
user5 | AppB | 01.05.2017, 06:27:25
| | 02.05.2017, 08:00:20
-------------------------------+-----------------------------+--------------------------------
user2 | AppA | 02.03.2017, 04:24:50
-------------------------------+-----------------------------+--------------------------------

I've googled around and searched through many thread here in the forum, but nothing worked for me. Am i looking for a join or appcols or whatever, i dont know...

Would be nice, if you can help somehow.

Kind regards,
Michael

0 Karma
Highlighted

Re: Merging to searches on specific field

Path Finder

Hi Micheal,

Line 15 is not possible as it does not exist in the input data. Apart from that, the required table can be obtained using the search below:

index="at0001_saas_centrify" extracted_EventType="Cloud.Saas.Application.AppLaunch" AND ( ApplicationID="6e6a5ea1-0886-4fe1-acbb-c01496ed20ff" OR ApplicationID="23af5793-e835-4d82-96f9-67c8cf51e0db" ) 
| join type=inner _time,normalizeduser 
    [ search index="at0001_saas_centrify" extracted_EventType="Cloud.Core.Login"] 
| stats values(_time) as Timestamps by normalizeduser,ApplicationID 
| eval ApplicationID="App".ApplicationID 
| rename ApplicationID as Application 
| rename normalizeduser as User

as shown above if ApplicationID is and if you want it to be AppA then use

| eval ApplicationID="App".ApplicationID
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.