04-27-2015 09:28 AM
My client has a large environment. What is the best way to set up a DQL report to only run a report on a single indexer, collector or filer?
Solved! Go to Solution.
04-29-2015 02:56 PM
Dave we need to clarify some things for the request. You could run a report based on data originating from a specific device or residing on a particular indexer for the indices to query. The collector is an instrument of the process and contains no data or better said only data in transit and has nothing to report against.
I'll take you query above as example since it is missing the MSU column and the filter will not exist in the SDK manual. That said I think you are trying to identify a filter like a particular device or share to report upon.
In the configdb there is a node table or you can right click the DataInsight servers column header to add the ID column to learn the id of an indexer and the same is true for the MSU table to the monitored shares page on a filer. In the example below I changed the query for my dataset and chose my Profiles share for users whose ID is 12. I removed the sensitive path value as it was not applicable for my dataset.
Run yourself a test with the column values form the SDK and the correct value from the environment and that should work for you.
Rod
FROM activity
GET user.name,
user.domain,
sum(count) AS access_count,
path.device.name,
path.absname,
path.msu.id,
formatdate(timestamp, "YYYY-MM-DD HH:mm")
IF
timestamp >= datetime("2014/01/01 00:00", "YYYY/MM/DD HH:mm")
AND
timestamp <= datetime("2015/04/28 00:00", "YYYY/MM/DD HH:mm")"
AND path.msu.id = "12"
GROUPBY user.name,
user.domain,
path.device.name,
path.absname;
example without the groupby just to show no errors:
user_name | user_domain | access_count | path_device_name | path_absname | path_msu_id | formatdate(timestamp,YYYY-MM-DD HH:mm) |
---|---|---|---|---|---|---|
Ailene | SAMGWIN.local | 178 | emcvnx | \\emcvnx\profile | 12 |
2014-12-23 07:47
|
04-29-2015 07:02 AM
FROM activity
GET user.name,
user.domain,
sum(count) AS access_count,
path.device.name,
path.absname,
path.msu,
formatdate(timestamp, "YYYY-MM-DD HH:mm")
IF path.issensitive = "1" AND
timestamp >= datetime("2015/01/01 00:00", "YYYY/MM/DD HH:mm")
AND
timestamp <= datetime("2015/04/28 00:00", "YYYY/MM/DD HH:mm")"
AND msu = "Indexer #1"
GROUPBY user.name,
user.domain,
path.device.name,
path.absname;
04-29-2015 02:56 PM
Dave we need to clarify some things for the request. You could run a report based on data originating from a specific device or residing on a particular indexer for the indices to query. The collector is an instrument of the process and contains no data or better said only data in transit and has nothing to report against.
I'll take you query above as example since it is missing the MSU column and the filter will not exist in the SDK manual. That said I think you are trying to identify a filter like a particular device or share to report upon.
In the configdb there is a node table or you can right click the DataInsight servers column header to add the ID column to learn the id of an indexer and the same is true for the MSU table to the monitored shares page on a filer. In the example below I changed the query for my dataset and chose my Profiles share for users whose ID is 12. I removed the sensitive path value as it was not applicable for my dataset.
Run yourself a test with the column values form the SDK and the correct value from the environment and that should work for you.
Rod
FROM activity
GET user.name,
user.domain,
sum(count) AS access_count,
path.device.name,
path.absname,
path.msu.id,
formatdate(timestamp, "YYYY-MM-DD HH:mm")
IF
timestamp >= datetime("2014/01/01 00:00", "YYYY/MM/DD HH:mm")
AND
timestamp <= datetime("2015/04/28 00:00", "YYYY/MM/DD HH:mm")"
AND path.msu.id = "12"
GROUPBY user.name,
user.domain,
path.device.name,
path.absname;
example without the groupby just to show no errors:
user_name | user_domain | access_count | path_device_name | path_absname | path_msu_id | formatdate(timestamp,YYYY-MM-DD HH:mm) |
---|---|---|---|---|---|---|
Ailene | SAMGWIN.local | 178 | emcvnx | \\emcvnx\profile | 12 |
2014-12-23 07:47
|