Collecting Intune Device Management data

[ghost]

Looking to ingest the Intune data for end user device compliance, which is available at https://graph.microsoft.com/v1.0/deviceManagement/managedDevices, so it's actually just an extension of what's already being collected by azure_collect.py.

Would it be preferable to extend this existing collector, or instead define a new data connector and name it "Azure Intune"? I suspect the latter, since existing SnowAlert users will have been through the connect process only on the current set of tables.

[sfc-gh-afedorov]

I can add this to the Azure Connector. Re: backwards incompatible connectors — we did break v1.9.0 AWS Collect with v1.9.1 in a similar situation (documented here), but I think that was a mistake. We've had code that upgrades schemas with whatever is missing (e.g. adding columns that are missing) so I think that sort of auto-migration is a better idea going forward, where possible.

That said, there's still a couple of things I want to test and fix about v1.9.2 before release, and if this is just about adding an extra endpoint / table, I think we can nudge the scope a little and that way we don't need to worry about folks having installed it just yet.

[ghost]

Sounds great, thanks Andrey. In case you want to pick apart the columns, here's an example of a response from that endpoint. For context, the main thing I'd be monitoring is the complianceState field having a value of 'compliant'. Grace periods for the device state are configured in Intune so it should be simple on the SnowAlert side.

{ "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#deviceManagement/managedDevices", "@odata.count": 1, "value": [ { "id": "1b6b5489-34b0-496f-b14a-bf445a70cb19", "userId": "172da1a0-6385-4f9c-bae7-85baae6a1f84", "deviceName": "DESKTOP-AEMHDIE", "managedDeviceOwnerType": "company", "enrolledDateTime": "2020-01-07T05:15:53Z", "lastSyncDateTime": "2020-01-12T23:34:57Z", "operatingSystem": "Windows", "complianceState": "compliant", "jailBroken": "Unknown", "managementAgent": "mdm", "osVersion": "10.0.18362.356", "easActivated": false, "easDeviceId": "", "easActivationDateTime": "0001-01-01T00:00:00Z", "azureADRegistered": true, "deviceEnrollmentType": "windowsAzureADJoin", "activationLockBypassCode": null, "emailAddress": "user@company.com", "azureADDeviceId": "a1bf2dc5-5e82-46af-ccd0-13fcce4b1d82", "deviceRegistrationState": "registered", "deviceCategoryDisplayName": "Unknown", "isSupervised": false, "exchangeLastSuccessfulSyncDateTime": "0001-01-01T00:00:00Z", "exchangeAccessState": "none", "exchangeAccessStateReason": "none", "remoteAssistanceSessionUrl": null, "remoteAssistanceSessionErrorDetails": null, "isEncrypted": true, "userPrincipalName": "jsmith", "model": "HP EliteBook 840 G6", "manufacturer": "HP", "imei": "", "complianceGracePeriodExpirationDateTime": "9999-12-31T23:59:59Z", "serialNumber": "5CG85503LDZ", "phoneNumber": "", "androidSecurityPatchLevel": "", "userDisplayName": "John Smith", "configurationManagerClientEnabledFeatures": null, "wiFiMacAddress": "4C1D962B91D2", "deviceHealthAttestationState": null, "subscriberCarrier": "", "meid": "", "totalStorageSpaceInBytes": 512103546880, "freeStorageSpaceInBytes": 451451813888, "managedDeviceName": "DESKTOP-AEMHDIE", "partnerReportedThreatState": "unknown", "deviceActionResults": [] } ] }

[sfc-gh-afedorov]

Yup, got them from docs. Is there an advantage to doing this while iterating over the users, i.e. hitting one of these over the others —

GET /users/{usersId}/managedDevices
GET /deviceManagement/managedDevices
GET /deviceManagement/detectedApps/{detectedAppId}/managedDevices

?

[ghost]

I tend to approach it in a device-oriented way, since I'm considering overall security of the business rather than a particular set of users or apps. At the end of the day, it's devices that are compliant or require remediation.

So I feel /deviceManagement/managedDevices is the most pertinent to a SIEM.

[sfc-gh-afedorov]

Makes sense — I'm not sure how users are assigned, but it's also possible that there's unassigned devices that would be there and won't be listed if you iterate over every user's device. Don't expect anything in the reverse.

I just pushed a commit that adds this, tested against a subscription without Intune enabled, with it enabled but without permissions, and with permissions, but no devices. If you could test it against your account, I would appreciate it. The new table should be —

CREATE OR REPLACE azure_collect_managed_devices COPY GRANTS (
    id STRING,
    userId STRING,
    deviceName STRING,
    managedDeviceOwnerType VARIANT,
    deviceActionResults VARIANT,
    enrolledDateTime TIMESTAMP_LTZ,
    lastSyncDateTime TIMESTAMP_LTZ,
    operatingSystem STRING,
    complianceState VARIANT,
    jailBroken STRING,
    managementAgent VARIANT,
    osVersion STRING,
    easActivated BOOLEAN,
    easDeviceId STRING,
    easActivationDateTime TIMESTAMP_LTZ,
    azureADRegistered BOOLEAN,
    deviceEnrollmentType VARIANT,
    activationLockBypassCode STRING,
    emailAddress STRING,
    azureADDeviceId STRING,
    deviceRegistrationState VARIANT,
    deviceCategoryDisplayName STRING,
    isSupervised BOOLEAN,
    exchangeLastSuccessfulSyncDateTime TIMESTAMP_LTZ,
    exchangeAccessState VARIANT,
    exchangeAccessStateReason VARIANT,
    remoteAssistanceSessionUrl STRING,
    remoteAssistanceSessionErrorDetails STRING,
    isEncrypted BOOLEAN,
    userPrincipalName STRING,
    model STRING,
    manufacturer STRING,
    imei STRING,
    complianceGracePeriodExpirationDateTime TIMESTAMP_LTZ,
    serialNumber STRING,
    phoneNumber STRING,
    androidSecurityPatchLevel STRING,
    userDisplayName STRING,
    configurationManagerClientEnabledFeatures VARIANT,
    wiFiMacAddress STRING,
    deviceHealthAttestationState VARIANT,
    subscriberCarrier STRING,
    meid STRING,
    totalStorageSpaceInBytes NUMBER,
    freeStorageSpaceInBytes NUMBER,
    managedDeviceName STRING,
    partnerReportedThreatState VARIANT
);

And the permission grants should look like —

^ in AD Blade > [your auditor app] > API Permissions

^ in App Blade > [your app] > Permissions

[ghost]

Great, will test it now

[ghost]

subscription_id is defined twice in subscriptions_locations which is breaking the table creation:
https://github.com/snowflakedb/SnowAlert/blob/v1.9.2/src/connectors/azure_collect.py#L92

[sfc-gh-afedorov]

yikes, ty, good catch.

[ghost]

Same for kid in vaults_keys:
https://github.com/snowflakedb/SnowAlert/blob/v1.9.2/src/connectors/azure_collect.py#L144

[sfc-gh-afedorov]

thanks. the first one should be vault_name. about to do a top-to-bottom run-through, as well.

[ghost]

SQL compilation error: Unsupported data type 'INT32'. :
https://github.com/snowflakedb/SnowAlert/blob/v1.9.2/src/connectors/azure_collect.py#L359

[sfc-gh-afedorov]

Just re-did a couple of the connections -- sorry for asking to test, that was naively optimistic of me. Should be all better now.

[ghost]

No trouble, i'll run again with the latest. In the meantime, the tables did get successfully created but got the following upon insert into that new managed_devices table:

SQL compilation error: error line 2 at position 47 invalid identifier 'USER_ID'

query on the Snowflake side (with actual values removed):

INSERT INTO data.azure_collect_managed_devices (id, user_id, device_name, managed_device_owner_type, enrolled_date_time, last_sync_date_time, operating_system, compliance_state, jail_broken, management_agent, os_version, eas_activated, eas_device_id, eas_activation_date_time, azure_a_d_registered, device_enrollment_type, activation_lock_bypass_code, email_address, azure_a_d_device_id, device_registration_state, device_category_display_name, is_supervised, exchange_last_successful_sync_date_time, exchange_access_state, exchange_access_state_reason, remote_assistance_session_url, remote_assistance_session_error_details, is_encrypted, user_principal_name, model, manufacturer, imei, compliance_grace_period_expiration_date_time, serial_number, phone_number, android_security_patch_level, user_display_name, configuration_manager_client_enabled_features, wi_fi_mac_address, device_health_attestation_state, subscriber_carrier, meid, total_storage_space_in_bytes, free_storage_space_in_bytes, managed_device_name, partner_reported_threat_state, device_action_results, recorded_at, tenant_id) SELECT column1, column2, column3, column4, column5, column6, column7, column8, column9, column10, column11, column12, column13, column14, column15, column16, column17, column18, column19, column20, column21, column22, column23, column24, column25, column26, column27, column28, column29, column30, column31, column32, column33, column34, column35, column36, column37, column38, column39, column40, column41, column42, column43, column44, column45, column46, PARSE_JSON(column47), TRY_TO_TIMESTAMP(column48), column49 FROM VALUES (......) ;

The columns in AZURE_COLLECT_MANAGED_DEVICES don't have the underscores.

[sfc-gh-afedorov]

Fixed, thanks.

[sfc-gh-afedorov]

Here's SQL to reset —

DROP TABLE DATA.azure_collect_connection;
DROP TABLE data.azure_collect_reports_credential_user_registration_details;
DROP TABLE data.azure_collect_subscriptions_locations;
DROP TABLE data.azure_collect_virtual_machines;
DROP TABLE data.azure_collect_managed_clusters;
DROP TABLE data.azure_collect_vaults;
DROP TABLE data.azure_collect_disks;
DROP TABLE data.azure_collect_hosting_environments;
DROP TABLE data.azure_collect_log_profiles;
DROP TABLE data.azure_collect_network_security_groups;
DROP TABLE data.azure_collect_network_watchers;
DROP TABLE data.azure_collect_network_watcher_flow_log_status;
DROP TABLE data.azure_collect_resource_groups;
DROP TABLE data.azure_collect_role_definitions;
DROP TABLE data.azure_collect_service_principals;
DROP TABLE data.azure_collect_storage_accounts;
DROP TABLE data.azure_collect_storage_accounts_containers;
DROP TABLE data.azure_collect_vaults_keys;
DROP TABLE data.azure_collect_vaults_secrets;
DROP TABLE data.azure_collect_webapps;
DROP TABLE data.azure_collect_groups;
DROP TABLE data.azure_collect_users;
DROP TABLE data.azure_collect_managed_devices;
SHOW TABLES LIKE 'AZURE_COLLECT_%';

[ghost]

The managed devices table columns still don't quite reconcile with the response mappings:

Table	Response map	Match
recorded_at	recorded_at	TRUE
tenant_id	tenant_id	TRUE
	subscription_id	FALSE
error	error	TRUE
id	id	TRUE
user_id	user_id	TRUE
device_name	device_name	TRUE
managed_device_owner_type	managed_device_owner_type	TRUE
device_action_results	device_action_results	TRUE
enrolled_date_time	enrolled	FALSE
last_sync_date_time	last_sync	FALSE
operating_system	operating_system	TRUE
compliance_state	compliance_state	TRUE
jail_broken	jail_broken	TRUE
management_agent	management_agent	TRUE
os_version	os_version	TRUE
eas_activated	eas_activated	TRUE
eas_device_id	eas_device_id	TRUE
eas_activation_date_time	eas_activation	FALSE
azure_a_d_registered	azure_a_d_registered	TRUE
device_enrollment_type	device_enrollment_type	TRUE
activation_lock_bypass_code	activation_lock_bypass_code	TRUE
email_address	email_address	TRUE
azure_a_d_device_id	azure_a_d_device_id	TRUE
device_registration_state	device_registration_state	TRUE
device_category_display_name	device_category_display_name	TRUE
is_supervised	is_supervised	TRUE
exchange_last_successful_sync_date_time	exchange_last_successful_sync	FALSE
exchange_access_state	exchange_access_state	TRUE
exchange_access_state_reason	exchange_access_state_reason	TRUE
remote_assistance_session_url	remote_assistance_session_url	TRUE
remote_assistance_session_error_details	remote_assistance_session_error_details	TRUE
is_encrypted	is_encrypted	TRUE
user_principal_name	user_principal_name	TRUE
model	model	TRUE
manufacturer	manufacturer	TRUE
imei	imei	TRUE
compliance_grace_period_expiration_date_time	compliance_grace_period_expiration	FALSE
serial_number	serial_number	TRUE
phone_number	phone_number	TRUE
android_security_patch_level	android_security_patch_level	TRUE
user_display_name	user_display_name	TRUE
configuration_manager_client_enabled_features	configuration_manager_client_enabled_features	TRUE
wi_fi_mac_address	wi_fi_mac_address	TRUE
device_health_attestation_state	device_health_attestation_state	TRUE
subscriber_carrier	subscriber_carrier	TRUE
meid	meid	TRUE
total_storage_space_in_bytes	total_storage_space_in_bytes	TRUE
free_storage_space_in_bytes	free_storage_space_in_bytes	TRUE
managed_device_name	managed_device_name	TRUE
partner_reported_threat_state	partner_reported_threat_state	TRUE

[ghost]

Whoops, you have to scroll over. Impressed that it handled my paste from Excel though.

[sfc-gh-afedorov]

Got it, thanks for bearing with me. This last push should get it. Do you know where it might get the subscriptionId? I think the other graph requests are tenant-level and don't have it in the query nor the response.

[sfc-gh-afedorov]

Oh, I think that might be a bug in the translation table, if that's where you got those. Could you check if the entirety of that column is NULL perchance?

(edit: removed the subscription_id col and map since that seems more likely the error)

[ghost]

correct, it's not in the actual response

[ghost]

Column names align now but there's at least one data type mismatch:
Expression type does not match column data type, expecting VARIANT but got VARCHAR(7) for column MANAGED_DEVICE_OWNER_TYPE

I'll check over the others and see if they look right

[ghost]

I can see how that happened. On the docs page you linked, when the type links off to another object, sometimes that's just an Enum of the possible values, not a complex type.

Any that say "Possible values are...." should just be a varchar

[sfc-gh-afedorov]

Updated the ones that were enum types to be strings of length a little over the longest listed. Better?

[ghost]

I just always set them to max in Snowflake, since there's no performance or storage difference.

[sfc-gh-afedorov]

We used to, then got feedback that there are some BI tools which inspect the type and allocate memory more efficiently if we set it to something close. Happy to bump them all up to 100 to be future-compatible, or is there another concern?

[ghost]

Ah yep fair enough, 100 is fine

[sfc-gh-afedorov]

About to switch trains to one without internet -- let me know if anything else is off and I'll fix shortly.

[ghost]

Almost successful, just the very last column needs to go to varchar(100):
Expression type does not match column data type, expecting VARIANT but got VARCHAR(7) for column PARTNER_REPORTED_THREAT_STATE

[sfc-gh-afedorov]

Oops, not sure how I missed that one. Fixed!

[ghost]

OK, all looking good now. 11 devices imported and all fields populated correctly.

Thanks for the fast turnaround on this, helps me a lot.

[sfc-gh-afedorov]

Awesome, glad to help, and thanks again for your patience. Even tho we didn't need it for the initial version, I think this piece will be super useful on our end, as well, so thanks for the pointer to the API, as well :-)

There's a couple of small fixes and docs and testing left, but I think tomorrow seems a likely landing for the v1.9.2 release. Thanks again for all your help on the other pieces, as well.

[sfc-gh-afedorov]

Let me know if anything seems off here, closing in meantime. Thanks again!