Search to display for list of applications to which users never logged in

[rvenkata]

Hello All,

I would like to list down the applications where users have never logged in. I have a input.csv file with the list of applications and I was able to write a search to list down the applications by login count. Now I want to list the applications where users have logged in (Login Count=0). My query to list all the apps and with login count is as follows: 

sourcetype="aduit" success NOT AUTHN_ATTEMPT
| lookup app_lookup.csv Connection_ID as connectionid OUTPUT App_Name
| stats count(App_Name) as "Number of Successful Logins" by App_Name
| sort - "Number of Successful Logins"  

How can I get list of apps with 0 logins?

Thanks,
Rakesh Venkata

[rvenkata]

Hello @Ron_Naken @gkanapathy @lguinn2 

I see that you guys have responded to similar posts previously. Can you please shed your valuable inputs?

Thanks in advance. 

[hc_joycechen]

Try:

| inputlookup app_lookup.csv
| join type=left App_Name
[ search sourcetype="aduit" success NOT AUTHN_ATTEMPT
| lookup app_lookup.csv Connection_ID as connectionid OUTPUT App_Name
| stats count(App_Name) as "Number of Successful Logins" by App_Name
]
| where isnull("Number of Successful Logins")

[hc_joycechen]

app_lookup.csv  OR App_lookup.csv ??

It's different.

| inputlookup App_lookup.csv
| join type=left App_Name
[ search sourcetype="aduit" success NOT AUTHN_ATTEMPT
| lookup App_lookup.csv Connection_ID as connectionid OUTPUT App_Name
| stats count(App_Name) as "Number of Successful Logins" by App_Name
]
| where isnull("Number of Successful Logins")

[rvenkata]

Hello @hc_joycechen 

Thanks for your response. 

I tried your solution it is not yielding any results. I modified your suggestion to following:

| inputlookup App_lookup.csv

| join type=left Connection_ID

[ search sourcetype="aduit" tid success NOT AUTHN_ATTEMPT

| lookup App_lookup.csv Connection_ID AS connectionid OUTPUT App_Name

| stats count(App_Name) as LoginCount by App_Name

]

| where isnull(LoginCount)

So my csv has two columns Connection_ID & App_Name and connectionid is a field in my events. My above solution results all apps in the csv but not apps which users never logged in. Following is a sample of my csv.

Connection_ID	App_Name
app1.domain.com	App1
app2.domain.com	App2

Can you please help me our here?

Thanks in advance.