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 Security | Microsoft Sentinel
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Sedat SALMAN 14,185 Reputation points MVP
    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 7,866 Reputation points MVP Volunteer Moderator
    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
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.