Is it possible to use the dbxquery command to get list of assets and a standard Splunk search to find last log entries for each?

[jchapell]

I am using a dbxquery to pull my assets(hosts) and a few other details from ManageEngine's SQL database for ServiceDeskPlus and would like to then use those results to match up the latest log entry from each host. Is it possible? My dbxquery works great, but nothing has worked in regards to leveraging those results to find the latest log from each host.

My current query is:

| dbxquery query="SELECT MAX(\"resource\".\"RESOURCENAME\") AS \"Asset Name\", MAX(\"resource\".\"ASSETTAG\") AS \"Asset Tag\", MAX(\"barcodes\".\"BARCODE\") AS \"Barcode\", MAX(\"product\".\"COMPONENTNAME\") AS \"Product\", MAX(\"productType\".\"COMPONENTTYPENAME\") AS \"Product Type\", MAX(\"aao\".\"NAME\") AS \"Vendor Name\", MAX(\"state\".\"DISPLAYSTATE\") AS \"Asset State\", MAX(\"rtype\".\"TYPE\") AS \"Asset Type\", MAX(\"rCategory\".\"CATEGORY\") AS \"Asset Category\", MAX(\"resource\".\"SERIALNO\") AS \"Serial Number\", MAX(\"aaaUser\".\"FIRST_NAME\") AS \"User\", MAX(\"resFields\".\"UDF_CHAR6\") AS \"Physical or Virtual\", MAX(\"resFields\".\"UDF_CHAR7\") AS \"Alternative Name\"  FROM \"Resources\" \"resource\" LEFT JOIN \"ComponentDefinition\" \"product\" ON \"resource\".\"COMPONENTID\"=\"product\".\"COMPONENTID\" LEFT JOIN \"ComponentType\" \"productType\" ON \"product\".\"COMPONENTTYPEID\"=\"productType\".\"COMPONENTTYPEID\" LEFT JOIN \"ResourceType\" \"rtype\" ON \"productType\".\"RESOURCETYPEID\"=\"rtype\".\"RESOURCETYPEID\" LEFT JOIN \"ResourceCategory\" \"rCategory\" ON \"productType\".\"RESOURCECATEGORYID\"=\"rCategory\".\"RESOURCECATEGORYID\" LEFT JOIN \"VendorDefinition\" \"resourceVendor\" ON \"resource\".\"VENDORID\"=\"resourceVendor\".\"VENDORID\" LEFT JOIN \"SDOrganization\" \"aao\" ON \"resourceVendor\".\"VENDORID\"=\"aao\".\"ORG_ID\" LEFT JOIN \"ResourceState\" \"state\" ON \"resource\".\"RESOURCESTATEID\"=\"state\".\"RESOURCESTATEID\" LEFT JOIN \"ResourceOwner\" \"rOwner\" ON \"resource\".\"RESOURCEID\"=\"rOwner\".\"RESOURCEID\" LEFT JOIN \"ResourceAssociation\" \"rToAsset\" ON \"rOwner\".\"RESOURCEOWNERID\"=\"rToAsset\".\"RESOURCEOWNERID\" LEFT JOIN \"SDUser\" \"sdUser\" ON \"rOwner\".\"USERID\"=\"sdUser\".\"USERID\" LEFT JOIN \"AaaUser\" \"aaaUser\" ON \"sdUser\".\"USERID\"=\"aaaUser\".\"USER_ID\" LEFT JOIN \"Resource_Fields\" \"resFields\" ON \"resource\".\"RESOURCEID\"=\"resFields\".\"RESOURCEID\" LEFT JOIN \"Barcodes\" \"barcodes\" ON \"resource\".\"BARCODEID\"=\"barcodes\".\"BARCODEID\"  GROUP BY \"resource\".\"RESOURCEID\"" connection="sdp_read_sql" 
| dedup "Asset Name"
| sort "Asset State","Asset Type","Physical or Virtual","Product Type"
| table "Asset Name","Asset State","Asset Type","Physical or Virtual","Product Type"

[woodcock · Accepted solution]

You need to dump the output to a lookup file with |outputlookup YourLookupHere. Then you can use this in any search like this:

Your Base Search Here | lookup YourLookupHere "Asset Name" AS host

[jchapell]

@woodcock

that worked with a little tweaking, but worked. Now, my search when I start filtering out hosts by specific statuses, I get several systems that do not appear in my lookup. What would be the easiest way to filter those? Here is my current query:

| metadata type=hosts  
| lookup sdp_lookup1.csv "Asset Name" as host Output "Asset State" as Reason 
| eval Reason = if(isnull(Reason) OR Reason="", "Status?", Reason) 
| convert ctime(recentTime) as Latest_Time ctime(firstTime) as First_Time 
| eval gap = (now() - recentTime) / 86400 
| where gap > 1.0 
| eval Days_of_Missing_Logs = round(gap,0) 
| sort Reason, 0 - Days_of_Missing_Logs 
| where Reason != "In Use" AND Reason != "In Store"
| table host Reason Days_of_Missing_Logs Latest_Time First_Time

I'm "assuming" adding from sdp_lookup1.csvon the |eval gap = (now() - recentTime)/86400 so that it reads | eval from sdp_lookup1.csv gap = (now() - recentTime) / 86400

[woodcock]

To filter out non-matches, you can do this:

... | eval Reason="NoLookupMatchSoDeleteMe"
| lookup sdp_lookup1.csv "Asset Name" as host Output "Asset State" as Reason 
| search NOT Reason="NoLookupMatchSoDeleteMe"

[Richfez]

NOT an answer to your question, just a note because I and many others find this easier: have you investigated moving your complex SQL logic into a SQL View, so that all you have to do in your Splunk is "SELECT * FROM MyViewName" ? Just makes life easy.

[jchapell]

yes, I know there are a lot of joins in there....it's the main query I am using for several panels in a dashboard for now, until I get just what I need and make it "pretty"