How do I convert this search into a tstats search leveraging the web datamodel?

[jaywilwk]

Here's the search:

index=proxysg sourcetype=proxysg | replace *pandora* with www.pandora.com in url | replace *facebook* with www.facebook.com in url | stats sum(bytes_in) as MB by url | eval MB=round(MB/1024/1024,2) | sort -MB

[dshpritz]

If your data model is accelerated, try this:

| tstats prestats=false local=false summariesonly=true sum(Web.bytes_in) AS bytes_in from datamodel=Web where (Web.dest = *facebook* OR Web.dest=*pandora*) AND nodename=Web.Proxy by Web.dest | rename Web.dest AS dest | eval parent_site=if(match(dest, "^.*facebook.*$"), "www.facebook.com", "www.pandora.com") | stats sum(bytes_in) AS bytes_in by parent_site | eval MB=round(bytes_in/1024/1024,2) | sort - MB

[jaywilwk]

ok, this works great. I'm actually getting data from the datamodel, but it is only limiting it to those two websites (facebook and pandora). In my original search, it showed all urls, bu for pandora and facebook it took all the different pandora & facebook servers and renamed them to be www.pandora.com and www.facebook. Is it possible to do this using the tstats search?

[dshpritz]

Sure! Check it out:

| tstats prestats=false local=false summariesonly=true sum(Web.bytes_in) AS bytes_in from datamodel=Web where nodename=Web.Proxy by Web.dest | rename Web.dest AS dest | replace *pandora* with www.pandora.com in dest | replace *facebook* with www.facebook.com in dest  | stats sum(bytes_in) AS bytes_in by dest | eval MB=round(bytes_in/1024/1024,2)

I think I missed what you were doing with the replace commands before.