How to set up an alert to trigger when a site has multiple versions?

[KindaWorking]

I have a table that shows something like this:

Site X          V 1.1
                V 1.2
                V 1.3

Site Y          V 1.3

Site Z          V 1.2

I want an alert to show me when a site has multiple versions. I cannot not quite get my search correct.

The search I am currently using:

| dbquery "SQLDB" "SELECT * FROM TABLE WHERE StatusType="Version" ORDER BY SITES"|Stats values(StatusValue) by SITES

[musskopf · Accepted solution]

Assuming the table above you have the columns: siteName and siteVersion, and assuming the siteVersion is a multi-value field you could use the function mvcount. Something like that:

... <YOUR SEARCH> | where mvcount(siteVersion) > 1

That would list only sites with multiple versions, so you could setup your alert based on that.

ps.: I've assumed lots of things as there was no much information provided.

[KindaWorking]

Sorry about being so vague. I have updated my question to include the search string I am using. I am going to try to incorporate the mcvount into my search and see how I go.

[musskopf]

It should work, as the values function you're using will produce a multi-value field. I would only suggest to rename it, like:

... | stats values(StatusValue) AS siteVersion by SITES | where mvcount(siteVersion) > 1

[KindaWorking]

You are my hero!
That works perfectly.

[ppablo]

Hi @KindaWorking

Can you share the actual search you're using so users can see how you're generating your current results and how they can edit it to help you?

[KindaWorking]

Sorry about that. Added it in.