DQL query to fetch hosts via ostype

[huzoor]

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 ?

[Robert_Conatser]

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

[huzoor]

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

[Robert_Conatser]

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

[huzoor]

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.

[Robert_Conatser · Accepted solution]

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

[huzoor]

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.

[Robert_Conatser]

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