Splunk Search

Join saved search twice

Engager

Hello,

I'm trying to use a saved, scheduled and accelerated report to produce multiple results and compare the totals.

Background
We log customer Login events with a ton of data. Browser, OS, Username, Customer, UniqueID, timestamp, etc. We are trying to communicate to users who login using IE that we will no longer support the browser going forward. I have created a saved search of "Login Success (Raw)" that has a stats table with the fields:

OBID, District, School, ua_family, ua_os_family, Logins

What I'm trying to do
I want to use these results to see how many logins there are with IE for a customer and compare those to the total logins of all browsers for that customer on the same row all using the same search results.

The resulting table would look like this:

OBID, District, School, Logins, IELogins, %

Here's what I tried but doesn't work

|savedsearch "Login Success (Raw)" | stats count as Logins by OBID District_Name STATE ua_family | join [|savedsearch "Login Success (Raw)" | search ua_family=IE |stats count as IELogins by OBID District_Name STATE ua_family]| rename percent as "%" | eval percent=round((100*IELogins)/Logins,0) | table OBID, District_Name, STATE, IELogins, Logins, %

Currently gives me the error of "Error in 'SearchParser': Found circular dependency when expanding savedsearch=Login Success (Raw)"

Any ideas would be greatly appreciated.

0 Karma

Revered Legend

Try like this

| savedsearch "Login Success (Raw)" | eval IELogins=if(ua_family="IE",1,0)
| stats count as Logins sum(IELogins) as IELogins by OBID District_Name STATE
| eval percent=round((100*IELogins)/Logins,0) | rename percent as "%"
| table OBID, District_Name, STATE, IELogins, Logins, %

Engager

I tried that but the value of the Logins must be passed if True. Then the Logins Column must be the Total number of Logins (including IE)

So I had to make a small tweak:

| savedsearch "Login Success (Raw)" 
 | eval IELogins=if(ua_family="IE",Logins,0) 
 | stats sum(Logins) as TotalLogins sum(IELogins) as IELogins by OBID District_Name STATE
 | eval percent=round((100*IELogins)/TotalLogins,0) | rename percent as "%"
 | table OBID, District_Name, STATE, IELogins, TotalLogins, % 
 | search TotalLogins>100 IELogins>0

Only problem I have now is I don't want to see rows where IELogins=0

EDIT: I've figured that out. the code above has been changed to reflect this.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!