5

I am very new to Splunk and basically been dropped in the deep end!! also very new to language so any help and tips on the below would be great.

The out come i am trying to get is to join the queries and get Username, ID and the amount of logins.

The queries are from diff source, sourcetype and host.

Query 1 is Username and ID and Query 2 is Username and Count of logins.

Query 1: userName="" entityNumber="" | eval userName=upper(userName) | dedup userName, entityNumber | rename userName as User | table User, entityNumber

Query 2: "Successfully logged in." | rex field=_raw "User[\":](?[^\"IP])"| eval User=upper(User) | Table User | stats count by User

Thanks in advance for your help. J

James
  • 51
  • 1
  • 1
  • 3

2 Answers2

8

Like skoelpin said, I would suggest you to use the join command :

myQuery1 | join commonField [search myQuery2]

In your situation, this would lead to something like :

userName="" entityNumber="" | eval userName=upper(userName) | dedup userName, entityNumber | rename userName as User | table User, entityNumber 
| join User 
[search "Successfully logged in." | rex field=_raw "User[\":](?[^\"IP])"| eval User=upper(User) | Table User | stats count by User]

Be aware that your query might be slow, and that you should optimize your subqueries (by specifying an index, like skoelpin proposed).

Akah
  • 1,890
  • 20
  • 28
  • Which query is executed first in this join? I want limit the number of rows as it is time-consuming. Shall i add `head 50` in the main or [sub query] ? – Kanagavelu Sugumar Feb 20 '23 at 10:02
1

Try this, it joins on User.. You should also specify index and sourcetypes in your searches

userName="" entityNumber="" | eval userName=upper(userName) | dedup userName, entityNumber | rename userName as User | table User, entityNumber | join User [ | search "Successfully logged in." | rex field=_raw "User\":"| eval User=upper(User) | table User | stats count by User]

skoelpin
  • 212
  • 1
  • 5
  • Hey thanks for this but it still seems not to work and get error message when trying the above!! – James Sep 12 '18 at 15:06