SQL Query help

[vnaidu]

Dear All,

I would like to know how to write an SQL query to pull the assigned policy information from the SQL table. I could do this from the ePO but for some requirement we would like to pull it from SQL using MSMS. Can someone please help me with my requirement.

We need to pull the list of assigned policies not just the applied ones.

I look forward your help.

[cdinet]

Write the query first in epo, save it and then select it, actions, view query sql.  You can copy that query syntax and paste it into sql query window.  That is the simplest way to get what you want.

[vnaidu]

 This is what I have tried in the first place. 😞 unfortunately, I get only the numbers as shown below rather than any details.

Query that I have tried.

select top 360 count(*) as 'count', [EPOAssignedPolicy].[PolicyObjectID], [EPOAssignedPolicy].[PolicyObjectID] from [EPOAssignedPolicy] where ( ( [EPOAssignedPolicy].[FeatureTextID] = N'ENDP_GS_1000' ) or ( [EPOAssignedPolicy].[FeatureTextID] = N'ENDP_FW_META_FW' ) or ( [EPOAssignedPolicy].[FeatureTextID] = N'ENDP_AM_1000' ) ) group by [EPOAssignedPolicy].[PolicyObjectID] order by 'count' desc, [EPOAssignedPolicy].[PolicyObjectID] asc

Out Put I received from MSMS:

count PolicyObjectID PolicyObjectID

----------- -------------- --------------

718 2057 2057

715 2060 2060

715 2061 2061

715 2062 2062

715 2063 2063

715 2064 2064

701 2058 2058

684 2091 2091

17 2059 2059

4 2231 2231

4 2232 2232

4 2233 2233

4 2234 2234

4 2235 2235

4 2236 2236

4 2238 2238

4 2336 2336

(17 row(s) affected)

😞

@cdinet

[cdinet]

If you look at your query, it is only looking for counts - most likely you created the query as a chart.  Configure it as a table instead.