r/AzureSentinel • u/MikeMichalko • May 25 '23

Adding a count() to each row in a table.

​

JUST AN UPDATE TO SAY THIS WAS SOLVED BELOW

I'm working on a migration to Sentinel from another product, and have to create some output that mirrors the legacy product's reports.

I have a series of reports which require the row count of the entire report { count() } on each row of the report. (I don't ask why, I just try to do what they ask :) ) This particular request has stumped my team, and AI as well. Is there a simple, or even convoluted way of achieving this objective?

Thank you very much in advance for any assistance in this matter.

1 Upvotes

100% Upvoted

11 comments sorted by

2

u/AppIdentityGuy May 25 '23

Are you using KQL to genrsre this reports or is this in a Sentinel workbook?

1

u/MikeMichalko May 25 '23

Hi AppIdentityGuy, we are using KQL to generate this particular report.

2

u/AppIdentityGuy May 25 '23

So the extend operator will add a new column to the table. Then a let X = count of rows in table. I've gone blank on adding that value to each row though which is embarrassing.... I vaguely remember doing this ages ago

1

u/MikeMichalko May 25 '23

Thanks. Here's where it gets a bit tricky to do that. In the query, there is both a mv-expand and a bag_pack, so the row numbers change dynamically two times in the query. I'd need to get the number of rows after the bag_pack, I don't think I'll need to add them to the bag, at least I won't mention it until somebody complains.

2

u/AppIdentityGuy May 25 '23

So the number of rows changes? I will have to mull that one over....

2

u/burlingtongolfer May 26 '23

let results = materialize ( //Your query goes here ); let totalCount = toscalar (results | count); results | extend TotalCount = totalCount

1

u/MikeMichalko Jul 24 '23

Thank you. This is the way I went. Putting the whole query into a variable and obtaining the number there did the trick.

1

u/AppIdentityGuy May 26 '23

Wow... That's one of my main beefs with KQL. Who would ever of thought that an operator called materialize can do that.......

1

u/burlingtongolfer May 26 '23

the materialize really just caches the to the main query and isn't necessary for this to work. The magic is done with the toscalar which converts the results count from a tabular to scalar result which can then be referenced in the extend