Re: Search all CI with attachment (645 Views)
Reply
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 1 of 21 (649 Views)
Accepted Solution

Search all CI with attachment

Hi,

is it possible to look for all CIs with attachment? I don't see any option to do it.
Thanks a lot!

Regards,
Marcin
Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 2 of 21 (649 Views)

Re: Search all CI with attachment

No, there's not a built in search like what you're suggesting.

What do you need it for? There are ways to get this information, but they depend on what you're trying to do.
Please use plain text.
Respected Contributor
Akhil_Mangla
Posts: 206
Registered: ‎02-01-2009
Message 3 of 21 (649 Views)

Re: Search all CI with attachment

If you only want to generate a report for CIs (only unique name) with attachments, then you may search for the same in SYSATTACHMENTS table but that is a separate table and you won't be able to see other details of CIs.

Is that what you are looking for?

Regards,
Akhil
Please use plain text.
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 4 of 21 (649 Views)

Re: Search all CI with attachment

Hi,

thanks for your responses.

I only want to look for and list all CIs (with details) which have attachments (no information like name, contents, etc about attachments needed). Is there any possibility to do it subtly without doing SELECT on the sql database etc?

Regards,
Marcin
Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 5 of 21 (649 Views)

Re: Search all CI with attachment

Ok... but that doesn't tell us what you're trying to do.

As Akhil said, all attachments in the system (regardless of which module they come from) are stored in the SYSATTACHMENTS table. The system uses the unique key of the record in the module to determine which attachments belong to which records.

So, the SYSATTACHMENTS table can tell you which records in the device table have attachments, but it won't give you any information about those CIs, since that information is stored in the device table.

So, what is it you're trying to do? There are solutions that use javascript, formatctrl queries and calculations... What is the goal you are trying to accomplish?

As I said, there's no OOB method to do what you're asking, so to help, we need to know what it is you're doing.
Please use plain text.
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 6 of 21 (649 Views)

Re: Search all CI with attachment

Hi,

1. I want to create a view in Configuration Item called for example 'All devices with attachments' which show me list of all devices with attachments. I only need information that there is or not an attachment (without any other details or links to attachments).

2. If possible, I want to add a checkbox 'Attachment' in Configuration Item Search which give me ability to looking for different information about CIs only among CIs with attachments.

Thanks for your help.
Regards,
Marcin

Regards,
Marcin
Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 7 of 21 (649 Views)

Re: Search all CI with attachment

Ok, that's better :)

The first can be accomplished with a customized view that calls a jsavascript.

Add the following script to your script library:

Script Name: FindAttachments

function Search(module, query) {
var Records = new Array

if (query == "" | query == null){
var Query = true
}

else {
var Query = query
}


var Attach = new SCFile("SYSATTACHMENTS")
var getAttach = Attach.doSelect(Query+ " and application=\""+module+"\"")

while (getAttach == RC_SUCCESS){
Records.push(Attach.topic)

getAttach = Attach.getNext()
}

return Records
}



Then, create a view like you normally would, but don't select a filter criteria.

Then, edit the view, and change the query to the following:

logical.name isin jscall("FindAttachment.Search", "device")



The Javascript performs a search against the SYSATTACHMENTS table to return the list of unique keys. The view calls the script and performs a search on those keys, returning the record set in your view.

I'll see what I can come up with for your second requirement.

(By the way, points are appreciated)
Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 8 of 21 (649 Views)

Re: Search all CI with attachment

Ok, for the second request, if you add the javascript above, you can set that as a Defined Query in the SearchConfig record.

Use Database Manager and look for the table named "SearchConfig"

Then, look for the record for the "device" table.

Add the following to the Defined Queries tab:

ID: withAttach
Query: logical.name isin jscall("FindAttachments.Search", "device")
Description: With Attachments



Now, there should be a checkbox in the More Options section of your Search form for Configuration Items that says "With Attachments". Checking this box will call the Javascript that will limit the search results to records with attachments.


Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 9 of 21 (649 Views)

Re: Search all CI with attachment

By the way, I wrote the javascript so that you could re-use this in other modules and with a more explicit query.

You could perform the same search for other modules by passing a different parameter instead of "device", (ie "incidents", "cm3r", "probsummary", etc)

Or you could find only specific records by adding a query parameter as well - like records of a particular size, or with a particular unique key or added by a particular user with something like:

jscall("FindAttachments.Search", "device", "sysmodtime
Please use plain text.
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 10 of 21 (649 Views)

Re: Search all CI with attachment

Hi,

Hm... this java script function gives me no result because in my SYSATTACHMENTS table depend on CI application field is: joincomputer or joinofficeelectronics for example...

Regards,
Marcin
Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 11 of 21 (645 Views)

Re: Search all CI with attachment

..that's odd...

What version are you running?
Please use plain text.
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 12 of 21 (645 Views)

Re: Search all CI with attachment

7.11.205
Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 13 of 21 (645 Views)

Re: Search all CI with attachment

Ok, this is still easily done regardless, you just have to edit the query to say something like:

logical.name isin jscall("FindAttachments.Search", "joincomputer") or logical.name isin jscall("FindAttachments.Search", "joinhandheld")

etc.
Please use plain text.
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 14 of 21 (645 Views)

Re: Search all CI with attachment

Hi,

thanks for your help!

I have change operatorn in script:
var getAttach = Attach.doSelect(Query+ " and application #\""+module+"\"")
so now it is ok.

Thanks for your help once again. Points for You.

Regards,
Marcin
Please use plain text.
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 15 of 21 (645 Views)

Re: Search all CI with attachment

And of course now query in view is
logical.name isin jscall("FindAttachments.Search", "join")
Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 16 of 21 (645 Views)

Re: Search all CI with attachment

Sounds good... And you do know you can assign points to any of the posts, not just the final one...
Please use plain text.
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 17 of 21 (645 Views)

Re: Search all CI with attachment

Hi,

I didn't know. I am new on this forum. Ok, I have repaired it.

Regards,
Marcin
Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 18 of 21 (645 Views)

Re: Search all CI with attachment

Not a problem :) If you have any other questions, feel free to ask. Most folks enjoy helping (and I liked this question because I like finding interesting and reusable solutions).
Please use plain text.
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 19 of 21 (645 Views)

Re: Search all CI with attachment

Hi,

good to know. I am sure I will ask many questions in the future.

Thanks a lot once again. See you in another thread. :)

Regards,
Marcin
Please use plain text.
Trusted Contributor
oscarferreira1
Posts: 234
Registered: ‎03-12-2009
Message 20 of 21 (645 Views)

Re: Search all CI with attachment

Is the SearchConfig with jscall the best way to search in the Configuration Management data from the auxilary tables? Particular to each type of CI?

Thank you
Please use plain text.
Frequent Advisor
Marcin Wiecek
Posts: 62
Registered: ‎04-08-2010
Message 21 of 21 (645 Views)

Re: Search all CI with attachment

Hi,

This solution doesn't work for me for large databases. I have an error: "error: the where clause string exceeds 64k (cc.search,select).
How can I repair this? Help, please.

Regards,
Marcin
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation