How to show eventstats over time?

[mikem]

i currently have a query that returns what I need for a single day.

( index=microsoftcloud sourcetype="ms:azure:accounts" source="rest*group*") OR (index=microsoftcloud sourcetype="ms:azure:accounts" source="rest*User*")
| where match(userPrincipalName,"domain name") or match(userPrincipalName,"domain name")
| eventstats count by id
| eventstats count(eval((source="rest://MSGraph Group1 Members" OR (source="rest://MSGraph Group 2 Members") or (source="rest://MSGraph Group 3 Members") ))) as total
| eventstats count(eval(source="rest://MSGraph CL Users" AND count>1)) as current
| dedup total, current
| eval perc=round(current*100/total,1)."%"
| eval missing=total-current

| rename total as "In Scope Users"
| rename current as "Current Users"
| rename perc as "Percent Compliant"
| rename missing as "Missing"
|  table "In Scope Users", "Current Users", "Missing", "Percent Compliant"

I am trying to make this show me a chart over the previous month that show me the daily result of the posted query.  
I have tried many "solutions" from the web, but nothing has worked.  Any help is appreciated

[bowesmana · Accepted solution]

@mikem 

your search is going to be massively inefficient. eventstats is a slow command and you are running it 3 times across the data and then using dedup to aggregate.

You should try to use stats where possible and in this case, I believe you can achieve the same with stats.

This uses 2 stats commands and also splits by day. I think it should give the same results

( index=microsoftcloud sourcetype="ms:azure:accounts" source="rest*group*") OR (index=microsoftcloud sourcetype="ms:azure:accounts" source="rest*User*")
| where match(userPrincipalName,"domain name") or match(userPrincipalName,"domain name")

| bin _time span=1d
| stats count(eval((source="rest://MSGraph Group1 Members" OR (source="rest://MSGraph Group 2 Members") or (source="rest://MSGraph Group 3 Members") ))) as total count(eval(source="rest://MSGraph CL Users")) as current by _time id
``` If id count was 1, then current will be 0 ```
| eval current=if(count<=1, 0, current)
``` this will calculate your original total ```
| stats sum(count) as total sum(current) as current by _time

| eval perc=round(current*100/total,1)."%"
| eval missing=total-current

| rename total as "In Scope Users"
| rename current as "Current Users"
| rename perc as "Percent Compliant"
| rename missing as "Missing"
|  table "In Scope Users", "Current Users", "Missing", "Percent Compliant"

[mikem]

i had thought this would work, and had tried something similar before using eventstats.   when I execute this nothing is returned.

I am wondering if this is because the data is populated by API calls and the timestamps on the data are when the API call was executed.  
the dedup is to cleanup if the API call was run more than once in a 24h period

[bowesmana]

In order to see why it doesn't work - start with taking line by line and adding the next line and see what your data looks like at that point.

You search for data with a time range and that means _time MUST be within that range to be found. If _time is the API call time, does the data have its own timestamp other than the one for the API call.

However, there is always _time, so it should return something. If you want to explore how to make this work, please post the query and results at various points when it goes from data to no data.

[mikem]

i walked thru this line by line

had to break up the total collection into

| stats count(eval(source="rest://MSGraph Group1 Members")) as total1 count(eval(source="rest://MSGraph Group 2 Members")) as total2 count(eval(source="rest://MSGraph Group 3 Members")) as total3 count(eval(source="rest://MSGraph CL Users")) as current by _time id 

| eval total=total1+total2+total3

[ITWhisperer]

Try adding a time element to your search

( index=microsoftcloud sourcetype="ms:azure:accounts" source="rest*group*") OR (index=microsoftcloud sourcetype="ms:azure:accounts" source="rest*User*")
| where match(userPrincipalName,"domain name") or match(userPrincipalName,"domain name")
| bin _time span=1d
| eventstats count by id _time
| eventstats count(eval((source="rest://MSGraph Group1 Members" OR (source="rest://MSGraph Group 2 Members") or (source="rest://MSGraph Group 3 Members") ))) as total by _time
| eventstats count(eval(source="rest://MSGraph CL Users" AND count>1)) as current by _time
| dedup total, current, _time
| eval perc=round(current*100/total,1)."%"
| eval missing=total-current

| rename total as "In Scope Users"
| rename current as "Current Users"
| rename perc as "Percent Compliant"
| rename missing as "Missing"
|  table _time, "In Scope Users", "Current Users", "Missing", "Percent Compliant"