Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SharePoint and Onedrive Workbook Parameter UserID Lookup Fix #4245

Closed
benatsb opened this issue Feb 22, 2022 · 4 comments · Fixed by #4267
Closed

SharePoint and Onedrive Workbook Parameter UserID Lookup Fix #4245

benatsb opened this issue Feb 22, 2022 · 4 comments · Fixed by #4267

Comments

@benatsb
Copy link

benatsb commented Feb 22, 2022

Describe the bug
Link to workbook:
https://github.com/Azure/Azure-Sentinel/blob/master/Workbooks/SharePointAndOneDrive.json

The "SharePoint and OneDrive workbook" parameter "Users" does not work when "All" operations are selected.

It appears the intended functionality is to perform a unique user ID lookup based on the operations selected via dropdown. However, when the option "all" is selected for operations the only option available for Users is "all". This is not desirable when you want to filter on a specific user ID when using "all" operations.

The KQL statement for the Users list is looking for a dynamic/array in variable "operations", but if the operations drop down is selected as "all" there are no results returned as the queries throughout the workbook use a "where all or checkbox items" logic and label for the parameter. This means the Users lookup is matching on a null variable lookup from "operations" and no user IDs are returned.

The solution I used was to check the Operations parameter for "all" and if so return a list of all users IDs.

Link to the specific line:

"query": "OfficeActivity\r\n| where OfficeWorkload in ('OneDrive', 'SharePoint')\r\n| where Operation in ({Operations})\r\n| summarize Count = count() by UserId\r\n| order by Count desc, UserId asc\r\n| project Value = UserId, Label = strcat(UserId, ' - ', Count)",

To Reproduce
Steps to reproduce the behavior:

  1. The workbook in your environment.
  2. Click a time range with sufficient data.
  3. Select "all" in Operations.
  4. Select the drop down for Users. You will only have "all" as an option for the users.
  5. Edit the workbook paste in the JSON line below under the parameter section for "users".
  6. Repeat step 3, and on step 4 you can select from any user id.

Expected behavior
The expected behavior is to be able to filter by user ID even when "all" operations are selected.

Screenshots
don't have any

Desktop (please complete the following information):

  • OS: Windows
  • Browser: Edge
  • Version: ?

Additional context

Solution:

I have tested the below and used the addition of where '{Operations:lable}'=='All' to get a populated user ID list even when the operations parameter is "all".

KQL expanded:

OfficeActivity
| where OfficeWorkload in ('OneDrive', 'SharePoint')
| where '{Operations:lable}'=='All' or Operation in ({Operations})
| summarize Count = count() by UserId
| order by Count desc, UserId asc
| project Value = UserId, Label = strcat(UserId, ' - ', Count)

JSON line:

OfficeActivity\r\n| where OfficeWorkload in ('OneDrive', 'SharePoint')\r\n| where '{Operations:lable}'=='All' or Operation in ({Operations})\r\n| summarize Count = count() by UserId\r\n| order by Count desc, UserId asc\r\n| project Value = UserId, Label = strcat(UserId, ' - ', Count)
@github-actions
Copy link
Contributor

Thank you for submitting an Issue to the Azure Sentinel GitHub repo! You should expect an initial response to your Issue from the team within 5 business days. Note that this response may be delayed during holiday periods. For urgent, production-affecting issues please raise a support ticket via the Azure Portal.

@benatsb
Copy link
Author

benatsb commented Feb 22, 2022

Related, I do not know why throughout the workbook this is used "Operations:lable" instead of "Operations:label". All the queries have the "lable" typo.

This workbook is otherwise un-editing or customized apart from the users parameter fix.

@samikroy
Copy link
Contributor

benatsb

@benatsb - Could you please try the above version.

And the possible reason is earlier workbook has "Operations:lable" as a parameter but is updated as "Operations:label" from a product standpoint.

However both the keywords works.

image

v-rucdu added a commit that referenced this issue Mar 9, 2022
Updated SharePointAndOneDrive.json to fix #4245
@github-actions
Copy link
Contributor

github-actions bot commented Mar 9, 2022

Thank you for submitting an Issue to the Azure Sentinel GitHub repo! You should expect an initial response to your Issue from the team within 5 business days. Note that this response may be delayed during holiday periods. For urgent, production-affecting issues please raise a support ticket via the Azure Portal.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants