How to get the license usage per host by sourcetype?

[bharadwaja30]

I have 2 individual reports - 'License Usage by Host' and 'License Usage by Sourcetype'. We know that one host can have more than one sourcetype. Now, I want to generate a report that gives me license usage per host by sourcetype. i.e., I want to know the license used by individual sourcetypes under a particular host. The report should look like the one as below:

Is there a way we can generate a report of this kind?

[javiergn · Accepted solution]

This could be one approach but it won't match exactly your table:

index=_internal source=*license_usage.log type="Usage" earliest=-2d@d latest=@d
| fields _time, st, b, h
| bucket span=1d _time
| stats sum(b) as b by _time, st, h
| eval b=round(b/1024/1024, 4)
| rename h as host, st as sourcetype, b as MB
| eval time=strftime(_time, "%Y-%m-%d")
| eval temp = host . "@@" . sourcetype
| xyseries temp, time, MB
| rex field=temp "^(?<host>.+?)@@(?<sourcetype>.+?)$"
| fields - temp
| stats first(*) as * by host, sourcetype

Take a look and let me know if that's enough. The table you are asking for is not trivial to do.

[bharadwaja30]

Thank you javiergn. This query answered my question.