topic Re: JOIN to list user per DeviceId in Splunk Search

JOIN to list user per DeviceId

davidcraven02 — Tue, 15 May 2018 10:40:20 GMT

Search is trying to show all users within the companyOu that have Mobile Iron setup (Status=Allowed) and those that do not (Mobile Iron not setup)

The below search is only showing one user listed but some users have more than one DeviceId configured. I can't work out why all DeviceId's are not showing for a user.

index=ad source=aduserscan 
| table samAccountName, companyOu, displayName 
| search samAccountName=*_cp companyOu=dacp.com 
| rename samAccountName as MailboxId 
| join type=left MailboxId 
    [ search index=msexchange source=otl_mobileiron MailboxId=*_cp 
    | dedup DeviceId 
    | search Retired="false" ] 
| rename companyOu as Company, MailboxId as "User ID", DeviceFriendlyName as Model, displayName as "Display Name", SyncStatus as Status 
| fillnull value="Mobile Iron not setup" Status 
| table Company, "User ID", "Display Name", "Status" DeviceId
| sort Company asc

Re: JOIN to list user per DeviceId

mayurr98 — Tue, 15 May 2018 12:39:20 GMT

can you try this?

index=ad source=aduserscan samAccountName=*_cp companyOu=dacp.com 
| stats count by samAccountName, companyOu, displayName 
| rename samAccountName as MailboxId 
| join type=left MailboxId 
    [ search index=msexchange source=otl_mobileiron MailboxId=*_cp Retired="false" 
    | dedup MailboxId DeviceId] 
| rename companyOu as Company, MailboxId as "User ID", DeviceFriendlyName as Model, displayName as "Display Name", SyncStatus as Status 
| fillnull value="Mobile Iron not setup" Status 
| table Company, "User ID", "Display Name", "Status" DeviceId 
| sort Company asc

Re: JOIN to list user per DeviceId

davidcraven02 — Tue, 15 May 2018 13:45:42 GMT

Thanks but this is gives the same number of results only a more efficient way to run it.

Re: JOIN to list user per DeviceId

niketn — Tue, 15 May 2018 13:59:54 GMT

@davidcraven02, Can you try the following:

index=msexchange source=otl_mobileiron MailboxId=*_cp Retired="false" 
| dedup MailboxId DeviceId 
| rename MailboxId as samAccountName 
| join type=left samAccountName 
    [ search index=ad source=aduserscan samAccountName=*_cp companyOu=dacp.com 
    | dedup samAccountName, companyOu, displayName ] 
| rename companyOu as Company, samAccountName as "User ID", DeviceFriendlyName as Model, displayName as "Display Name", SyncStatus as Status 
| fillnull value="Mobile Iron not setup" Status 
| table Company, "User ID", "Display Name", "Status" DeviceId 
| sort Company asc

If this does not work, can you share example of which record from specific index does not show up? Mock/Anonymize sensitive data.

Re: JOIN to list user per DeviceId

davidcraven02 — Tue, 15 May 2018 14:27:38 GMT

Thanks but this only returned 60 results whereas my original search returns 146 results.

I cant upload a screenshot but this search returned all users who have MobileIron (listing several DeviceId's per user) but it did not include those users who don't have it. The source aduserscan contains all users within the companyOu.

Re: JOIN to list user per DeviceId

niketn — Tue, 15 May 2018 14:56:04 GMT

@davidcraven02 without having a look at some data values from events for the two sources, it would be difficult to come up with exact query, but can you try a different approach to check whether all Device IDs for various MailBoxIds show up

( index=msexchange source=otl_mobileiron MailboxId=*_cp Retired="false" ) OR (index=ad source=aduserscan samAccountName=*_cp companyOu=dacp.com)
| rename samAccountName as MailboxId 
| stats count values(DeviceId) as DeviceId by MailboxId

Re: JOIN to list user per DeviceId

davidcraven02 — Tue, 29 Sep 2020 19:32:42 GMT

This returned 146 results which is the total number of users for this company. However each MailboxId has a count of 1 and no DeviceId is displayed.

MailboxId count DeviceId
Haver_cp 1

dadams_cp 1

Forindex=msexchange source=otl_mobileiron MailboxId=*_cp Retired="false" the user field is called MailboxId not samAccountName.

And the user field for index=ad source=aduserscanis called samAccountName

( index=msexchange source=otl_mobileiron MailboxId=*_cp Retired="false" )
      | stats count values(DeviceId) as DeviceId by MailboxId

When I search the above I get the below data

MailboxId   count   DeviceId
abon_cp          1         nine54011c5277ab
acavez_cp   1         ninebe03364cd694
acori_cp            1        iqj4udgc1h1q31j8vik9vceflo
akura_cp    1        rbmm3dea9d45v2kbt1rs36a7ok
aruma_cp    2        e53fd31687aa1ae3
                                     ninea8a1b3156df0

Re: JOIN to list user per DeviceId

niketn — Tue, 15 May 2018 16:17:44 GMT

@davidcraven02, give the following a try, see whether matches for MailboxIds are coming from both sources otl_mobileiron and aduserscan or not.

( index=msexchange source=otl_mobileiron MailboxId=*_cp Retired="false" ) OR (index=ad source=aduserscan samAccountName=*_cp companyOu=dacp.com)
 | eval MailboxId=coalesce(MailboxId,samAccountName)
 | stats count values(source) as source  values(DeviceId) as DeviceId by MailboxId