Thursday, 4 May 2023

how to get the latest item by date in sql

 

how to get the latest item by date in sql
 

To get the latest item by date in SQL, you can use a subquery or a CTE (Common Table Expression) to identify the latest date for each item, and then join the result back to the original table to retrieve the other columns associated with that latest date. Here's an example:

Let's say you have a table called "sales" with columns "item" (the name of the item sold), "date" (the date the item was sold), and "quantity" (the quantity of the item sold).

To retrieve the latest item by date, you can use the following SQL query:

vbnet
WITH latest_sales AS ( SELECT item, MAX(date) AS latest_date FROM sales GROUP BY item ) SELECT s.* FROM sales s JOIN latest_sales ls ON s.item = ls.item AND s.date = ls.latest_date

In this query, we first use a CTE called "latest_sales" to retrieve the latest date for each item using the MAX function and grouping by item. Then, we join the "latest_sales" CTE back to the "sales" table on both "item" and "date" columns to retrieve the other columns associated with the latest date for each item.

This will give you a result set that shows the latest sales for each item:

mathematica
| item | date | quantity | | ------- | ---------- | -------- | | Item A | 2021-01-05 | 10 | | Item B | 2021-01-03 | 5 | | Item C | 2021-01-02 | 7 |

This shows the latest sales for each item, with the associated date and quantity.


Friday, 10 February 2023

SQL - How to convert multiple rows into single rows with more columns?

 https://community.spiceworks.com/topic/973727-sql-how-to-convert-multiple-rows-into-single-rows-with-more-columns

SQL - How to convert multiple rows into single rows with more columns?

check
  • This is how I would get the result you want using the sample table and data you posted.

    SQL
    -- Create table for sample query
    Declare @Orders Table (OrderID int,
    	Product varchar(10),
    	Quantity int);
    
    -- Insert sample data
    Insert Into @Orders
    Values (100, 'Cup', 1),
    	(100, 'Plate', 2),
    	(101, 'Cup', 1),
    	(102, 'Cup', 2),
    	(103, 'Cup', 1),
    	(103, 'Plate', 2),
    	(103, 'Glass', 1);
    
    
    With Orders
    As (Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
    		*
    	From @Orders)
    Select OrderID As [Order#],
    	Min(Product1) As Product1,
    	Min(Quantity1) As Quantity1,
    	Min(Product2) As Product2,
    	Min(Quantity2) As Quantity2,
    	Min(Product3) As Product3,
    	Min(Quantity3) As Quantity3
    From (Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
    		OrderID,
    		'Product' + Cast(RowID as varchar) As ProductNum,
    		'Quantity' + Cast(RowID as varchar) As QuantityNum,
    		Product,
    		Quantity
    	From Orders) As Pvt
    Pivot (Min(Product)
    	For ProductNum In ([Product1], [Product2], [Product3])) As Pvt1
    Pivot (Min(Quantity)
    	For QuantityNum In ([Quantity1], [Quantity2], [Quantity3])) As Pvt2
    Group By OrderID;
  •  

    How to get filename from file path in MySQL?

     

    How to get filename from file path in MySQL?

     


    SELECT id
    , name, SUBSTRING_INDEX(filepath, '/', -1) FROM `categories`

    SPN Discovery

     https://pentestlab.blog/2018/06/04/spn-discovery/

    SPN Discovery

     

    Services that support Kerberos authentication require to have a Service Principal Name (SPN) associated to point users to the appropriate resource for connection. Discovery of SPNs inside an internal network is performed via LDAP queries and can assist red teams to identify hosts that are running important services such as Terminal, Exchange, Microsoft SQL etc. and being stealthy at the same time. Furthermore identification of SPNs is the first step to the kerberoasting attack.

    Tim Medin explained SPN really well in his talk Attacking Kerberos: Kicking the Guard Dog of Hades with practical examples. Sean Metcalf also provided some good resources regarding SPN including an extensive list of Active Directory Service Principal Names which can be found at the end of the article.

    SetSPN

    SetSPN is a native windows binary which can be used to retrieve the mapping between user accounts and services. This utility can add, delete or view SPN registrations.

    1
    setspn -T pentestlab -Q */*
    setspn - Service Discovery
    setspn – Service Discovery

    Services that are bind to a domain user account and not a computer account are more likely configured with a weak password since the user has selected the password. Therefore services which they have their Canonical-Name to Users should be targeted for Kerberoasting. From the list of SPNs below the service PENTESTLAB_001 is associated with a user account.

    setspn - SPN Records
    SetSPN – SPN Records

    GetUserSPNs

    Tim Medin developed a PowerShell script which is part of kerberoast toolkit and can query the active directory to discover only services that are associated with a user account as a more focused approached compared to SetSPN.

    1
    powershell_import /root/Desktop/GetUserSPNs.ps1
    GetUserSPNs - PowerShell Script
    GetUserSPNs – PowerShell Script

    There is also a VBS script which is part of the same tookit and can provide the same information. The script can be executed from the windows command prompt by using the native Windows binary cscript.

    1
    cscript.exe GetUserSPNs.vbs
    GetUserSPNs - VBS Script CMD
    GetUserSPNs – VBS Script

    PowerShell AD Recon

    Similarly to what Tim Medin developed Sean Metcalf wrote various PowerShell scripts to perform recon against Kerberos. These scripts are part of PowerShell AD Recon repository and can query the Active Directory for interesting services such as Exchange, Microsoft SQL, Terminal etc. Sean bind each script to a specific service depending on what SPN the red teamer would like to discover. The following script will identify all the Microsoft SQL instances on the network.

    1
    2
    powershell_import /root/Discover-PSMSSQLServers.ps1
    powershell_execute Discover-PSMSSQLServers
    PowerShell AD Recon - MSSQL Servers Discovery via Metasploit
    PowerShell AD Recon – MSSQL Servers Discovery

    Microsoft Exchange servers can be also discovered with the PSMSExchangeServers script.

    1
    2
    powershell_import /root/Discover-PSMSExchangeServers.ps1
    powershell_execute Discover-PSMSExchangeServers
    PowerShell AD Recon - Exchange Servers Discovery via Metasploit
    PowerShell AD Recon – Exchange Servers Discovery

    Enumeration of service accounts is important as these accounts might be configured with a weak password. The attributes PasswordLastSet and LastLogon can provide an indication of services which have a higher possibility to have a weak password set.

    1
    2
    powershell_import /root/Find-PSServiceAccounts.ps1
    powershell_execute Find-PSServiceAccounts
    PowerShell AD Recon - Service Accounts via Metasploit
    PowerShell AD Recon – Service Accounts

    Empire

    PowerShell Empire has also a module which can display Service Principal Names (SPN) for domain accounts. This module is part of the Situational Awareness category and it should be used as stealth network recon in a red team engagement.

    1
    usemodule situational_awareness/network/get_spn
    Empire - SPN Module
    Empire – SPN Module

    The services will be presenting in the following format.

    Empire - SPN Discovery
    Empire – SPN Discovery

    PowerShellery

    Scott Sutherland before implementing the Get-SPN module to Empire had created several Powershell scripts as part of PowerShellery which can gather SPNs for various services. Some of these require PowerShell v2.0 and some other PowerShell v3.0.

    1
    Get-SPN -type service -search "*"
    Powershellery - GetSPN
    Powershellery – GetSPN

    Results can be also formatted as a table for easier mapping of accounts and services.

    1
    Get-SPN -type service -search "*" -List yes | Format-Table
    PowerShellery - GetSPN Table
    PowerShellery – GetSPN Table

    There is also an additional script which can obtain the UserSID, the service and the actual User.

    1
    2
    Import-Module .\Get-DomainSpn.psm1
    Get-DomainSpn
    PowerShellery - Get-DomainSpn
    PowerShellery – Get-DomainSpn

    Impacket

    Service Principal Names can be also discovered from non-joined domain systems with the python version of GetUserSPNs which is part of impacket. However valid domain credentials are required for communication with the Active Directory as token based authentication cannot be used.

    1
    ./GetUserSPNs.py -dc-ip 10.0.0.1 pentestlab.local/test
    Impacket - Get User SPN