date format

[sharmas2]

In the query below, I want to add a new column that displays only the date (MM-DD-YYYY) of the ingested logs data. Additionally, I want to include another column named "total" that will show the combined count of Blocked and Detected logs.

below is the format of required data.

Local_Market	Total	ngssdublin	ngssmilan	ngssrat	date
Blocked	12262651	450273	4648609	7163769	11/5/2025
Detected	3893652	4266	3887955	1431	11/5/2025

below is the query ..

fetch logs // scanLimitGBytes: , samplingRatio: 1000
| filter contains(dt.security_context, "ngss")
//| fieldsAdd a=matchesValue(index,"*ngss")
//| filter a=="true"
| fieldsAdd content2 = concat(content, "}")
| parse content2, "JSON:data"
| fieldsAdd InlineResultID = data[`InlineResultID`]
| fieldsAdd Status = if(InlineResultID == 0, "Detected", else:
if(InlineResultID !=0 AND InlineResultID != 2, "Blocked", else:"Blocked"))
| filterOut InlineResultID == 2
| fieldsAdd market = substring(dt.security_context, to: indexOf(dt.security_context, "_"))
// | fieldsAdd market = if(contains(market, "cita"), substring(market, from:4), else:market)
| fieldsAdd market = replaceString(market, "cita", "")

| summarize {ngssdublin = countIf(market == "ngssdublin"),
ngssmilan = countIf(market == "ngssmilan"),
ngssrat = countIf(market == "ngssrat")}, by:{Status}

//| fieldsadd timestamp('yyyy-MM-dd),timezone:'UTC'):datetime

[marco_irmer · Accepted solution]

If we assume that your logs already have a timestamp, then there are threesteps to take here:

Step 1:

Just before your summarize command, use the formatTimestamp function to extract just the calendar date from the existing timestamp field into a new field called 'date', as seen in the example below.

| fieldsAdd date = formatTimestamp(timestamp, format:"MM-dd-yyyy")

Step 2:

Amend your summarize command to summarize your the data by status and also date.

| summarize {ngssdublin = countIf(market == "ngssdublin"),
ngssmilan = countIf(market == "ngssmilan"),
ngssrat = countIf(market == "ngssrat")}, 
by:{Status, date}

Step 3: 

To calculate the total column, use the fieldsAdd command at the end of your query (after the summarize command) to calculate the new field, as seen below.

| fieldsAdd total = ngssdublin + gssmilan + ngssrat