How to show statistics of daily volume and latest events for all the sourcetypes in single table?

[PavanSeerapu]

I want to show statistics of daily volume and latest events for all the sourcetypes in single table, can you please help.

[PavanSeerapu]

• I got the individual query to get the latest time stamp and individual query to get the daily volume in GB , but i couldn't get the both in single query 

[somesoni2]

Can you post the two queries that you're using?

They both come from different data sources (daily license usage comes from internal logs and event data comes from your data indexes), so you'd need to use append to combine both data sources.

[PavanSeerapu]

index=_internal source=*metrics.log

| eval GB=kb/(1024*1024)

| search group="per_sourcetype_thruput"

| stats  sum(GB) by series

| tstats latest(_time) as latest where index=*  earliest=-24h by sourcetype

 |eval LastReceivedEventTime  = strftime(latest,"%c")

|table sourcetype LastReceivedEventTime

[somesoni2]

Tstats queries are faster than regular query to I'll use them in append subsearch. Try something like this

index=_internal source=*metrics.log
| eval GB=kb/(1024*1024)
| search group="per_sourcetype_thruput"
| stats  sum(GB) by series | rename series as sourcetype
| append [| tstats latest(_time) as latest where index=*  earliest=-24h by sourcetype  |eval LastReceivedEventTime  = strftime(latest,"%c")
|table sourcetype LastReceivedEventTime ]
| stats values(*) as * by sourcetype

[PavanSeerapu]

When using this query getting sourcetypes as duplicates one starting with capital and one starting with small as shown below, beacuse of this values are not appending correctly.

WinEventLog:Security	Thu Jun  9 02:35:06 2022
wineventlog:security		33.97319556

[somesoni2 · Accepted solution]

Try to change the case of sourcetype to either lowercase or uppercase in both searches, like this:

index=_internal source=*metrics.log
| eval GB=kb/(1024*1024)
| search group="per_sourcetype_thruput"
| stats  sum(GB) by series | eval sourcetype=lower(series) 
| table sourcetype "sum(GB)"
| append [| tstats latest(_time) as latest where index=*  earliest=-24h by sourcetype  |eval LastReceivedEventTime  = strftime(latest,"%c")
|table sourcetype LastReceivedEventTime | eval sourcetype=lower(sourcetype)]
| stats values(*) as * by sourcetype

Regarding performance, what timerange are you using for your search?

[jamie00171]

Hi All, 

if you add TERM() around group=per_sourcetype_thruput you'll read less event off disk which might make a noticeable difference depending on the time range of the search e.g.

index=_internal source=*metrics.log TERM(group=per_sourcetype_thruput)
| eval GB=kb/(1024*1024)

[PavanSeerapu]

If im using TERM im not getting any events 😞

[PavanSeerapu]

It worked but taking lot of time for the search to get complete 😞

[aasabatini]

Hi @PavanSeerapu 

I think the bestway is metadata

try this search to check the count of events splitted by sourcetype

| metadata type=sourcetypes index=*

Regards