Splunk Search

Merging to searches on specific field

bluedragon
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

vidhyaArumalla
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...