Fetch Database Host

[gokmen]

Hi all , we want to make an availability dashboard only for Database hosts.

Is there a way of fetching only Database hosts. So not to add a newly added host to our DQL filter every time. 

Thanks in advance

[AntonPineiro]

Hi,

Are you talking about some specific database? SQL? Oracle? All of them?

Best regards

[gokmen]

Hi,
I am talking about all database hosts that are in Databases App. Actually what we do is manual work. We go Databases app and list hosts by name then we see all Database host names then put them in our availability DQL.
But I believe that there must be a  DQL code for fetching them at once. 

[firmanprasetyo · Accepted solution]

I handle this for customers using automatic tagging. Basically, you create a rule to detect a process by its technology, and then use that tag to pull your host list.

Example setup:

• Tag name: Database

• Tag value: MySQL / SQLite / MongoDB (Optional)

• Rule type: Monitored Entity

• Rule applies to: Process Groups

• Conditions: Technology process group equals [your database tech] -> Apply to underlying hosts of matching process groups.

Once set up, this happens automatically. Any new database server running that technology will get tagged right away. You can just filter by the tag to get your host list, so you never have to manually update your queries with new servers.

[gokmen]

Hi thank you for the response.

I am doing the same the step as you mention here but when I hit the Display entities it brings other Hosts that are not in the Database app.. The technologies used are PostgreSQL and MSQL. For now we have 17 Database host in the Database app. But this tagging brings other brings more.

And so far I could not find another pattern that brings only the ones that we need. 
Maybe I am doing something wrong.

[gokmen · Accepted solution]

Well okay, now we have created DQL that captures the Database host. In our case it is only PostgreSQL and MSQL. 

fetch `dt.entity.sql:postgres_instance` 
| fieldsAdd entity.type, dt.ip_addresses, 
            vendor_icon = "postgre", 
            postgresql_entity_id = id, 
            tmp.host.id = runs_on[`dt.entity.sql:postgres_host`], 
            rds.endpoint = concat(hostname, ":", port)
 
| lookup
[fetch `dt.entity.sql:postgres_host` 
| fieldsAdd host.id = arrayFirst(same_as[`dt.entity.host`])], 
            sourceField: tmp.host.id, 
            lookupField: id, fields: {host.id}
 
| append 
[fetch `dt.entity.sql:sql_server_instance` 
| fieldsAdd entity.type, dt.ip_addresses, 
            vendor_icon = "sql-microsoft", 
            mssql_entity_id = id, 
            tmp.host.id = runs_on[`dt.entity.sql:sql_server_host`]
            ,rds.endpoint = null
            | parse entity.name, "LD ' on ' LD:hostname SPACE LD"// This line is because SQL instances dont have hostname, Ray's solution :)
 
| lookup
[fetch `dt.entity.sql:sql_server_host` 
| fieldsAdd host.id = arrayFirst(same_as[`dt.entity.host`])], 
            sourceField: tmp.host.id, 
            lookupField: id, fields: {host.id}] 
| dedup host.id

[AlanFoley]

Hi @gokmen 
You could try some smartscape DQL queries:

smartscapeNodes PROCESS
| filter contains(toString(process.software_technologies),"MICROSOFT_SQL_SERVER") or contains(toString(process.software_technologies),"POSTGRE_SQL")
| fieldsAdd hostId = references[runs_on.host]
| expand hostId
| lookup [smartscapeNodes HOST ],sourceField:hostId,lookupField:id,fields:{host=name}
| fields host,name,processName=dt.process_group.detected_name,technology=process.software_technologies
| dedup host,name,processName,technology
| sort host

Regards
Alan

[gokmen]

Hi @AlanFoley   thank you for the response.

Yes it seems that we all need to start working with the new command smartscapeNodes. 
Somehow your query suggestion returns more than we have database hosts in the environment. (It is 17 hosts for now). 
Also can be an option if we can tailor it or narrow down filtering. 

Thanks,

Gokmen

[AlanFoley]

Hi @gokmen 
Note that this query is at the PROCESS level not HOST level so duplicates are certainly possible
I suggest you then use summarize

smartscapeNodes PROCESS
| filter contains(toString(process.software_technologies),"MICROSOFT_SQL_SERVER") or contains(toString(process.software_technologies),"POSTGRE_SQL")
| fieldsAdd technology = coalesce(
if(contains(toString(process.software_technologies),"MICROSOFT_SQL_SERVER"), "MICROSOFT_SQL_SERVER"),
if(contains(toString(process.software_technologies),"POSTGRE_SQL"), "POSTGRE_SQL"),
"NOMATCH")
| fieldsAdd hostId = references[runs_on.host]
| expand hostId
| lookup [smartscapeNodes HOST ],sourceField:hostId,lookupField:id,fields:{host=name}
| fields host,technology
| summarize count=count(),by:{host,technology}
| sort count desc

Regards
ALan