topic Re: DQL query to fetch hosts via ostype in DQL

DQL query to fetch hosts via ostype

huzoor — Tue, 03 Sep 2024 15:35:48 GMT

Hi,

I am new to DQL query stuff, can anyone tell me the query to use to fetch the hostgroups which have hosts running on ostype like linux and windows and also along with monitoringmode they are configured on ?

Re: DQL query to fetch hosts via ostype

Robert_Conatser — Tue, 03 Sep 2024 19:29:40 GMT

See if this gets close to what you are asking for.

fetch dt.entity.host
| fieldsAdd hostGroupName, Host=entity.name, entity.type, osType, state, monitoringMode
| filter entity.type == "HOST" and state == "RUNNING"
| fieldsRemove entity.type, state
| sort hostGroupName, Host

Re: DQL query to fetch hosts via ostype

huzoor — Tue, 03 Sep 2024 19:47:11 GMT

thank you so much, can you let me know how to filterout ec2 instances? and also how to avoid names that has "db" in them

Re: DQL query to fetch hosts via ostype

Robert_Conatser — Tue, 03 Sep 2024 20:00:13 GMT

fetch dt.entity.host
| fieldsAdd hostGroupName, Host=entity.name, entity.type, osType, state, monitoringMode, cloudType
| filter entity.type == "HOST" and state == "RUNNING" and cloudType != "EC2" and NOT contains(Host,"db")
| fieldsRemove entity.type, state
| sort hostGroupName, Host

Re: DQL query to fetch hosts via ostype

huzoor — Tue, 03 Sep 2024 20:05:26 GMT

I did try the above one before as well, its not fetching any records.. without the cloudtype boolean.. it does fetch all EC2 instance but when we include that it is not.

Re: DQL query to fetch hosts via ostype

Robert_Conatser — Tue, 03 Sep 2024 20:16:56 GMT

Try this one. It seem that it was filtering out the ones with null. (not sure way).

fetch dt.entity.host
| fieldsAdd hostGroupName, Host=entity.name, entity.type, osType, state, monitoringMode, cloudType
| filter entity.type == "HOST" and state == "RUNNING" and (cloudType != "EC2" or isNull(cloudType)) and NOT contains(Host,"db")
| fieldsRemove entity.type, state
| sort cloudType, hostGroupName, Host

Re: DQL query to fetch hosts via ostype

huzoor — Tue, 03 Sep 2024 20:19:15 GMT

you are awesome, thank you so much .. if you can guide me for knowledge guide on how to fetch all these details would be helpful.

Re: DQL query to fetch hosts via ostype

Robert_Conatser — Tue, 03 Sep 2024 20:29:43 GMT

Better solution (too many "Not", "and" and "or" 😂)

fetch dt.entity.host
| fieldsAdd hostGroupName, Host=entity.name, entity.type, osType, state, monitoringMode, cloudType
| filter entity.type == "HOST" and state == "RUNNING"
| filterOut cloudType == "EC2" or contains(Host,"db")
| fieldsRemove entity.type, state
| sort hostGroupName, Host