I would like to have the prefix of the machine name stored. Then the variable values ​​are displayed together as a graph. The right thing, what should I do?

Koonnamchok Klongkaew 140 Reputation points
2023-05-13T08:49:50.7966667+00:00
let Thailand = "Thailand";
let Myanmar = "Myanmar";

let ThailandEvents = 
    DeviceEvents
    | where ActionType contains "UsbDriveMounted"
    | where DeviceName contains ".xxxx.com"
    | where (DeviceName startswith "art" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "th") and Thailand in (Thailand)
    | where DeviceName !contains "desktop" or DeviceName !contains "laptop"
    | extend ParsedFields = parse_json(AdditionalFields)
    | project Timestamp, ProductName = tostring(ParsedFields.ProductName), SerialNumber = tostring(ParsedFields.SerialNumber), Manufacturer = tostring(ParsedFields.Manufacturer), DriveLetter = tostring(ParsedFields.DriveLetter), LoggedOnUsers = tostring(ParsedFields.LoggedOnUsers), DeviceName, ActionType, MachineGroup;

let MyanmarEvents = 
    DeviceEvents
    | where ActionType contains "UsbDriveMounted"
    | where DeviceName contains ".xxxx.com"
    | where (DeviceName startswith "rgn" or DeviceName startswith "rn" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "xx" or DeviceName startswith "zoc" or DeviceName startswith "xx") and Myanmar in (Myanmar)
    | where DeviceName !contains "desktop" or DeviceName !contains "laptop"
    | extend ParsedFields = parse_json(AdditionalFields)
    | project Timestamp, ProductName = tostring(ParsedFields.ProductName), SerialNumber = tostring(ParsedFields.SerialNumber), Manufacturer = tostring(ParsedFields.Manufacturer), DriveLetter = tostring(ParsedFields.DriveLetter), LoggedOnUsers = tostring(ParsedFields.LoggedOnUsers), DeviceName, ActionType, MachineGroup;

union ThailandEvents, MyanmarEvents
| summarize dcount(Manufacturer) by Thailand, Myanmar, 
| | render  piechart 


Microsoft Sentinel
Microsoft Sentinel
A scalable, cloud-native solution for security information event management and security orchestration automated response. Previously known as Azure Sentinel.
1,018 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Sedat SALMAN 13,185 Reputation points
    2023-05-13T19:01:38.65+00:00

    Here's how you might adjust your script, assuming that you add a Country column in each of your let statements:

    let Thailand = "Thailand";
    let Myanmar = "Myanmar";
    
    let ThailandEvents = 
        DeviceEvents
        // Your previous filters...
        | extend Country = Thailand
        | project Timestamp, Country, ProductName = tostring(ParsedFields.ProductName), SerialNumber = tostring(ParsedFields.SerialNumber), Manufacturer = tostring(ParsedFields.Manufacturer), DriveLetter = tostring(ParsedFields.DriveLetter), LoggedOnUsers = tostring(ParsedFields.LoggedOnUsers), DeviceName, ActionType, MachineGroup;
    
    let MyanmarEvents = 
        DeviceEvents
        // Your previous filters...
        | extend Country = Myanmar
        | project Timestamp, Country, ProductName = tostring(ParsedFields.ProductName), SerialNumber = tostring(ParsedFields.SerialNumber), Manufacturer = tostring(ParsedFields.Manufacturer), DriveLetter = tostring(ParsedFields.DriveLetter), LoggedOnUsers = tostring(ParsedFields.LoggedOnUsers), DeviceName, ActionType, MachineGroup;
    
    union ThailandEvents, MyanmarEvents
    | summarize dcount(Manufacturer) by Country
    | render piechart 
    
    

  2. Clive Watson 5,951 Reputation points MVP
    2023-05-15T14:01:16.67+00:00

    To save you having to define each country, do you have a Table that contains it? Most people have SigninLogs (if running this from Sentinel), so an example of using that would be

    
    DeviceEvents
    | extend ParsedFields = parse_json(AdditionalFields)
    | distinct DeviceName, Manufacturer = tostring(ParsedFields.Manufacturer)
    | join kind=inner
    (
        SigninLogs
        | extend DeviceName = tolower(tostring(DeviceDetail.displayName)) 
        | where isnotempty( DeviceName)
    ) on DeviceName
    | project DeviceName, Location, LocationDetails, Manufacturer
    | summarize dcount_=dcount(Manufacturer), make_set(Manufacturer) by Location
    | order by dcount_ desc