https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495660#M138175 <P>Ah I see, its more like "from this list of users, who has logged in?"<BR /> In that case the lookup is still a viable solution - but maybe you can use a field name like 'reviewLogin' instead of 'isAuthorised'</P> Fri, 24 Jan 2020 17:34:51 GMT nickhills 2020-01-24T17:34:51Z https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495656#M138171 <P>Hey folks. Help!</P> <P>I have two indexes. </P> <UL> <LI>Index 1 - Contains an authoritative list of AWSconfig accounts it.</LI> <LI>index 2 - Contains cloudtrail data - logins by account</LI> </UL> <P>I want to list accounts that haven’t been logged into using my AWSconfig account list (index 1) as Index 2 (cloudtrail) only has logs of what has been logged into at some point...</P> <P>I was going to use a subsearch to get a list of unique accounts from index 1 and then pass that into a search against cloudtrail (index 2) - but was wondering if I could use stats instead (cause y’know subsearches have limitations...)</P> <P>Thoughts? </P> <P>Thanks!</P> Fri, 24 Jan 2020 11:15:18 GMT https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495656#M138171 RocIngersol 2020-01-24T11:15:18Z https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495657#M138172 <P>Personally, I might use a lookup for this task.</P> <P>I would create a scheduled search to build a list of authorisedAWSAccounts.csv from index1 and run that hourly/daily/weekly depending on your needs.</P> <P>Then you can use that lookup in your search against data in index2 to add an 'isAuthorised" field.</P> <P>Scheduled Lookup Builder<BR /> <CODE>index=index1 yourSearchFilter|eval isAuthorised=true|table userName arn isAuthorised|outputlookup authorisedAWSAccounts.csv</CODE></P> <P>Search index2<BR /> <CODE>index=index2 yourSearchFilter|lookup authorisedAWSAccounts.csv [userName|arn] OUTPUT isAuthorised|search isAuthorised!=true</CODE></P> Fri, 24 Jan 2020 12:41:43 GMT https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495657#M138172 nickhills 2020-01-24T12:41:43Z https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495658#M138173 <P>What are the event counts in both indexes (based on the search time range you'll be using)?</P> Fri, 24 Jan 2020 14:32:20 GMT https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495658#M138173 somesoni2 2020-01-24T14:32:20Z https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495659#M138174 <P>I’m not looking for isAuthed per se more ‘from the a deduped list of master accounts in index1, have they been found logging in determined by the Cloudtrail logs in index2.</P> <P>Lookup could work but.. will try and report back. Thx!</P> Fri, 24 Jan 2020 17:31:18 GMT https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495659#M138174 RocIngersol 2020-01-24T17:31:18Z https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495660#M138175 <P>Ah I see, its more like "from this list of users, who has logged in?"<BR /> In that case the lookup is still a viable solution - but maybe you can use a field name like 'reviewLogin' instead of 'isAuthorised'</P> Fri, 24 Jan 2020 17:34:51 GMT https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495660#M138175 nickhills 2020-01-24T17:34:51Z https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495661#M138176 <P>Yeah. Sounds good!</P> Fri, 24 Jan 2020 19:11:55 GMT https://community.splunk.com/t5/Splunk-Search/Subsearch-or-let-stats-sort-it-out/m-p/495661#M138176 RocIngersol 2020-01-24T19:11:55Z