Hi Forum
I am wondering if anyone can help me in relation to an SQL query to allow me to see the detailed authentication for a list of assets ?
I am looking to get only the last authentication attempt here but not sure how i can achieve this.
Any help would be greatly appreciated.
Regards
Mike.
@mike_fennell Similar to this recent post, it is possible to use the dim_credential_status and dim_aggregated_credential_status tables to get authentication details about assets: Adding Owner Tag to SQL Query
That post is probably a good starting point as well as the data model documentation for SQL queries: https://insightvm.help.rapid7.com/docs/understanding-the-reporting-data-model-dimensions#section-dim-aggregated-credential-status
Hey @zyoutz
Thanks for coming back on this appreciate your reply and link to your SQL query.
I had something already written but what i am unsure of is are the authentication results for last scan or is it showing me multiple scan results.
This is what i have
SELECT dsite.name “Site”, da.ip_address as “IP Address”, da.host_name as “Host Name”, ds.name as “Service”, dp.description as “Protocol”, dasc.port as “Port”, dcs.credential_status_description “Access Level”
FROM dim_asset da
JOIN fact_asset_scan_service fass using (asset_id)
JOIN fact_asset fa using (asset_id)
JOIN dim_site_asset dsa using (asset_id)
JOIN dim_site dsite using (site_id)
JOIN dim_service ds using (service_id)
JOIN dim_protocol dp using (protocol_id)
JOIN dim_credential_status dcs using (credential_status_id)
JOIN dim_asset_service_credential dasc using (asset_id)
GROUP BY dsite.name, da.ip_address, da.host_name, ds.name, dp.description, dasc.port, dcs.credential_status_description
ORDER BY da.ip_address DESC
The above does work but again i am not 100% sure how to get the date showing when the auth results were for.
If the auth results are only for the last scan i am guessing i can pull the date from last scan but not sure.
Hope that makes sense as to what i am after, what we are trying to see here is we know we are getting some failure on windows port 135 and i want to try and flush that out , however in my results i am seeing sometimes two login attempts on CIFS and DCS which is a bit confusing and hence why i am wondering if i am seeing results from more than just the last scan .
Regards
Mike Fennell.
If you need to get down to the specific service used for authentication, it might even be helpful to use the dim_asset_service_credential table. This will return multiple results for a particular asset (keep that in mind); however, it may be helpful if you are narrowing your focus.
As for getting the credential status and knowing the time of the status, it is probably best to use dim_aggregated_credential_status along with fact_asset or the fact_asset_scan tables.
If using fact_asset it will be for the last scan and contain the last scan start and end times for that device. If you need to look for results for a particular scan, then fact_asset_scan will be what you want to use and then still join dim_aggregated_credential_status for the auth status. Both of those contain aggregated_credential_status_id as a column of the table to give you the status aggregated across all available services for the given asset.
Thanks Zac , much appreciated i will take a look at your recommendations.
Hey Zac
Quick one when i pull the scan_stated (timestamp with timezone) from the fact_asset table i am getting a very strange output for the year, firstly it comes out as just a number and i have to run a date format over it in excel, but it is not showing the correct year.
Any idea of why this is have you seen it happen before ?
That’s strange. Have you checked the output CSV to make sure it isn’t some formatting that Excel is providing when the document is opened? The format of scan_started and scan_finished should look similar to 2019-05-13 13:46:02.067.
If you can provide your full query you put together I’m happy to take a look and see what I get exported when running.
Hey Zac
Appreciate you looking at this.
SELECT dsite.name “Site”, da.ip_address as “IP Address”, da.host_name as “Host Name”, ds.name as “Service”, dp.description as “Protocol”, dasc.port as “Port”, dcs.credential_status_description “Access Level”, fa.last_scan_id as “Cred Scan Date”
FROM dim_asset da
JOIN fact_asset_scan_service fass using (asset_id)
JOIN fact_asset fa using (asset_id)
JOIN dim_site_asset dsa using (asset_id)
JOIN dim_site dsite using (site_id)
JOIN dim_service ds using (service_id)
JOIN dim_protocol dp using (protocol_id)
JOIN dim_credential_status dcs using (credential_status_id)
JOIN dim_asset_service_credential dasc using (asset_id)
GROUP BY dsite.name, da.ip_address, da.host_name, ds.name, dp.description, dasc.port, dcs.credential_status_description, fa.last_scan_id
ORDER BY da.ip_address DESC
Hey Zac
Ok i think i seen my issue i was using last_scan_id, i think it might have been too long a day.
No wonder the date would not convert for me :).
I will change it and see how i get on .
Thanks for the update @mike_fennell. Good luck on your query! Feel free to contribute it back here if you think others might find what you are doing useful as well.
Hey @zyoutz no problem appreciate you help.
For anyone who might find it useful this is the sql i created to extract the data around authentication, you can run this via the SQL report within nexpose and it can be ran against a site, asset group (dynamic or static), single IP. It should be useful to pull out authentication results for groups of assets that is our aim with it, i generally pull it out and pivot it in excel or use something like tableau if you have that.
The SQL pulls the following information Site, IP Address, Host Name, Service (cifs, ssh etc), Protocol (TCP , UDP etc), Port , Access Level (login info successful, failed etc), Scan start date
Above is the info we required but you could add extra columns as you require.
SQL:
SELECT dsite.name “Site”, da.ip_address as “IP Address”, da.host_name as “Host Name”, dos.family as “OS Type”, ds.name as “Service”, dp.description as “Protocol”, dasc.port as “Port”, dcs.credential_status_description “Access Level”, fa.scan_started “Scan Date”
FROM dim_asset da
JOIN fact_asset_scan_service fass using (asset_id)
JOIN fact_asset fa using (asset_id)
JOIN dim_operating_system dos using (operating_system_id)
JOIN dim_site_asset dsa using (asset_id)
JOIN dim_site dsite using (site_id)
JOIN dim_service ds using (service_id)
JOIN dim_protocol dp using (protocol_id)
JOIN dim_credential_status dcs using (credential_status_id)
JOIN dim_asset_service_credential dasc using (asset_id)
GROUP BY dsite.name, da.ip_address, da.host_name, dos.family, ds.name, dp.description, dasc.port, dcs.credential_status_description, fa.scan_started
ORDER BY da.ip_address DESC
Hi All
update to this the last SQL i posted was bringing back all auth for multiple scans so i have refined it to bring back only the last scan date results as follows.
SELECT asset_id as “Asset ID”, da.ip_address as “IP Address”, da.host_name as “Host Name”, dos.family as “OS Family”, dos.description as “Operating System”,
ds.name as “Service”, port as “Port”, dcs.credential_status_description as “Authentication Status”, da.last_assessed_for_vulnerabilities as “Last Vulnerability Scan Date”
FROM dim_asset_service_credential
JOIN dim_credential_status As dcs USING(credential_status_id)
JOIN dim_service As ds USING(service_id)
JOIN dim_asset As da USING(asset_id)
JOIN dim_operating_system As dos USING(operating_system_id)
ORDER BY da.ip_address DESC
Hope it helps someone else to get the results they require.